我想从当前年份的下一个月(例如,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;