GROUP BY做不到“既要分组又要明细”是因为其本质是降维聚合操作,执行后原始行被压缩,非分组字段值不可控,导致无法保留明细;而窗口函数如SUM() OVER(PARTITION BY x)不压缩行数,可在每行直接计算分组聚合值、排名等,无需子查询或关联,既保明细又得统计。

因为窗口函数不压缩行数,能在保留每条原始记录的同时,直接算出分组聚合值、排名、前后行差值等——不用子查询嵌套、不丢明细、不依赖用户变量。
为什么GROUP BY做不到“既要分组又要明细”
GROUP BY本质是降维操作:输入1000行,输出可能只剩10行。一旦执行了GROUP BY dept_id,非分组字段(比如employee_name)的值就不可控,MySQL 8.0默认开启ONLY_FULL_GROUP_BY,连SELECT dept_id, employee_name, AVG(salary)都会报错。Expression #2 of SELECT list is not in GROUP BY clause。想强行保留明细,只能靠关联子查询或自连接,SQL变长、执行计划变重、索引难生效。
ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 替代“取每组最新一条”
常见错误写法:SELECT user_id, order_id, MAX(created_at) FROM orders GROUP BY user_id——它不保证order_id对应的是MAX(created_at)那条记录。正确做法是用窗口函数打标再过滤:
安装、修复、更新或打包一个基于 Python 和 PyMySQL 实现的 Windows `mysql` 命令。适用于用户希望在 PowerShell/CMD 中输入 `mysql` 时重定向到 Python 实现、需要交互式输入 MySQL 连接信息、需要同步到当前用户本地 bin 目录,或需要将该能力封装成可公开复用的 Codex skill。
-
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC)为每个用户按时间倒序编号 - 外层
WHERE rn = 1拿到最新完整行(注意:必须用子查询或CTE包裹,窗口函数不能出现在WHERE中) - 若存在并列(如毫秒级重复时间),改用
RANK()或DENSE_RANK(),语义不同,别混用
SUM() OVER 和 LAG() OVER 替代关联子查询和自连接
查“每个订单的用户历史累计消费”,传统写法是关联子查询:
SELECT o1.order_id, o1.user_id, o1.amount, (SELECT SUM(o2.amount) FROM orders o2 WHERE o2.user_id = o1.user_id AND o2.created_at <= o1.created_at) AS cumsum FROM orders o1;
这种写法在10万行上可能跑14秒;而窗口函数一行搞定:
-
SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS UNBOUNDED PRECEDING)——物理行偏移累加,稳定可预期 -
LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sale_date)替代自连接查上月销售额,避免JOIN开销 - 注意:
LAG()首行返回NULL,业务逻辑里得显式判断,不能直接参与+或/运算
容易被忽略的硬性约束和性能卡点
窗口函数不是语法糖,它有明确执行时序和底层依赖:
-
ROW_NUMBER()、RANK()等必须带OVER,漏写直接报ERROR 1064,不是警告 -
OVER里没写ORDER BY?RANK()结果未定义,MySQL不报错但每次执行可能不同 - 性能瓶颈不在函数本身,而在
PARTITION BY和ORDER BY字段是否建了联合索引——EXPLAIN看到Using temporary; Using filesort就说明索引没对上 - 日期字段有重复?加二级排序保序:
ORDER BY sale_date, id,否则ROWS帧行为不可控

















