将递归查询从周到月转换为月到月

Question

我有一个递归查询，它提供每周下订单的数量(week_no、week_start和week_end)。我想为一个月的分析创建一个类似的细目。

WITH recursive weeks (week_start, week_end, time_end, weekno) AS (
VALUES ('2015-12-27'::date, '2016-01-02'::date, '2016-04-02'::date, 1) 
UNION ALL 
SELECT (week_end + interval '1 day')::date, 
       (CASE 
              WHEN (week_end + interval '7 days')::date > time_end THEN time_end 
              ELSE (week_end + interval '7 days')::date 
       END)::date, 
       time_end, 
       weekno+1 
FROM   weeks 
WHERE  time_end > week_end)

任何帮助都将不胜感激。

Answer 1

为什么要使用递归查询呢？使用generate_series()

select g.week_start, g.week_start + interval '6 day' as week_end,
       row_number() over (order by g.week_start) as weeknum
from generate_series('2015-12-27'::timestamp,
                     '2016-01-02'::timestamp,
                     interval '1 week'
                    ) g(week_start);

几个月的等价物如下：

select g.month_start, g.month_start + interval '1 month' - interval '1 day' as month_end,
       row_number() over (order by g.month_start) as monthnum
from generate_series('2015-12-01'::timestamp,
                     '2016-01-01'::timestamp,
                     interval '1 month'
                    ) g(month_start);