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 中计算名次,再在外层按名次筛选。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用排名窗口函数

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通