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

MySQL 阅读量

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

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用户id、amount订单金额、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就不能满足需求，需要rank和dense_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_amount和last_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 - 2023 Keep It Simple And Stupid All Rights Reserved.

访客数 : | 访问量 :