窗口函数

本文最后更新于：2025年3月1日凌晨

窗口函数

窗口函数是MySQL8.0版本新增的，相对于聚合函数把所有行聚合成一行，窗口函数可以将每一行的数据进行分别处理。

一、序号函数

用来实现分组排序。

# 格式
row_number() | rank() | dense_rank() over (
	partition by ... # 按什么字段分组/分区
	order by ... # 按照什么排序
)

例如：成绩表Score，序号函数皆以此表查询为例。

| id | score |
| -- | ----- |
| 1  | 3.5   |
| 2  | 3.65  |
| 3  | 4     |
| 4  | 3.85  |
| 5  | 4     |
| 6  | 3.65  |

1.1、ROW_NUMBER()

此函数进行排序时，相同的字段会分开排序。

查询成绩表，按分数进行排序，相同分数的不算在同一名（分开排）

如果不指定 PARTITION BY，窗口函数默认会将整个结果集视为一个分组，即不进行分组【全局排序】，计算基于所有行。

SELECT 
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS `rank`
FROM Scores
ORDER BY score DESC;

结果：

| score | rank |
| ----- | ---- |
| 4     | 1    |  # <<< 看这两个相同的成绩对应的排序
| 4     | 2    |  # <<< 看这两个相同的成绩对应的排序
| 3.85  | 3    |
| 3.65  | 4    |
| 3.65  | 5    |
| 3.5   | 6    |

1.2、RANK()

此函数排序时，相同的字段会排在同一名，之后的字段排名为：当前字段排名+当前排名字段总数-1

SELECT 
    score,
    RANK() OVER (ORDER BY score DESC) AS `rank`
FROM Scores
ORDER BY score DESC;

结果：

| score | rank |
| ----- | ---- |
| 4     | 1    |  # <<< 看这两个相同的成绩对应的排序
| 4     | 1    |  # <<< 看这两个相同的成绩对应的排序
| 3.85  | 3    |
| 3.65  | 4    |
| 3.65  | 4    |
| 3.5   | 6    |

1.3、DENSE_RANK()

此函数排序时，相同的字段会排在同一名，之后的字段不会受当前字段重复数影响，直接就是当前排名+1

SELECT 
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS `rank`
FROM Scores
ORDER BY score DESC;

结果：

| score | rank |
| ----- | ---- |
| 4     | 1    |  # <<< 看这两个相同的成绩对应的排序
| 4     | 1    |  # <<< 看这两个相同的成绩对应的排序
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |
| 3.5   | 4    |

放在一起对比：

SELECT 
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS `row_number`,
    RANK() OVER (ORDER BY score DESC) AS `rank`,
    DENSE_RANK() OVER (ORDER BY score DESC) AS `dense_rank`
FROM Scores
ORDER BY score DESC;

结果：

| score | row_number | rank | dense_rank |
| ----- | ---------- | ---- | ---------- |
| 4     | 1          | 1    | 1          |  # <<< 看这两个相同的成绩对应的排序
| 4     | 2          | 1    | 1          |  # <<< 看这两个相同的成绩对应的排序
| 3.85  | 3          | 3    | 2          |
| 3.65  | 4          | 4    | 3          |
| 3.65  | 5          | 4    | 3          |
| 3.5   | 6          | 6    | 4          |

思考：如果现在需要找出排名前3的成绩，该怎么做呢？（以dense_rank()排序）

SELECT score,`rank`
FROM (
         SELECT
             score,
             DENSE_RANK() OVER (ORDER BY score DESC) AS `rank`
         FROM Scores
         ORDER BY score DESC
     ) as temp
WHERE temp.rank<=3;

结果：

| score | rank |
| ----- | ---- |
| 4     | 1    |  # <<< 看这两个相同的成绩对应的排序
| 4     | 1    |  # <<< 看这两个相同的成绩对应的排序
| 3.85  | 2    |
| 3.65  | 3    |
| 3.65  | 3    |

二、分布函数

应用场景：查询小于等于当前薪资的比例。

2.1、PERCENT_RANK() 【不常用】

在以下场景中 PERCENT_RANK() 非常有用：

