MySQL8.0之窗口函数-阿里云开发者社区

开发者社区> 数据库> 正文

MySQL8.0之窗口函数

简介: MySQL8.0 窗口函数

一、窗口函数简介

1.1 什么是窗口函数

  MySQL从8.0开始支持窗口函数,这个功能在大多数据库中早已支持,有的也叫分析函数。那么什么是窗口呢?
  窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,有的函数随着记录不同,窗口大小都是固定的,这种属于静态窗口;有的函数则相反,不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。简单的说窗口函数就是对于查询的每一行,都使用与该行相关的行进行计算。
  窗口函数和普通聚合函数很容易混淆,二者区别如下:

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

1.2 窗口函数功能

名称 描述
CUME_DIST() 计算一组值中一个值的累积分布
DENSE_RANK() 根据该ORDER BY子句为分区中的每一行分配一个等级。它将相同的等级分配给具有相等值的行。如果两行或更多行具有相同的排名,则排名值序列中将没有间隙
FIRST_VALUE() 返回相对于窗口框架第一行的指定表达式的值
LAG() 返回分区中当前行之前的第N行的值。如果不存在前一行,则返回NULL
LAST_VALUE() 返回相对于窗口框架中最后一行的指定表达式的值
LEAD() 返回分区中当前行之后的第N行的值。如果不存在后续行,则返回NULL
NTH_VALUE() 从窗口框架的第N行返回参数的值
NTILE() 将每个窗口分区的行分配到指定数量的排名组中
PERCENT_RANK() 计算分区或结果集中行的百分数等级
RANK() 与DENSE_RANK()函数相似,不同之处在于当两行或更多行具有相同的等级时,等级值序列中存在间隙
ROW_NUMBER() 为分区中的每一行分配一个顺序整数

  将上述函数按照功能划分,可以把MySQL支持的窗口函数分为如下几类:

  • 序号函数:ROW_NUMBER()、RANK()、DENSE_RANK()
  • 分布函数:PERCENT_RANK()、CUME_DIST()
  • 前后函数:LAG()、LEAD()
  • 头尾函数:FIRST_VALUE()、LAST_VALUE()
  • 其他函数:NTH_VALUE()、NTILE()

二、窗口函数语法

  窗口函数的相关语法是:

[WINDOW window_name AS (window_spec)
    [, window_name AS (window_spec)] ...]

window_function_name(window_name/expression) 
    OVER (
        [partition_defintion]
        [order_definition]
        [frame_definition]
    )

  先指定作为窗口函数的函数名,后面跟一个表达式,然后是OVER(…),就算OVER里面没有内容,括号也需要保留。
  窗口函数的一个概念是当前行,当前行属于某个窗口,窗口由“[partition_defintion]”,“[order_definition]”,“[frame_definition]“确定。
  window_name:给窗口指定一个别名,如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读。
  partition_defintion:窗口按照指定字段进行分区,两个分区由分区边界分隔,窗口功能在分区内执行,并在跨越分区边界时重新初始化。
  order_definition:按照指定字段进行排序,窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用,也可以单独使用。
  frame子句:frame是当前分区的一个子集,在分区里面再进一步细分窗口,子句用来定义子集的规则,通常用来作为滑动窗口使用。
  具体语法如下:

frame_unit {<frame_start>|<frame_between>}

  frame_unit有两种,分别是ROWS和RANGE,由ROWS定义的frame是由开始和结束位置的行确定的,由RANGE定义的frame由在某个值区间的行确定。
_

  • 基于行:
      通常使用BETWEEN frame_start AND frame_end语法来表示行范围,frame_start和frame_end可以支持如下关键字,来确定不同的动态行记录:
CURRENT ROW 边界是当前行,一般和其他范围关键字一起使用
UNBOUNDED PRECEDING 边界是分区中的第一行
UNBOUNDED FOLLOWING 边界是分区中的最后一行
expr PRECEDING  当前行之前的expr(数字或表达式)行
expr FOLLOWING  当前行之后的expr(数字或表达式)行
比如,下面都是合法的范围:
rows BETWEEN 1 PRECEDING AND 1 FOLLOWING 窗口范围是当前行、前一行、后一行一共三行记录。
rows  UNBOUNDED FOLLOWING 窗口范围是当前行到分区中的最后一行
rows BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 窗口范围是当前分区中所有行,等同于不写。
  • 基于范围:
      和基于行类似,但有些范围不是直接可以用行数来表示的,比如希望窗口范围是一周前的订单开始,截止到当前行,则无法使用rows来直接表示,此时就可以使用范围来表示窗口:INTERVAL 7 DAY PRECEDING。Linux中常见的最近1分钟、5分钟负载是一个典型的应用场景。

  如果未frame_definition在OVER子句中指定,则MySQL默认使用以下框架:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

