sql - 从 PostgreSQL 数据库获取每月和每年的运行总计

Question

这是我的示例数据(表“sumtest”):

+-------+--------+-------+
| month |  value |  year |
+-------+--------+-------+
|     1 |     10 |  2017 |
|     2 |      0 |  2017 |
|     2 |     10 |  2016 |  # note: different year
|     2 |      5 |  2017 |
|     3 |     88 |  2017 |
|     3 |      2 |  2017 |
|     5 |      1 |  2017 |
|     5 |      4 |  2017 |
|     5 |      5 |  2017 |
+-------+--------+-------+

我想获得每个月的总值(value)，以及该特定月份每年的累计总值(value)，即我希望我的结果是这样的:

+------+-------+-----------+----------+
| year | month | sum_month | sum_year |
+------+-------+-----------+----------+
| 2016 |     2 |        10 |       10 |
| 2017 |     1 |        10 |       10 |
| 2017 |     2 |         5 |       15 |
| 2017 |     3 |        90 |      105 |
| 2017 |     5 |        10 |      115 |
+------+-------+-----------+----------+

我是 Postgres 的新手，我尝试了以下方法:

    SELECT *, sum(value) OVER (PARTITION BY month, year) AS sum_month, 
sum(value) OVER (PARTITION BY year) AS sum_year
    FROM sumtest
    ORDER BY year, month

但这会为每个原始条目生成一行，并且每行列出的年度总和而不是到目前为止的累计总和:

+-------+-------+------+-----------+----------+
| month | value | year | sum_month | sum_year |
+-------+-------+------+-----------+----------+
|     2 |    10 | 2016 | '10'      | '10'     |
|     1 |    10 | 2017 | '10'      | '115'    |
|     2 |     5 | 2017 | '5'       | '115'    |
|     2 |     0 | 2017 | '5'       | '115'    |
|     3 |     2 | 2017 | '90'      | '115'    |
|     3 |    88 | 2017 | '90'      | '115'    |
|     5 |     4 | 2017 | '10'      | '115'    |
|     5 |     1 | 2017 | '10'      | '115'    |
|     5 |     5 | 2017 | '10'      | '115'    |
+-------+-------+------+-----------+----------+

我也尝试过使用 GROUP BY，它适用于月份的累计总和，但后来我不知道如何包括年度的总计(因为这不应该按月分组)。

如有任何帮助，我们将不胜感激。

Answer 1

您可以在查询上方添加 MAX 和 GROUP BY，并在 OVER() 中添加 ORDER BY

select year,month,MAX( sum_month) sum_month,  MAX(sum_year) sum_year 
FROM
(
 SELECT *, sum(value) OVER (PARTITION BY month, year ORDER BY year,month) AS sum_month, 
sum(value) OVER (PARTITION BY year ORDER BY year,month) AS sum_year
    FROM sumtest
)  a
GROUP BY year,month;

DEMO