百分位数计算：

当需要将数据划分为百分位数（如 90th percentile）时，PERCENT_RANK() 可以帮助确定每个值的相对位置。

数据分布分析：

通过 PERCENT_RANK() 可以快速了解数据在整个数据集中的分布情况。

性能评估：

在评估学生成绩、员工绩效等场景中，PERCENT_RANK() 可以直观地反映某个人在整体中的表现。

数据标准化：

在机器学习或统计分析中，PERCENT_RANK() 可以用于将数据标准化到 [0, 1] 范围内。

此函数基于排名的相对位置，公式为 (rank - 1) / (总行数 - 1)。

SELECT
    dname,
    salary,
    rank() OVER (partition by dname ORDER BY salary) AS `rank`,
    PERCENT_RANK() OVER (partition by dname ORDER BY salary) AS `percent_rank`
FROM user

结果为：

| dname | salary | rank | percent_rank |
| ----- | ------ | ---- | ------------ | /* 研发部总计4项*/
| 研发部 | 5000   | 1    | 0            | /* 0 = (1-1)/(4-1) */
| 研发部 | 5000   | 1    | 0            |
| 研发部 | 6000   | 3    | 0.6666666666 | /* 0.66 = (3-1)/(4-1) */
| 研发部 | 8000   | 4    | 1            |
| 销售部 | 3000   | 1    | 0            |
| 销售部 | 3000   | 1    | 0            |
| 销售部 | 3000   | 1    | 0            |
| 销售部 | 4000   | 4    | 1            |

2.2、CUME_DIST()

此函数用于计算累积分布值，表示在有序数据集中，某一项的值小于或等于当前值的比例。

正序（默认）：统计每一项内，小于或等于此项的值与总数的比值。

公式为：

CUME_DIST(x)=小于等于 x 的行数总行数CUME_DIST(x)=总行数小于等于 x 的行数
倒序：统计每一项内，大于或等于此项的值与总数的比值。

公式为：

CUME_DIST(x)=大于等于 x 的行数总行数CUME_DIST(x)=总行数大于等于 x 的行数

应用场景：

SELECT
    salary,
    cume_dist() OVER (partition by dname ORDER BY salary) AS `cume_dist`
FROM user

结果：

| salary | cume_dist |
| ------ | --------- |
| 5000   | 0.5       |
| 5000   | 0.5       |
| 6000   | 0.75      |
| 8000   | 1         |
| 3000   | 0.75 	 |
| 3000   | 0.75 	 |
| 3000   | 0.75		 |
| 4000   | 1 		 |

三、前后函数

用途：返回位于当前行的前n行或后n行的expr的值

3.1、`LAG()` 函数

LAG() 函数用于访问当前行之前的某一行的数据。

语法：

1	`LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)`

column_name：要访问的列。
offset：偏移量，表示要访问的行数（默认为 1，即前一行）。
default_value：如果偏移量超出范围（如第一行没有前一行），则返回的默认值（可选，默认为 NULL）。

应用场景：

计算当前行与前一行的差值。
分析时间序列数据中的变化趋势。

示例：

假设有一组每日销售数据，需要计算每天的销售额与前一天的差值。

SELECT
    sales_date,
    sales_amount,
    LAG(sales_amount, 1, 0) OVER (ORDER BY sales_date) AS previous_sales_amount,
    sales_amount - LAG(sales_amount, 1, 0) OVER (ORDER BY sales_date) AS sales_difference
FROM
    daily_sales;

结果可能如下：

sales_date	sales_amount	previous_sales_amount	sales_difference
2023-01-01	100	0	100
2023-01-02	150	100	50
2023-01-03	200	150	50
2023-01-04	180	200	-20

3.2、`LEAD()` 函数

LEAD() 函数用于访问当前行之后的某一行的数据。

语法：

1	`LEAD(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)`

column_name：要访问的列。
offset：偏移量，表示要访问的行数（默认为 1，即后一行）。
default_value：如果偏移量超出范围（如最后一行没有后一行），则返回的默认值（可选，默认为 NULL）。

应用场景：

