sql - PostgreSQL 根据表字段生成月份和年份序列，如果给定月份没有数据，则用空值填充

Question

我想生成从当年的下个月(例如，start_month)到 start_month 的 12 个月的月份和年份系列以及相应的数据(如果any, else return 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;