每周Postgres条目，每周

Question

我想返回每个日历周的每周条目数。我当前的代码：

SELECT COUNT(*) AS count, date_trunc('week', occured_on) AS week
from incidents
GROUP BY date_trunc('week', occured_on)
ORDER BY date_trunc('week', occured_on);

这将返回：

count  |        week         
-------+---------------------
     1 | 2009-09-28 00:00:00
     2 | 2009-10-19 00:00:00
     6 | 2009-10-26 00:00:00
     3 | 2009-11-02 00:00:00
     6 | 2009-11-09 00:00:00
    22 | 2009-11-16 00:00:00

跳过未发生任何事件的一周。如何获得该周的计数0，从而显示每个日历周的条目总数，而不是每周发生的事件？

Answer 1

您可以通过生成一系列周，然后在聚合之前使用left join来完成此操作。下面的代码使用CTE生成序列。这不是必需的，但我认为它显示了所使用的逻辑：

with dates as (
      select min(date_trunc('week', occured_on)) as startw,
             max(date_trunc('week', occured_on)) as endw
      from incidents
     ),
     weeks as (
      select generate_series(startw, endw, '7 days') as week
      from dates
     )
select w.week, count(i.occured_on)
from weeks w left outer join
     incidents i
     on date_trunc('week', i.occured_on) = w.week
group by w.week;

SQL Fiddle是here。

Answer 2

SELECT  my_weeks.week_number  AS week , IFNULL(i.count,0)
from 
(
 SELECT generate_series (1,54,1) AS week_number
) my_weeks
LEFT OUTER JOIN 
(
  SELECT COUNT(*) AS count, date_trunc('week', occured_on) AS week
  from incidents
  GROUP BY date_trunc('week', occured_on)
) i
ON my_weeks.week_number = i.week
ORDER BY my_weeks.week_number ;