计算当前行与后一行的差值。
预测或分析未来趋势。

示例：

假设有一组每日销售数据，需要计算每天的销售额与后一天的差值。

SQLSELECT
    sales_date,
    sales_amount,
    LEAD(sales_amount, 1, 0) OVER (ORDER BY sales_date) AS next_sales_amount,
    LEAD(sales_amount, 1, 0) OVER (ORDER BY sales_date) - sales_amount AS sales_difference
FROM
    daily_sales;

结果可能如下：

sales_date	sales_amount	next_sales_amount	sales_difference
2023-01-01	100	150	50
2023-01-02	150	200	50
2023-01-03	200	180	-20
2023-01-04	180	0	-180

3.3、`LAG()` 和 `LEAD()` 的区别

函数	方向	作用
`LAG()`	向后	访问当前行之前的某一行的数据。
`LEAD()`	向前	访问当前行之后的某一行的数据。

3.4、常见应用场景

时间序列分析：
- 计算每日、每月或每年的变化趋势。
- 例如：计算每天的销售额与前一天的差值。
相邻行比较：
- 比较当前行与相邻行的值，分析变化或异常。
- 例如：检查库存变化或温度波动。
预测或回填数据：
- 使用 LEAD() 预测未来值，或使用 LAG() 回填历史值。
数据填充：
- 对于缺失值，可以使用 LAG() 或 LEAD() 从相邻行填充。

总结

LAG() 用于访问当前行之前的某一行的数据，适合分析历史趋势或回填数据。
LEAD() 用于访问当前行之后的某一行的数据，适合预测未来趋势或分析未来变化。
两者在时间序列分析、相邻行比较和数据填充等场景中非常有用。如果需要分析数据的变化趋势或预测未来值，LAG() 和 LEAD() 是强大的工具。

四、头尾函数

用于在窗口框架内访问第一行或最后一行的数据。它们通常用于计算累积值、分析趋势或提取边界值。

4.1、`FIRST_VALUE()` 函数

FIRST_VALUE() 函数返回窗口框架内第一行的值。

语法：

1	`FIRST_VALUE(column_name) OVER (PARTITION BY ... ORDER BY ... [ROWS BETWEEN ...])`

column_name：要访问的列。
PARTITION BY：可选，用于分组。
ORDER BY：指定窗口内的排序。
ROWS BETWEEN：可选，定义窗口框架的范围（默认为 UNBOUNDED PRECEDING AND CURRENT ROW）。

应用场景：

计算累积值（如累积销售额）。
提取每组中的第一个值。

示例：

假设有一组每日销售数据，需要计算每天的销售额与第一天的销售额的差值。

SELECT
    sales_date,
    sales_amount,
    FIRST_VALUE(sales_amount) OVER (ORDER BY sales_date) AS first_sales_amount,
    sales_amount - FIRST_VALUE(sales_amount) OVER (ORDER BY sales_date) AS sales_difference
FROM
    daily_sales;

结果可能如下：

sales_date	sales_amount	first_sales_amount	sales_difference
2023-01-01	100	100	0
2023-01-02	150	100	50
2023-01-03	200	100	100
2023-01-04	180	100	80

4.2、`LAST_VALUE()` 函数

LAST_VALUE() 函数返回窗口框架内最后一行的值。

语法：

1	`LAST_VALUE(column_name) OVER (PARTITION BY ... ORDER BY ... [ROWS BETWEEN ...])`

column_name：要访问的列。
PARTITION BY：可选，用于分组。
ORDER BY：指定窗口内的排序。
ROWS BETWEEN：可选，定义窗口框架的范围（默认为 UNBOUNDED PRECEDING AND CURRENT ROW）。

注意：

默认情况下，LAST_VALUE() 的窗口框架是 UNBOUNDED PRECEDING AND CURRENT ROW，这意味着它只会返回当前行之前的值。为了获取整个窗口的最后一个值，需要将窗口框架设置为 UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING。

应用场景：

计算每组中的最后一个值。
分析趋势或提取边界值。

示例：

假设有一组每日销售数据，需要计算每天的销售额与最后一天的销售额的差值。

