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

本文介绍了每月的累计值总和，填写缺少的月份的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有此数据表，我想知道是否有可能创建一个查询，该查询考虑了 直到当前月份的所有月份 的每月累积总数。

I have this data table and I'm wondering if is possible create a query that get a cumulative sum by month considering all months until the current month.

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

推荐答案

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

This is very similar to other questions, but the best query is still tricky.

基本查询可快速获得运行总和：

Basic query to get the running sum quickly:

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;

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

Keeping months from different years apart. You did not ask for that, but you'll most likely want it.

请注意，月份在某种程度上取决于当前会话的时区设置！详细信息：

Note that the "month" to some degree depends on the time zone setting of the current session! Details:

Ignoring timezones altogether in Rails and PostgreSQL