排名函数：ROW_NUMBER、RANK、DENSE_RANK

三种排名函数

SQL 提供三种排名窗口函数，处理并列值的方式不同。

示例数据（考试成绩）：

CREATE TABLE scores (
    student VARCHAR(20),
    score INT
);

INSERT INTO scores VALUES
    ('Alice', 95),
    ('Bob', 90),
    ('Charlie', 90),
    ('Diana', 85),
    ('Eve', 80);

ROW_NUMBER：连续行号

ROW_NUMBER 为每行分配唯一的连续编号，即使值相同。

SELECT 
    student,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM scores;

结果：

 student | score | row_num
---------+-------+---------
 Alice   |    95 |       1
 Bob     |    90 |       2
 Charlie |    90 |       3
 Diana   |    85 |       4
 Eve     |    80 |       5

Bob 和 Charlie 分数相同，但 row_num 不同（2 和 3）。

RANK：有间隙排名

RANK 为相同值分配相同排名，下一个排名跳过中间值。

SELECT 
    student,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank
FROM scores;

结果：

 student | score | rank
---------+-------+------
 Alice   |    95 |    1
 Bob     |    90 |    2
 Charlie |    90 |    2
 Diana   |    85 |    4
 Eve     |    80 |    5

Bob 和 Charlie 并列第 2，Diana 是第 4（跳过第 3）。

DENSE_RANK：无间隙排名

DENSE_RANK 为相同值分配相同排名，下一个排名连续。

SELECT 
    student,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

结果：

 student | score | dense_rank
---------+-------+------------
 Alice   |    95 |          1
 Bob     |    90 |          2
 Charlie |    90 |          2
 Diana   |    85 |          3
 Eve     |    80 |          4

Bob 和 Charlie 并列第 2，Diana 是第 3（连续）。

三者对比

函数	并列处理	下一个排名	用途
ROW_NUMBER	不同编号	连续	唯一行号、分页
RANK	相同排名	跳跃	传统排名（奥运会）
DENSE_RANK	相同排名	连续	等级划分（A/B/C）

实际应用

应用 1：Top-N 查询

查询每个用户消费最高的 2 个订单：

WITH ranked_orders AS (
    SELECT 
        u.name,
        o.product,
        o.amount,
        ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC) AS rn
    FROM orders o
    JOIN users u ON o.user_id = u.id
)
SELECT name, product, amount
FROM ranked_orders
WHERE rn <= 2;

结果：

  name   |  product | amount
---------+----------+---------
 Alice   | Laptop   | 1200.00
 Alice   | Keyboard |   80.00
 Bob     | Headphones|   50.00
 Bob     | Mouse    |   25.00
 Charlie | Monitor  |  300.00

为什么用 ROW_NUMBER？ 确保每个用户返回恰好 2 行，即使有并列。

应用 2：去重（保留第一行）

删除重复数据，保留最早的一条：

WITH duplicates AS (
    SELECT 
        id,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM duplicates WHERE rn > 1);

应用 3：分页

相比 OFFSET，ROW_NUMBER 更适合需要稳定分页的场景：

WITH numbered AS (
    SELECT 
        product,
        amount,
        ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT product, amount
FROM numbered
WHERE rn BETWEEN 11 AND 20;  -- 第 2 页（每页 10 条）

应用 4：百分位排名

PERCENT_RANK 计算相对排名（0 到 1）：

SELECT 
    student,
    score,
    PERCENT_RANK() OVER (ORDER BY score DESC) AS percentile
FROM scores;

结果：

 student | score | percentile
---------+-------+------------
 Alice   |    95 |       0.00
 Bob     |    90 |       0.25
 Charlie |    90 |       0.25
 Diana   |    85 |       0.75
 Eve     |    80 |       1.00

分区排名

PARTITION BY 在每个分区内独立排名：

-- 每个类别内的产品排名
SELECT 
    category,
    product,
    amount,
    RANK() OVER (PARTITION BY category ORDER BY amount DESC) AS rank_in_category
FROM products;

结果：

  category   |  product | amount | rank_in_category
-------------+----------+--------+------------------
 Electronics | Laptop   | 1200   |                1
 Electronics | Monitor  |  300   |                2
 Electronics | Mouse    |   25   |                3
 Furniture   | Desk     |  500   |                1
 Furniture   | Chair    |  150   |                2

NTILE：分桶

NTILE 将数据分成 N 个大致相等的桶：

SELECT 
    student,
    score,
    NTILE(3) OVER (ORDER BY score DESC) AS quartile
FROM scores;

结果：

 student | score | quartile
---------+-------+----------
 Alice   |    95 |        1
 Bob     |    90 |        1
 Charlie |    90 |        2
 Diana   |    85 |        2
 Eve     |    80 |        3

用途：ABC 分类、四分位数分析。

常见错误

错误 1：ROW_NUMBER 不稳定排序

-- 危险：相同分数的学生顺序不确定
SELECT student, ROW_NUMBER() OVER (ORDER BY score DESC)
FROM scores;

解决：添加第二排序列确保稳定性：

SELECT student, ROW_NUMBER() OVER (ORDER BY score DESC, student)
FROM scores;

错误 2：混淆 RANK 和 DENSE_RANK

-- 期望连续排名，但用了 RANK
SELECT product, RANK() OVER (ORDER BY amount DESC)
FROM orders;
-- 可能出现 1, 2, 2, 4（跳过 3）

-- 应该用 DENSE_RANK
SELECT product, DENSE_RANK() OVER (ORDER BY amount DESC)
FROM orders;
-- 结果：1, 2, 2, 3（连续）

错误 3：WHERE 中使用窗口函数

-- 错误：WHERE 不能直接用窗口函数
SELECT * FROM orders
WHERE ROW_NUMBER() OVER (ORDER BY amount DESC) <= 10;

正确：用 CTE 或子查询：

WITH ranked AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
    FROM orders
)
SELECT * FROM ranked WHERE rn <= 10;

练习

1. 基本排名：对订单按金额降序排名，使用三种排名函数对比结果。

2. Top-N：查询每个用户最贵的订单。

3. 去重：找出每个产品的第一个订单（按日期）。

4. 分页：使用 ROW_NUMBER 实现分页，每页 3 条。

5. 分区排名：查询每个类别中最贵的 2 个产品。

6. 百分位：计算每个订单在所有订单中的百分位排名。

7. 分桶：将用户按消费总额分成高、中、低三档。

8. 实际场景：创建销售排行榜，显示产品、销量、排名（处理并列）。

完成练习后，打开本章答案页核对。

下一步

学会了排名函数。下一节学习窗口聚合：如何使用滑动窗口进行移动平均、累计求和等计算。

回到第四章概览 · 返回SQL 学习路线