Postgresql - Group By

Question

我有一个简单的分组场景。以下是查询的输出。

查询如下： select target_date, type, count(*) from table_name group by target_date, type 查询和输出结果完全正确。 我的问题是我在Grafana中使用它进行绘图。这是带有postgres后端的Grafana。 问题在于，由于“type2”类别在2020年01月10日和2020年03月10日缺失，“type2”类别根本没有被绘制（并排条形图）。尽管“type2”存在于其他日期。 它希望得到像这样的东西：

所以每当一个类别在日期上被遗漏时，我们需要一个值为0的计数。 需要在查询中处理此问题，因为源数据无法修改。 非常感谢任何帮助。

- Learner

---

“WHERE t3.weekday = 'N'” 将您的外连接转换为内连接，因为在外连接的行中，“t3.weekday” 为空。将其更改为“AND t3.weekday = 'N'”，使其成为“ON”子句的一部分。 - Thorsten Kettner

Answer 1

您可以在这里使用日历表的方法：

SELECT
    t1.target_date,
    t2.type,
    COUNT(t3.target_date) AS count
FROM (SELECT DISTINCT target_date FROM yourTable) t1
CROSS JOIN (SELECT DISTINCT type FROM yourTable) t2
LEFT JOIN yourTable t3
    ON t3.target_date = t1.target_date AND
       t3.type = t2.type
GROUP BY
    t1.target_date,
    t2.type
ORDER BY
    t1.target_date,
    t2.type;

这里的想法是交叉连接子查询，找到所有不同的目标日期和类型，以生成查询的起点。然后，我们将这个中间表与实际表进行左连接，并查找每个日期和类型的计数。

- Tim Biegeleisen

---

修改了问题并添加了条件。已经给出了我使用的查询和相应的输出。您能帮忙如何向左连接或主表添加额外条件吗？ - Learner

我已经撤销了你的问题。只需将WHERE条件移至最后一个连接的ON子句中即可。请不要在许多人已经回答问题之后更改您的问题。这不符合Stack Overflow的格式。 - Tim Biegeleisen

Answer 2

您需要创建一个包括所有 target_date/type 组合的列表。可以通过两个 DISTINCT 查询 target_date 和 type 的 CROSS JOIN 完成。然后，将此列表与 table_name 进行 LEFT JOIN，以获取每个组合的计数：

SELECT dates.target_date, types.type, COUNT(t.target_date)
FROM (
  SELECT DISTINCT target_date
  FROM table_name
) dates
CROSS JOIN (
  SELECT DISTINCT type
  FROM table_name
) types
LEFT JOIN table_name t ON t.target_date = dates.target_date AND t.type = types.type
GROUP BY dates.target_date, types.type
ORDER BY dates.target_date, types.type

在 dbfiddle 上的演示

- Nick

---

非常感谢。假设我想在左连接的表table_name上添加条件检查，即where weekday="N" [选择target_date，type，count（*）from table_name group by target_date，type where weekday="N"] 我尝试在联接后添加它，但结果是错误的。这里可以帮忙吗？ - Learner

你需要使用t.weekday = 'N'，请注意你需要一个表别名，并且应该在n周围使用单引号而不是双引号。 - Nick

是的，我这样做了。使用了这个查询更新了问题。但结果缺少了之前的类别。 - Learner

@Learner 你需要在Nick的查询中最后一个左连接的ON子句中添加条件t.weekday = 'N'。问题在于，如果你将weekday的限制放入WHERE子句中，可能会丢失一些你想要保留的记录。 - Tim Biegeleisen

@ Learner，就像Tim所说的那样，只需要将LEFT JOIN table_name t ON t.target_date = dates.target_date AND t.type = types.type更改为LEFT JOIN table_name t ON t.target_date = dates.target_date AND t.type = types.type AND t.weekday = 'N'，你就能得到想要的结果了。 - Nick

Answer 3

select t.target_date, tmp.type, sum(case when t.type = tmp.type then 1 else 0 end) 
from your_table t
cross join (select distinct type from your_table) tmp
group by t.target_date, tmp.type

演示

- juergen d