MySQL 8.0 新特性 —— 窗口函数 详解

1. 窗口函数定义解读

窗口函数介于单行函数和分组函数之间。与分组函数相比，窗口函数对记录进行分组聚合之后，将聚合结果置于每一条记录之中。

窗口函数根据窗口是否可变可以分为静态窗口函数和动态窗口函数。

2. 窗口函数语法

函数 OVER（[PARTITION BY 字段名][ORDER BY 字段名 ASC|DESC][frame_definition]） as 窗口名

 (1) over 关键字指定窗口的范围，即窗口数据集由partition、order 和 frame 子句共同决定；

若省略所有子句（即over()) ，则默认窗口数据集为符合where筛选的所有数据，基于所有记录进行聚合后将结果置于每一条记录之中（例若有100条记录，sum聚合后为一个值，则每条记录都有一个相同的sum值）；

（2）partition by 子句：指定窗口按照哪些字段进行分组，分组后，窗口函数可在每个组内分别执行。若没有指定partition子句，且后面没有frame子句，则将所有数据当成一组；

（3）order by 子句：指定窗口数据按照哪些字段进行排序。指定order by子句后，函数区域变为到当前行的数据集（因此有一个很好的累计功能！）。若和partition子句连用，函数区域即为某分区内数据排序后从首行到当前行的数据集（累计功能好好体会！！！！！！！！！！）。

（4）frame 子句：框架frame是当前分区的一个子集，在分区里面再进一步细分窗口，子句用来定义子集的规则，通常用来作为滑动窗口使用。静态窗口函数没有frame子句。

frame_unit有两种，分别是ROWS和RANGE，ROWS是基于行号，RANGE是基于值的范围。

使用ROWS BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字：

关键字	含义
CURRENT ROW	当前行，一般和其他范围关键字一起使用
UNBOUNDED PRECEDING	分区中的第一行
UNBOUNDED FOLLOWING	分区中的最后一行
expr PRECEDING	当前行之前的expr(数字或表达式)行
expr FOLLOWING	当前行之后的expr(数字或表达式)行

3. 具体窗口函数

（1）排序函数 （静态窗口函数--组内排序）

假设某一分区内number列的值经过排序后为 1，1，2，3 （注意是值），则在以下三个窗口函数中得到的排序（注意是排序，可命名为rk列）为：

① row_number()        rk列依次为1，2, 3, 4

② rank()   rk列依次为1,1,3,4

③ dense_rank()      rk列依次为1,1,2,3   

（2）分布函数（动态窗口函数）

① percent_rank()  累计百分比。为小于该条记录的所有记录的行数/(该分组内所有记录的行数-1)

② cume_dist()   累计分布值。 为小于等于该条记录的所有记录的行数/(该分组内所有记录的行数)

（3）前后函数（动态窗口函数）

① lag(expr, n)  当前行的前n行。若无，则取null。

② lead(expr, n)  当前行的后n行。若无， 则取null。

（4）首尾函数（动态窗口函数）

① first_value(expr) 返回分组内截止当前行的第一个值。

② last_value(expr)  返回分组内截止当前行的最后一个值。

（5）其他取值函数

① nth_value(expr, n) 返回分组内截止当前行的第n个值。

② ntile(n) 将当前分组里的记录分为n个桶，返回当前记录的分桶号。

（6）聚合函数

窗口函数还可以为任意聚合函数，如avg, sum, max 等。（注意牢记累计思想，即一但添加了order字句或者frame字句，则函数区域都变为到当前行为止，类似于累计思想）

4. 窗口函数的应用

推荐几个牛客上的题目，帮助更好理解。

每篇文章同一时刻最大在看人数_牛客题霸_牛客网    （难度：中等）

国庆期间每类视频点赞量和转发量_牛客题霸_牛客网   （难度：较难）

连续签到领金币_牛客题霸_牛客网   （难度：困难）

有一定难度，做完再好好理解，我觉得重点就在于这个累计！！！！！！！！！！！！！！