MySQL窗口函数

MySQL从8.0开始支持窗口函数,这个功能在大多商业数据库和部分开源数据库中早已支持,有的也叫分析函数。

窗口函数简介

什么叫窗口?

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

窗口函数和普通聚合函数的区别

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

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

窗口函数的类别

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

  • 序号函数:row_number() / rank() / dense_rank()
  • 分布函数:percent_rank() / cume_dist()
  • 前后函数:lag() / lead()
  • 头尾函数:first_val() / last_val()
  • 其他函数:nth_value() / nfile()

窗口函数基本用法

1
函数名([expr]) over子句

其中,over是关键字,用来指定函数执行的窗口范围,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下四种语法来设置窗口:

  • window_name:给窗口指定一个别名,如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读。如果指定一个别名w,则改写如下:

    1
    2
    3
    4
    5
    6
    7
    8
    select * 
    from
    (
    select row_number()over w as row_num,
    order_id,user_no,amount,create_date
    from order_info
    WINDOW w AS (partition by user_no order by amount desc)
    ) as t;
  • partition子句:窗口按照那些字段进行分组,窗口函数在不同的分组上分别执行。上面的例子就按照用户id进行了分组。在每个用户id上,按照order by的顺序分别生成从1开始的顺序编号。

  • order by子句:按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号。可以和partition子句配合使用,也可以单独使用。上例中二者同时使用,如果没有partition子句,则会按照所有用户的订单金额排序来生成序号。
  • frame子句:frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用。比如要根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额,则可以设置如下frame子句来创建滑动窗口:
