发布于 由admin

MySQL函数：`RANK()`与`DENSE_RANK()`在分组排序中的排名差异和应用。

好的，下面我们开始关于MySQL中RANK()和DENSE_RANK()函数在分组排序中的排名差异和应用的讲座。

讲座主题：MySQL排名函数：RANK() vs DENSE_RANK()

大家好，今天我们来深入探讨MySQL中两个非常实用的窗口函数：RANK()和DENSE_RANK()。 这两个函数都用于在结果集的分组中进行排序，但它们在处理并列排名时存在关键差异。 理解这些差异对于编写高效且准确的SQL查询至关重要。

1. 窗口函数基础

在深入了解RANK()和DENSE_RANK()之前，我们先简要回顾一下窗口函数。 窗口函数允许我们对查询结果的“窗口”（即一组相关的行）执行计算，而无需像GROUP BY那样折叠行。 窗口函数通常与OVER()子句一起使用，该子句定义了窗口的范围和排序方式。

2. RANK()函数详解

RANK()函数为结果集分区中的每一行分配一个排名。 排名基于ORDER BY子句中指定的列。 关键在于，RANK()会跳过排名。 如果两行或多行具有相同的排序值，它们将获得相同的排名，并且下一个排名将跳过相应的数量。

语法:

RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3 [ASC | DESC], column4 [ASC | DESC], ...
)
  • PARTITION BY子句（可选）将结果集分成多个分区。 如果省略PARTITION BY子句，则将整个结果集视为单个分区。
  • ORDER BY子句定义了每个分区中行的排序方式。
  • ASC和DESC分别表示升序和降序。 默认为升序。

示例:

假设我们有一个名为employees的表，包含以下数据：

