按月累计值,补缺月份[英] Cumulative sum of values by month, filling in for missing months

本文是小编为大家收集整理的关于按月累计值,补缺月份的处理方法,想解了按月累计值,补缺月份的问题怎么解决?按月累计值,补缺月份问题的解决办法?那么可以参考本文帮助大家快速定位并解决问题。

问题描述

我有这个数据表,我想知道是否有可能创建一个查询,考虑到直到当前月份的所有月份..>

date_added                    | qty
------------------------------------
2015-08-04 22:28:24.633784-03 | 1
2015-05-20 20:22:29.458541-03 | 1
2015-04-08 14:16:09.844229-03 | 1
2015-04-07 23:10:42.325081-03 | 1
2015-07-06 18:50:30.164932-03 | 1
2015-08-22 15:01:54.03697-03  | 1
2015-08-06 18:25:07.57763-03  | 1
2015-04-07 23:12:20.850783-03 | 1
2015-07-23 17:45:29.456034-03 | 1
2015-04-28 20:12:48.110922-03 | 1
2015-04-28 13:26:04.770365-03 | 1
2015-05-19 13:30:08.186289-03 | 1
2015-08-06 18:26:46.448608-03 | 1
2015-08-27 16:43:06.561005-03 | 1
2015-08-07 12:15:29.242067-03 | 1

我需要这样的结果:

Jan|0
Feb|0
Mar|0
Apr|5
May|7
Jun|7
Jul|9
Aug|15

推荐答案

这与其他问题非常相似,但最好的查询仍然很棘手.

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

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_sum
FROM   tbl
GROUP  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_sum
FROM  (SELECT min(mon) AS min_mon FROM cte) init
     , generate_series(init.min_mon, now(), interval '1 month') mon
LEFT   JOIN cte c USING (mon)
ORDER  BY mon;

隐式 CROSS JOIN LATERAL 需要 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_sum
FROM   generate_series('2015-01-01'::date, now(), interval '1 month') mon
LEFT   JOIN cte c USING (mon)
ORDER  BY mon;

SQL 小提琴.

将不同年份的几个月分开.你没有要求,但你很可能会想要它.

请注意,"月份"在某种程度上取决于当前会话的时区设置!详情:

相关:

本文地址:https://www.itbaoku.cn/post/1763888.html