三、窗口函数示例

MySQL [test]> select * from t1;
+----+------+--------+---------------------+
| id | name | amount | time                |
+----+------+--------+---------------------+
|  1 | a1   |    100 | 2019-01-01 00:00:00 |
|  2 | a1   |    200 | 2019-01-02 00:00:00 |
|  3 | a1   |    300 | 2019-01-02 00:00:00 |
|  4 | a1   |    300 | 2019-01-03 00:00:00 |
|  5 | a1   |    300 | 2019-01-04 00:00:00 |
|  6 | a2   |    500 | 2019-01-05 00:00:00 |
|  7 | a2   |    600 | 2019-01-06 00:00:00 |
|  8 | a2   |    600 | 2019-01-07 00:00:00 |
|  9 | a2   |    600 | 2019-01-07 00:00:00 |
| 10 | a2   |    600 | 2019-01-07 00:00:00 |
+----+------+--------+---------------------+
10 rows in set (0.00 sec)

MySQL [test]> select id,name,amount,time,avg(amount) over w as avg_sum from t1 window w as (partition by name order by time desc rows BETWEEN 1 PRECEDING AND 1 FOLLOWING);
+----+------+--------+---------------------+----------+
| id | name | amount | time                | avg_sum  |
+----+------+--------+---------------------+----------+
|  5 | a1   |    300 | 2019-01-04 00:00:00 | 300.0000 |
|  4 | a1   |    300 | 2019-01-03 00:00:00 | 266.6667 |
|  2 | a1   |    200 | 2019-01-02 00:00:00 | 266.6667 |
|  3 | a1   |    300 | 2019-01-02 00:00:00 | 200.0000 |
|  1 | a1   |    100 | 2019-01-01 00:00:00 | 200.0000 |
|  8 | a2   |    600 | 2019-01-07 00:00:00 | 600.0000 |
|  9 | a2   |    600 | 2019-01-07 00:00:00 | 600.0000 |
| 10 | a2   |    600 | 2019-01-07 00:00:00 | 600.0000 |
|  7 | a2   |    600 | 2019-01-06 00:00:00 | 566.6667 |
|  6 | a2   |    500 | 2019-01-05 00:00:00 | 550.0000 |
+----+------+--------+---------------------+----------+
10 rows in set (0.00 sec)
#从结果可以看出,id为5的记录属于边界值,没有前一行,因此avg_sum为(300+300)/2=300;id为4的记录前后都有记录,所以avg_sum为(300+300+200)/3=266.6667,以此类推就可以得到一个基于滑动窗口的动态平均值。此例中,窗口函数用到了传统的聚合函数avg(),用来计算动态的平均值。
MySQL [test]> select CUME_DIST() over (partition by name order by time desc ) as win_fun, id,name,amount,time from t1;
+---------+----+------+--------+---------------------+
| win_fun | id | name | amount | time                |
+---------+----+------+--------+---------------------+
|     0.2 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
|     0.4 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
|     0.8 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
|     0.8 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
|       1 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
|     0.6 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
|     0.6 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
|     0.6 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
|     0.8 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
|       1 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#在某种排序条件下,小于等于当前行值的行数/总行数,得到的是数据在某一个纬度的分布百分比情况
MySQL [test]> select DENSE_RANK() over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------+----+------+--------+---------------------+
| win_fun | id | name | amount | time                |
+---------+----+------+--------+---------------------+
|       1 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
|       2 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
|       3 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
|       3 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
|       4 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
|       1 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
|       1 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
|       1 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
|       2 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
|       3 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------+----+------+--------+---------------------+
10 rows in set (0.01 sec)
#dense_rank()的出现是为了解决rank()编号存在的问题的,rank()编号的时候存在跳号的问题,如果有两个并列第1,那么下一个名次的编号就是3,结果就是没有编号为2的数据。如果不想跳号,可以使用dense_rank()替代
MySQL [test]> select FIRST_VALUE(time) over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------------------+----+------+--------+---------------------+
| win_fun             | id | name | amount | time                |
+---------------------+----+------+--------+---------------------+
| 2019-01-04 00:00:00 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
| 2019-01-04 00:00:00 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
| 2019-01-04 00:00:00 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
| 2019-01-04 00:00:00 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
| 2019-01-04 00:00:00 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
| 2019-01-07 00:00:00 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
| 2019-01-07 00:00:00 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------------------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#first_value就是取某一组数据,按照某种方式排序的,最早的一个字段的值。
MySQL [test]> select LAG(time,1) over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------------------+----+------+--------+---------------------+
| win_fun             | id | name | amount | time                |
+---------------------+----+------+--------+---------------------+
| NULL                |  5 | a1   |    300 | 2019-01-04 00:00:00 |
| 2019-01-04 00:00:00 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
| 2019-01-03 00:00:00 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
| 2019-01-02 00:00:00 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
| 2019-01-02 00:00:00 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
| NULL                |  8 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
| 2019-01-06 00:00:00 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------------------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#lag(column,n)获取当前数据行按照某种排序规则的上n行数据的某个字段
MySQL [test]> select LAST_VALUE(time) over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------------------+----+------+--------+---------------------+
| win_fun             | id | name | amount | time                |
+---------------------+----+------+--------+---------------------+
| 2019-01-04 00:00:00 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
| 2019-01-03 00:00:00 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
| 2019-01-02 00:00:00 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
| 2019-01-02 00:00:00 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
| 2019-01-01 00:00:00 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
| 2019-01-07 00:00:00 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-06 00:00:00 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
| 2019-01-05 00:00:00 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------------------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#last_value就是取某一组数据,按照某种方式排序的,最新的一个字段的值。
MySQL [test]> select LEAD(time,1) over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------------------+----+------+--------+---------------------+
| win_fun             | id | name | amount | time                |
+---------------------+----+------+--------+---------------------+
| 2019-01-03 00:00:00 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
| 2019-01-02 00:00:00 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
| 2019-01-02 00:00:00 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
| 2019-01-01 00:00:00 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
| NULL                |  1 | a1   |    100 | 2019-01-01 00:00:00 |
| 2019-01-07 00:00:00 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-06 00:00:00 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-05 00:00:00 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
| NULL                |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------------------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#lead(column,n)获取当前数据行按照某种排序规则的下n行数据的某个字段
MySQL [test]> select NTH_VALUE(time,2) over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------------------+----+------+--------+---------------------+
| win_fun             | id | name | amount | time                |
+---------------------+----+------+--------+---------------------+
| NULL                |  5 | a1   |    300 | 2019-01-04 00:00:00 |
| 2019-01-03 00:00:00 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
| 2019-01-03 00:00:00 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
| 2019-01-03 00:00:00 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
| 2019-01-03 00:00:00 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
| 2019-01-07 00:00:00 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
| 2019-01-07 00:00:00 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
| 2019-01-07 00:00:00 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------------------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#从排序的第n行还是返回nth_value字段中的值
MySQL [test]> select NTILE(2) over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------+----+------+--------+---------------------+
| win_fun | id | name | amount | time                |
+---------+----+------+--------+---------------------+
|       1 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
|       1 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
|       1 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
|       2 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
|       2 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
|       1 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
|       1 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
|       1 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
|       2 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
|       2 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#按照某列的倒序排列,将字段分成N组,可以得到哪个数据在N组中哪一部分
MySQL [test]> select PERCENT_RANK() over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------+----+------+--------+---------------------+
| win_fun | id | name | amount | time                |
+---------+----+------+--------+---------------------+
|       0 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
|    0.25 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
|     0.5 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
|     0.5 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
|       1 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
|       0 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
|       0 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
|       0 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
|    0.75 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
|       1 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#数据分布的计算方式:当前RANK值-1/总行数-1 
MySQL [test]> select RANK() over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------+----+------+--------+---------------------+
| win_fun | id | name | amount | time                |
+---------+----+------+--------+---------------------+
|       1 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
|       2 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
|       3 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
|       3 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
|       5 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
|       1 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
|       1 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
|       1 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
|       4 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
|       5 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#排序条件一样的情况下,其编号也一样。
MySQL [test]> select ROW_NUMBER() over (partition by name order by time desc) as win_fun, id,name,amount,time from t1;
+---------+----+------+--------+---------------------+
| win_fun | id | name | amount | time                |
+---------+----+------+--------+---------------------+
|       1 |  5 | a1   |    300 | 2019-01-04 00:00:00 |
|       2 |  4 | a1   |    300 | 2019-01-03 00:00:00 |
|       3 |  2 | a1   |    200 | 2019-01-02 00:00:00 |
|       4 |  3 | a1   |    300 | 2019-01-02 00:00:00 |
|       5 |  1 | a1   |    100 | 2019-01-01 00:00:00 |
|       1 |  8 | a2   |    600 | 2019-01-07 00:00:00 |
|       2 |  9 | a2   |    600 | 2019-01-07 00:00:00 |
|       3 | 10 | a2   |    600 | 2019-01-07 00:00:00 |
|       4 |  7 | a2   |    600 | 2019-01-06 00:00:00 |
|       5 |  6 | a2   |    500 | 2019-01-05 00:00:00 |
+---------+----+------+--------+---------------------+
10 rows in set (0.00 sec)
#对排序结果编号

四、总结

  MySQL8.0中加入了窗口函数的功能,这一点方便了SQL的编写,可以说是MySQL8.0的亮点之一。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章