MySQL8.0 窗口函数

原创 CuiHulong 2020-04-06

3183

什么叫窗口?

窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。

窗口函数和普通聚合函数也很容易混淆，二者区别如下：

聚合函数是将多条记录聚合为一条；而窗口函数是每条记录都会执行，有几条记录执行完还是几条。
聚合函数也可以用于窗口函数中。

窗口函数语法

window_spec:
    [window_name] [partition_clause] [order_clause] [frame_clause]

window_name：对于多个OVER子句定义指定一个别名，如果SQL中涉及的窗口较多，采用别名可以看起来更清晰易读。
partition_clause:指示如何将查询行划分为组。给定行的窗口函数结果基于包含该行的分区的行。
order_clause：ORDER BY子句指示如何对每个分区中的行进行排序。
frame_clause：frame子句指定如何定义这个子集，在分区里面再进一步细分窗口，子句用来定义子集的规则，通常用来作为滑动窗口使用。详细内容参考：https://dev.mysql.com/doc/refman/8.0/en/window-functions-frames.html

下面进入本次介绍的内容非聚合窗口函数。

非聚合窗口函数

对于查询中的每一行，这些函数使用与该行相关的行执行计算。大多数聚合函数也可以用作窗口函数。8.0版本给的函数如下：

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

按照功能可分为以下5种：

排序分组函数：row_number() / rank() / dense_rank()
分布函数：cume_dist()/percent_rank()
前后函数：lag() / lead()
头尾中函数：first_val() / last_val()/nth_value
分组函数： nfile()

下面详细介绍使用方式：

分组排序

ROW_NUMBER() over_clause：
返回分区中当前行的编号。行号的范围从1到分区行数。
RANK() over_clause：
返回分区中当前行的排名，并带有间隔。对等行被认为是平起平坐的，得到同样的排名。如果存在大小大于一的组，则此函数不为对等组分配连续的排名；结果是不连续的排名。这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。没有ORDER BY，所有行都是对等的。
DENSE_RANk over_clause：
返回分区内当前行的排名，没有间隔。对等项被认为是平起平坐的，得到同样的排名。该函数将连续的排名分配给对等组，结果是，大小大于1的组不会产生不连续的排名。具体示例，请参见RANK()函数描述。这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。没有ORDER BY，所有行都是对等的。

mysql> SELECT name,
    ->          ROW_NUMBER() OVER w AS 'row_number',
    ->          RANK()       OVER w AS 'rank',
    ->          DENSE_RANK() OVER w AS 'dense_rank'
    ->        FROM wtable
    ->        WINDOW w AS (ORDER BY name);
+------+------------+------+------------+
| name | row_number | rank | dense_rank |
+------+------------+------+------------+
| C    |          1 |    1 |          1 |
| H    |          2 |    2 |          2 |
| L    |          3 |    3 |          3 |
| L    |          4 |    3 |          3 |
| L    |          5 |    3 |          3 |
| P    |          6 |    6 |          4 |
| W    |          7 |    7 |          5 |
+------+------------+------+------------+

分布函数

CUME_DIST over_clause：
返回一组值中某个值的累积分布，即分区值小于或等于当前行的值的百分比。它表示窗口分区的窗口顺序中在当前行之前或与当前行对等的行数，除以窗口分区中的总行数。返回值的范围从0到1。这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。如果没有ORDER BY，所有行都是对等的，值N/N = 1，其中N是分区大小。
PERCENT_RANK() over_clause：
返回分区值小于当前行的值的百分比，不包括最大值。返回值范围为0 ~ 1，表示行相对排名，根据公式(rank - 1) / (rows - 1)计算得到，其中rank为行排名，row为分区行数：

mysql> select *from wtable;
+----+------+
| id | name |
+----+------+
|  1 | C    |
|  2 | H    |
|  3 | L    |
|  5 | W    |
|  4 | P    |
+----+------+
5 rows in set (0.00 sec)

mysql> SELECT
    ->          id,name,
    ->          ROW_NUMBER()   OVER w AS 'row_number',
    ->          CUME_DIST()    OVER w AS 'cume_dist',
    ->          PERCENT_RANK() OVER w AS 'percent_rank'
    ->        FROM wtable
    ->        WINDOW w AS (ORDER BY id);
+----+------+------------+-----------+--------------+
| id | name | row_number | cume_dist | percent_rank |
+----+------+------------+-----------+--------------+
|  1 | C    |          1 |       0.2 |            0 |
|  2 | H    |          2 |       0.4 |         0.25 |
|  3 | L    |          3 |       0.6 |          0.5 |
|  4 | P    |          4 |       0.8 |         0.75 |
|  5 | W    |          5 |         1 |            1 |
+----+------+------------+-----------+--------------+

Row_NUMBER: 进行了排序
CUME_DIST：它表示当前行位置/总行数。返回值的范围从0到1。
PERCENT_RANK： (rank - 1) / (rows - 1) 比如：H值 rank是2 ，rows是5 = 1/4=0.25

头尾中函数
- FIRST_VALUE(expr) [null_treatment] over_clause：
从窗口框架的第一行返回expr的值。
- LAST_VALUE(expr) [null_treatment] over_clause：
从窗口框架的最后一行返回expr的值。
- NTH_VALUE(expr, N) [from_first_last] [null_treatment] over_clause：
从窗口框架的第n行返回expr的值。如果没有这样的行，则返回值为NULL。N必须是一个正整数。

mysql> select *from wtable;
+----+------+---------+
| id | name | subject |
+----+------+---------+
|  1 | C    | C001    |
|  2 | H    | B001    |
|  3 | L    | A001    |
|  5 | W    | C001    |
|  4 | P    | B001    |
|  6 | L    | A001    |
|  7 | L    | B001    |
+----+------+---------+
7 rows in set (0.00 sec)

