MySQL：over()开窗函数

在 MySQL 中，`OVER()` 开窗函数是一种用于在查询结果集中处理行的方式，使得可以对某些行进行计算，而不会改变结果集的行数。开窗函数通常用于需要在查询结果中完成复杂计算的场景，如计算累计和、移动平均值、排名等。

1. 开窗函数的基本构成

开窗函数的基本语法格式如下：

function_name() OVER (
    [PARTITION BY partition_expression]
    [ORDER BY order_expression]
    [ROWS frame_specification]
)

        function_name(): 要使用的聚合函数，例如 `SUM()`, `AVG()`, `ROW_NUMBER()`, `RANK()` 等。
        PARTITION BY: （可选）用于将结果集分隔成多个分区，函数将在每个分区内计算。
        ORDER BY: （可选）指定计算的顺序。
        ROWS: （可选）定义窗口大小，用于设定计算的行范围。

2. 常见的开窗函数

2.1 行号函数（ROW_NUMBER）

        `ROW_NUMBER()` 函数为结果集中的每一行分配一个唯一的序号。

示例：

根据薪资对员工进行排序并分配一个排名

select
    employees.name,
    employees.salary,
    row_number() over (order by salary desc) as `rank`
from
    employees;

2.2 累计和（SUM）

使用 `SUM()` 函数计算从结果集的开头到当前行的累计和。

示例：

select
    name,
    salary,
    sum(salary) over (order by employees.employee_id) as cumulative_salary
from
    employees;

此查询计算员工薪资的累计和，按员工 ID 排序。

2.3 移动平均（AVG）

计算当前行及前 n 行（或后 n 行）的平均值。

示例：

select
    name,
    salary,
    avg(salary) over (order by employees.employee_id rows between 1 preceding and current row) as moving_avg_salary
from
    employees;

此查询计算目前及前一行的移动平均薪资。

2.4 排名函数（RANK 和 DENSE_RANK）

        `RANK()`: 如果有相同的值，排名会跳过数字。
        `DENSE_RANK()`: 如果有相同的值，排名连续。

示例：

select
    name,
    salary,
    rank() over (order by salary desc)       as salary_rank,
    dense_rank() over (order by salary desc) as dense_salary_rank
from
    employees;

该查询为员工的薪资提供排名。

3. PARTITION BY和ORDER BY的作用

        PARTITION BY：将结果集分成多个组，函数对每个组单独计算。例如：

select
    department_id,
    name,
    salary,
    rank() over (partition by department_id order by salary desc) as department_rank
from
    employees;

        这个查询为每个部门的员工基于薪资进行排名。

        ORDER BY：定义计算的顺序，对于一些函数（如 `RANK` 和 `SUM`）来说非常重要。

4. 注意事项

        性能：开窗函数可能会影响性能，尤其是在大型数据集上使用。需确保查询被合理优化。
        兼容性：虽然 MySQL 8.0 及以上版本支持开窗函数，建议使用前检查您所使用的 MySQL 版本。

5. 总结

MySQL 中的 `OVER()` 开窗函数为数据分析提供了强大的能力，使得对行的逐行处理变得更加灵活和高效。它极大地扩展了数据库查询的功能，适用于各种复杂的计算需求。