【My SQL】进阶知识 -- 一文搞懂SQL窗口排序函数

在SQL中，窗口函数（Window Functions）是一个非常强大的工具，允许你在查询结果的基础上进行进一步的操作，而不必对数据进行聚合或修改。窗口排序函数（Window Sorting Functions）是窗口函数的一种，它帮助我们在不改变数据结构的前提下，对查询结果集进行排序、排名和分组计算。本文将详细讲解窗口排序函数的使用方法，帮助你快速掌握这一进阶技能。

什么是窗口函数？

在深入窗口排序函数之前，我们先了解一下什么是窗口函数。
窗口函数是SQL中的一种特殊函数，它可以在查询结果的每一行上进行计算，但不需要像聚合函数那样将数据行汇总或去重。窗口函数通过窗口（Window）来定义计算的范围，窗口可以是整张表，也可以是根据某些条件动态划分出的子集。

窗口排序函数的基本语法

窗口排序函数通常有以下的基本语法结构：

SELECT column1, column2, ..., 
       window_function() OVER (PARTITION BY column_name ORDER BY column_name) AS alias
FROM table_name;

1.window_function()：是具体的窗口函数，比如 ROW_NUMBER()、RANK()、DENSE_RANK() 等。
2.PARTITION BY：用于将数据分成不同的“窗口”，类似于分组，窗口内的计算互不干扰。
3.ORDER BY：指定排序的规则，窗口函数会按照这个顺序进行操作。

常见的窗口排序函数

1. ROW_NUMBER() - 排序并编号
   ROW_NUMBER() 是最基础的窗口排序函数，它为每一行分配一个唯一的行号，按照 ORDER BY 中指定的列进行排序。
   示例：

SELECT name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

在这个例子中，我们按 salary（薪水）降序对员工进行排序，并为每个员工分配一个排名。注意，ROW_NUMBER() 会为每一行分配一个唯一的编号，也就是说如果有两个相同的值它会随机排序，并不会把它们排序成同一个排名。

2. RANK() - 排名（可能有重复）
   RANK() 函数与 ROW_NUMBER() 类似，也会给每一行分配一个排名，但它会处理排名重复的情况。如果两行数据有相同的排序值，它们将共享相同的排名，但后面的排名会跳过。
   示例：

SELECT name, department, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

假设有两名员工的薪水相同，排名会并列。例如，薪水最高的两人排名第一，接下来是第三名。

3. DENSE_RANK() - 密集排名（无跳过）
   DENSE_RANK() 函数与 RANK() 类似，不同的是，它不会跳过排名。也就是说，如果两行数据排名相同，它们会共享相同的排名，但后续的排名不会跳过。
   示例：

SELECT name, department, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

在这个例子中，如果有两名员工的薪水相同，他们将排名第一，接下来的员工将排名第二，而不是跳到第三名。

4. NTILE() - 等分排名
   NTILE() 函数将数据分成指定数量的“桶”（即分组），并为每一行分配一个桶编号。例如，如果你想将员工按薪水分成 4 组，并给每组分配一个编号，可以使用 NTILE(4)。
   示例：

SELECT name, department, salary,
       NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

此查询将员工根据薪水从高到低分成 4 组，quartile 列显示了每个员工所属的组编号。

使用窗口排序函数的实际案例

假设我们有一个包含员工信息的表 employees，表结构如下：

id	name	department	salary
1	Alice	HR	5000
2	Bob	IT	8000
3	Charlie	IT	7500
4	Dave	HR	6500
5	Eve	Finance	9000

我们将通过窗口排序函数来做以下分析：

1. 计算员工薪水的排名

SELECT name, department, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

结果：

name	department	salary	rank
Eve	Finance	9000	1
Bob	IT	8000	2
Charlie	IT	7500	3
Dave	HR	6500	4
Alice	HR	5000	5

2. 根据薪水分组并计算每组的排名

SELECT name, department, salary,
       RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

结果：

name	department	salary	rank
Eve	Finance	9000	1
Bob	IT	8000	2
Charlie	IT	7500	3
Dave	HR	6500	4
Alice	HR	5000	5

3. 将员工分成三组（等分）

SELECT name, department, salary,
       NTILE(3) OVER (ORDER BY salary DESC) AS group_id
FROM employees;

结果：

name	department	salary	group_id
Eve	Finance	9000	1
Bob	IT	8000	1
Charlie	IT	7500	2
Dave	HR	6500	2
Alice	HR	5000	3

总体对比

为了更清晰地展示这四种函数的排序效果，下面我将使用一个示例来展示几种常见的窗口排序函数（ROW_NUMBER()、RANK()、DENSE_RANK() 和 NTILE()）的效果，并将它们的排序结果通过表格对比展现。
假设我们有以下示例数据：

ID	Name	Score
1	Alice	95
2	Bob	85
3	Charlie	90
4	Dave	95
5	Eve	80

示例 SQL 查询：

SELECT
    ID,
    Name,
    Score,
    ROW_NUMBER() OVER (ORDER BY Score DESC) AS row_num,
    RANK() OVER (ORDER BY Score DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS dense_rank,
    NTILE(4) OVER (ORDER BY Score DESC) AS ntile
FROM students;

排序对比表格

ID	Name	Score	ROW_NUMBER()	RANK()	DENSE_RANK()	NTILE(4)
1	Alice	95	1	1	1	1
4	Dave	95	2	1	1	1
3	Charlie	90	3	3	2	2
2	Bob	85	4	4	3	3
5	Eve	80	5	5	4	4

SQL窗口排序函数是一个非常强大的工具，可以帮助你在不改变数据结构的情况下，进行排名、分组等多种操作。通过 ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE() 等函数，你可以灵活地处理各种排序和排名需求，尤其是在分析和报表生成过程中非常有用。

通过本文的介绍，希望你能轻松理解并掌握窗口排序函数的使用，提升你的SQL技能。如果你有更多关于SQL的问题，欢迎留言讨论！