SQL如何实现复杂的分组统计需求_窗口函数全场景解析-SQL-PHP中文网

窗口函数不可替代，因其在保留原始行基础上计算统计值，而group by会压缩行数丢失明细；常见错误是混用聚合列与非分组列，需用子查询+join绕过，但性能差、可读性低。

sql如何实现复杂的分组统计需求_窗口函数全场景解析

因为 GROUP BY 会压缩行数，一聚合就丢原始数据；而窗口函数在保留每行基础上加统计值——比如“每个订单金额占所属用户总额的百分比”，这种既要看局部又要看全局的场景，GROUP BY 根本写不出来。

常见错误现象：SELECT user_id, amount, SUM(amount) FROM orders GROUP BY user_id 报错或结果错乱，本质是混用了聚合列和非分组列；想绕开只能嵌套子查询+JOIN，可读性差、性能低、改起来像拆炸弹。

使用场景：排名（前3名/并列排名）、移动平均、同比环比、累计求和、分组内Top N
关键区别：OVER() 里写什么，决定统计范围——空括号=全表，PARTITION BY user_id=按用户分组，ORDER BY create_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW=从头累加到当前行
兼容性注意：MySQL 8.0+、PostgreSQL、SQL Server 2012+、Oracle 都支持；但 SQLite 默认不支持（需编译时开启），老版本 MySQL 只能靠变量模拟，极不稳定

三者都干排名，但对相同值的处理逻辑完全不同，选错直接导致业务逻辑出错——比如发奖金时把并列第2名算成第3名，用户会找运维吵架。

示例：数据 [100, 100, 90] 排降序

性能影响：三者底层实现几乎无差别，但 ORDER BY 字段如果有 NULL 值，默认排最前，可能打乱业务预期；显式加 NULLS LAST（PostgreSQL/Oracle）或用 COALESCE 处理更稳妥。

核心原因：LAG() 和 LEAD() 默认取相邻行，但时间维度往往不连续——比如某用户3月没下单，LAG(amount, 1) OVER (PARTITION BY user_id ORDER BY month) 就会跨到1月，中间缺2月数据，计算出来的“环比”毫无意义。

正确做法：先用 GENERATE_SERIES（PostgreSQL）或递归 CTE 补齐时间维度，再 LEFT JOIN 原表，最后用窗口函数
MySQL 8.0+ 可用 LAG(amount) OVER (PARTITION BY user_id ORDER BY month RANGE BETWEEN INTERVAL '1' MONTH PRECEDING AND INTERVAL '1' MONTH PRECEDING)，但注意 RANGE 对时间字段的支持有限，不如显式补全可靠
容易踩的坑：LAG(amount, 1) 第二个参数是偏移量，不是“上个月”，别硬凑日期运算；另外 ORDER BY 必须唯一，否则同月多笔订单会导致 LAG 结果随机

因为窗口函数执行顺序在 WHERE 之后、ORDER BY 之前，所以 WHERE rank 会报错 “invalid reference to FROM-clause entry”。

示例：

SELECT * FROM (
  SELECT user_id, amount,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn
  FROM orders
) t WHERE rn

性能提示：如果原表很大，先 WHERE 过滤无关数据（比如只查近30天），再进窗口计算，别让窗口函数扫全表
替代思路：某些场景可用 QUALIFY（BigQuery / Snowflake / PostgreSQL 14+），一行解决：SELECT user_id, amount FROM orders QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) ，但别指望它在 MySQL 或旧版 PG 里能跑

复杂点在于：窗口函数的 PARTITION BY 和 ORDER BY 一旦涉及多字段或表达式，执行计划容易退化；实际写之前，先 EXPLAIN 看是否走了索引，别光盯着语法对不对。