SQL窗口函数分组排名与累计计算
PixelQuest
9 分钟阅读
窗口函数把"分组内排序、排名、累计"从多层子查询里解放出来——它不折叠行，而是给每一行附上同组的计算结果，这是它和普通 GROUP BY 最根本的区别。过去要算"每个部门薪资排名""每个用户累计消费""同比环比",动辄自连接加子查询，可读性差且易错；窗口函数一行 OVER 就说清了。
窗口函数的基本形态
函数() OVER (PARTITION BY 分组 ORDER BY 排序) 定义一个"窗口"。PARTITION BY 决定哪些行同组，ORDER BY 决定组内顺序，结果行数不变：
SELECT
dept,
name,
salary,
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
FROM emp;
每个员工都保留一行，多出一列 rn 表示"在本部门按薪资降序的排名"。同样的数据用 GROUP BY 只能留一行，窗口函数则完整保留明细，又能同时看到组内位置。这是报表类查询最大的便利：既要明细，又要组内统计。
三种排名函数的差别
ROW_NUMBER 严格给唯一连续序号；RANK 并列同号、后续跳号；DENSE_RANK 并列同号、后续不跳号：
SELECT
name, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS r1,
RANK() OVER (ORDER BY score DESC) AS r2,
DENSE_RANK() OVER (ORDER BY score DESC) AS r3
FROM exam;
分数 100,100,90 会得到 r1=1,2,3；r2=1,1,3；r3=1,1,2。取"前 N 名不漏人"用 DENSE_RANK，取"第几名"用 ROW_NUMBER，二者需求别混。RANK 的跳号在"取第 3 名"这类语义下会漏掉并列，要小心。并列时 ROW_NUMBER 给的序号是确定的但不稳定——同分谁排前依赖数据库的实现与 tie-break，需要再加一列做确定排序。
累计与滑动聚合
SUM 配合 ORDER BY 在窗口内做"到当前行为止的累计"；ROWS BETWEEN 限定滑动范围：
SELECT
day,
amount,
SUM(amount) OVER (ORDER BY day) AS running_total,
SUM(amount) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS last3_total
FROM sales;
running_total 是逐行累加；last3_total 只看当前行加前 2 行。把 ROWS 换成 RANGE 则按值的范围（如同一天合并）而非物理行数框定窗口。RANGE 在按时间分组时更贴近业务语义，但要注意边界包含规则。AVG、MIN、MAX、COUNT 同样可用在窗口里做滑动统计，做移动平均、近 N 日最高值都不必子查询。
取组内对照值
FIRST_VALUE、LAG 等能在同组里横向取值，省掉自连接：
SELECT
dept, name, salary,
LAG(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS prev_salary,
FIRST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_salary
FROM emp;
LAG 取上一行（前一名薪资），FIRST_VALUE 取组内最高薪资。算"我和第一名的差距"一行搞定，不必再 join 一次自己。LEAD 则取下一行，做"下一条记录的时间差"很方便。注意窗口边界外的 LAG/LEAD 默认返回 NULL，要用第三个参数给默认值。
边界与坑
没有 ORDER BY 的窗口函数，窗口覆盖整组，排名/累计会失去确定意义——ROW_NUMBER 在无 ORDER BY 时顺序由实现决定，不可依赖。需要"整组一个值"（如占比的分母）才故意省掉 ORDER BY，例如算每行占部门总额的比例：
SELECT name, salary,
salary * 1.0 / SUM(salary) OVER (PARTITION BY dept) AS pct
FROM emp;
PARTITION BY 与 GROUP BY 可共存于一条语句：先 GROUP BY 聚合出粗粒度，再在 SELECT 里用窗口函数基于聚合结果做排名，二者层次不同——GROUP BY 决定行被压缩到哪一层，窗口函数在压缩后的行上再做组内计算。
窗口函数执行在 WHERE、GROUP BY、HAVING 之后、ORDER BY 之前。这意味着不能在 WHERE 里直接过滤窗口函数的结果，需要再包一层子查询或 CTE：
SELECT * FROM (
SELECT name, salary,
RANK() OVER (ORDER BY salary DESC) AS r
FROM emp
) t WHERE r <= 3;
NULL 参与排序时默认排最前（可用 NULLS LAST 调整），累计求和里 NULL 被 SUM 忽略但会打乱序号连续性，先用 COALESCE 处理更稳。跨数据库语义差异也要留心：NULLS LAST、某些聚合的窗口默认框架在不同引擎上不完全一致，写跨库 SQL 前确认目标引擎。老版本数据库可能缺失窗口函数，需要退回相关子查询或变量兜底。
窗口函数把"分组内有序计算"这件事变得可读且高效。原本要自连接、变量、多层嵌套才能表达的排名与累计，现在一行 OVER 就能说清——它不减少数据行，却大幅减少了你为了描述计算而写的行数，也减少了因嵌套过深而写错逻辑的机会。
报表中的组合用法
窗口函数常叠在同一select里各管一摊。例如既算每个用户的累计消费、又算该用户消费占全站的比例、再算在省份内的排名：
SELECT
user_id, province, amount,
SUM(amount) OVER (PARTITION BY user_id ORDER BY day) AS user_running,
amount * 1.0 / SUM(amount) OVER () AS global_pct,
RANK() OVER (PARTITION BY province ORDER BY amount DESC) AS prov_rank
FROM orders;
三条窗口各用不同的 PARTITION 与框架，互不干扰，却在一行结果里同时给出"个人进度、全局占比、省内名次"。这种写法替代了过去要 join 三张聚合子表的笨办法。
NTILE(n) 能把组内行等分桶，做"按分数切前 25%"之类的分位：NTILE(4) OVER (ORDER BY score DESC) 给每行打 1–4 的象限。注意桶大小不均时它会把余数摊到前面的桶，分位边界不是整数时要看清语义。
窗口函数与 CTE 配合最清爽：先把脏数据过滤、聚合到合适粒度，再在 CTE 外层用窗口算排名与累计，逻辑分层、可读、易调。把窗口计算塞进十几层嵌套子查询是反模式，既难读又难让优化器选对计划。
最后提醒性能：窗口函数虽快，但 PARTITION BY 列若无索引、或 ORDER BY 引发大规模排序，仍可能成为慢查询。对大表先确认分组列有索引、或先缩小数据范围再开窗，别把它当成免费用的无成本操作。
浏览 12
点赞 0
评论 0