SQL 按周分组包括计数为 0 的周

Question

我有一个按周对结果进行分组的查询。然而，有几个星期总数为 0，这使我的图表出现偏差。是否可以在有 0 条评论的周内添加 0？

SELECT COUNT(*) AS total, 
DATE_TRUNC('week', created_at) AS week FROM "reviews" 
GROUP BY DATE_TRUNC('week', created_at) 
ORDER BY DATE_TRUNC('week', created_at)

Answer 1

使用generate_series():

SELECT w.week, COUNT(*) AS total
FROM (SELECT generate_series(min(DATE_TRUNC('week', created_at)),
                             max(DATE_TRUNC('week', created_at)),
                             interval '7 day'
                            ) as week
      FROM reviews
     ) w LEFT JOIN
     "reviews" 
     ON DATE_TRUNC('week', created_at) = w.week
GROUP BY w.week
ORDER BY w.week;