课程 7.2 · 阅读时间：约 9 分钟

本课程重点介绍排名函数 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE。您将学习每个函数在值相同的情况下的行为以及在实践中何时使用它。课程结束时，您将能够为报告、前 N 列表和客户细分构建准确的排名。

使用 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE

在上一课中，我们介绍了窗口函数并探讨了 ROW_NUMBER()。现在我们将深入了解 SQL 提供的排名函数家族：ROW_NUMBER、RANK、DENSE_RANK 和 NTILE。每个函数都有其独特的目的，理解何时使用每一个函数对于有效的数据分析至关重要。

理解差异

这四个函数都根据排序为行分配数值，但它们处理平局（相等值）的方式不同。让我们逐一探讨。

ROW_NUMBER(): 唯一的顺序编号

ROW_NUMBER() 为每一行分配一个唯一的顺序编号，即使值相同。它将平局视为不同的行。

语法：

ROW_NUMBER() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

示例：排名交易

SELECT
    customer_id,
    amount,
    payment_date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS payment_rank
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_rank;

输出示例：

customer_id | amount | payment_date | payment_rank
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 2
1           | 10.99  | 2005-06-19   | 3
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

关键点： 尽管客户 1 的前两个支付金额相同（11.99），但它们获得了不同的行号（1 和 2）。

RANK(): 带间隙的排名

RANK() 为具有相同排序值的行分配相同的排名，但在编号序列中留下间隙。如果两行并列排名 1，则下一个排名为 3（跳过 2）。

语法：

RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

示例：按金额排名支付

SELECT
    customer_id,
    amount,
    payment_date,
    RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS payment_rank
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_rank;

输出示例：

customer_id | amount | payment_date | payment_rank
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 1
1           | 10.99  | 2005-06-19   | 3
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

关键点： 客户 1 的两个支付 11.99 都获得了排名 1，下一笔支付获得排名 3（而不是 2）。这在您想要识别平局但在整个数据集中保留排名位置时非常有用。

DENSE_RANK(): 无间隙的排名

DENSE_RANK() 类似于 RANK()，但不跳过数字。如果两行并列排名 1，则下一个排名为 2（而不是 3）。

语法：

DENSE_RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

示例：密集排名支付金额

SELECT
    customer_id,
    amount,
    payment_date,
    DENSE_RANK() OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS payment_rank
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    payment_rank;

输出示例：

customer_id | amount | payment_date | payment_rank
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 1
1           | 10.99  | 2005-06-19   | 2
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

关键点： 客户 1 的两个支付 11.99 都获得排名 1，下一笔不同金额的支付获得排名 2。排名序列中没有间隙。这在您想要识别没有间隙的不同组时非常理想。

NTILE(): 将行分配到桶中

NTILE(n) 将分区分成 n 组（桶），并为每一行分配一个桶编号。这在百分位数分析和将数据分成四分位数、三分位数等时非常有用。

语法：

NTILE(number_of_buckets) OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression
)

示例：四分位数分析

SELECT
    customer_id,
    amount,
    payment_date,
    NTILE(4) OVER (
        PARTITION BY customer_id 
        ORDER BY amount DESC
    ) AS quartile
FROM
    payment
WHERE
    customer_id IN (1, 2, 3)
ORDER BY
    customer_id,
    quartile;

输出示例：

customer_id | amount | payment_date | quartile
1           | 11.99  | 2005-08-01   | 1
1           | 11.99  | 2005-07-08   | 2
1           | 10.99  | 2005-06-19   | 3
2           | 11.99  | 2005-08-02   | 1
2           | 10.99  | 2005-07-09   | 2
3           | 9.99   | 2005-08-03   | 1

关键点： 行被分配到 4 个四分位数中。这在百分位数分析中非常有用——识别前 25%（四分位数 1）、下一个 25%（四分位数 2）等。

并排比较

让我们看看这四个函数如何应用于相同的数据：

SELECT
    customer_id,
    amount,
    row_number() OVER (ORDER BY amount DESC) AS row_num,
    rank() OVER (ORDER BY amount DESC) AS rnk,
    dense_rank() OVER (ORDER BY amount DESC) AS dense_rnk,
    ntile(3) OVER (ORDER BY amount DESC) AS tertile
FROM
    payment
LIMIT 10;

输出示例：

customer_id | amount | row_num | rnk | dense_rnk | tertile
1           | 11.99  | 1       | 1   | 1         | 1
1           | 11.99  | 2       | 1   | 1         | 1
2           | 11.99  | 3       | 1   | 1         | 1
5           | 10.99  | 4       | 4   | 2         | 1
6           | 10.99  | 5       | 4   | 2         | 1
3           | 9.99   | 6       | 6   | 3         | 2
4           | 9.99   | 7       | 6   | 3         | 2
7           | 8.99   | 8       | 8   | 4         | 3
8           | 8.99   | 9       | 8   | 4         | 3
9           | 7.99   | 10      | 10  | 5         | 3

观察：

row_number: 始终唯一，没有间隙
rank: 分组平局但产生间隙（1, 1, 1, 4, 4, 6, 6, 8, 8, 10）
dense_rank: 分组平局没有间隙（1, 1, 1, 2, 2, 3, 3, 4, 4, 5）
ntile(3): 根据排序分配到 3 个组中

实际应用

查找顶级表现者 (ROW_NUMBER)

获取每个月租赁的最高支付客户：

WITH ranked_payments AS (
    SELECT
        customer_id,
        amount,
        DATE_TRUNC('month', payment_date) AS month,
        ROW_NUMBER() OVER (
            PARTITION BY DATE_TRUNC('month', payment_date)
            ORDER BY amount DESC
        ) AS rank
    FROM
        payment
)
SELECT
    customer_id,
    amount,
    month