mysql> SELECT
    ->          id, subject, name,
    ->          FIRST_VALUE(name)  OVER w AS 'first',
    ->          LAST_VALUE(name)   OVER w AS 'last',
    ->          NTH_VALUE(name, 2) OVER w AS 'second',
    ->          NTH_VALUE(name, 3) OVER w AS 'fourth'
    ->        FROM wtable
    ->        WINDOW w AS (PARTITION BY subject ORDER BY id
    ->                     ROWS UNBOUNDED PRECEDING);
+----+---------+------+-------+------+--------+--------+
| id | subject | name | first | last | second | fourth |
+----+---------+------+-------+------+--------+--------+
|  3 | A001    | L    | L     | L    | NULL   | NULL   |
|  6 | A001    | L    | L     | L    | L      | NULL   |
|  2 | B001    | H    | H     | H    | NULL   | NULL   |
|  4 | B001    | P    | H     | P    | P      | NULL   |
|  7 | B001    | L    | H     | L    | P      | L      |
|  1 | C001    | C    | C     | C    | NULL   | NULL   |
|  5 | C001    | W    | C     | W    | W      | NULL   |
+----+---------+------+-------+------+--------+--------+
7 rows in set (0.00 sec)

FIRST_VALUE：窗口第一行开始出现的值
LAST_VALUE：窗口之后一行出现的值
NTH_VALUE：窗口第N行开始之前是否出现过。

前后中函数

LAG(expr [, N[, default]]) [null_treatment] over_clause：
从分区中滞后于(先于)当前行的行中返回expr的值。如果没有这样的行，则返回值为默认值。例如，如果N为3，则前两行返回值为默认值。如果缺少N或default，则默认值分别为1和NULL。N必须是非负整数。
LEAD(expr [, N[, default]]) [null_treatment] over_clause：
从在其分区中超前当前行N行的行返回expr的值。如果没有这样的行，则返回值为默认值。例如，如果N是3，则最后三行的返回值是默认值。如果缺少N或default，则默认值分别为1和NULL。
N必须是非负整数。如果N为0，则对当前行计算expr

mysql> select * from wtable order by id;
+----+------+---------+
| id | name | subject |
+----+------+---------+
|  1 | C    | C001    |
|  2 | H    | B001    |
|  3 | L    | A001    |
|  4 | P    | B001    |
|  5 | W    | C001    |
|  6 | L    | A001    |
|  7 | L    | B001    |
+----+------+---------+
7 rows in set (0.00 sec)

mysql> SELECT
    ->          id,
    ->          LAG(id, 2, 'N')      OVER w AS 'lag',
    ->          LEAD(id, 2, 'N')     OVER w AS 'lead'
    ->        FROM wtable
    ->        WINDOW w AS (ORDER BY id);
+----+-----+------+
| id | lag | lead |
+----+-----+------+
|  1 | N   | 3    |
|  2 | N   | 4    |
|  3 | 1   | 5    |
|  4 | 2   | 6    |
|  5 | 3   | 7    |
|  6 | 4   | N    |
|  7 | 5   | N    |
+----+-----+------+
7 rows in set (0.00 sec)

假设有一个客户之前下了订单，想看看这次订单距离下一次下单时间相差多少天，这类问题可以通过 lead 函数来求解，Lag和Lead函数可以在同一次查询中取出同一字段的前N行的数据(Lag)和后N行的数据(Lead)作为独立的列。实际应用当中，相比于 left join 等自连接方式的sql更加简洁。

5.分组

NTILE(N) over_clause：
将一个分区分成N组(bucket)，为分区中的每一行分配其bucket号，并返回分区中当前行的bucket号。例如，如果N是4,NTILE()将行分成4个bucket。如果N是100,NTILE()将行分成100个bucket。
N必须是一个正整数。bucket号返回值的范围从1到N。
这个函数应该与ORDER BY一起使用，将分区行按所需的顺序排序。

mysql> SELECT id,
    ->          ROW_NUMBER() OVER w AS 'row_number',
    ->          NTILE(2)     OVER w AS 'ntile2',
    ->          NTILE(4)     OVER w AS 'ntile4'
    -> FROM wtable
    ->        WINDOW w AS (ORDER BY id);
+----+------------+--------+--------+
| id | row_number | ntile2 | ntile4 |
+----+------------+--------+--------+
|  1 |          1 |      1 |      1 |
|  2 |          2 |      1 |      1 |
|  3 |          3 |      1 |      2 |
|  4 |          4 |      1 |      2 |
|  5 |          5 |      2 |      3 |
|  6 |          6 |      2 |      3 |
|  7 |          7 |      2 |      4 |
+----+------------+--------+--------+

总结
作为分析性函数用法还是比较简单，在很多场景上都可以有效的用到。但是对于大量数据来说，还需要注意性能和影响。Explain解析执行计划：正常使用索引的同时还需用到排序。

在使用窗口函数时，有些资源需要重点优化才行。

mysql mysql8.0

最后修改时间：2022-12-27 13:52:57

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

专家专栏 | 崔虎龙（共172篇）

云和恩墨开源MySQL技术顾问，长期服务于数据中心（银行，金融、游戏、物流）行业，熟悉数据中心运营管理的流程及规范、自动化运维等方面。擅长MySQL，Redis，MongoDB数据库高可用设计和运维故障处理、备份恢复、升级迁移、性能优化。自我学习通过了MySQL OCP5.6 和 MySQL OCP5.7认证证书。

MySQL8.0 窗口函数

文章被以下合辑收录

评论