MySQL8窗口函数详解-结合案例

窗口函数再深入,结合案例详解

MySQL窗口函数列表

名称 描述
序号函数
ROW_NUMBER 为其分区中的每一行分配一个连续整数
DENSE_RANK 根据ORDER BY子句为其分区中的每一行分配一个排名。 它为具有相同值的行分配相同的排名。 如果两行或更多行具有相同的等级,则排序值序列中将没有间隙。
RANK DENSE_RANK()函数类似,只是当两行或更多行具有相同的排名时,排序值序列中存在间隙。
分布函数
PERCENT_RANK 计算分区或结果集中行的百分位数
CUME_DIST 计算一组值中值的累计分布
前后函数
LAG 返回分区中当前行之前的第N行的值。 如果不存在前一行,则返回NULL。
LEAD 返回分区中当前行之后的第N行的值。 如果不存在后续行,则返回NULL。
头尾函数
FIRST_VALUE 返回指定表达式相对于窗口框架中第一行的值。
LAST_VALUE 返回指定表达式相对于窗口框架中最后一行的值。
聚合函数
SUM,AVG,COUNT,MAX
MEDIAN 中位数
STDDEV 总体标准差
STDDEV_SAMP 样本标准差
其他函数
NTH_VALUE 返回窗口框架第N行的参数值
NTILE 将每个窗口分区的行分配到指定数量的已排名组中。

窗口函数详解-结合案例