FROM
    ranked_payments
WHERE
    rank = 1
ORDER BY
    month DESC;

识别表现层级 (DENSE_RANK)

按租赁频率对电影进行分类：

WITH rental_counts AS (
    SELECT
        film_id,
        COUNT(*) AS rental_count,
        DENSE_RANK() OVER (
            ORDER BY COUNT(*) DESC
        ) AS popularity_tier
    FROM
        rental r
        JOIN inventory i ON r.inventory_id = i.inventory_id
    GROUP BY
        film_id
)
SELECT
    film_id,
    rental_count,
    CASE
        WHEN popularity_tier = 1 THEN 'Blockbuster'
        WHEN popularity_tier <= 3 THEN 'Popular'
        WHEN popularity_tier <= 10 THEN 'Standard'
        ELSE 'Niche'
    END AS popularity_category
FROM
    rental_counts
LIMIT 20;

百分位数分析 (NTILE)

将客户分为消费四分位数：

WITH customer_spending AS (
    SELECT
        customer_id,
        SUM(amount) AS total_spent,
        NTILE(4) OVER (ORDER BY SUM(amount)) AS spending_quartile
    FROM
        payment
    GROUP BY
        customer_id
)
SELECT
    spending_quartile,
    COUNT(*) AS customer_count,
    MIN(total_spent) AS low_amount,
    MAX(total_spent) AS high_amount
FROM
    customer_spending
GROUP BY
    spending_quartile
ORDER BY
    spending_quartile;

何时使用每个函数

函数	用例	处理平局
`ROW_NUMBER`	需要唯一的顺序编号；不关心平局	否（全部唯一）
`RANK`	需要识别位置但考虑平局；间隙可以	是（有间隙）
`DENSE_RANK`	需要无间隙的层级识别	是（无间隙）
`NTILE`	需要百分位数/四分位数/桶分析	分配到组中

常见问题解答

何时选择 RANK 而不是 DENSE_RANK？

当间隙可以接受且您想要竞争式排名时使用 RANK。当您需要没有间隙的紧凑层级时使用 DENSE_RANK。

我可以在没有 PARTITION BY 的情况下使用 ROW_NUMBER 吗？

可以。在这种情况下，编号将在整个结果集中作为一个分区运行。

如果我已经有排名函数，为什么还需要 NTILE？

NTILE 解决了一个不同的问题：它将行分成固定数量的桶，例如四分位数或十分位数。

面试问题

ROW_NUMBER 和 RANK 之间有什么区别？

ROW_NUMBER 始终为每一行分配一个唯一的编号，而 RANK 为相等值赋予相同的排名，并可能跳过编号。

NTILE(4) 是如何工作的？

它在窗口内对行进行排序，并将它们分配到四个大致相等的组中，为每一行分配一个从 1 到 4 的四分位数编号。

如何在每个组内获取前 N 行？

在子查询中使用 ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...)，并使用 WHERE rn <= N 进行过滤。

关键要点

ROW_NUMBER() 为每一行提供一个唯一的编号，适用于从每个组中获取前 N 条记录。
RANK() 为平局值分配相同的排名，但跳过排名（1, 1, 3），适用于竞争排名。
DENSE_RANK() 为平局值分配相同的排名而没有间隙（1, 1, 2），适用于层级识别。
NTILE(n) 将行分成桶，用于百分位数和分布分析。
这四个函数都是窗口函数家族的一部分，并使用 OVER 子句。
关键区别在于它们如何处理排序列中的相同值。
选择正确的函数取决于您的分析目标：定位、分组或分布。

在下一课中，我们将探讨高级窗口函数概念，包括窗口框架、分区策略以及其他分析函数，如 LAG、LEAD、FIRST_VALUE 和 LAST_VALUE。

练习

课程

练习场

测试

使用 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE

理解差异

ROW_NUMBER(): 唯一的顺序编号

RANK(): 带间隙的排名

DENSE_RANK(): 无间隙的排名

NTILE(): 将行分配到桶中

并排比较

实际应用

查找顶级表现者 (ROW_NUMBER)

识别表现层级 (DENSE_RANK)

百分位数分析 (NTILE)

何时使用每个函数

常见问题解答

何时选择 RANK 而不是 DENSE_RANK？

我可以在没有 PARTITION BY 的情况下使用 ROW_NUMBER 吗？

如果我已经有排名函数，为什么还需要 NTILE？

面试问题

ROW_NUMBER 和 RANK 之间有什么区别？

NTILE(4) 是如何工作的？

如何在每个组内获取前 N 行？

关键要点

尝试解决以下任务，以巩固您在本课中学到的内容。

登录

注册

使用 ROW_NUMBER、RANK、DENSE_RANK 和 NTILE

理解差异

ROW_NUMBER(): 唯一的顺序编号

RANK(): 带间隙的排名

DENSE_RANK(): 无间隙的排名

NTILE(): 将行分配到桶中

并排比较

实际应用

查找顶级表现者 (ROW_NUMBER)

识别表现层级 (DENSE_RANK)

百分位数分析 (NTILE)

何时使用每个函数

常见问题解答

何时选择 RANK 而不是 DENSE_RANK？

我可以在没有 PARTITION BY 的情况下使用 ROW_NUMBER 吗？

如果我已经有排名函数，为什么还需要 NTILE？

面试问题

ROW_NUMBER 和 RANK 之间有什么区别？

NTILE(4) 是如何工作的？

如何在每个组内获取前 N 行？

关键要点

尝试解决以下任务，以巩固您在本课中学到的内容。