【sql开窗函数详解】在SQL中，开窗函数（Window Function）是一种强大的工具，用于在查询结果集中对数据进行分组、排序和计算。与传统的聚合函数不同，开窗函数可以在不改变原始行数的前提下，对每行数据执行计算，并返回每个窗口内的结果。
一、什么是开窗函数？
开窗函数是在一个“窗口”内对一组行进行操作的函数。这个“窗口”可以是整个表、某个分区或某个范围。它通常与 `OVER()` 子句一起使用，以定义窗口的范围。
常见的开窗函数包括：
- `ROW_NUMBER()`
- `RANK()`
- `DENSE_RANK()`
- `NTILE()`
- `SUM()`, `AVG()`, `MAX()`, `MIN()` 等聚合函数也可以作为开窗函数使用
二、开窗函数的语法结构
```sql
FUNCTION_NAME() OVER (
| PARTITION BY column1, column2... |
| ORDER BY column1, column2... |
| ROWS BETWEEN start AND end |
)
```
- PARTITION BY：将数据按指定列分组，相当于分组后的子集。
- ORDER BY：在每个分组内按指定列排序。
- ROWS BETWEEN：定义窗口的范围，如 `UNBOUNDED PRECEDING` 表示从开始到当前行。
三、常见开窗函数功能对比
| 函数名称 | 功能描述 | 是否支持排序 | 是否支持分区 |
| `ROW_NUMBER()` | 为每一行分配唯一的序号（连续） | 是 | 是 |
| `RANK()` | 返回行的排名，相同值会占用相同的排名，后续排名跳过 | 是 | 是 |
| `DENSE_RANK()` | 返回行的排名，相同值会占用相同的排名，后续排名不跳过 | 是 | 是 |
| `NTILE(n)` | 将数据分为n个桶，按顺序分配桶号 | 是 | 是 |
| `SUM()` | 计算窗口内的总和 | 是 | 是 |
| `AVG()` | 计算窗口内的平均值 | 是 | 是 |
| `MAX()` | 返回窗口内的最大值 | 是 | 是 |
| `MIN()` | 返回窗口内的最小值 | 是 | 是 |
四、开窗函数的使用场景
1. 排名统计：如学生考试成绩排名、销售员业绩排名等。
2. 数据分组分析：如按部门统计员工工资分布。
3. 趋势分析：如计算每个月的销售额累计值。
4. 数据去重：通过 `ROW_NUMBER()` 实现去重逻辑。
5. 移动平均：如计算过去3天的平均销量。
五、示例说明
假设有一个销售表 `sales`，包含以下字段：
| id | product | sales_date | amount |
| 1 | A | 2024-01-01 | 100 |
| 2 | B | 2024-01-02 | 200 |
| 3 | A | 2024-01-03 | 150 |
| 4 | B | 2024-01-04 | 250 |
示例1：按产品分组，计算每行的排名
```sql
SELECT
id,
product,
amount,
RANK() OVER (PARTITION BY product ORDER BY amount DESC) AS rank
FROM sales;
```
示例2：计算每个产品的累计销售额
```sql
SELECT
id,
product,
amount,
SUM(amount) OVER (PARTITION BY product ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM sales;
```
六、总结
开窗函数是SQL中非常实用的功能，能够帮助我们在不改变数据结构的前提下，对数据进行复杂的分析和计算。掌握其基本语法和使用方式，有助于提升查询效率和数据分析能力。通过合理使用 `PARTITION BY` 和 `ORDER BY`，可以灵活控制窗口范围，满足多种业务需求。
| 特点 | 描述 |
| 提升查询灵活性 | 可在原数据基础上进行复杂计算 |
| 不影响行数 | 保留所有原始记录 |
| 支持多种聚合函数 | 如 `SUM`, `AVG`, `MAX` 等 |
| 常用于数据分析 | 如排名、累计、趋势分析等 |
原创内容，转载请注明来源。