MySQL 官方文档:https://dev.mysql.com/doc/refman/8.0/en/window-functions.html
概述
窗口函数常用的三大功能:分区、排序、计算。
partition by —— 分区 (分组)
类似于 group by
的 “分组” 。不同的是,group by
会在分组后将每个组的结果聚合成单行,窗口函数则会在分区后为每一行生成一个结果。
通俗地说,group by
分组后合并;窗口函数分组后不合并,计算结果放到每一行中。
例如,有一张 sales 表,数据如下:
mysql> SELECT * FROM sales;
+------+---------+------------+--------+
| year | country | product | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer | 1500 |
| 2000 | Finland | Phone | 100 |
| 2001 | Finland | Phone | 10 |
| 2000 | India | Calculator | 75 |
| 2000 | India | Calculator | 75 |
| 2000 | India | Computer | 1200 |
| 2000 | USA | Calculator | 75 |
| 2000 | USA | Computer | 1500 |
| 2001 | USA | Calculator | 50 |
| 2001 | USA | Computer | 1500 |
| 2001 | USA | Computer | 1200 |
| 2001 | USA | TV | 150 |
| 2001 | USA | TV | 100 |
+------+---------+------------+--------+
不使用窗口函数,查询各个国家的利润总和 country_profit
,并根据国家 country
分组:
mysql> SELECT country, SUM(profit) AS country_profit
FROM sales
GROUP BY country;
+---------+----------------+
| country | country_profit |
+---------+----------------+
| Finland | 1610 |
| India | 1350 |
| USA | 4575 |
+---------+----------------+
使用窗口函数,查询各个国家的利润总和 country_profit
,并根据国家 country
分区:
mysql> SELECT
year, country, product, profit,
SUM(profit) OVER(PARTITION BY country) AS country_profit
FROM sales;
+------+---------+------------+--------+----------------+
| year | country | product | profit | country_profit |
+------+---------+------------+--------+----------------+
| 2000 | Finland | Computer | 1500 | 1610 |
| 2000 | Finland | Phone | 100 | 1610 |
| 2001 | Finland | Phone | 10 | 1610 |
| 2000 | India | Calculator | 75 | 1350 |
| 2000 | India | Calculator | 75 | 1350 |
| 2000 | India | Computer | 1200 | 1350 |
| 2000 | USA | Calculator | 75 | 4575 |
| 2000 | USA | Computer | 1500 | 4575 |
| 2001 | USA | Calculator | 50 | 4575 |
| 2001 | USA | Computer | 1200 | 4575 |
| 2001 | USA | Computer | 1500 | 4575 |
| 2001 | USA | TV | 100 | 4575 |
| 2001 | USA | TV | 150 | 4575 |
+------+---------+------------+--------+----------------+
order by —— 排序
对每个分区中的记录进行排序,各个分区是对等的。
ASC
是升序排序,DESC
是降序排序,什么都不写默认是升序。
函数 —— 计算
函数根据 OVER
子句的内容来进行计算,计算结果将放到每一行中。
例如,查询上面 sales 表中的所有记录,根据国家 country
分区,并根据年份 year
和产品 product
进行升序排序,用窗口函数 ROW_NUMBER()
计算各个分区中行的编号。
mysql> SELECT
year, country, product, profit,
ROW_NUMBER() OVER(PARTITION BY country ORDER BY year, product) AS row_num
FROM sales;
+------+---------+------------+--------+---------+
| year | country | product | profit | row_num |
+------+---------+------------+--------+---------+
| 2000 | Finland | Computer | 1500 | 1 |
| 2000 | Finland | Phone | 100 | 2 |
| 2001 | Finland | Phone | 10 | 3 |
| 2000 | India | Calculator | 75 | 1 |
| 2000 | India | Calculator | 75 | 2 |
| 2000 | India | Computer | 1200 | 3 |
| 2000 | USA | Calculator | 75 | 1 |
| 2000 | USA | Computer | 1500 | 2 |
| 2001 | USA | Calculator | 50 | 3 |
| 2001 | USA | Computer | 1500 | 4 |
| 2001 | USA | Computer | 1200 | 5 |
| 2001 | USA | TV | 150 | 6 |
| 2001 | USA | TV | 100 | 7 |
+------+---------+------------+--------+---------+
语法
函数 OVER ( [ PARTITION BY 字段名 ] [ ORDER BY 字段名 ASC|DESC ] [ FRAME子句 ] )
-
OVER
子句表示查询中的一个窗口操作。OVER(...)
括号中的子句用来指定查询结果是如何进行分区和排序的,以及窗口函数使用哪些行来计算函数结果。
OVER()
如果为空括号,则窗口由查询到的所有记录组成,窗口函数使用所有记录来计算结果。注意: 仅在
SELECT
列表和ORDER BY
子句中允许使用窗口函数。 -
PARTITION BY
子句指定按照哪些字段进行分区。窗口函数基于各个分区分别进行计算,计算结果放到各个分区所包含的每一行中。
如果PARTITION BY
省略,则查询到的所有记录组成一个分区。 -
ORDER BY
子句指定各个分区中的行按照哪些字段进行排序。根据
ORDER BY
子句相等的分区行被视为对等。
如果ORDER BY
省略,则分区中的行是无序的,各个分区的行都是对等的。注意: 这里
ORDER BY
的排序是各个分区各排各的,互不影响。如果要将结果集作为一个整体进行排序,那就在查询最外层加上一个ORDER BY
。对于NULL
值升序排序时排在最前,降序排序时排在最后。 -
FRAME
子句指定如何定义分区中的子集,类似于滑动窗口。关于
FRAME
的更多内容,可参考 官方文档 。
命名窗口
函数 OVER 窗口名
… WINDOW 窗口名 AS ( [ PARTITION BY 字段名 ] [ ORDER BY 字段名 ASC|DESC ] [ FRAME子句 ] )
定义一个窗口 WINDOW
,给它起个名,然后在 OVER
子句中引用该名称。
例如,可以把这条查询语句:
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;
写为:
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);
这样的好处是便于编写,也便于修改。除此之外,在 OVER
子句里,引用的窗口名后面还可以添加其他子句进行组合,例如:
SELECT
DISTINCT year, country,
FIRST_VALUE(year) OVER (w ORDER BY year ASC) AS first,
FIRST_VALUE(year) OVER (w ORDER BY year DESC) AS last
FROM sales
WINDOW w AS (PARTITION BY country);
注意:OVER
子句里添加的子句类型不能与引用的 WINDOW
所包含的子句类型相同,也就是说,组合后的 OVER
子句里不能出现重复的子句类型,否则会发生错误。
例如,这样没问题:
OVER (w ORDER BY country)
... WINDOW w AS (PARTITION BY country)
这样不行,因为有重复的子句 PARTITION BY
:
OVER (w PARTITION BY year)
... WINDOW w AS (PARTITION BY country)
命名窗口的定义还可以 “套娃”,即给 “命名窗口” 命名。但“套娃”归“套娃”,只能向前“套”或者向后“套”,不允许出现循环。
例如,可以这样“套”:
WINDOW w1 AS (w2), w2 AS (w3), w4 AS (w1)
但不能这样无限“套”:
WINDOW w1 AS (w2), w2 AS (w3), w3 AS (w1)
函数
大多数聚合函数都可以用作窗口函数。例如:
AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()
以上函数是否为窗口函数,取决于 OVER
子句是否存在。加上 OVER
子句,就变成了窗口函数。关于聚合函数的详细内容,可参考 官方文档 。
非聚合窗口函数,也就是窗口函数,这些函数是必须要加上 OVER
子句的:
窗口函数 | 返回结果 |
---|---|
ROW_NUMBER() | 当前行在其分区内的序号,例如:1,2,3,4… |
RANK() | 当前行在其分区内的排名 (有间隔),例如:1,2,2,4… |
DENSE_RANK() | 当前行在其分区内的排名 (无间隔),例如:1,2,2,3… |
PERCENT_RANK() | 当前值在其分区内的百分比排名,例如:0,0.25,0.75… |
CUME_DIST() | 当前值在其分区中的累积分布 |
LAG(expr, N, default) | 同一分区内当前行的前 N 行的 expr 的值 |
LEAD(expr, N, default) | 同一分区内当前行的后 N 行的 expr 的值 |
FIRST_VALUE(expr) | 当前窗口第一行的 expr 的值 |
LAST_VALUE(expr) | 当前窗口最后一行的 expr 的值 |
NTH_VALUE(expr, N) | 当前窗口第 N 行的 expr 的值 |
NTILE(N) | 将分区划分为 N 个桶,当前行所属的桶号 |
注意: 在 MySQL 中,窗口函数在计算结果时会考虑 NULL
值,并且是从窗口的第一行开始计算。如果想要从窗口的最后一行开始计算,那么需要用 ORDER BY
进行逆序排序。
ROW_NUMBER()
ROW_NUMBER()
函数的返回值为当前行在其分区中的编号。
编号的范围从 1 开始到该分区的行数。
编号的顺序取决于 ORDER BY
子句,如果没有 ORDER BY
,编号是不确定的。
RANK()
RANK()
函数的返回值为当前行在其分区中的排名,有间隔。
排名相同的行返回值也相同,然后会跳过这些行的编号继续向下排名。也就是说,如果出现相同排名,那么下面的编号与上面的编号是不连续的、有间隔的。
排名的顺序取决于 ORDER BY
子句,如果没有 ORDER BY
,所有行都是对等的。
DENSE_RANK()
DENSE_RANK()
函数的返回值为当前行在其分区中的排名,没有间隔。
排名相同的行返回值也相同,接着上面的编号继续向下排名,排名的编号是连续的、没有间隔的。
排名的顺序取决于 ORDER BY
子句,如果没有 ORDER BY
,所有行都是对等的。
示例
ROW_NUMBER(),RANK(),DENSE_RANK()
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 |
+------+------------+------+------------+
PERCENT_RANK()
PERCENT_RANK()
函数的返回值为分区中各行的值小于当前行中的值的百分比,最大值不参与比较。
返回值的范围从 0 到 1,表示行的相对排名。计算公式如下,其中 rank 是行的排名 ( 有间隔 ), rows 是分区中的总行数:
(rank - 1) / (rows - 1)
排名的顺序取决于 ORDER BY
子句,如果没有 ORDER BY
,所有行都是对等的。
CUME_DIST()
CUME_DIST()
函数的返回值为当前值在分区的值中的累积分布,即:分区中各行的值小于或等于当前行中的值的百分比。
返回值的范围从 0 到 1。计算公式为排名在当前行之前或与当前行对等的行数除以分区中的总行数。
排名的顺序取决于 ORDER BY
子句,如果没有 ORDER BY
,所有行都是对等的,并且各行的返回值都为 1 。
示例
ROW_NUMBER(),CUME_DIST(),PERCENT_RANK()
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 |
+------+------------+--------------------+--------------+
LAG(expr, N, default)
LAG(expr,N,default)
函数的返回值为同一分区内当前行的前 N 行的 expr 的值。
如果没有这样的行,则返回值为 default
。N
和 default
可以省略,如果不写的话,它们的默认值分别为 1
和 NULL
。
N 必须是非负整数。如果 N 为 0,则表示当前行的 expr 的值。
LEAD(expr, N, default)
LEAD(expr, N, default)
函数的返回值为同一分区内当前行的后 N 行的 expr 的值。
如果没有这样的行,则返回值为 default
。N
和 default
可以省略,如果不写的话,它们的默认值分别为 1
和 NULL
。
N 必须是非负整数。如果 N 为 0,则表示当前行的 expr 的值。
示例
LAG(expr, N, default),LEAD(expr, N, default)
LAG()
和 LEAD()
函数通常用于计算行之间的差异。
例如,查询一组按时间 t
排序的值 val
,计算相邻行的 LAG()
和 LEAD()
,以及当前行和相邻行之间的差值:
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 |
+----------+------+------+------+----------+-----------+
LAG()
和 LEAD()
函数还可用于计算各行数据的总和。
例如,查询一组数据 n
(包含斐波那契数列的前几个数字),计算相邻行的 LAG()
和 LEAD()
,将相邻行的值与当前值相加,得到的效果是生成斐波那契数列中的下一个数字和下下个数字:
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 |
+------+------+------+--------+-------------+
FIRST_VALUE(expr)
FIRST_VALUE(expr)
函数的返回值为当前窗口第一行的 expr 的值。
LAST_VALUE(expr)
LAST_VALUE(expr)
函数的返回值为当前窗口最后一行的 expr 的值。
NTH_VALUE(expr, N)
NTH_VALUE(expr, N)
函数的返回值为当前窗口第 N 行的 expr 的值。
N 必须是正整数,如果没有这样的行,则返回值为 NULL
。
示例
FIRST_VALUE(expr),LAST_VALUE(expr),NTH_VALUE(expr, 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 |
+----------+---------+------+-------+------+--------+--------+
NTILE(N)
NTILE(N)
函数将分区划分为 N 个桶 ( 组 ),为分区中的每一行分配其所属的桶号,返回值为当前行所属的桶号。
N 必须是正整数,返回值的范围从 1 到 N。分区中行的顺序取决于 ORDER BY
子句。
示例
ROW_NUMBER(),NTILE(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 |
+------+------------+--------+--------+
高版本对部分函数的参数限制
LAG(expr, N, default)
,LEAD(expr, N, default)
,NTH_VALUE(expr, N)
,NTILE(N)
这些参数中有 N
的函数,从 MySQL 8.0.22 开始,N
不允许为 NULL
.。此外,N
现在必须是 1 到 2的六十三次方 范围内的整数,可采用以下形式:
-
无符号整数常量
-
位置参数标记 (?)
-
用户定义的变量
-
存储过程中的局部变量