1
2
3
4
5
6
7
8
9
10
select * 
from
(
select
order_id,user_no,amount,
avg(amount)over w as avg_num,
create_date
from order_info
WINDOW w AS (partition by user_no order by create_date desc ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
) as t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----------+---------+--------+----------+---------------------+
| order_id | user_no | amount | avg_num | create_date |
+----------+---------+--------+----------+---------------------+
| 5 | u0001 | 900 | 850.0000 | 2018-01-20 00:00:00 |
| 4 | u0001 | 800 | 666.6667 | 2018-01-10 00:00:00 |
| 2 | u0001 | 300 | 466.6667 | 2018-01-02 00:00:00 |
| 3 | u0001 | 300 | 233.3333 | 2018-01-02 00:00:00 |
| 1 | u0001 | 100 | 200.0000 | 2018-01-01 00:00:00 |
| 10 | u0002 | 800 | 800.0000 | 2018-01-22 00:00:00 |
| 9 | u0002 | 800 | 633.3333 | 2018-01-16 00:00:00 |
| 8 | u0002 | 300 | 566.6667 | 2018-01-10 00:00:00 |
| 7 | u0002 | 600 | 466.6667 | 2018-01-06 00:00:00 |
| 6 | u0002 | 500 | 550.0000 | 2018-01-05 00:00:00 |
+----------+---------+--------+----------+---------------------+

从结果可以看出,order_id为5订单属于边界值,没有前一行,因此平均订单金额为(900+800)/2=850;order_id为4的订单前后都有订单,所以平均订单金额为(900+800+300)/3=666.6667,以此类推可以得到一个基于滑动窗口的动态平均订单值。此例中,窗口函数用到了传统的聚合函数avg(),用来计算动态的平均值。

对于滑动窗口的范围指定,有两种方式,基于行和基于范围,具体区别如下:

  • 基于行:通常使用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分钟负载是一个典型的应用场景。

测试数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create table order_info
(
order_id int primary key,
user_no varchar(10),
amount int,
create_date datetime
);

insert into order_info values (1,'u0001',100,'2018-1-1');
insert into order_info values (2,'u0001',300,'2018-1-2');
insert into order_info values (3,'u0001',300,'2018-1-2');
insert into order_info values (4,'u0001',800,'2018-1-10');
insert into order_info values (5,'u0001',900,'2018-1-20');

insert into order_info values (6,'u0002',500,'2018-1-5');
insert into order_info values (7,'u0002',600,'2018-1-6');
insert into order_info values (8,'u0002',300,'2018-1-10');
insert into order_info values (9,'u0002',800,'2018-1-16');
insert into order_info values (10,'u0002',800,'2018-1-22');

窗口函数使用实例

序号函数

row_number()

应用场景:求每个用户的最新的一个订单。

思路:使用row_number() over(partition by user_no order by create_date desc) as row_num给原始记录编一个号,然后取第一个编号的数据,自然就是“用户的最新的一条订单”。

1
2
3
4
5
6
7
select * 
from
(
select row_number() over(partition by user_no order by create_date desc) as row_num,order_id,user_no,amount,create_date
from order_info
) as t
where row_num=1;
1
2
3
4
5
6
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date |
+---------+----------+---------+--------+---------------------+
| 1 | 5 | u0001 | 900 | 2018-01-20 00:00:00 |
| 1 | 10 | u0002 | 800 | 2018-01-22 00:00:00 |
+---------+----------+---------+--------+---------------------+

row_number():(分组)排序编号。

正如上面的例子, row_number()over(partition by user_no order by create_date desc) as row_num,按照用户分组,按照create_date排序,对已有数据生成一个编号。当然也可以不分组,对整体进行排序。任何一个窗口函数,都可以分组统计或者不分组统计(也即可以不要partition by ***都可以,看你的需求了)。

rank()

类似于 row_number(),也是排序功能,但是rank()有什么不一样?

如果再往测试表中写入一条数据:insert into order_info values (11,'u0002',800,'2018-1-22');对于测试表中的U002用户来说,有两条create_date完全一样的数据(假设有这样的数据),那么在row_number()编号的时候,这两条数据却被编了两个不同的号。理论上讲,这两条的数据的排名是并列最新的。因此rank()就是为了解决这个问题的,也即:排序条件一样的情况下,其编号也一样。

1
2
3
4
5
6
select * 
from
(
select rank() over(partition by user_no order by create_date desc) as row_num,order_id,user_no,amount,create_date
from order_info
) as t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date |
+---------+----------+---------+--------+---------------------+
| 1 | 5 | u0001 | 900 | 2018-01-20 00:00:00 |
| 2 | 4 | u0001 | 800 | 2018-01-10 00:00:00 |
| 3 | 2 | u0001 | 300 | 2018-01-02 00:00:00 |
| 3 | 3 | u0001 | 300 | 2018-01-02 00:00:00 |
| 5 | 1 | u0001 | 100 | 2018-01-01 00:00:00 |
| 1 | 10 | u0002 | 800 | 2018-01-22 00:00:00 |
| 2 | 9 | u0002 | 800 | 2018-01-16 00:00:00 |
| 3 | 8 | u0002 | 300 | 2018-01-10 00:00:00 |
| 4 | 7 | u0002 | 600 | 2018-01-06 00:00:00 |
| 5 | 6 | u0002 | 500 | 2018-01-05 00:00:00 |
+---------+----------+---------+--------+---------------------+

dense_rank()

dense_rank()的出现是为了解决rank()编号存在的问题的,rank()编号的时候存在跳号的问题,如果有两个并列第1,那么下一个名次的编号就是3,结果就是没有编号为2的数据。如果不想跳号,可以使用dense_rank()替代。

1
2
3
4
5
6
select * 
from
(
select dense_rank() over(partition by user_no order by create_date desc) as row_num,order_id,user_no,amount,create_date
from order_info
) as t;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+---------+----------+---------+--------+---------------------+
| row_num | order_id | user_no | amount | create_date |
+---------+----------+---------+--------+---------------------+
| 1 | 5 | u0001 | 900 | 2018-01-20 00:00:00 |
| 2 | 4 | u0001 | 800 | 2018-01-10 00:00:00 |
| 3 | 2 | u0001 | 300 | 2018-01-02 00:00:00 |
| 3 | 3 | u0001 | 300 | 2018-01-02 00:00:00 |
| 4 | 1 | u0001 | 100 | 2018-01-01 00:00:00 |
| 1 | 10 | u0002 | 800 | 2018-01-22 00:00:00 |
| 2 | 9 | u0002 | 800 | 2018-01-16 00:00:00 |
| 3 | 8 | u0002 | 300 | 2018-01-10 00:00:00 |
| 4 | 7 | u0002 | 600 | 2018-01-06 00:00:00 |
| 5 | 6 | u0002 | 500 | 2018-01-05 00:00:00 |
+---------+----------+---------+--------+---------------------+

分布函数cume_dist()

用途:分组内大于等于当前rank值的行数/分组内总行数。

应用场景:大于等于当前订单金额的订单比例有多少。

1
2
3
4
5
6
select 
rank() over w as row_num,
cume_dist() over w as percent,
order_id,user_no,amount,create_date
from order_info
window w as (partition by user_no order by amount desc);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+---------+---------+----------+---------+--------+---------------------+
| row_num | percent | order_id | user_no | amount | create_date |
+---------+---------+----------+---------+--------+---------------------+
| 1 | 0.2 | 5 | u0001 | 900 | 2018-01-20 00:00:00 |
| 2 | 0.4 | 4 | u0001 | 800 | 2018-01-10 00:00:00 |
| 3 | 0.8 | 2 | u0001 | 300 | 2018-01-02 00:00:00 |
| 3 | 0.8 | 3 | u0001 | 300 | 2018-01-02 00:00:00 |
| 5 | 1 | 1 | u0001 | 100 | 2018-01-01 00:00:00 |
| 1 | 0.4 | 9 | u0002 | 800 | 2018-01-16 00:00:00 |
| 1 | 0.4 | 10 | u0002 | 800 | 2018-01-22 00:00:00 |
| 3 | 0.6 | 7 | u0002 | 600 | 2018-01-06 00:00:00 |
| 4 | 0.8 | 6 | u0002 | 500 | 2018-01-05 00:00:00 |
| 5 | 1 | 8 | u0002 | 300 | 2018-01-10 00:00:00 |
+---------+---------+----------+---------+--------+---------------------+

聚合函数avg()、sum()、max()、min()

我们可以在聚合函数中使用窗口功能,比如sum(amount)over(partition by user_no order by create_date) as sum_amont,达到一个累积计算sum的功能。这种需求在没有窗口函数的情况下,用纯sql写起来很麻烦。

应用场景:每个用户按照订单id,截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少?

1
2
3
4
5
6
7
8
select 
order_id,user_no,amount,create_date,
sum(amount) over w as sum_amount,
avg(amount) over w as avg_amount,
max(amount) over w as max_amount,
min(amount) over w as min_amount
from order_info
window w as (partition by user_no order by create_date desc);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----------+---------+--------+---------------------+------------+------------+------------+------------+
| order_id | user_no | amount | create_date | sum_amount | avg_amount | max_amount | min_amount |
+----------+---------+--------+---------------------+------------+------------+------------+------------+
| 5 | u0001 | 900 | 2018-01-20 00:00:00 | 900 | 900.0000 | 900 | 900 |
| 4 | u0001 | 800 | 2018-01-10 00:00:00 | 1700 | 850.0000 | 900 | 800 |
| 2 | u0001 | 300 | 2018-01-02 00:00:00 | 2300 | 575.0000 | 900 | 300 |
| 3 | u0001 | 300 | 2018-01-02 00:00:00 | 2300 | 575.0000 | 900 | 300 |
| 1 | u0001 | 100 | 2018-01-01 00:00:00 | 2400 | 480.0000 | 900 | 100 |
| 10 | u0002 | 800 | 2018-01-22 00:00:00 | 800 | 800.0000 | 800 | 800 |
| 9 | u0002 | 800 | 2018-01-16 00:00:00 | 1600 | 800.0000 | 800 | 800 |
| 8 | u0002 | 300 | 2018-01-10 00:00:00 | 1900 | 633.3333 | 800 | 300 |
| 7 | u0002 | 600 | 2018-01-06 00:00:00 | 2500 | 625.0000 | 800 | 300 |
| 6 | u0002 | 500 | 2018-01-05 00:00:00 | 3000 | 600.0000 | 800 | 300 |
+----------+---------+--------+---------------------+------------+------------+------------+------------+

前后函数lag()、lead()

  • lag(column,n)获取当前数据行按照某种排序规则的上n行数据的某个字段
  • lead(column,n)获取当前数据行按照某种排序规则的下n行数据的某个字段

应用场景:按照时间排序,获取当前订单的上一笔订单发生时间和下一笔订单发生时间,(可以计算订单的时间上的间隔度或者说买买买的频繁程度)。

1
2
3
4
5
6
select 
order_id,user_no,amount,create_date,
lag(create_date,1) over w 'last_transaction_time',
lead(create_date,1) over w 'next_transaction_time'
from order_info
window w as (partition by user_no order by create_date asc);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----------+---------+--------+---------------------+-----------------------+-----------------------+
| order_id | user_no | amount | create_date | last_transaction_time | next_transaction_time |
+----------+---------+--------+---------------------+-----------------------+-----------------------+
| 1 | u0001 | 100 | 2018-01-01 00:00:00 | NULL | 2018-01-02 00:00:00 |
| 2 | u0001 | 300 | 2018-01-02 00:00:00 | 2018-01-01 00:00:00 | 2018-01-02 00:00:00 |
| 3 | u0001 | 300 | 2018-01-02 00:00:00 | 2018-01-02 00:00:00 | 2018-01-10 00:00:00 |
| 4 | u0001 | 800 | 2018-01-10 00:00:00 | 2018-01-02 00:00:00 | 2018-01-20 00:00:00 |
| 5 | u0001 | 900 | 2018-01-20 00:00:00 | 2018-01-10 00:00:00 | NULL |
| 6 | u0002 | 500 | 2018-01-05 00:00:00 | NULL | 2018-01-06 00:00:00 |
| 7 | u0002 | 600 | 2018-01-06 00:00:00 | 2018-01-05 00:00:00 | 2018-01-10 00:00:00 |
| 8 | u0002 | 300 | 2018-01-10 00:00:00 | 2018-01-06 00:00:00 | 2018-01-16 00:00:00 |
| 9 | u0002 | 800 | 2018-01-16 00:00:00 | 2018-01-10 00:00:00 | 2018-01-22 00:00:00 |
| 10 | u0002 | 800 | 2018-01-22 00:00:00 | 2018-01-16 00:00:00 | NULL |
+----------+---------+--------+---------------------+-----------------------+-----------------------+

头尾函数first_value()、last_value()

头尾函数可以得到分区中的第一个/最后一个指定参数的值。

应用场景:查询截止到当前订单,按照日期排序第一个订单和最后一个订单的订单金额。

1
2
3
4
5
6
select 
order_id,user_no,amount,create_date,
first_value(create_date) over w 'first_transaction_time',
last_value(create_date) over w 'last_transaction_time'
from order_info
window w as (partition by user_no order by create_date asc);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----------+---------+--------+---------------------+------------------------+-----------------------+
| order_id | user_no | amount | create_date | first_transaction_time | last_transaction_time |
+----------+---------+--------+---------------------+------------------------+-----------------------+
| 1 | u0001 | 100 | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 |
| 2 | u0001 | 300 | 2018-01-02 00:00:00 | 2018-01-01 00:00:00 | 2018-01-02 00:00:00 |
| 3 | u0001 | 300 | 2018-01-02 00:00:00 | 2018-01-01 00:00:00 | 2018-01-02 00:00:00 |
| 4 | u0001 | 800 | 2018-01-10 00:00:00 | 2018-01-01 00:00:00 | 2018-01-10 00:00:00 |
| 5 | u0001 | 900 | 2018-01-20 00:00:00 | 2018-01-01 00:00:00 | 2018-01-20 00:00:00 |
| 6 | u0002 | 500 | 2018-01-05 00:00:00 | 2018-01-05 00:00:00 | 2018-01-05 00:00:00 |
| 7 | u0002 | 600 | 2018-01-06 00:00:00 | 2018-01-05 00:00:00 | 2018-01-06 00:00:00 |
| 8 | u0002 | 300 | 2018-01-10 00:00:00 | 2018-01-05 00:00:00 | 2018-01-10 00:00:00 |
| 9 | u0002 | 800 | 2018-01-16 00:00:00 | 2018-01-05 00:00:00 | 2018-01-16 00:00:00 |
| 10 | u0002 | 800 | 2018-01-22 00:00:00 | 2018-01-05 00:00:00 | 2018-01-22 00:00:00 |
+----------+---------+--------+---------------------+------------------------+-----------------------+

注意:如果不加order by, 就没有窗口,计算范围是整个分区;如果加上order by, 默认窗口是range between unbounded preceding and current row,就是排序后从分区第一行一直到当前行为止

由于我们需要求的是每个用户的第一个和最后一个订单,所以这里要指定窗口:从第一行unbounded preceding到最后一行unbounded following

1
2
3
4
5
6
select 
order_id,user_no,amount,create_date,
first_value(create_date) over w 'first_transaction_time',
last_value(create_date) over w 'last_transaction_time'
from order_info
window w as (partition by user_no order by create_date asc rows between unbounded preceding and unbounded following);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
+----------+---------+--------+---------------------+------------------------+-----------------------+
| order_id | user_no | amount | create_date | first_transaction_time | last_transaction_time |
+----------+---------+--------+---------------------+------------------------+-----------------------+
| 1 | u0001 | 100 | 2018-01-01 00:00:00 | 2018-01-01 00:00:00 | 2018-01-20 00:00:00 |
| 2 | u0001 | 300 | 2018-01-02 00:00:00 | 2018-01-01 00:00:00 | 2018-01-20 00:00:00 |
| 3 | u0001 | 300 | 2018-01-02 00:00:00 | 2018-01-01 00:00:00 | 2018-01-20 00:00:00 |
| 4 | u0001 | 800 | 2018-01-10 00:00:00 | 2018-01-01 00:00:00 | 2018-01-20 00:00:00 |
| 5 | u0001 | 900 | 2018-01-20 00:00:00 | 2018-01-01 00:00:00 | 2018-01-20 00:00:00 |
| 6 | u0002 | 500 | 2018-01-05 00:00:00 | 2018-01-05 00:00:00 | 2018-01-22 00:00:00 |
| 7 | u0002 | 600 | 2018-01-06 00:00:00 | 2018-01-05 00:00:00 | 2018-01-22 00:00:00 |
| 8 | u0002 | 300 | 2018-01-10 00:00:00 | 2018-01-05 00:00:00 | 2018-01-22 00:00:00 |
| 9 | u0002 | 800 | 2018-01-16 00:00:00 | 2018-01-05 00:00:00 | 2018-01-22 00:00:00 |
| 10 | u0002 | 800 | 2018-01-22 00:00:00 | 2018-01-05 00:00:00 | 2018-01-22 00:00:00 |
+----------+---------+--------+---------------------+------------------------+-----------------------+

参考

赞赏一杯咖啡
0%