SQL统计分组内累计增长值_利用窗口函数优化实现

陆砚码

2026-09-30 • 👁 3人浏览过

累计增长值等于当前行值减去组内首行值后的差值再累计求和，正确写法是SUM(value - FIRST_VALUE(value) OVER(PARTITION BY group_col ORDER BY time_col)) OVER(PARTITION BY group_col ORDER BY time_col)。

怎么用

ROW_NUMBER()

和

SUM() OVER()

算分组内累计增长直接说结论：累计增长值 ≠ 累计求和，它得是「当前行值减去组内首行值」，再叠加成累计。很多人一上来就写

SUM(value) OVER(PARTITION BY group_col ORDER BY time_col)

，结果算出来是累计和，不是累计增长。正确做法是先用

FIRST_VALUE()

拿到每组第一行的基准值，再用当前行值减它，最后套一层

SUM() OVER()

累加差值：

SELECT
  group_col,
  time_col,
  value,
  SUM(value - FIRST_VALUE(value) OVER(PARTITION BY group_col ORDER BY time_col))
    OVER(PARTITION BY group_col ORDER BY time_col) AS cum_growth
FROM t;

FIRST_VALUE(value) OVER(...)

必须带

ORDER BY

，否则窗口默认是

UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING

，取不到“首行” 两层

OVER

嵌套没问题，但外层必须和内层

PARTITION BY

一致，否则基准值会错乱如果时间字段有重复，

ORDER BY time_col

可能导致

FIRST_VALUE()

结果不稳定，建议加个唯一排序键（比如

id

）防歧义为什么不能用

LAG()

逐行相减再累加有人想先算每行相比上一行的增长（

value - LAG(value)

），再对这个差值做累计和。逻辑看似通，但实际漏掉了起点偏移——累计增长是从组内第一行开始算“涨了多少”，不是“每步涨多少的累加”。比如组内值是

[100, 120, 110, 130]

，逐行差值是

[NULL, 20, -10, 20]

，累计差值变成

[NULL, 20, 10, 30]

，而真正的累计增长应是

[0, 20, 10, 30]

—— 第一行必须为 0。

LAG()

无法天然处理首行，得额外用

CASE WHEN ROW_NUMBER() = 1 THEN 0 ELSE ... END

补零，代码变冗长一旦中间有

NULL

值，

LAG()

NULL

会导致整条链断裂，而

FIRST_VALUE()

在

IGNORE NULLS

支持下更可控（注意：PostgreSQL 不支持该修饰符，MySQL 8.0+ 和 SQL Server 支持）性能上，单次窗口扫描 + 一次聚合比两次独立窗口函数（

LAG

+ 外层

SUM

）更优，尤其数据量大时

ORDER BY

顺序错了会怎样窗口函数里

ORDER BY

决定“累计”的方向和范围，错一点，结果全偏。最常见的是把时间字段写成

DESC

，结果累计增长从最新往最旧倒着加，业务上完全反了。确保

ORDER BY

和业务时间轴一致，比如按日期升序，不是按 ID 升序（除非 ID 严格递增且代表时序）如果排序字段有重复，不同数据库行为不一：MySQL 8.0 默认稳定排序，PostgreSQL 可能打乱相同键的行序，导致

FIRST_VALUE()

随机取一个“首行” 测试时务必查几组完整数据，看第一行

cum_growth

是否全为 0，如果不是，大概率是

ORDER BY

或

PARTITION BY

分组粒度不对兼容性陷阱：不同数据库对

FIRST_VALUE()

的处理差异核心问题不在语法，而在默认行为。MySQL 8.0+、SQL Server、Oracle 都支持

FIRST_VALUE(value) OVER(...)

，但 PostgreSQL 默认不支持，得用

first_value(value) OVER(...)

（小写函数名），且不支持

IGNORE NULLS

。 PostgreSQL 用户若要跳过

NULL

基准值，得先用子查询过滤或用

COALESCE(FIRST_VALUE(...), ...)

回退，但回退值难保证是真正首非空值 SQLite 目前（3.45+）仍不支持

FIRST_VALUE()

，只能用关联子查询模拟，性能差很多如果目标库不确定，优先用

MIN(value) OVER(PARTITION BY group_col ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

替代？不行——

MIN

是找最小值，不是首值，语义完全不同真要跨库兼容，最稳的方式是先用主键或时间戳排序后取

ROW_NUMBER()

，再自连接找每组

rn = 1

的行作为基准，虽然多一步，但逻辑清晰、各库都能跑通。

相关文章