Oracle如何实现按年度聚合结果 新增列计算年度累计值

核心逻辑 #

和月度累计逻辑完全一致，仅需将聚合维度从「单个月度日期」调整为「年份」，分两层计算即可：

1. 第一层：按年份分组，汇总每个自然年的period总和
2. 第二层：对按年聚合后的结果，使用窗口函数按年份升序累加，得到跨年度累计值

实现SQL #

SELECT
  year AS date,
  year_period AS period,
  SUM(year_period) OVER(ORDER BY year) AS cumulative
FROM (
  -- 子查询：按年聚合计算每年的period总和
  SELECT
    TO_CHAR(date, 'yyyy') AS year,
    SUM(period) AS year_period
  FROM 你的实际业务表名 -- 替换为存储月度数据的真实表名
  GROUP BY TO_CHAR(date, 'yyyy')
)
ORDER BY year

结果校验 #

上述SQL执行后返回的结果和预期完全匹配：

date  period  cumulative
2019  8       8
2020  10      18
2021  4       22

对应计算逻辑：

• 2019年period总和：1+2+4+1+0=8，累计值为8
• 2020年period总和：10+0+0=10，累计值为8+10=18
• 2021年period总和：1+1+1+1=4，累计值为18+4=22

可选优化 #

如果担心字符串类型的年份排序出现异常，可以将年份提取为数字类型，计算结果完全相同：

SELECT
  year AS date,
  year_period AS period,
  SUM(year_period) OVER(ORDER BY year) AS cumulative
FROM (
  SELECT
    EXTRACT(YEAR FROM date) AS year, -- 提取数字类型的年份
    SUM(period) AS year_period
  FROM 你的实际业务表名
  GROUP BY EXTRACT(YEAR FROM date)
)
ORDER BY year

内容的提问来源于stack exchange，提问作者Мариян Цветков