【sql开窗函数详解】在SQL中，开窗函数（Window Function）是一种强大的工具，用于在查询结果集中对数据进行分组计算，同时保留原始行的详细信息。与传统的聚合函数不同，开窗函数不会将多行合并为一行，而是允许在每一行上显示聚合结果。这使得它在数据分析、报表生成和复杂查询中非常有用。
一、什么是开窗函数？
开窗函数是SQL中的一种特殊函数，可以在不减少行数的前提下，对一组数据进行计算。它的基本结构如下：
```sql
function_name() OVER (PARTITION BY column_name ORDER BY column_name ROWS BETWEEN ... )
```
其中：
- `function_name()` 是具体的函数，如 `SUM`, `AVG`, `ROW_NUMBER`, `RANK` 等。
- `OVER()` 是定义窗口范围的关键字。
- `PARTITION BY` 用于分组，类似于 `GROUP BY`。
- `ORDER BY` 用于排序。
- `ROWS BETWEEN ...` 用于定义窗口的范围。
二、常见的开窗函数
| 函数名称 | 功能描述 | 示例 |
| `ROW_NUMBER()` | 为每行分配一个唯一的序号 | `ROW_NUMBER() OVER (ORDER BY salary DESC)` |
| `RANK()` | 返回当前行在分区中的排名，相同值会并列 | `RANK() OVER (ORDER BY sales DESC)` |
| `DENSE_RANK()` | 返回当前行在分区中的排名，相同值不跳过 | `DENSE_RANK() OVER (ORDER BY sales DESC)` |
| `NTILE(n)` | 将数据分成n个桶，并分配相应的编号 | `NTILE(4) OVER (ORDER BY score DESC)` |
| `SUM()` | 对窗口内的数值求和 | `SUM(sales) OVER (PARTITION BY region)` |
| `AVG()` | 计算窗口内平均值 | `AVG(salary) OVER (PARTITION BY department)` |
| `MIN()` / `MAX()` | 找出窗口内的最小/最大值 | `MIN(order_date) OVER (PARTITION BY customer_id)` |
三、使用场景
| 场景 | 说明 |
| 排名统计 | 如销售排行榜、成绩排名等 |
| 滚动计算 | 如移动平均、累计求和等 |
| 分组分析 | 在保持原数据的基础上进行分组汇总 |
| 数据对比 | 比较当前行与前一行或后一行的数据差异 |
四、简单示例
假设有一个员工表 `employees`，包含字段：`employee_id`, `name`, `department`, `salary`。
示例1：按部门计算平均工资
```sql
SELECT
employee_id,
name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;
```
此查询会为每个员工显示其所在部门的平均工资，同时保留所有原始记录。
示例2：为员工分配排名
```sql
SELECT
employee_id,
name,
department,
salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
```
此查询会根据工资从高到低对员工进行排名。
五、总结
开窗函数是SQL中处理复杂查询的重要工具，能够帮助开发者在不丢失原始数据的情况下进行多种聚合操作。掌握这些函数可以显著提升查询效率和数据处理能力。通过合理使用 `PARTITION BY` 和 `ORDER BY`，可以灵活地控制窗口的范围和计算方式，满足各种业务需求。
| 关键点 | 说明 |
| 开窗函数 | 不减少行数，支持聚合计算 |
| 常用函数 | `ROW_NUMBER`, `RANK`, `AVG`, `SUM` 等 |
| 使用场景 | 排名、统计、对比、滚动计算 |
| 优势 | 保留原始数据，提高查询灵活性 |
通过不断练习和应用，你可以更熟练地使用SQL开窗函数，提升数据分析和报表开发的能力。