每月的累计值总和，填写缺少的月份

每月的累计值总和，填写缺少的月份

这与其他问题非常相似，但是最佳查询仍然很棘手。

基本查询 以快速获取运行总和：

SELECt to_char(date_trunc('month', date_added), 'Mon YYYY') AS mon_text     , sum(sum(qty)) OVER (ORDER BY date_trunc('month', date_added)) AS running_sumFROM   tblGROUP  BY date_trunc('month', date_added)ORDER  BY date_trunc('month', date_added);

棘手的部分是 填写缺少的月份 ：

WITH cte AS (   SELECt date_trunc('month', date_added) AS mon, sum(qty) AS mon_sum   FROM   tbl   GROUP  BY 1   )SELECt to_char(mon, 'Mon YYYY') AS mon_text     , sum(c.mon_sum) OVER (ORDER BY mon) AS running_sumFROM  (SELECt min(mon) AS min_mon FROM cte) init     , generate_series(init.min_mon, now(), interval '1 month') monLEFT   JOIN cte c USING (mon)ORDER  BY mon;

该隐含

CROSS JOINLATERAL

需要的Postgres 9.3+。从表中的第一个月开始。
要 开始一个特定的月份 ：

WITH cte AS (   SELECt date_trunc('month', date_added) AS mon, sum(qty) AS mon_sum   FROM   tbl   GROUP  BY 1   )SELECt to_char(mon, 'Mon YYYY') AS mon_text     , **COALESCE(** sum(c.mon_sum) OVER (ORDER BY mon) **, 0)** AS running_sumFROM   generate_series( **'2015-01-01'::date** , now(), interval '1 month') monLEFT   JOIN cte c USING (mon)ORDER  BY mon;

SQL提琴。

将不同年份的月份间隔数月。您并没有要求，但是您很可能会想要它。