我要评论前言
sql 开窗函数（window function）是一种强大的分析工具，它能在保留原有数据行的基础上，对 "窗口"（指定范围的行集合）进行聚合、排名或分析计算，解决了传统group by聚合会合并行的局限性。
一、开窗函数的核心特点
- 不合并行：与
group by不同，开窗函数计算后会保留所有原始行，只是为每行附加一个计算结果。 - 窗口定义：通过
over()子句定义 "窗口"（即计算范围），可按条件分区、排序或限定行范围。 - 适用场景：排名（如 top n）、累计计算（如累计求和）、移动分析（如近 3 天平均值）、前后行数据获取等。
二、基本语法结构
开窗函数的通用语法：
函数名(参数) over ( [partition by 分区列1, 分区列2...] -- 可选：按列分组，每组独立计算 [order by 排序列1 [asc|desc], ...] -- 可选：分区内的排序方式 [rows | range 窗口范围] -- 可选：定义窗口的具体行范围（行级窗口） )
- 函数名：可以是排名函数（
rank()、row_number()等）、聚合函数（sum()、avg()等）或分析函数（lag()、lead()等）。 - over()子句：核心部分，用于定义 "窗口" 的规则。
三、over()子句详解
1. partition by：分区（分组）
- 作用：将数据按指定列分成多个独立的 "分区"，开窗函数在每个分区内单独计算（类似
group by的分组，但不合并行）。 - 示例：按 "部门" 分区，每个部门内部独立计算工资排名。
2. order by：分区内排序
- 作用：指定分区内的行排序规则，影响排名函数的结果和窗口范围的界定。
- 注意：若不指定
partition by，则全表视为一个分区，按order by整体排序。
3. rows | range：窗口范围（行级窗口）
- 作用：在分区内，进一步限定参与计算的行范围（如 "当前行 + 前 2 行 + 后 1 行"）。
- 关键字：
rows：基于物理行数界定范围（如 "前 2 行"）。range：基于值的逻辑范围界定（如 "值在当前行 ±10 以内的行"），仅支持数值 / 日期类型。
- 常用范围表达式：
unbounded preceding：分区的第一行current row：当前行n preceding：当前行之前的第 n 行n following：当前行之后的第 n 行- 组合示例：
rows between 2 preceding and 1 following（当前行 + 前 2 行 + 后 1 行）
四、常用开窗函数分类及示例
以下示例基于员工表employee，结构如下：
| id | name | department | salary | hire_date |
|---|---|---|---|---|
| 1 | 张三 | 技术部 | 8000 | 2020-01-15 |
| 2 | 李四 | 技术部 | 9000 | 2019-03-20 |
| 3 | 王五 | 技术部 | 9000 | 2018-05-10 |
| 4 | 赵六 | 市场部 | 7000 | 2021-02-05 |
| 5 | 钱七 | 市场部 | 8500 | 2020-08-18 |
1. 排名函数（用于生成排名）
（1）row_number()：生成唯一序号
- 功能：为分区内的每行分配一个连续的唯一序号（即使值相同，序号也不同）。
- 示例：按部门分区，按工资降序排名（工资相同则按入职时间升序）：
select
name,
department,
salary,
row_number() over (
partition by department
order by salary desc, hire_date asc
) as row_num
from employee;
- 结果：
name department salary row_num 李四 技术部 9000 1 （同工资，入职早排前） 王五 技术部 9000 2 张三 技术部 8000 3 钱七 市场部 8500 1 赵六 市场部 7000 2
（2）rank()：带跳号的排名
- 功能：相同值排名相同，后续排名会 "跳号"（如两个第 1 名，下一个是第 3 名）。
- 示例：按部门分区，按工资降序排名：
select
name,
department,
salary,
rank() over (
partition by department
order by salary desc
) as rank_num
from employee;
- 结果：
name department salary rank_num 李四 技术部 9000 1 王五 技术部 9000 1 （与李四并列第 1） 张三 技术部 8000 3 （跳号，直接第 3） 钱七 市场部 8500 1 赵六 市场部 7000 2
（3）dense_rank()：无跳号的排名
- 功能：相同值排名相同，后续排名不跳号（如两个第 1 名，下一个是第 2 名）。
- 示例：按部门分区，按工资降序排名：
select
name,
department,
salary,
dense_rank() over (
partition by department
order by salary desc
) as dense_rank_num
from employee;
- 结果：
name department salary dense_rank_num 李四 技术部 9000 1 王五 技术部 9000 1 张三 技术部 8000 2 （不跳号，第 2） 钱七 市场部 8500 1 赵六 市场部 7000 2
2. 聚合开窗函数（聚合函数 +over()）
将sum()、avg()、count()等聚合函数与over()结合，为每行计算所在窗口的聚合结果。
（1）全分区聚合（无order by和范围）
- 功能：计算整个分区的聚合值（每行的结果相同）。
- 示例：计算每个部门的平均工资，附加到每行：
select name, department, salary, avg(salary) over (partition by department) as dept_avg_salary from employee;
- 结果：
name department salary dept_avg_salary 张三 技术部 8000 8666.67 （(8000+9000+9000)/3） 李四 技术部 9000 8666.67 王五 技术部 9000 8666.67
（2）累计聚合（带order by和范围）
- 功能：按排序顺序计算 "累计" 聚合值（如累计求和、累计平均值）。
- 示例：按部门分区，按入职时间升序，计算累计工资总和：
select
name,
department,
hire_date,
salary,
sum(salary) over (
partition by department
order by hire_date asc
rows between unbounded preceding and current row -- 从第一行到当前行
) as cumulative_salary
from employee;
- 结果（技术部）：
name department hire_date salary cumulative_salary 王五 技术部 2018-05-10 9000 9000 （第一行，累计 = 自身） 李四 技术部 2019-03-20 9000 18000 （累计 = 9000+9000） 张三 技术部 2020-01-15 8000 26000 （累计 = 9000+9000+8000）
3. 分析函数（获取前后行数据）
（1）lag(列名, n)：获取当前行的前 n 行数据
- 功能：返回当前行之前第 n 行的指定列值（默认 n=1）。
- 示例：获取每个部门中，当前员工的前一位入职员工的工资：
select
name,
department,
hire_date,
salary,
lag(salary, 1) over (
partition by department
order by hire_date asc
) as prev_emp_salary
from employee;
- 结果（技术部）：
name department hire_date salary prev_emp_salary 王五 技术部 2018-05-10 9000 null （第一行，无前一行） 李四 技术部 2019-03-20 9000 9000 （前一行是王五的工资） 张三 技术部 2020-01-15 8000 9000 （前一行是李四的工资）
（2）lead(列名, n)：获取当前行的后 n 行数据
- 功能：返回当前行之后第 n 行的指定列值（默认 n=1）。
- 示例：获取每个部门中，当前员工的后一位入职员工的工资：
select
name,
department,
hire_date,
salary,
lead(salary, 1) over (
partition by department
order by hire_date asc
) as next_emp_salary
from employee;
- 结果（技术部）：
name department hire_date salary next_emp_salary 王五 技术部 2018-05-10 9000 9000 （后一行是李四的工资） 李四 技术部 2019-03-20 9000 8000 （后一行是张三的工资） 张三 技术部 2020-01-15 8000 null （最后一行，无后一行）
五、开窗函数与group by的区别
| 特性 | group by聚合 | 开窗函数 |
|---|---|---|
| 行处理 | 合并分组后的行（一行 / 组） | 保留所有原始行 |
| 计算范围 | 整个分组 | 可自定义窗口范围（分区、行范围） |
| 结果列 | 仅聚合结果 + 分组列 | 原始列 + 开窗计算结果 |
六、注意事项
- 排序影响：
order by在开窗函数中不仅影响排名，还会影响窗口范围的界定（如累计计算）。 - 性能考量：复杂的窗口范围（如
range）可能导致性能下降，大表建议优先用rows。 - 数据库支持：主流数据库（mysql 8.0+、postgresql、sql server、oracle）均支持开窗函数，但部分细节可能有差异。
通过上述讲解，可掌握开窗函数的核心语法和应用场景。实际使用时，需根据业务需求灵活组合partition by、order by和窗口范围，实现复杂的数据分析。
到此这篇关于mysql开窗函数的文章就介绍到这了,更多相关mysql开窗函数内容请搜索代码网以前的文章或继续浏览下面的相关文章希望大家以后多多支持代码网！
发表评论