PostgreSQL基于表字段生成月和年度系列，如果给定月份没有数据，则填充零

Question

我想生成从start_month的下一个月（例如start_month）到12个月的一个月和年度系列，以及相应的数据（如果有的话，否则，返回nulls）从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列有空或零。

Answer 1

将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; 

Answer 2

您想要FROM子句中的generate_series并加入它，有点像

SELECT months.m::date, ...
FROM generate_series(
        start_month,
        start_month + INTERVAL '11 months',
        INTERVAL '1 month'
     ) AS months(m)
   LEFT JOIN items
      ON months.m::date = items.date_added