MySQL窗口函数详解（概念+练习+实战）
窗口函数是MySQL 8.0版本引入的一项强大功能。它允许在查询中对结果集中的某些行执行计算，并返回这些行，同时保留其各自的行。窗口函数能够实现诸如排名、累计和移动平均等操作，非常适合数据分析任务。
一、窗口函数概念
窗口函数的语法格式如下：
```sql <窗口函数> OVER ([PARTITION BY <列> ] [ORDER BY <列> ] [<窗口框架>]) ```
其中，`<窗口函数>`可以是RANK()、DENSE_RANK()、ROW_NUMBER()等函数，`PARTITION BY`用于将结果集分区，`ORDER BY`用于指定排序列，`<窗口框架>`用于定义窗口范围。
常见的窗口函数
1. **ROW_NUMBER()**
计算每行的序号，序号是连续的。
2. **RANK()**
计算每行的排名，如果有相同值会跳过排名。
3. **DENSE_RANK()**
类似RANK()，但是不跳过排名。
4. **NTILE(n)**
将结果集分成n个桶，并为每行分配桶编号。
5. **LAG() 和 LEAD()**
返回当前行之前或之后的值。
6. **SUM()、AVG()、MIN()、MAX()**
聚合函数可以作为窗口函数使用。
二、窗口函数的使用
1. 数据准备
我们以一个简单的员工表为例：
```sql CREATE TABLE employees ( id INT PRIMARY KEY, department VARCHAR(50), name VARCHAR(50), salary DECIMAL(10, 2) ); INSERT INTO employees VALUES (1, 'HR', 'Alice', 5000), (2, 'HR', 'Bob', 5500), (3, 'IT', 'Charlie', 6000), (4, 'IT', 'David', 7000), (5, 'IT', 'Eve', 6500); ```
2. 使用ROW_NUMBER()函数
```sql SELECT id, department, name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num FROM employees; ```
此查询将对每个部门的员工按工资降序排列，并为每个员工分配一个序号。
3. 使用RANK()函数
```sql SELECT id, department, name, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank FROM employees; ```
此查询将对每个部门的员工按工资降序排列，并为每个员工分配一个排名，遇到相同工资时，排名会跳过。
4. 使用LAG()函数
```sql SELECT id, department, name, salary, LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary) as prev_salary FROM employees; ```
此查询将为每个员工返回其工资的前一个值。
三、窗口函数的实战
1. 计算每个部门的累计工资
```sql SELECT id, department, name, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_salary FROM employees; ```
此查询将计算每个部门的累计工资。
2. 计算移动平均
```sql SELECT id, department, name, salary, AVG(salary) OVER (PARTITION BY department ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg FROM employees; ```
此查询将计算每个部门的移动平均工资，窗口为当前行及其前后各一行。
3. 获取每个部门的最高和最低工资
```sql SELECT id, department, name, salary, MAX(salary) OVER (PARTITION BY department) as max_salary, MIN(salary) OVER (PARTITION BY department) as min_salary FROM employees; ```
此查询将获取每个部门的最高和最低工资。
四、练习
为了巩固对窗口函数的理解，可以尝试以下练习：
1. 对每个部门按工资排名，找出工资第二高的员工。
2. 计算每个部门员工工资的累计百分比。
3. 计算每个部门员工工资的标准差。
结论
窗口函数是处理复杂数据分析任务的强大工具。通过本文的介绍和实战示例，希望你能掌握窗口函数的基本用法，并能够在实际项目中应用这些函数来处理数据。
