PostgreSQL / 系统课时
使用排名窗口函数
使用排名窗口函数
本课解决“同一分组内如何排序并保留明细行”的问题。完成后,你可以用 row_number、rank 和 dense_rank 为每个部门独立编号,并正确处理并列成绩。
学习目标
- 理解三种排名函数对并列值的处理差异
- 使用
PARTITION BY在分组内独立排名 - 为稳定排序补充明确的并列规则
排名窗口函数解决什么问题
普通聚合会把多行压缩成一行,窗口函数则在保留每条明细的同时计算排名。基本形式是在函数后添加 OVER,其中 PARTITION BY 决定“每组重新开始”,ORDER BY 决定组内顺序。三种函数的核心区别如下:
| 函数 | 并列时是否同名次 | 后续名次是否跳号 |
|---|---|---|
row_number() |
否,每行唯一 | 不涉及 |
rank() |
是 | 是 |
dense_rank() |
是 | 否 |
完整示例:按部门为成绩排名
下面的 SQL 创建临时表、写入测试数据并一次性计算三种排名。临时表只在当前数据库会话中存在,适合直接练习。
sql
DROP TABLE IF EXISTS sales_score;
CREATE TEMP TABLE sales_score (
department text NOT NULL,
employee text NOT NULL,
score integer NOT NULL
);
INSERT INTO sales_score (department, employee, score) VALUES
('研发', '阿杰', 95),
('研发', '小林', 90),
('研发', '小周', 90),
('研发', '小吴', 80),
('销售', '小陈', 88),
('销售', '小郑', 88),
('销售', '小赵', 75);
SELECT
department,
employee,
score,
row_number() OVER (
PARTITION BY department
ORDER BY score DESC, employee
) AS row_no,
rank() OVER (
PARTITION BY department
ORDER BY score DESC
) AS rank_no,
dense_rank() OVER (
PARTITION BY department
ORDER BY score DESC
) AS dense_rank_no
FROM sales_score
ORDER BY department, score DESC, employee;预期结果
text
department | employee | score | row_no | rank_no | dense_rank_no
-----------+----------+-------+--------+---------+--------------
研发 | 阿杰 | 95 | 1 | 1 | 1
研发 | 小林 | 90 | 2 | 2 | 2
研发 | 小周 | 90 | 3 | 2 | 2
研发 | 小吴 | 80 | 4 | 4 | 3
销售 | 小陈 | 88 | 1 | 1 | 1
销售 | 小郑 | 88 | 2 | 1 | 1
销售 | 小赵 | 75 | 3 | 3 | 2研发组中两个 90 分并列。row_number() 仍分配 2 和 3;rank() 都为 2,下一名跳到 4;dense_rank() 都为 2,下一名连续为 3。销售组会重新从 1 开始,因为窗口按部门分区。
如何选择函数
需要“每行唯一序号”时使用 row_number(),常见于每组取最新一条记录。需要比赛式名次时使用 rank();需要连续等级时使用 dense_rank()。
使用 row_number() 时,若排序列可能重复,应增加稳定的次级排序字段。例如示例用员工名打破同分;生产环境通常更适合使用唯一主键。否则并列行的先后顺序可能随执行计划变化。
窗口函数的结果不能直接在同一层查询的 WHERE 中过滤。要取每组前两名,应先在子查询或 CTE 中计算名次,再在外层按名次筛选。
按自己的节奏,确认真正理解。