一、窗口函数简介
在日常开发工作中，经常会遇到下面这些需求
- 去医院看病，怎样知道上次就医距现在的时间？
- 环比如何计算？
- 怎么样得到各部门工资排名前N名员工列表?
- 查找各部门每人工资占部门总工资的百分比？
如果用传统SQL来解决这些问题，理论上是可以的，但逻辑会相当复杂。这类需求都有一个共同的特点，为了的到结果，都需要在某个结果集内做一些特定的函数操作。为了很方便的解决这一类问题，MySQL8.0中引入了窗口函数。
窗口的概念非常重要，它可以理解为记录集合，窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数，有的函数随着记录不同，窗口大小都是固定的，这种属于静态窗口；有的函数则相反，不同的记录对应着不同的窗口，这种动态变化的窗口叫滑动窗口。
窗口函数和聚合函数有些类似，两者最大的区别是聚合函数是多行聚合为一行，窗口函数则是多行聚合为相同的行数，每行会多一个聚合后的新列(在窗口函数中可以使用聚合函数)。窗口函数在其他数据库中（比如ORACLE数据库）也叫做分析函数，但功能大多相似。
举例：先创建表结构并导入测试数据
CREATE TABLE sales(
id INT PRIMARY KEY AUTO_INCREMENT,
city VARCHAR(15),
county VARCHAR(15),
sales_value DECIMAL
);
INSERT INTO sales ( city, county, sales_value )
VALUES
( '北京', '海淀', 10.00 ),
( '北京', '朝阳', 20.00 ),
( '上海', '黄埔', 30.00 ),
( '上海', '长宁', 10.00 );
现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售
额中的比率，以及占总销售额中的比率。若使用窗口函数可以很简单的实现：
SELECT
city,
county,
sales_value AS 区销售金额,
SUM( sales_value ) OVER ( PARTITION BY city ) AS 市销售额,
sales_value / sum( sales_value ) OVER ( PARTITION BY city ) AS 市比率,
SUM( sales_value ) OVER () AS 总销售额,
sales_value / SUM( sales_value ) OVER () AS 总比率
FROM
sales
结果如下：
显然在这种需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函可以很简单的实现。
二、窗口函数的语法结构
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数，如下表：
其基本语法结构如何
函数 OVER([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
函数 OVER window_name... WINDOW window_name AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
-
OVER 关键字指定函数窗口的范围。
如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所
有满足WHERE条件的记录进行计算。
如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
-
window_name：为窗口设置一个别名，用来标识窗口。
-
PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分
别执行。
-
ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据
记录的顺序进行编号。
-
FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
三、窗口函数的使用
添加测试数据
CREATE TABLE order_tab(
order_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '订单号',
user_no INT COMMENT '用户ID',
amount int COMMENT '订单金额',
create_time DATETIME COMMENT' 订单创建日期'
);
INSERT INTO order_tab ( order_id, user_no, amount, create_time)
VALUES
(1,001,100, '2018-01-01 00:00:00'),
(2,001,300, '2018-01-02 00:00:00'),
(3,001,500, '2017-01-01 00:00:00'),
(4,001,800, '2017-01-07 00:00:00'),
(5,001,1000, '2018-01-04 00:00:00'),
(6,002,500, '2018-01-04 00:00:00'),
(7,002,300, '2018-01-04 00:00:00'),
(8,002,1000, '2017-01-04 00:00:00'),
(9,002,8000, '2019-01-04 00:00:00'),
(10,002,2000, '2019-01-04 00:00:00')
3.1、序号函数
-
ROW_NUMBER()函数:ROW_NUMBER()函数能够对分区中的每一行记录增加一个序号，如需查询每个用户最新的一笔订单便可以使用ROW_NUMBER()函数按照用户进行分组并按照订单日期倒序，最后找出每组中序号为1的记录。
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_no ORDER BY create_time DESC ) AS row_num FROM order_tab ) t WHERE row_num = 1 # 或者使用别名 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER w AS row_num FROM order_tab WINDOW w AS ( PARTITION BY user_no ORDER BY create_time DESC ) ) t WHERE row_num = 1
row_number()后面的over是关键字，用来指定函数执行的窗口范围，如果后面的括号中什么都不写，则意味着窗口包含所有行，窗口函数在所有行上进行计算。
如要根据根据每个订单动态计算包括本订单和按时间顺序前后两个订单的平均订单金额，则可以使用上边提到的frame子句来创建滑动窗口。
SELECT * FROM
(
SELECT
*,
AVG(amount) OVER w AS avg_num
FROM order_tab
WINDOW w AS ( PARTITION BY user_no ORDER BY create_time DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
) t
从结果可以看出，order_id为5的订单属于边界值，没有前一行，因此平均订单金额为(1000+300)/2;order_id等于2的订单前后都有订单，所以平均订单金额为(1000+300+100)/3 = 466.6667,以此类推就可以得到一个基于滑动窗口的动态平均订单值。
对于滑动窗口的范围指定，有如下两种方式。
-
基于行：通常使用 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。
-
RANK()函数/DENSE_RANK()函数:RANK()和DENSE_RANK()这两个函数与ROW_NUMBER()非常类似。只是在出现重复值时处理逻辑有所不同，假设需要查询不同用户的订单，按照订单时间进行排序，显示相应的排名序号，SQL中使用ROW_NUMBER()、RANK()、DENSE_RANK()分别显示序号
如图，ROW_NUMBER在create_time都是2019-01-04 00:00:00的两条记录随机排序但序号按照1、2递增，其后面的序号不会产生序号空隙，RANK()/DENSE_RANK()则把2019-01-04 00:00:00的两条记录序号都设置为1，但后边紧接着的序号则分别设置为3(RANK)和2(DENSE_RANK)，很明显RANK()和DENSE_RANK()会产生相同的序号但是RANK()会产生序号间隙而DENSE_RANK()并不会产生序号间隙。
3.2、分布函数
-
PERCENT_RANK()函数/CUME_DIST()函数PERCENT_RANK()函数和CUME_DIST()函数都是计算数据分布的函数，PERCENT_RANK()和之前的RANK()函数相关，每行按照以下公式进行计算
(rank -1 ) / (rows -1)其中rank为RANK()函数产生的序号，rows为当前窗口的记录总行数
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_no ORDER BY create_time DESC ) AS row_num, PERCENT_RANK() OVER ( PARTITION BY user_no ORDER BY create_time DESC ) percent FROM order_tab ) t结果如下：
相较于PERCENT_RANK()，CUME_DIST()函数的应用场景更多。它的作用是分组内小于等于当前rank值的行数/分组内总行数。上例中，统计大于等于当前订单金额的订单数，占总订单数据的比例如下：
结果如下：
3.3、前后函数
-
LAG(expr,N)函数/LEAD(expr,N)函数LAG(expr,N)和LEAD(expr,N)这两个函数的功能是获取当前数据行按照某种排序规则的上N行(LAG)/下N行(LEAD)数据的某个字段。比如每个订单中希望增加一个字段，用来记录本订单距离上一个订单的时间间隔，那么就可以使用LAG函数来实现：
SELECT *, DATEDIFF( create_time,last_time ) FROM ( SELECT *, LAG( create_time,1 ) OVER w AS last_time FROM order_tab WINDOW w AS ( PARTITION BY user_no ORDER BY create_time ASC ) ) t结果如下：
3.4、首尾函数
-
FIRST_VALUE(expr)函数/LAST_VALUE(expr)函数FIRST_VALUE(expr)函数和LAST_VALUE(expr)函数的功能分别是获得滑动窗口范围内的参数字段中第一个(FIRST_VALUE)和最后一个(LAST_VALUE)的值。下例中。每个用户在每个订单记录中希望看到截止到当前订单为止，按照日期排序的最早订单和最晚订单的订单金额。
SELECT * FROM ( SELECT *, FIRST_VALUE(amount) OVER w AS first_amount, LAST_VALUE(amount) OVER w AS last_amount FROM order_tab WINDOW w AS ( PARTITION BY user_no ORDER BY create_time ASC ) ) t 结果如下：
3.5、其它函数
-
NTILE(n)函数NFILE()函数的功能是对一个数据分区中的有序结果集进行划分，将其分为N个组，并未每个小组分配一个唯一的组编号。接下来我们对每个用户的订单分为3组，NFILE()函数记录每组组编号
SELECT * FROM ( SELECT *, NTILE(3) OVER w AS nf FROM order_tab WINDOW w AS ( PARTITION BY user_no ORDER BY amount ASC ) ) t结果如下：
次函数爱数据分析中应用较多，比如由于数据量大，需要将数据分配到N个并行的进程中分别计算，此时就可以用NTILE(N)函数对数据进行分组，由于记录数不一定被N整除，所以每组记录数不一定完全一致。
-
NTH_VALUE(expr,n)函数NTH_VALUE(expr,N)函数可以返回窗口中第N个expr的值，expr既可以是表达式，也可以是列名。我们可以查找每个用户的所有订单按照金额排序的第三个订单的金额.
SELECT * FROM ( SELECT *, NTH_VALUE(amount,2) OVER w AS nth_amount FROM order_tab WINDOW w AS ( PARTITION BY user_no ORDER BY amount ASC ) ) t结果如下：
3.6、聚合函数作为窗口函数
除了以上的各类窗口函数外，我们经常使用各种聚合函数来作为窗口函数使用
SELECT *, sum(amount) OVER w AS sum_value, avg(amount) OVER w AS avg_value, max(amount) OVER w AS max_value, min(amount) OVER w AS min_value, count(amount) OVER w AS count_value FROM order_tab WINDOW w AS ( PARTITION BY user_no ORDER BY amount ASC )结果如下：
扫一扫
688
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