CREATE TABLE employees (
    department VARCHAR(50),
    employee_name VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (department, employee_name, salary) VALUES
('Sales', 'Alice', 60000.00),
('Sales', 'Bob', 55000.00),
('Sales', 'Charlie', 60000.00),
('Marketing', 'David', 70000.00),
('Marketing', 'Eve', 65000.00),
('Marketing', 'Frank', 70000.00),
('IT', 'Grace', 80000.00),
('IT', 'Henry', 75000.00),
('IT', 'Ivy', 80000.00);

现在，让我们使用RANK()函数按部门对员工的薪水进行排名：

SELECT
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
    employees;

结果如下：

department employee_name salary salary_rank
IT Grace 80000.00 1
IT Ivy 80000.00 1
IT Henry 75000.00 3
Marketing David 70000.00 1
Marketing Frank 70000.00 1
Marketing Eve 65000.00 3
Sales Alice 60000.00 1
Sales Charlie 60000.00 1
Sales Bob 55000.00 3

注意，在IT部门中，Grace和Ivy的薪水相同，因此它们都获得了排名1。 下一个员工Henry获得了排名3，因为排名2被跳过了。 同样，在Marketing和Sales部门也发生了类似的情况。

3. DENSE_RANK()函数详解

DENSE_RANK()函数与RANK()函数类似，也为结果集分区中的每一行分配一个排名。 但是，DENSE_RANK()不会跳过排名。 即使两行或多行具有相同的排序值，下一个排名仍然是连续的。

语法:

DENSE_RANK() OVER (
    [PARTITION BY column1, column2, ...]
    ORDER BY column3 [ASC | DESC], column4 [ASC | DESC], ...
)
  • PARTITION BY和ORDER BY子句的含义与RANK()函数相同。

示例:

让我们使用DENSE_RANK()函数对相同的employees表进行排名：

SELECT
    department,
    employee_name,
    salary,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
FROM
    employees;

结果如下：

department employee_name salary salary_rank
IT Grace 80000.00 1
IT Ivy 80000.00 1
IT Henry 75000.00 2
Marketing David 70000.00 1
Marketing Frank 70000.00 1
Marketing Eve 65000.00 2
Sales Alice 60000.00 1
Sales Charlie 60000.00 1
Sales Bob 55000.00 2

注意，在IT部门中，Grace和Ivy的薪水相同，因此它们都获得了排名1。 下一个员工Henry获得了排名2，没有跳过任何排名。 同样，在Marketing和Sales部门也发生了类似的情况。

4. RANK() vs DENSE_RANK()：关键差异

特性 RANK() DENSE_RANK()
排名跳跃 是 (跳过排名以反映并列) 否 (不跳过排名，保持连续性)
排名数量 可能大于分区中的行数（如果存在并列） 小于或等于分区中的行数
应用场景 需要识别并列情况下的相对位置，允许跳跃 需要知道 distinct 值的排名，排名需要连续

总结：

  • RANK()会跳过排名，产生不连续的排名序列。
  • DENSE_RANK()不会跳过排名，产生连续的排名序列。

5. 应用场景分析

让我们通过一些更具体的应用场景来理解RANK()和DENSE_RANK()的适用性。

场景1：找出每个部门薪水最高的员工（允许并列）。

使用RANK()：

SELECT
    department,
    employee_name,
    salary
FROM (
    SELECT
        department,
        employee_name,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM
        employees
) AS ranked_employees
WHERE
    salary_rank = 1;

这个查询将返回每个部门中薪水最高的 所有 员工，即使有多个员工的薪水相同。

场景2：找出每个部门薪水第二高的员工。

使用DENSE_RANK()：

SELECT
    department,
    employee_name,
    salary
FROM (
    SELECT
        department,
        employee_name,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
    FROM
        employees
) AS ranked_employees
WHERE
    salary_rank = 2;

这个查询将返回每个部门中薪水第二高的员工。 如果有多个员工的薪水相同，并且他们都是第二高的，那么他们都会被返回。 使用DENSE_RANK() 保证了即使第一名有并列，也能找到第二高的薪资水平。 如果使用 RANK()，当第一名有并列时，第二名的 salary_rank 可能是3，导致查不到结果。

场景3：计算每个部门的薪水百分位数。

虽然MySQL没有直接的百分位数函数，但我们可以结合RANK()或DENSE_RANK()来模拟这个功能。 这里展示使用RANK()的例子：

SELECT
    department,
    employee_name,
    salary,
    (RANK() OVER (PARTITION BY department ORDER BY salary DESC) - 1) / (COUNT(*) OVER (PARTITION BY department) - 1) AS percentile
FROM
    employees
WHERE (COUNT(*) OVER (PARTITION BY department) - 1) > 0; -- 避免除以零的错误，当部门只有一个人时

这个查询计算每个员工在其部门中的薪水百分位数。 注意，我们需要处理部门只有一个员工的情况，以避免除以零的错误。

6. 其他排名函数

除了RANK()和DENSE_RANK()，MySQL还提供了其他排名函数：

  • ROW_NUMBER(): 为结果集分区中的每一行分配一个唯一的序列号，从1开始。 它不考虑并列情况。
  • NTILE(n): 将结果集分区中的行分成n组，并为每行分配一个组号（从1到n）。

示例：ROW_NUMBER()

SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM
    employees;

结果:

department employee_name salary row_num
IT Grace 80000.00 1
IT Ivy 80000.00 2
IT Henry 75000.00 3
Marketing David 70000.00 1
Marketing Frank 70000.00 2
Marketing Eve 65000.00 3
Sales Alice 60000.00 1
Sales Charlie 60000.00 2
Sales Bob 55000.00 3

即使Grace和Ivy薪资相同，ROW_NUMBER() 也为它们分配了不同的序号。

示例：NTILE(2)

SELECT
    department,
    employee_name,
    salary,
    NTILE(2) OVER (PARTITION BY department ORDER BY salary DESC) AS tile
FROM
    employees;

结果:

department employee_name salary tile
IT Grace 80000.00 1
IT Ivy 80000.00 1
IT Henry 75000.00 2
Marketing David 70000.00 1
Marketing Frank 70000.00 1
Marketing Eve 65000.00 2
Sales Alice 60000.00 1
Sales Charlie 60000.00 1
Sales Bob 55000.00 2

这个查询将每个部门的员工分成两组，基于薪资排序。

7. 性能考量

窗口函数通常比使用子查询或临时表来实现相同逻辑更有效。 然而，对于大型数据集，性能仍然是一个重要的考虑因素。 确保为ORDER BY子句中使用的列创建适当的索引，并避免在窗口函数中使用复杂的表达式。

8. 总结： 函数的选择很重要

在选择RANK()和DENSE_RANK()时，你需要仔细考虑你的应用场景和对并列排名的处理方式。 RANK()适用于需要反映并列情况下的相对位置的情况，而DENSE_RANK()适用于需要知道 distinct 值的排名，并且排名需要连续的情况。 了解其他排名函数（如ROW_NUMBER()和NTILE()）可以帮助你更灵活地解决各种排名问题。选择正确的函数，并注意性能优化，可以编写出高效且准确的SQL查询。

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注