如何从PostgreSQL数据库按年月计算月度合计与年度累计值

嘿，作为PostgreSQL新手，你遇到的这个问题其实很常见——想要同时拿到月度汇总和截至当月的年度累计，咱们一步步来搞定它！

首先先拆解你之前尝试的问题：

• 直接用窗口函数sum(value) OVER (PARTITION BY year)时，因为没指定排序逻辑，它会直接计算整个年份的总价值，而不是截至当前月的累计值；而且没有做分组去重，所以会保留原表的每一行数据。
• 用GROUP BY year, month能正确算出月度合计，但不知道怎么在这个基础上叠加年度累计，对吧？

正确的解决方案 #

我们可以分两步走：先算出每个月的合计值，再基于这个结果计算年度累计值。

第一步：计算月度合计

先通过分组得到每个year+month的总价值，这一步会自动去重，只保留每个月的汇总行：

SELECT year, month, SUM(value) AS sum_month
FROM sumtest
GROUP BY year, month
ORDER BY year, month;

第二步：叠加年度累计

在上面的月度汇总结果基础上，用带排序规则的窗口函数来计算年度累计。窗口函数里指定PARTITION BY year（只在同一年份内累加），再加上ORDER BY month（按月份顺序累加），这样就能得到截至当月的年度总和：

SELECT 
    year,
    month,
    sum_month,
    SUM(sum_month) OVER (PARTITION BY year ORDER BY month) AS sum_year
FROM (
    -- 子查询先得到月度合计
    SELECT year, month, SUM(value) AS sum_month
    FROM sumtest
    GROUP BY year, month
) AS monthly_sums
ORDER BY year, month;

结果验证 #

执行这个SQL后，你会得到和你期望完全一致的结果：

+------+-------+-----------+----------+
| 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      |
+------+-------+-----------+----------+

补充说明 #

窗口函数SUM(sum_month) OVER (PARTITION BY year ORDER BY month)的核心逻辑：

• PARTITION BY year：把数据按年份拆分成独立的组，每个组内单独计算累计值
• ORDER BY month：确保在每个年份组内，按月份从小到大的顺序累加，这样每个月的sum_year就是从当年第一个月到当前月的总和

内容的提问来源于stack exchange，提问作者canavanin