【sql开窗函数详解】在SQL中，开窗函数（Window Function）是一种强大的工具，能够对查询结果集中的行进行分组计算，并返回与原始行相关的聚合值。它不仅保留了每条记录的细节信息，还能在同一查询中展示汇总数据，极大提升了数据分析的灵活性。
一、什么是开窗函数？
开窗函数是用于在SQL查询中对一组行执行计算，同时保留这些行的原始信息。它的核心在于“窗口”——即定义哪些行参与计算的范围。常见的开窗函数包括 `ROW_NUMBER()`、`RANK()`、`DENSE_RANK()`、`NTILE()`、`SUM()`、`AVG()` 等。
二、开窗函数的基本语法
```sql
FUNCTION_NAME() OVER (
| PARTITION BY column |
| ORDER BY column |
| ROWS BETWEEN ... |
)
```
- PARTITION BY：将数据分成不同的分区，每个分区独立计算。
- ORDER BY：指定排序方式，影响排名和累计计算。
- ROWS BETWEEN：定义窗口的范围（如当前行前后多少行）。
三、常见开窗函数及其用途
| 函数名称 | 功能说明 | 示例场景 |
| ROW_NUMBER() | 为每一行分配唯一的序号 | 分页显示、排名 |
| RANK() | 返回相同的值具有相同的排名，后续排名跳过 | 学生成绩排名 |
| DENSE_RANK() | 返回相同的值具有相同的排名，后续不跳过 | 排名连续 |
| NTILE(n) | 将数据分为n个组 | 按销售额分组 |
| SUM() | 计算窗口内的总和 | 累计销售金额 |
| AVG() | 计算窗口内的平均值 | 平均工资 |
| COUNT() | 统计窗口内的行数 | 各部门员工数量 |
| LAG() / LEAD() | 获取前一行或后一行的数据 | 员工薪资变化趋势 |
四、使用示例
假设有一个销售表 `sales`，包含字段：`id`, `product`, `amount`, `date`。
示例1：按产品统计销量并添加排名
```sql
SELECT
id,
product,
amount,
ROW_NUMBER() OVER (PARTITION BY product ORDER BY amount DESC) AS row_num,
RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
FROM sales;
```
示例2：计算累计销售额
```sql
SELECT
date,
amount,
SUM(amount) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
FROM sales;
```
五、注意事项
- 开窗函数不会减少行数，只会在原有行上添加额外列。
- 如果没有 `PARTITION BY`，整个结果集视为一个窗口。
- 使用 `ROWS BETWEEN` 可以更精细地控制窗口范围。
- 避免在性能敏感的查询中滥用开窗函数，需结合索引优化。
六、总结
开窗函数是SQL中处理复杂分析任务的重要工具，尤其适合需要同时保留明细数据和聚合信息的场景。掌握其语法和应用场景，能显著提升数据处理的效率和灵活性。通过合理使用 `PARTITION BY` 和 `ORDER BY`，可以实现多样化的分析需求，是数据分析师和开发人员必备技能之一。