以订单表orders为例,来介绍每个函数的使用方法。表中各字段含义按顺序分别为order_id订单号user_no用户idamount订单金额create_date订单创建日期

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE `orders` (
`order_id` int(11) NOT NULL,
`user_no` int(11) NOT NULL,
`amount` decimal(14,2) NOT NULL,
`create_date` datetime NOT NULL,
PRIMARY KEY (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO orders ( order_id, user_no, amount, create_date )
VALUES
( 1, 1, 100, '2020-01-01 00:00:00' ),
( 2, 1, 300, '2020-01-02 00:00:00' ),
( 3, 1, 500, '2020-01-02 00:00:00' ),
( 4, 1, 800, '2020-01-03 00:00:00' ),
( 5, 1, 900, '2020-01-04 00:00:00' ),
( 6, 2, 500, '2020-01-03 00:00:00' ),
( 7, 2, 600, '2020-01-04 00:00:00' ),
( 8, 2, 300, '2020-01-10 00:00:00' ),
( 9, 2, 800, '2020-01-16 00:00:00' ),
( 10, 2, 800, '2020-01-22 00:00:00' );

序号函数

  • 序号函数: row_number(), rank(), dense_rank()
    • 用途: 显示分区中的当前行号
    • 使用场景: 查询每个用户订单金额最高的前三个订单
1
2
3
4
5
6
SELECT * FROM ( 
SELECT
ROW_NUMBER() over ( PARTITION BY user_no ORDER BY amount DESC ) AS row_num,
order_id, user_no, amount, create_date
FROM orders
) t WHERE row_num <= 3

此时可以使用ROW_NUMBER()函数按照用户进行分组并按照订单金额进行由大到小排序,最后查找每组中序号<=3的记录。

1
2
3
4
5
6
7
row_num  order_id  user_no amount  create_date
1 5 1 900 2020-01-04 00:00:00
2 4 1 800 2020-01-03 00:00:00
3 3 1 500 2020-01-02 00:00:00
1 9 2 800 2020-01-16 00:00:00
2 10 2 800 2020-01-22 00:00:00
3 7 2 600 2020-01-04 00:00:00

对于用户2的订单,大家发现订单金额为800的有两条,序号随机排了1和2,但很多情况下二者应该是并列第一,而订单为600的序号则可能是第二名,也可能为第三名,这时候,row_number就不能满足需求,需要rankdense_rank出场。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT	* FROM
(
SELECT
ROW_NUMBER() over ( PARTITION BY user_no ORDER BY amount DESC ) AS row_num1,
RANK() over ( PARTITION BY user_no ORDER BY amount DESC ) AS row_num2,
DENSE_RANK() over ( PARTITION BY user_no ORDER BY amount DESC ) AS row_num3,
order_id,
user_no,
amount,
create_date
FROM
orders
) t
WHERE row_num1 <= 3
1
2
3
4
5
6
7
row_num1    row_num2    row_num3    order_id    user_no  amount  create_date
1 1 1 5 1 900 2020-01-04 00:00:00
2 2 2 4 1 800 2020-01-03 00:00:00
3 3 3 3 1 500 2020-01-02 00:00:00
1 1 1 9 2 800 2020-01-16 00:00:00
2 1 1 10 2 800 2020-01-22 00:00:00
3 3 2 7 2 600 2020-01-04 00:00:00
  • row_number()在amount都是800的两条记录上随机排序,但序号按照1、2递增,后面amount为600的的序号继续递增为3,中间不会产生序号间隙;
  • rank()/dense_rank()则把amount为800的两条记录序号都设置为1,但后续amount为600的需要则分别设置为3(rank)和2(dense_rank)。

    即rank()会产生序号相同的记录,同时可能产生序号间隙;而dense_rank()也会产生序号相同的记录,但不会产生序号间隙。

分布函数

  • 分布函数: percent_rank(), cume_dist()
    • 用途: 和之前的RANK()函数相关,每行按照如下公式进行计算:(rank - 1) / (rows - 1)
    • 应用场景: 大于等于当前订单金额的订单比例有多少。
1
2
3
4
5
6
7
8
9
10
11
SELECT * FROM
(
SELECT
RANK() over w AS row_num,
CUME_DIST() over w AS percent,
order_id,
user_no,
amount
FROM
orders window w AS ( PARTITION BY user_no ORDER BY amount DESC )
) t;
1
2
3
4
5
6
7
8
9
10
11
row_num  percent  order_id  user_no   amount
1 0.2 5 1 900
2 0.4 4 1 800
3 0.6 3 1 500
4 0.8 2 1 300
5 1 1 1 100
1 0.4 9 2 800
1 0.4 10 2 800
3 0.6 7 2 600
4 0.8 6 2 500
5 1 8 2 300

前后函数

  • 前后函数: lead(), lag()
    • 用途: 分区中位于当前n行(lead) / 后n行(lag)的记录值
    • 使用场景: 查询上一个订单距离当前订单的时间间隔
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT
order_id,
user_no,
amount,
create_date,
last_date,
DATEDIFF( create_date, last_date ) AS diff
FROM
(
SELECT
order_id,
user_no,
amount,
create_date,
lag( create_date, 1 ) over w AS last_date
FROM
orders window w AS ( PARTITION BY user_no ORDER BY create_date )
) t;
1
2
3
4
5
6
7
8
9
10
11
order_id  user_no  amount    create_date          last_date          diff
1 1 100 2020-01-01 00:00:00
2 1 300 2020-01-02 00:00:00 2020-01-01 00:00:00 1
3 1 500 2020-01-02 00:00:00 2020-01-02 00:00:00 0
4 1 800 2020-01-03 00:00:00 2020-01-02 00:00:00 1
5 1 900 2020-01-04 00:00:00 2020-01-03 00:00:00 1
6 2 500 2020-01-03 00:00:00
7 2 600 2020-01-04 00:00:00 2020-01-03 00:00:00 1
8 2 300 2020-01-10 00:00:00 2020-01-04 00:00:00 6
9 2 800 2020-01-16 00:00:00 2020-01-10 00:00:00 6
10 2 800 2020-01-22 00:00:00 2020-01-16 00:00:00 6

内层SQL先通过lag函数得到上一次订单的日期,外层SQL再将本次订单和上次订单日期做差得到时间间隔diff。

头尾函数

  • 头尾函数: first_val(expr), last_val(expr)
    • 用途: 得到分区中的第一个/最后一个指定参数的值
    • 使用场景: 查询截止到当前订单, 按照日期顺序第一个订单和最后一个订单的订单金额
1
2
3
4
5
6
7
8
9
SELECT * FROM
(
SELECT
order_id,user_no,amount,create_date,
FIRST_VALUE(amount) over w as first_amount,
LAST_VALUE(amount) over w as last_amount
FROM orders
window w AS ( PARTITION BY user_no ORDER BY create_date )
) t;
1
2
3
4
5
6
7
8
9
10
11
order_id  user_no  amount   create_date         first_amount  last_amount
1 1 100 2020-01-01 00:00:00 100 100
2 1 300 2020-01-02 00:00:00 100 500
3 1 500 2020-01-02 00:00:00 100 500
4 1 800 2020-01-03 00:00:00 100 800
5 1 900 2020-01-04 00:00:00 100 900
6 2 500 2020-01-03 00:00:00 500 500
7 2 600 2020-01-04 00:00:00 500 600
8 2 300 2020-01-10 00:00:00 500 300
9 2 800 2020-01-16 00:00:00 500 800
10 2 800 2020-01-22 00:00:00 500 800

结果和预期一致,比如order_id为4的记录,first_amountlast_amount分别记录了用户‘1’截止到时间2018-01-03 00:00:00为止,第一条订单金额100和最后一条订单金额800,注意这里是按时间排序的最早订单和最晚订单,并不是最小金额和最大金额订单。

其他函数

  • 其他函数: nth_value(expr,n), nfile(n)

nth_value(expr,n)

  • 用途: 返回窗口中第N个expr的值,expr可以是表达式,也可以是列名
  • 应用场景: 每个用户订单显示本用户金额第二和第三的订单金额
1
2
3
4
5
6
7
8
9
SELECT * FROM
(
SELECT
order_id,user_no,amount,create_date,
NTH_VALUE(amount,2) over w as second_amount,
NTH_VALUE(amount,3) over w as third_amount
FROM orders
window w AS ( PARTITION BY user_no ORDER BY amount )
) t;
1
2
3
4
5
6
7
8
9
10
11
order_id  user_no  amount    create_date          second_amount   third_amount
1 1 100 2020-01-01 00:00:00
2 1 300 2020-01-02 00:00:00 300
3 1 500 2020-01-02 00:00:00 300 500
4 1 800 2020-01-03 00:00:00 300 500
5 1 900 2020-01-04 00:00:00 300 500
8 2 300 2020-01-10 00:00:00
6 2 500 2020-01-03 00:00:00 500
7 2 600 2020-01-04 00:00:00 500 600
9 2 800 2020-01-16 00:00:00 500 600
10 2 800 2020-01-22 00:00:00 500 600

聚合函数

  • 用途: 在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count),可以动态计算在指定的窗口内的各种聚合函数值
  • 使用场景: 每个用户按照订单id,截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少?
1
2
3
4
5
6
7
8
9
10
11
12
SELECT * FROM
(
SELECT
order_id,user_no,amount,create_date,
sum(amount) over w as sum1,
avg(amount) over w as avg1,
max(amount) over w as max1,
min(amount) over w as min1,
count(amount) over w as count1
FROM orders
window w AS ( PARTITION BY user_no ORDER BY order_id )
) t;
1
2
3
4
5
6
7
8
9
10
11
order_id  user_no  amount  create_date           sum1  avg1    max1   min1  count1
1 1 100 2020-01-01 00:00:00 100 100 100 100 1
2 1 300 2020-01-02 00:00:00 400 200 300 100 2
3 1 500 2020-01-02 00:00:00 900 300 500 100 3
4 1 800 2020-01-03 00:00:00 1700 425 800 100 4
5 1 900 2020-01-04 00:00:00 2600 520 900 100 5
6 2 500 2020-01-03 00:00:00 500 500 500 500 1
7 2 600 2020-01-04 00:00:00 1100 550 600 500 2
8 2 300 2020-01-10 00:00:00 1400 466 600 300 3
9 2 800 2020-01-16 00:00:00 2200 550 800 300 4
10 2 800 2020-01-22 00:00:00 3000 600 800 300 5

总结

窗口函数语法

<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)

  • <窗口函数>的位置,可以放以下两种函数:
    1. 专用的窗口函数, 比如 rank(), dense_rank(), row_number()
    2. 聚合函数, 如 sum,avg,count,max,min

窗口函数的功能

    1. 同时具有 分组(partition by)排序(order by) 的功能
    1. 不减少原表的行数, 所以经常用来每组内排名

参考

Powered by Hexo and Hexo-theme-hiker

Copyright © 2017 - 2022 Keep It Simple And Stupid All Rights Reserved.

访客数 : | 访问量 :