MySQL窗口函数
本文最后更新于：3 年前
引言
随着业务的不断发展，数据分析需求愈发复杂，传统聚合查询往往不能同时保留明细数据与聚合结果。MySQL 8.0 所提供的窗口函数（Window Functions），为此类分析型查询提供了全新的解决方案。通过定义 “窗口” 对数据分组、排序并限制帧范围，能在一条查询中同时实现聚合、排名、偏移访问等操作，而不会像传统聚合函数那样缩减结果集。本文将系统介绍窗口函数的原理、语法、常见用法与注意事项，并结合实际示例进行说明。
概述
MySQL 从 8.0 版本开始支持窗口函数，它允许对查询结果集中的每一行执行基于一组相关行（窗口）的计算。例如，可以计算累计和、排名、前后行数据的比较等。与普通聚合函数不同，窗口函数不会减少返回行数，而是为每一行附加一个计算结果。
使用场景
- 需要在保留原始行数据的同时计算统计信息（如累计求和、移动平均）。
- 进行数据排名、分位数划分等操作。
- 访问当前行之前或之后的某行数据（例如前后行的差值计算）。
核心概念
窗口（Window）
定义
一个 “窗口” 是由 OVER() 子句定义的一组行。窗口函数将在这个窗口范围内对数据进行计算。每一行都会根据其所在窗口返回一个计算结果。
关键点
- 窗口并不改变结果集的行数。
- 允许在同一查询中同时返回原始数据和计算结果。
分区（Partition）
定义
使用 PARTITION BY 子句将数据分割成多个分区，相当于对数据进行分组，每个分区内的行将独立参与窗口函数的计算。
示例
1 | |
这表示对每个部门（department）分别计算薪资总和。
排序（Order）
定义
在窗口函数的 OVER() 子句中，可以使用 ORDER BY 对每个分区内的行进行排序，这会影响某些窗口函数的计算结果，对于排名、偏移及帧计算非常关键。
示例
1 | |
在每个部门中，按照薪资从高到低排序，并为每一行分配一个行号。
帧规范（Frame Specification）
ROWS/RANGE BETWEEN，进一步定义在当前行的上下文中，窗口包含哪些行。
定义
窗口帧限定了在排序后的分区内，参与窗口函数计算的行的范围，即窗口包含了哪些行。MySQL 支持两种帧模式：
ROWS：基于物理行数进行界定。例如：
1
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW表示以当前行及其前一行为计算范围。
RANGE：基于排序列的值范围。例如：
1
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从分区开始到当前行所有符合排序条件的行。
边界说明
可以使用以下关键字来定义帧的边界：
- UNBOUNDED PRECEDING：从分区的第一行开始。
- N PRECEDING：前 N 行。
- CURRENT ROW：当前行。
- N FOLLOWING：后 N 行。
- UNBOUNDED FOLLOWING：到分区的最后一行。
语法
1 | |
- window_function：可以是聚合函数（如
SUM、AVG、COUNT、MIN、MAX）或其他专用窗口函数（如ROW_NUMBER()、RANK()、LAG()等）。 - expression：要计算的表达式或列。
- OVER 子句：
- **PARTITION BY
**：定义分区。 - **ORDER BY
[ASC|DESC]**：定义分区内的排序规则。 - **[ROWS BETWEEN <start> AND <end> | RANGE BETWEEN <start> AND <end>]**：定义窗口帧的范围。
- **PARTITION BY
常见的窗口函数
聚合函数
这些函数在窗口内计算聚合值，与普通聚合函数类似，但不会压缩行数。
SUM(column_name)
- 计算指定窗口内所有行指定列的总和。
- 在需要对数据集中的特定窗口（如某个时间段内、某个部门内）计算总和时使用。
AVG(column_name)
- 计算指定窗口内所有行指定列的平均值。
- 在需要对数据集中的特定窗口（如某个时间段内、某个部门内）计算平均值时使用。
MIN(column_name)
- 计算指定窗口内所有行指定列的最小值。
- 在需要找出数据集中特定窗口（如某个时间段内、某个部门内）的最小值时使用。
MAX(column_name)
- 计算指定窗口内所有行指定列的最大值。
- 在需要找出数据集中特定窗口（如某个时间段内、某个部门内）的最大值时使用。
COUNT(column_name)
- 计算指定窗口内所有行指定列的行数。
- 在需要计算数据集中特定窗口（如某个时间段内、某个部门内）的行数时使用。
VAR_POP(column_name)
- 计算指定窗口内所有行指定列的总体方差。
- 在需要分析数据集中特定窗口（如某个时间段内、某个部门内）数据的变异性时使用。
STDDEV_POP()
- 计算指定窗口内所有行指定列的总体标准差。
- 在需要分析数据集中特定窗口（如某个时间段内、某个部门内）数据的标准差，即变异性度量时使用。
排名函数
ROW_NUMBER()
- 为每个窗口分组内的行提供一个唯一的序列号，从 1 开始，如1、2、3、…
- 在需要对数据集中特定窗口（如某个时间段内、某个部门内）的行进行唯一编号时使用，常用于排序并提取排名。
RANK()
- 为指定窗口内的每一行分配一个排名。如果存在相同的值，它们会获得相同的排名，而下一个排名将跳过之前的重复排名数。
- 在需要对数据集中特定窗口（如某个时间段内、某个部门内）的行进行排名时使用，特别是当存在并列排名的情况时。
DENSE_RANK()
- 与
RANK()类似，但排名不会跳过任何排名数。 - 在需要对数据集中特定窗口（如某个时间段内、某个部门内）的行进行连续排名时使用，特别是当排名不应因并列而跳号时。
PERCENT_RANK()
- 计算当前行的百分比排名。这个排名是基于当前行在分组中的位置，公式为：(排名 - 1) / (总行数 - 1)。如果只有一行，结果是 0。
- 在需要确定某个值在整个数据集中的相对位置时使用，常用于数据分析，特别是当需要评估数据相对于其它数据的表现时。
CUME_DIST()
- 计算当前行的累积分布，这意味着计算有多少比例的值小于等于当前行的值。公式为：当前行之前的行数（包括当前行） / 总行数。
- 在需要确定某个值在整个数据集中的累积分布位置时使用，常用于统计分析，尤其是当需要评估一个值相对于集合的位置时。
NTILE()
- 将窗口内的行分成 n 个大致相等的组，并为每一行分配一个组号，从 1 到 n。如果窗口中的行数不能被 n 整除，那么前面的几个组将包含一个额外的行。
- 在需要将数据集分组进行分析时使用，如分位数分析、性能评级或将数据分成多个性能层次。
值获取函数
FIRST_VALUE(column_name)
- 返回窗口内第一行的指定列值。
- 在需要获取数据集中特定窗口（如某个时间段内、某个部门内）第一个记录的值时使用，常用于时间序列分析，或当需要参考某个范围内的起始值进行比较时。
LAST_VALUE(column_name)
- 返回窗口内最后一行的指定列值，使用时要注意定义正确的窗口帧（ROWS/RANGE BETWEEN），否则可能不会得到预期的结果。
- 在需要获取数据集中特定窗口（如某个时间段内、某个部门内）最后一个记录的值时使用，常用于时间序列分析，或当需要参考某个范围内的结束值进行比较时。
NTH_VALUE(column_name, n)
- 返回窗口内第 n 行的指定列值。
- 在需要获取数据集中特定窗口（如某个时间段内、某个部门内）特定位置的记录值时使用，常用于需要定位序列中特定点的数据分析。
位移类（偏移）函数
LAG(column_name [, offset, default_value])
- 返回当前行的指定列在窗口中前一行的值，可指定前
offset行的值并指定一个默认值作为返回。 - 在需要访问当前行之前的数据时使用，常用于时间序列数据分析、比较连续记录之间的变化，或计算增长率等。
LEAD(column_name [, offset, default_value])
- 返回当前行的指定列在窗口中后一行的值，可指定前后
offset行的值并指定一个默认值作为返回。 - 在需要访问当前行之后的数据时使用，常用于预测未来的数据点、比较连续记录之间的变化，或者在数据序列中提前反应趋势和模式。
示例说明
前置数据
创建城市 GDP 信息表，模拟插入若干数据。
1 | |
场景
查询 2022 年广东省各城市 GDP 与第一名城市 GDP 的差值。
SQL 示例：
1
2
3
4
5
6
7
8
9SELECT cg.year,
cg.province,
cg.city,
cg.gdp,
MAX(cg.gdp) OVER (PARTITION BY cg.province) AS max_gdp,
MAX(cg.gdp) OVER (PARTITION BY cg.province) - cg.gdp AS diff
FROM city_gdp cg
WHERE cg.province = '广东省'
AND cg.year = 2022;查询结果：
year province city gdp max_gdp diff 2022 广东省 广州市 28839.00 32387.68 3548.68 2022 广东省 深圳市 32387.68 32387.68 0.00 2022 广东省 佛山市 12698.39 32387.68 19689.29 2022 广东省 东莞市 11200.32 32387.68 21187.36 2022 广东省 惠州市 5401.24 32387.68 26986.44 2022 广东省 珠海市 4045.45 32387.68 28342.23 查询 2022 年各城市 GDP 在省份内的排名。
SQL 示例：
1
2
3
4
5
6
7SELECT cg.year,
cg.province,
cg.city,
cg.gdp,
RANK() OVER (PARTITION BY cg.province ORDER BY cg.gdp DESC) AS rk
FROM city_gdp cg
WHERE cg.year = 2022;查询结果：
year province city gdp rk 2022 广东省 深圳市 32387.68 1 2022 广东省 广州市 28839.00 2 2022 广东省 佛山市 12698.39 3 2022 广东省 东莞市 11200.32 4 2022 广东省 惠州市 5401.24 5 2022 广东省 珠海市 4045.45 6 2022 浙江省 杭州市 18753.00 1 2022 浙江省 宁波市 15704.30 2 2022 浙江省 温州市 8029.80 3 2022 浙江省 绍兴市 7351.00 4 2022 浙江省 嘉兴市 6739.45 5 2022 福建省 福州市 12308.23 1 2022 福建省 泉州市 12102.97 2 2022 福建省 厦门市 7802.70 3 查询 2022 年各城市 GDP 与第二名 GDP 的差值。
SQL 示例：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16SELECT cg.year,
cg.province,
cg.gdp,
cg.city,
NTH_VALUE(cg.gdp, 2)
OVER (
PARTITION BY cg.province
ORDER BY cg.gdp DESC
) AS sec_gdp,
cg.gdp - NTH_VALUE(cg.gdp, 2)
OVER (
PARTITION BY cg.province
ORDER BY cg.gdp DESC
) AS diff
FROM city_gdp cg
WHERE cg.year = 2022;查询结果：
year province gdp city sec_gdp diff 2022 广东省 32387.68 深圳市 null null 2022 广东省 28839.00 广州市 28839.00 0.00 2022 广东省 12698.39 佛山市 28839.00 -16140.61 2022 广东省 11200.32 东莞市 28839.00 -17638.68 2022 广东省 5401.24 惠州市 28839.00 -23437.76 2022 广东省 4045.45 珠海市 28839.00 -24793.55 2022 浙江省 18753.00 杭州市 null null 2022 浙江省 15704.30 宁波市 15704.30 0.00 2022 浙江省 8029.80 温州市 15704.30 -7674.50 2022 浙江省 7351.00 绍兴市 15704.30 -8353.30 2022 浙江省 6739.45 嘉兴市 15704.30 -8964.85 2022 福建省 12308.23 福州市 null null 2022 福建省 12102.97 泉州市 12102.97 0.00 2022 福建省 7802.70 厦门市 12102.97 -4300.27 可以看到查询结果中第一名所在行的
sec_gdp为null，这是因为NTH_VALUE()函数的默认行为只考虑从当前行往前到窗口开始的行，此时就需要调整OVER()子句中的窗口帧了，这里我们需要将其调整为从分区的第一行到最后一行，调整后 SQL 为1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18SELECT cg.year,
cg.province,
cg.gdp,
cg.city,
NTH_VALUE(cg.gdp, 2)
OVER (
PARTITION BY cg.province
ORDER BY cg.gdp DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS sec_gdp,
cg.gdp - NTH_VALUE(cg.gdp, 2)
OVER (
PARTITION BY cg.province
ORDER BY cg.gdp DESC
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS diff
FROM city_gdp cg
WHERE cg.year = 2022;查询结果：
year province gdp city sec_gdp diff 2022 广东省 32387.68 深圳市 28839.00 3548.68 2022 广东省 28839.00 广州市 28839.00 0.00 2022 广东省 12698.39 佛山市 28839.00 -16140.61 2022 广东省 11200.32 东莞市 28839.00 -17638.68 2022 广东省 5401.24 惠州市 28839.00 -23437.76 2022 广东省 4045.45 珠海市 28839.00 -24793.55 2022 浙江省 18753.00 杭州市 15704.30 3048.70 2022 浙江省 15704.30 宁波市 15704.30 0.00 2022 浙江省 8029.80 温州市 15704.30 -7674.50 2022 浙江省 7351.00 绍兴市 15704.30 -8353.30 2022 浙江省 6739.45 嘉兴市 15704.30 -8964.85 2022 福建省 12308.23 福州市 12102.97 205.26 2022 福建省 12102.97 泉州市 12102.97 0.00 2022 福建省 7802.70 厦门市 12102.97 -4300.27 获取每个年度每个城市的GDP与前一名的GDP差距。
SQL 示例：
1
2
3
4
5
6
7SELECT cg.year,
cg.city,
cg.province,
cg.gdp,
LAG(cg.gdp) OVER (PARTITION BY cg.year, cg.province ORDER BY cg.gdp DESC) AS pre_gdp,
cg.gdp - LAG(cg.gdp) OVER (PARTITION BY cg.year, cg.province ORDER BY cg.gdp DESC) AS diff
FROM city_gdp cg;查询结果：
year city province gdp pre_gdp diff 2022 深圳市 广东省 32387.68 null null 2022 广州市 广东省 28839.00 32387.68 -3548.68 2022 佛山市 广东省 12698.39 28839.00 -16140.61 2022 东莞市 广东省 11200.32 12698.39 -1498.07 2022 惠州市 广东省 5401.24 11200.32 -5799.08 2022 珠海市 广东省 4045.45 5401.24 -1355.79 2022 杭州市 浙江省 18753.00 null null 2022 宁波市 浙江省 15704.30 18753.00 -3048.70 2022 温州市 浙江省 8029.80 15704.30 -7674.50 2022 绍兴市 浙江省 7351.00 8029.80 -678.80 2022 嘉兴市 浙江省 6739.45 7351.00 -611.55 2022 福州市 福建省 12308.23 null null 2022 泉州市 福建省 12102.97 12308.23 -205.26 2022 厦门市 福建省 7802.70 12102.97 -4300.27 2023 深圳市 广东省 34606.40 null null 2023 广州市 广东省 30355.73 34606.40 -4250.67 2023 佛山市 广东省 13276.14 30355.73 -17079.59 2023 东莞市 广东省 11438.13 13276.14 -1838.01 2023 惠州市 广东省 5639.68 11438.13 -5798.45 2023 珠海市 广东省 4233.20 5639.68 -1406.48 2023 杭州市 浙江省 20058.98 null null 2023 宁波市 浙江省 16452.83 20058.98 -3606.15 2023 温州市 浙江省 8730.63 16452.83 -7722.20 2023 绍兴市 浙江省 7791.14 8730.63 -939.49 2023 嘉兴市 浙江省 7062.45 7791.14 -728.69 2023 福州市 福建省 12928.47 null null 2023 泉州市 福建省 12172.33 12928.47 -756.14 2023 厦门市 福建省 8066.49 12172.33 -4105.84
性能问题
性能开销
- 数据排序：窗口函数经常需要对数据进行排序，这可以是一个资源密集型的操作，尤其是当数据集非常大时。排序操作通常涉及内存和可能的磁盘 I/O，这会影响查询的执行时间。
- 分区处理：如果使用
PARTITION BY子句，数据库需要为每个分区执行函数，这可能导致处理时间增加，尤其是当有大量小分区时。 - 计算开销：某些窗口函数本身（如计算排名或计算移动平均）就需要额外的计算开销。特别是当每个窗口大小较大或窗口函数涉及复杂操作（例如标准差或线性回归）时，这些计算可能变得相当昂贵。
优化策略
- 合理的索引：对用于
ORDER BY和PARTITION BY的列建立索引可以显著提高窗口函数的性能，因为这可以加速排序和分区的数据检索过程。 - 减少数据量：在使用窗口函数之前通过过滤条件减少处理的数据量可以提高性能。
- 精简窗口规格：尽量使用精简的
ROWS或RANGE规格。例如，如果只需要访问前一行或后一行数据，使用LAG()或LEAD()而不是更广泛的窗口可能更有效。 - 避免冗余计算：如果可能，尝试重用已经计算过的结果，或者在应用层而不是数据库层面进行某些计算，尤其是在结果集较小的情况下。
使用场景
- 窗口函数虽然在处理复杂的分析查询时非常强大，但它们可能不适合实时查询或需要高度优化的系统，除非能够很好地控制查询条件和数据量。
- 在处理大规模数据、需要高性能和高可用性的场景下，处理数据分析应优先考虑交给应用层实现，应用层往往有并行处理、缓存优化、资源分配、负载均衡等更多优势，虽然可能会涉及更多的开发工作，但通常能为系统提供更好的总体性能和更高的灵活性。
总结
在 MySQL 8.0 中，窗口函数解决了 “既要聚合数据，又要保留明细” 的棘手问题。它通过 OVER() 子句使同一分组或排序下的多种计算（如排名、求和、移动平均、前后行比较等）在单条 SQL 中得以实现，大大减少了复杂分析场景下的开发成本。开发者需要充分理解窗口函数的机制并结合自身业务场景加以运用，以在数据库层面实现更灵活、高效的分析与统计处理，为数据驱动的决策和应用开发提供重要支撑。
本博客所有文章除特别声明外，均采用 CC BY-SA 4.0 协议 ，转载请注明出处！