每月的累计值总和，填写缺少的月份 [英] Cumulative sum of values by month, filling in for missing months

问题描述

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

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;

隐式交叉加入横向 需要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小提琴。

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

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:

• 完全忽略Rails和PostgreSQL中的时区

• Ignoring timezones altogether in Rails and PostgreSQL

相关：

• 计算累积和PostgreSQL


• PostgreSQL：按分钟运行查询的行数


• Postgres窗口函数和按例外分组

• Calculating Cumulative Sum in PostgreSQL
• PostgreSQL: running count of rows for a query 'by minute'
• Postgres window function and group by exception

这篇关于每月的累计值总和，填写缺少的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！