PostgreSQL缺失日期的聚合

Question

我正在尝试这个查询

SELECT date_trunc('day', commit_at) AS day, count(*)
  FROM commits
  GROUP BY date_trunc('day', commit_at)
  ORDER BY date_trunc('day', commit_at) ASC;

并且它返回

      day         | count 
---------------------+-------
2015-05-18 00:00:00 |     5
2015-05-19 00:00:00 |     2
2015-05-21 00:00:00 |     2
(3 lignes)

问题是：如何强制空的日期也在结果中显示？

      day         | count 
---------------------+-------
2015-05-18 00:00:00 |     5
2015-05-19 00:00:00 |     2
2015-05-20 00:00:00 |     0
2015-05-21 00:00:00 |     2
(3 lignes)

- m4tm4t

---

SQL 无法从空白处创建数据。您必须拥有一个带有日期的系统，或使用一种方法来创建这些日期以进行连接或联合。例如，使用递归CTE返回日期范围内的所有日期。加入此集合以获取所有日期。以下是一种方法：https://dev59.com/RGrWa4cB1Zd3GeqP7iNN，请查看@a_horse_with_no_name提供的答案或erwin的... - xQbert

好的，那么这个想法是在第一个和最后一个日期之间生成一系列日期，然后查询这些日期？我尝试过了，但是我无法让它正常工作，如果有人有例子，请分享。 - m4tm4t

请参见第一条评论中的链接，提供了示例。是的，Erwin使用generate_series函数。 - xQbert

Answer 1

一种做法是使用nu generate_series生成最小日期和最大日期之间的所有日期，然后将其加入到聚合查询中：

SELECT    DATE_TRUNC ('day', 
                      GENERATE_SERIES (MIN(commit_at), MAX(commit_at), '1 day') 
             AS day, 
          COALESCE (cnt, 0)
FROM      commits
LEFT JOIN (SELECT   DATE_TRUNC('day', commit_at) AS cday
           FROM     commits
           GROUP BY DATE_TRUNC('day', commit_at)) agg ON day = cday
ORDER BY  1 ASC

- Mureinik