PostgreSQL中如何用SUM OVER实现按10月起的周期重置累计求和

核心实现思路 #

要实现「每年10月到次年9月为一个周期、周期内逐月累加、10月自动重置累计值」的需求，不需要写复杂的递归查询，只需要通过窗口函数的自定义分区规则就能完成，核心是先为每条记录计算出它所属的统计周期唯一标识，再按这个标识做分区累计即可。
周期标识的计算规则：

• 如果当月是10-12月，属于从「当年10月」开始的统计周期，用当前年份作为周期标识
• 如果当月是1-9月，属于从「前一年10月」开始的统计周期，用当前年份减1作为周期标识
  按照这个规则，2017年10月-2018年9月的所有记录，都会被打上2017的周期标识；2018年10月-2019年9月的记录，都会被打上2018的周期标识，完全匹配要求的统计周期划分。

可直接运行的SQL实现 #

SELECT
  year,
  month,
  count,
  SUM(count) OVER (
    -- 按自定义统计周期做分区，同周期的记录才会参与累计
    PARTITION BY CASE WHEN month >= 10 THEN year ELSE year - 1 END
    -- 按自然月份顺序排序，保证累计方向正确
    ORDER BY make_date(year, month, 1)
    -- 从分区内第一行开始累计到当前行
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS count_sum_ytd
FROM 你的实际数据表名
-- 最终输出按时间顺序排列
ORDER BY make_date(year, month, 1);

结果匹配验证 #

对照给出的示例输出，这个实现的计算逻辑完全一致：

• 表中最早的2017年4-9月记录，因为月份都小于10，会被打上2016的周期标识，分区内没有更早的历史数据，所以累计值从2017年4月的1开始逐月累加，到9月累计到22，和示例完全匹配
• 2017年10月的记录因为月份>=10，分区标识变为2017，累计值自动重置为当月的4，11月的累计值为4+3=7，和示例输出完全一致
• 后续到2018年10月时，分区标识再次从2017切换为2018，累计值自动重置，进入下一个统计周期的累计流程

注意事项 #

• 排序时不要直接拼接年份和月份字符串（比如year || month）作为排序依据，用make_date()生成标准日期类型的月份起点排序，能避免跨年份排序、字符格式异常等问题
• 即使中间存在缺失的月份数据，这个逻辑依然可以正常工作，累计顺序会严格按照表内实际存在的月份时间先后计算，不会出现断档
• 不需要额外写判断是否重置累计值的条件，窗口函数的分区逻辑本身就会在不同分区的边界自动重新开始累计，比手动写变量判断的写法更简洁、更适配PostgreSQL的执行计划

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