SQLSELECT
    sales_date,
    sales_amount,
    LAST_VALUE(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_sales_amount,
    LAST_VALUE(sales_amount) OVER (ORDER BY sales_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - sales_amount AS sales_difference
FROM
    daily_sales;

结果可能如下：

sales_date	sales_amount	last_sales_amount	sales_difference
2023-01-01	100	180	80
2023-01-02	150	180	30
2023-01-03	200	180	-20
2023-01-04	180	180	0

4.3、`FIRST_VALUE()` 和 `LAST_VALUE()` 的区别

函数	作用	默认窗口框架
`FIRST_VALUE()`	返回窗口框架内第一行的值。	`UNBOUNDED PRECEDING AND CURRENT ROW`
`LAST_VALUE()`	返回窗口框架内最后一行的值。	`UNBOUNDED PRECEDING AND CURRENT ROW`

4.4、常见应用场景

累积值计算：
- 使用 FIRST_VALUE() 计算从开始到当前行的累积值。
- 使用 LAST_VALUE() 计算从当前行到结束的累积值。
趋势分析：
- 使用 FIRST_VALUE() 和 LAST_VALUE() 分析数据的起始和结束趋势。
边界值提取：
- 使用 FIRST_VALUE() 提取每组中的第一个值。
- 使用 LAST_VALUE() 提取每组中的最后一个值。
数据标准化：
- 使用 FIRST_VALUE() 和 LAST_VALUE() 将数据标准化到某个范围。

4.5、总结

FIRST_VALUE() 用于访问窗口框架内的第一个值，适合计算累积值或提取起始值。
LAST_VALUE() 用于访问窗口框架内的最后一个值，适合分析趋势或提取边界值。
两者在累积值计算、趋势分析和边界值提取等场景中非常有用。如果需要分析数据的起始或结束趋势，FIRST_VALUE() 和 LAST_VALUE() 是强大的工具。

五、其他函数

5.1、`NTH_VALUE()` 函数

NTH_VALUE() 函数用于返回窗口框架内第 N 行的值。

语法：

1	`NTH_VALUE(column_name, N) OVER (PARTITION BY ... ORDER BY ... [ROWS BETWEEN ...])`

column_name：要访问的列。
N：指定要返回的行号（例如，1 表示第一行，2 表示第二行，依此类推）。
PARTITION BY：可选，用于分组。
ORDER BY：指定窗口内的排序。
ROWS BETWEEN：可选，定义窗口框架的范围（默认为 UNBOUNDED PRECEDING AND CURRENT ROW）。

应用场景：

提取窗口框架内指定行的值。
分析数据集中特定位置的值。

示例：

假设有一组每日销售数据，需要获取每天销售额的第二个值。

SELECT
    sales_date,
    sales_amount,
    NTH_VALUE(sales_amount, 2) OVER (ORDER BY sales_date) AS second_sales_amount
FROM
    daily_sales;

结果可能如下：

sales_date	sales_amount	second_sales_amount
2023-01-01	100	NULL
2023-01-02	150	150
2023-01-03	200	150
2023-01-04	180	150

注意：如果窗口框架内没有第 N 行，NTH_VALUE() 会返回 NULL。

5.2、`NTILE()` 函数

NTILE() 函数用于将数据分区划分为指定数量的桶（bucket），并为每一行分配一个桶号。

语法：

1	`NTILE(N) OVER (PARTITION BY ... ORDER BY ...)`

N：指定要划分的桶数。
PARTITION BY：可选，用于分组。
ORDER BY：指定窗口内的排序。

应用场景：

将数据划分为百分位数、四分位数等。
数据分箱（binning）或分组。

示例：

假设有一组学生成绩数据，需要将成绩分为 4 个等级（四分位数）。

SELECT
    student_id,
    score,
    NTILE(4) OVER (ORDER BY score) AS quartile
FROM
    student_scores;

结果可能如下：

student_id	score	quartile
1	50	1
2	60	1
3	70	2
4	80	3
5	90	4

5.3、`NTH_VALUE()` 和 `NTILE()` 的区别

函数	作用	主要用途
`NTH_VALUE()`	返回窗口框架内第 N 行的值。	提取指定位置的值。
`NTILE()`	将数据划分为 N 个桶并分配桶号。	数据分箱、百分位数或分组。

5.4、常见应用场景

`NTH_VALUE()` 的应用场景

提取特定位置的值：
- 例如，获取某个月的第二高销售额或第三低温度。
分析数据集中特定位置的值：
- 例如，找到某个时间段的中间值。
数据填充或回填：
- 例如，用第 N 行的值填充缺失值。

`NTILE()` 的应用场景

百分位数计算：
- 例如，将成绩分为 100 个百分位数。
数据分箱（Binning）：
- 例如，将销售额分为高、中、低三组。
分组分析：
- 例如，将用户活跃度分为 5 个等级。

5.5、总结

NTH_VALUE()：用于提取窗口框架内第 N 行的值，适合分析特定位置的数据。
NTILE()：用于将数据划分为指定数量的桶，适合数据分箱、百分位数计算或分组分析。

如果需要提取特定位置的值，使用 NTH_VALUE()；如果需要将数据划分为多个组或桶，使用 NTILE()。两者在数据分析和处理中都非常有用，但应用场景不同。

六、开窗聚合函数

以此数据表为例：user

| id    | dname | ename  | salary |
| ----- | ----- | -----  | ------ |
| 1     | 研发部 | 张三    | 5000   |  
| 2     | 研发部 | 李四    | 6000   |  
| 3     | 研发部 | 王五    | 5000   | 
| 4     | 研发部 | 赵四    | 8000   |  
| 5     | 销售部 | 卡尔    | 3000   | 
| 6     | 销售部 | 可可    | 3000   | 
| 7     | 销售部 | 丽丽    | 4000   | 
| 8     | 销售部 | 楠楠    | 3000   |

6.1、SUM()聚合函数 + 开窗函数

SELECT
    salary,
    sum(salary) over (order by id desc) as `total`
FROM user
ORDER BY id DESC;

结果：每行都是把之前的所有行加起来求和

| salary | total  |
| ------ | ------ |
| 3000   | 3000   |  
| 4000   | 7000   |  /* 7000 = 3000 + 4000 */
| 3000   | 10000  |  /* 10000 = 7000 + 3000 */
| 3000   | 13000  |  /* 以此类推 */
| 8000   | 21000  |  
| 5000   | 26000  |  
| 6000   | 32000  |  
| 5000   | 37000  |

如果不加order by，每一行都会显示与排序字段相同的所有行求和的最终值，可以用来对比个人与总体的数据。【这次以部门分组，这样后面的求和就是此人所在组的总值】

SELECT
    dname,
    salary,
    sum(salary) over (PARTITION BY dname) as `sum`
FROM user

结果：

| dname | salary | sum    |
| ----- | -----  | ------ |
| 研发部 | 5000   | 24000  |  
| 研发部 | 6000   | 24000  |  
| 研发部 | 5000   | 24000  | 
| 研发部 | 8000   | 24000  |  
| 销售部 | 3000   | 10000  | 
| 销售部 | 3000   | 10000  | 
| 销售部 | 4000   | 10000  | 
| 销售部 | 3000   | 10000  |

还可以指定求和范围：

rows between unbounded preceding and current row 这个就是从起始行到当前行

rows between 3 preceding and current row 这个就是从我的前3行开始到当前行

rows between 3 preceding and 1 following 这个就是从我的前3行开始到我的后一行

rows between current and unbounded following 这个就是从当前行到最后一行

SELECT
    dname,
    salary,
    sum(salary) over (partition by dname order by dname rows between unbounded preceding and current row) as `sum`
FROM user

结果：

| dname | salary | sum    |
| ----- | -----  | ------ |
| 研发部 | 5000   | 5000   |  
| 研发部 | 6000   | 11000  |  
| 研发部 | 5000   | 16000  | 
| 研发部 | 8000   | 24000  |  
| 销售部 | 3000   | 30000  | 
| 销售部 | 3000   | 60000  | 
| 销售部 | 4000   | 10000  | 
| 销售部 | 3000   | 13000  |

6.2、AVG()聚合函数 + 开窗函数

SELECT
    salary,
    avg(salary) over (order by id desc) as `avg`
FROM user
ORDER BY id DESC;

结果：每行都是把之前的所有行加起来求平均值

| salary | avg       |
| ------ | --------- |
| 3000   | 3000.0000 | 
| 4000   | 3500.0000 |  /* 7000/2  ## (7000 = 4000 + 3000) */
| 3000   | 3333.3333 |  /* 10000/3 ## (10000 = 7000 + 3000) */
| 3000   | 3250.0000 |  /* 以此类推 */
| 8000   | 4200.0000 |  
| 5000   | 4333.3333 |  
| 6000   | 4571.4286 |  
| 5000   | 4625.0000 |

6.3、MIN()聚合函数 + 开窗函数

SELECT
    salary,
    min(salary) over (order by id desc) as `min`
FROM user
ORDER BY id DESC;

结果：每一行都与之前的所有行相比得出最小值

| salary |  min  |
| ------ | ----- |
| 3000   | 3000  | 
| 4000   | 3000  |  
| 3000   | 3000  | 
| 3000   | 3000  | 
| 8000   | 3000  |  
| 5000   | 3000  |  
| 6000   | 3000  | 
| 5000   | 3000  |

6.4、MAX()聚合函数 + 开窗函数

SELECT
    salary,
    max(salary) over (order by id desc) as `max`
FROM user
ORDER BY id DESC;

结果：每一行都与之前的所有行相比得出最大值

| salary |  max  |
| ------ | ----- |
| 3000   | 3000  | 
| 4000   | 4000  |  
| 3000   | 4000  | 
| 3000   | 4000  |  
| 8000   | 8000  |  
| 5000   | 8000  |  
| 6000   | 8000  | 
| 5000   | 8000  |

数据库

#MySQL

窗口函数

https://superlovelace.top/2025/02/27/窗口函数/

作者

棱境

发布于

2025年2月27日

更新于

2025年3月1日

许可协议

数据库分区上一篇

idea插件推荐下一篇

窗口函数

窗口函数

一、序号函数

1.1、ROW_NUMBER()

1.2、RANK()

1.3、DENSE_RANK()

二、分布函数

2.1、PERCENT_RANK() 【不常用】

2.2、CUME_DIST()

三、前后函数

3.1、LAG() 函数

语法：

应用场景：

示例：

3.2、LEAD() 函数

语法：

应用场景：

示例：

3.3、LAG() 和 LEAD() 的区别

3.4、常见应用场景

总结

四、头尾函数

4.1、FIRST_VALUE() 函数

语法：

应用场景：

示例：

4.2、LAST_VALUE() 函数

语法：

注意：

应用场景：

示例：

4.3、FIRST_VALUE() 和 LAST_VALUE() 的区别

4.4、常见应用场景

4.5、总结

五、其他函数

5.1、NTH_VALUE() 函数

语法：

应用场景：

示例：

5.2、NTILE() 函数

语法：

应用场景：

示例：

5.3、NTH_VALUE() 和 NTILE() 的区别

5.4、常见应用场景

NTH_VALUE() 的应用场景

NTILE() 的应用场景

5.5、总结

六、开窗聚合函数

6.1、SUM()聚合函数 + 开窗函数

6.2、AVG()聚合函数 + 开窗函数

6.3、MIN()聚合函数 + 开窗函数

6.4、MAX()聚合函数 + 开窗函数

3.1、`LAG()` 函数

3.2、`LEAD()` 函数

3.3、`LAG()` 和 `LEAD()` 的区别

4.1、`FIRST_VALUE()` 函数

4.2、`LAST_VALUE()` 函数

4.3、`FIRST_VALUE()` 和 `LAST_VALUE()` 的区别

5.1、`NTH_VALUE()` 函数

5.2、`NTILE()` 函数

5.3、`NTH_VALUE()` 和 `NTILE()` 的区别

`NTH_VALUE()` 的应用场景

`NTILE()` 的应用场景