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，结构如下：

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 ASCROWS 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的区别

六、注意事项

1. 排序影响：ORDER BY在开窗函数中不仅影响排名，还会影响窗口范围的界定（如累计计算）。
2. 性能考量：复杂的窗口范围（如RANGE）可能导致性能下降，大表建议优先用ROWS。
3. 数据库支持：主流数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle）均支持开窗函数，但部分细节可能有差异。

通过上述讲解，可掌握开窗函数的核心语法和应用场景。实际使用时，需根据业务需求灵活组合PARTITION BY、ORDER BY和窗口范围，实现复杂的数据分析。