Mysql实现开窗 mysql窗口函数使用方法

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

• 无符号整数常量
• 位置参数标记 (?)
• 用户定义的变量
• 存储过程中的局部变量