SQL开窗函数(Window Function)是数据库查询中的一种强大工具,它允许你执行聚合计算,同时返回多个行。与传统的聚合函数不同,开窗函数可以在不改变原始数据集的情况下,为每一行返回一个结果。
以下是一些常见的SQL开窗函数:
1. **ROW_NUMBER()**:
- 为结果集中的每一行分配一个唯一的序号。
```sql
SELECT ROW_NUMBER() OVER (ORDER BY column1) AS row_num, column1, column2
FROM table_name;
```
2. **RANK()**:
- 为结果集中的每一行分配一个排名,相同值的行会得到相同的排名(排名可能存在空位)。
```sql
SELECT RANK() OVER (ORDER BY column1 DESC) AS rank, column1
FROM table_name;
```
3. **DENSE_RANK()**:
- 与RANK()类似,但在排名中不留下空位。
```sql
SELECT DENSE_RANK() OVER (ORDER BY column1 DESC) AS dense_rank, column1
FROM table_name;
```
4. **NTILE()**:
- 将结果集分为指定数量的桶(tile),并为每一行分配一个桶号。
```sql
SELECT NTILE(4) OVER (ORDER BY column1) AS quartile, column1
FROM table_name;
```
5. **LEAD()**:
- 返回当前行后一行的值。
```sql
SELECT LEAD(column1, 1, default_value) OVER (ORDER BY column2) AS next_value, column1
FROM table_name;
```
6. **LAG()**:
- 返回当前行前一行的值。
```sql
SELECT LAG(column1, 1, default_value) OVER (ORDER BY column2) AS prev_value, column1
FROM table_name;
```
7. **SUM()**:
- 计算从查询开始到当前行的总和。
```sql
SELECT SUM(column1) OVER (ORDER BY column2) AS cumulative_sum, column1
FROM table_name;
```
8. **AVG()**:
- 计算从查询开始到当前行的平均值。
```sql
SELECT AVG(column1) OVER (ORDER BY column2) AS moving_avg, column1
FROM table_name;
```
9. **MIN()** 和 **MAX()**:
- 分别计算从查询开始到当前行的最小值和最大值。
```sql
SELECT MIN(column1) OVER (ORDER BY column2) AS min_value, MAX(column1) OVER (ORDER BY column2) AS max_value
FROM table_name;
```
开窗函数通过`OVER()`子句定义了函数操作的数据窗口和排序方式。这些函数在数据分析和报告生成中非常有用,它们可以提供更丰富的数据洞察和更灵活的数据处理能力。