postgresql - 计算每月运行总计

Question

假设我有这张表:

id; date; units
1; Jan 1; 1
2; Jan 2; 4
3; Feb 9; 6
4; Mar 1; 1
5; Mar 4; 2

我现在如何相应地计算“accumulated_month”列？计算应在每个新月重新开始。

id; date; units; accumulated_month
1; Jan 1; 1; 1
2; Jan 2; 4; 5
3; Feb 9; 6; 6
4; Mar 1; 1; 1
5; Mar 4; 2; 3

我设法得到的是:

id; date; units; accumulated_month
1; Jan 1; 1; 5
2; Jan 2; 4; 5
3; Feb 9; 6; 6
4; Mar 1; 1; 3
5; Mar 4; 2; 3

Answer 1

create table t (id int, date date, units int);
insert into t (id, date, units) values
(1, '2016-01-01', 1),
(2, '2016-01-02', 4),
(3, '2016-02-09', 6),
(4, '2016-03-01', 1),
(5, '2016-03-04', 2);

不清楚您是想要月总计还是月内的累计总计。本月总计:

select
    id, date, units,
    sum(units) over (partition by date_trunc('month', date)) as acumm
from t
order by 1,2
;
 id |    date    | units | acumm 
----+------------+-------+-------
  1 | 2016-01-01 |     1 |     5
  2 | 2016-01-02 |     4 |     5
  3 | 2016-02-09 |     6 |     6
  4 | 2016-03-01 |     1 |     3
  5 | 2016-03-04 |     2 |     3

如果您想要一个月内的运行总计，则向窗口函数添加一个排序依据:

select
    id, date, units,
    sum(units) over (
        partition by date_trunc('month', date)
        order by id
    ) as acumm
from t
order by 1,2
;
 id |    date    | units | acumm 
----+------------+-------+-------
  1 | 2016-01-01 |     1 |     1
  2 | 2016-01-02 |     4 |     5
  3 | 2016-02-09 |     6 |     6
  4 | 2016-03-01 |     1 |     1
  5 | 2016-03-04 |     2 |     3