目标：MySQL 窗口函数
分析：
详解：
第一部分 简介
重要的话说三遍
从版本8.0开始，MySQL支持窗口函数。
从版本8.0开始，MySQL支持窗口函数。
从版本8.0开始，MySQL支持窗口函数。
第二部分 语法
1、特征
- 发生函数评估的行称为当前行。
- 与发生函数评估的当前行相关的查询行构成当前行的窗口。
2、语法
WINDOW window_name AS (window_spec)
[, window_name AS (window_spec)] ...
over_clause:
{OVER (window_spec) | OVER window_name}
window_spec:
[window_name] [partition_clause] [order_clause] [frame_clause]
partition_clause:
PARTITION BY expr [, expr] ...
order_clause:
ORDER BY expr [ASC|DESC] [, expr [ASC|DESC]] ...
frame_clause:
frame_units frame_extent
frame_units:
{ROWS | RANGE}
frame_extent:
{frame_start | frame_between}
frame_between:
BETWEEN frame_start AND frame_end
frame_start, frame_end: {
CURRENT ROW
| UNBOUNDED PRECEDING
| UNBOUNDED FOLLOWING
| expr PRECEDING
| expr FOLLOWING
}
3、示例
mysql> SELECT
year, country, product, profit,
SUM(profit) OVER() AS total_profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales
ORDER BY country, year, product, profit;
+------+---------+------------+--------+--------------+----------------+
| year | country | product | profit | total_profit | country_profit |
+------+---------+------------+--------+--------------+----------------+
| 2000 | Finland | Computer | 1500 | 7535 | 1610 |
| 2000 | Finland | Phone | 100 | 7535 | 1610 |
| 2001 | Finland | Phone | 10 | 7535 | 1610 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Calculator | 75 | 7535 | 1350 |
| 2000 | India | Computer | 1200 | 7535 | 1350 |
| 2000 | USA | Calculator | 75 | 7535 | 4575 |
| 2000 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | Calculator | 50 | 7535 | 4575 |
| 2001 | USA | Computer | 1200 | 7535 | 4575 |
| 2001 | USA | Computer | 1500 | 7535 | 4575 |
| 2001 | USA | TV | 100 | 7535 | 4575 |
| 2001 | USA | TV | 150 | 7535 | 4575 |
+------+---------+------------+--------+--------------+----------------+
考虑这个查询，它多次定义同一个窗口：
SELECT
val,
ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
RANK() OVER (ORDER BY val) AS 'rank',
DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM numbers;
通过使用 一次定义窗口并在 子句 WINDOW中按名称引用窗口，可以更简单地编写查询
SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
第二部分 窗口函数介绍
| 名称 | 描述 |
|---|---|
CUME_DIST() | 累计分配值 |
DENSE_RANK() | 当前行在其分区内的排名，没有间隙 |
FIRST_VALUE() | 窗口框架第一行的参数值 |
LAG() | 来自分区内滞后当前行的行的参数值 |
LAST_VALUE() | 窗口框架最后一行的参数值 |
LEAD() | 分区内行前导当前行的参数值 |
NTH_VALUE() | 来自第 N 行窗口框架的参数值 |
NTILE() | 其分区内当前行的桶数。 |
PERCENT_RANK() | 百分比排名值 |
RANK() | 当前行在其分区内的排名，有间隙 |
ROW_NUMBER() | 其分区内的当前行数 |
将上述函数按照功能划分，可以把MySQL支持的窗口函数分为如下几类：
- 序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()
- 分布函数：PERCENT_RANK()、CUME_DIST()
- 前后函数：LAG()、LEAD()
- 头尾函数：FIRST_VALUE()、LAST_VALUE()、NTH_VALUE()
- 其他函数：NTILE()
1、序号函数 ROW_NUMBER()、RANK()、DENSE_RANK()
- ROW_NUMBER：没有重复值的排序，排序：1，2，3
- RANK ：跳跃排序，排序：1，1，3
- DENSE_RANK ：连续排序，排序：1，1，2
mysql> SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
RANK() OVER w AS 'rank',
DENSE_RANK() OVER w AS 'dense_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
+------+------------+------+------------+
| val | row_number | rank | dense_rank |
+------+------------+------+------------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 1 | 1 |
| 2 | 3 | 3 | 2 |
| 3 | 4 | 4 | 3 |
| 3 | 5 | 4 | 3 |
| 3 | 6 | 4 | 3 |
| 4 | 7 | 7 | 4 |
| 4 | 8 | 7 | 4 |
| 5 | 9 | 9 | 5 |
+------+------------+------+------------+
使用场景：
- 用途：显示分区中的当前行号
- 使用场景：
- 组内排名-排名问题：每个部门按业绩排名
- 组内排名-topN问题：找出每个部门排名前N的员工
2、分布函数：PERCENT_RANK()、CUME_DIST()
- PERCENT_RANK : 百分比排名值 (rank - 1) / (rows - 1)
- CUME_DIST : 计算某个值在一组有序的数据中累计的分布
- 相对位置/总行数。
- 对于重复值，计算的时候，取重复值的最后一行位置。
((统计的列的种类)/(统计的列的总数))* 每个重复种类中的个数
分组内小于、等于当前rank值的行数 / 分组内总行数
cumulative 累计的; 累积的; (在力量或重要性方面)聚积的，积累的，渐增的;
distribution 分配; 分布;
mysql> SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
CUME_DIST() OVER w AS 'cume_dist',
PERCENT_RANK() OVER w AS 'percent_rank'
FROM numbers
WINDOW w AS (ORDER BY val);
+------+------------+--------------------+--------------+
| val | row_number | cume_dist | percent_rank |
+------+------------+--------------------+--------------+
| 1 | 1 | 0.2222222222222222 | 0 |
| 1 | 2 | 0.2222222222222222 | 0 |
| 2 | 3 | 0.3333333333333333 | 0.25 |
| 3 | 4 | 0.6666666666666666 | 0.375 |
| 3 | 5 | 0.6666666666666666 | 0.375 |
| 3 | 6 | 0.6666666666666666 | 0.375 |
| 4 | 7 | 0.8888888888888888 | 0.75 |
| 4 | 8 | 0.8888888888888888 | 0.75 |
| 5 | 9 | 1 | 1 |
+------+------------+--------------------+--------------+
使用场景：
- 应用场景：cume_dist 大于等于当前订单金额的订单比例有多少。
3、前后函数：LAG()、LEAD()
- LAG ：LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值，默认1。
- LEAD ：LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值，默认1。
lags：滞后;落后于;
leads: 领先地位;超前量;
mysql> SELECT
t, val,
LAG(val) OVER w AS 'lag',
LEAD(val) OVER w AS 'lead',
val - LAG(val) OVER w AS 'lag diff',
val - LEAD(val) OVER w AS 'lead diff'
FROM series
WINDOW w AS (ORDER BY t);
+----------+------+------+------+----------+-----------+
| t | val | lag | lead | lag diff | lead diff |
+----------+------+------+------+----------+-----------+
| 12:00:00 | 100 | NULL | 125 | NULL | -25 |
| 13:00:00 | 125 | 100 | 132 | 25 | -7 |
| 14:00:00 | 132 | 125 | 145 | 7 | -13 |
| 15:00:00 | 145 | 132 | 140 | 13 | 5 |
| 16:00:00 | 140 | 145 | 150 | -5 | -10 |
| 17:00:00 | 150 | 140 | 200 | 10 | -50 |
| 18:00:00 | 200 | 150 | NULL | 50 | NULL |
+----------+------+------+------+----------+-----------+
mysql> SELECT
n,
LAG(n, 1, 0) OVER w AS 'lag',
LEAD(n, 1, 0) OVER w AS 'lead',
n + LAG(n, 1, 0) OVER w AS 'next_n',
n + LEAD(n, 1, 0) OVER w AS 'next_next_n'
FROM fib
WINDOW w AS (ORDER BY n);
+------+------+------+--------+-------------+
| n | lag | lead | next_n | next_next_n |
+------+------+------+--------+-------------+
| 1 | 0 | 1 | 1 | 2 |
| 1 | 1 | 2 | 2 | 3 |
| 2 | 1 | 3 | 3 | 5 |
| 3 | 2 | 5 | 5 | 8 |
| 5 | 3 | 8 | 8 | 13 |
| 8 | 5 | 0 | 13 | 8 |
+------+------+------+--------+-------------+
使用场景：
- 用途：分区中位于当前行前n行（lead）/后n行(lag)的记录值。
- 使用场景：查询上一个订单距离当前订单的时间间隔。
4、头尾函数：FIRST_VALUE()、LAST_VALUE()
- FIRST_VALUE ：返回组中数据窗口的第一个值。
- LAST_VALUE ：返回组中数据窗口的最后一个值。
- NTH_VALUE : 返回组中数据窗口的第n个值。
mysql> SELECT
time, subject, val,
FIRST_VALUE(val) OVER w AS 'first',
LAST_VALUE(val) OVER w AS 'last',
NTH_VALUE(val, 2) OVER w AS 'second',
NTH_VALUE(val, 4) OVER w AS 'fourth'
FROM observations
WINDOW w AS (PARTITION BY subject ORDER BY time
ROWS UNBOUNDED PRECEDING);
+----------+---------+------+-------+------+--------+--------+
| time | subject | val | first | last | second | fourth |
+----------+---------+------+-------+------+--------+--------+
| 07:00:00 | st113 | 10 | 10 | 10 | NULL | NULL |
| 07:15:00 | st113 | 9 | 10 | 9 | 9 | NULL |
| 07:30:00 | st113 | 25 | 10 | 25 | 9 | NULL |
| 07:45:00 | st113 | 20 | 10 | 20 | 9 | 20 |
| 07:00:00 | xh458 | 0 | 0 | 0 | NULL | NULL |
| 07:15:00 | xh458 | 10 | 0 | 10 | 10 | NULL |
| 07:30:00 | xh458 | 5 | 0 | 5 | 10 | NULL |
| 07:45:00 | xh458 | 30 | 0 | 30 | 10 | 30 |
| 08:00:00 | xh458 | 25 | 0 | 25 | 10 | 30 |
+----------+---------+------+-------+------+--------+--------+
FIRST_VALUE、LAST_VALUE 使用场景：
- 用途：得到分区中的第一个/最后一个指定参数的值。
- 使用场景：查询截止到当前订单，按照日期排序第一个订单和最后一个订单的订单金额。 NTH_VALUE 使用场景：
- 用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。
- 应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额。
5、其他函数：NTILE()
- RANK ：将数据平均分配成n组。
mysql> SELECT
val,
ROW_NUMBER() OVER w AS 'row_number',
NTILE(2) OVER w AS 'ntile2',
NTILE(4) OVER w AS 'ntile4'
FROM numbers
WINDOW w AS (ORDER BY val);
+------+------------+--------+--------+
| val | row_number | ntile2 | ntile4 |
+------+------------+--------+--------+
| 1 | 1 | 1 | 1 |
| 1 | 2 | 1 | 1 |
| 2 | 3 | 1 | 1 |
| 3 | 4 | 1 | 2 |
| 3 | 5 | 1 | 2 |
| 3 | 6 | 2 | 3 |
| 4 | 7 | 2 | 3 |
| 4 | 8 | 2 | 4 |
| 5 | 9 | 2 | 4 |
+------+------------+--------+--------+
使用场景：
- 用途：将分区中的有序数据分为n个桶，记录桶号。
- 应用场景：将每个用户的订单按照订单金额分成3组。
第三部分 聚合函数
- SUM ：求和
- MIN ：最小值
- MAX ：最大值
- AVG ：平均值
- COUNT ：计数
- 用途：在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。
- 应用场景：每个用户按照订单id，截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少？
第四部分 窗口函数优化
对于EXPLAIN，窗口执行计划信息过于广泛，无法以传统的输出格式显示。要查看窗口信息，请使用 EXPLAIN FORMAT=JSON并查找 windowing元素。
