MySQL 窗口函数详解

窗口函数是 SQL 中一种强大的分析工具，允许用户对查询结果集中的行进行计算和分析，而不需要对整个结果集进行分组。窗口函数执行计算时，可以访问当前行及其“窗口”内的其他行。常见的窗口函数包括 ROW_NUMBER(), RANK(), DENSE_RANK(), SUM(), AVG() 等。

窗口函数的概念

窗口函数由两部分组成：函数和窗口定义。窗口定义通常通过 OVER 子句来指定，包含以下几个部分：

1. PARTITION BY: 将结果集分为多个子集，窗口函数将在每个子集内独立计算。
2. ORDER BY: 指定处理每个分区的行的顺序。
3. ROWS BETWEEN: 可选参数，指定当前行视图窗口的起止行，默认是从当前行到当前行。

窗口函数示例

为了便于理解，我们假设有一张员工表 employees，表结构如下：

| employee_id | name | salary | department_id | |-------------|-------|--------|----------------| | 1 | Alice | 70000 | 1 | | 2 | Bob | 60000 | 1 | | 3 | Carol | 80000 | 2 | | 4 | Dave | 50000 | 2 |

示例 1: 使用 ROW_NUMBER()

ROW_NUMBER() 函数用于为每一行生成唯一的递增序列。在每个部门中对员工的薪水进行排序时，可以使用此函数。

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

执行结果：

| employee_id | name | salary | department_id | rank | |-------------|-------|--------|----------------|------| | 3 | Carol | 80000 | 2 | 1 | | 4 | Dave | 50000 | 2 | 2 | | 1 | Alice | 70000 | 1 | 1 | | 2 | Bob | 60000 | 1 | 2 |

示例 2: 使用 RANK()

RANK() 在排序时会为相同的值赋相同的排名，并跳过以下排名。接着对薪水进行排名。

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

执行结果：

| employee_id | name | salary | department_id | rank | |-------------|-------|--------|----------------|------| | 3 | Carol | 80000 | 2 | 1 | | 4 | Dave | 50000 | 2 | 2 | | 1 | Alice | 70000 | 1 | 1 | | 2 | Bob | 60000 | 1 | 2 |

示例 3: 使用聚合窗口函数

SELECT 
    employee_id,
    name,
    salary,
    SUM(salary) OVER (PARTITION BY department_id) AS total_salary
FROM 
    employees;

执行结果：

| employee_id | name | salary | total_salary | |-------------|-------|--------|--------------| | 3 | Carol | 80000 | 130000 | | 4 | Dave | 50000 | 130000 | | 1 | Alice | 70000 | 130000 | | 2 | Bob | 60000 | 130000 |

小结

窗口函数在 MySQL 中提供了强大的分析能力，使得用户能够在不对数据进行分组的情况下，轻松地对数据进行排序、排名和聚合计算。正确使用窗口函数可以提升 SQL 查询的灵活性和可读性， 是数据分析工作中的重要工具。了解这些窗口函数，并在实际项目中合理应用，将会大大提高你的数据处理效率。