mysql 窗口函数（三）

数据分析从0到1 2021-11-23

713

前言

在mysql 窗口函数（一）、mysql 窗口函数（二）讲了序号函数、分布函数、前后函数、头尾函数，本章节我们学习一些其他少见的函数，但是能处理复杂的业务需求。

测试数据在文章《mysql窗口函数（一）》

聚合函数作为窗口函数

应用场景：求每个订单的近三次订单金额均值。

SELECT order_id,
       user_no,
       amount,
       avg(amount) OVER w AS avg_num,
                        create_date
FROM order_tab WINDOW w AS (PARTITION BY user_no
                            ORDER BY create_date DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);

从结果可以看出，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 窗口范围是当前分区中所有行，等同于不写。

用途：在窗口中每条记录动态应用聚合函数(sum/avg/max/min/count)，可以动态计算在指定的窗口内的各种聚合函数值。
应用场景：每个用户按照订单id，截止到当前的累计订单金额/平均订单金额/最大订单金额/最小订单金额/订单数是多少？

SELECT order_id,
       user_no,
       amount,
       create_date,
       sum(amount) OVER w as sum_num,
       avg(amount) OVER w as avg_num,
       max(amount) OVER w as max_num,
       min(amount) OVER w as min_num,
       count(amount) OVER w as count_num
FROM order_tab WINDOW w AS (PARTITION BY user_no
                            ORDER BY order_id);

nth_value函数

nth_value(expr,n)

用途：返回窗口中第N个expr的值，expr可以是表达式，也可以是列名。
应用场景：每个用户订单中显示本用户金额排名第二和第三的订单金额。

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 order_tab WINDOW w AS (PARTITION BY user_no
                            ORDER BY amount);

ntile函数

ntile(n)

用途：将分区中的有序数据分为n个桶，记录桶号。
应用场景：将每个用户的订单按照订单金额分成3组。

SELECT order_id,
       user_no,
       amount,
       create_date,
       ntile(3) over w as k
FROM order_tab WINDOW w AS (PARTITION BY user_no
                            ORDER BY amount);