我想从当前年份的下一个月(例如,start_month)到12个月(从start_month)生成一系列月份和年份,并从PostgreSQL的另一个表中生成相应的数据(如果有,则返回空值)。

SELECT ( ( DATE '2019-03-01' + ( interval '1' month * generate_series(0, 11) ) )
         ::  DATE ) dd,
       extract(year FROM ( DATE '2019-03-01' + ( interval '1' month *
                                                 generate_series(0, 11) )
                         )),
       coalesce(SUM(price), 0)
FROM   items
WHERE  s.date_added >= '2019-03-01'
       AND s.date_added < '2020-03-01'
       AND item_type_id = 3
GROUP  BY 1,
          2
ORDER  BY 2;

上面的查询的问题是,它为price提供了所有月份的相同值。要求是,如果给定月份没有可用的price数据,price列必须用空或零填充。

最佳答案

generate_series()放入FROM子句中。你在总结数据——即计算整个范围内的价格——然后将其预测到所有月份。相反:

SELECT gs.yyyymm,
       coalesce(SUM(i.price), 0)
FROM generate_series('2019-03-01'::date, '2020-02-01', INTERVAL '1 MONTH'
                    ) gs(yyyymm) LEFT JOIN
     items i
     ON gs.yyyymm = DATE_TRUNC('month', s.date_added) AND
       i.item_type_id = 3
GROUP BY gs.yyyymm
ORDER BY gs.yyyymm;

09-10 09:53
查看更多