mysql窗口函数（分析函数）知识笔记_mysql分析函数

窗口函数

• MySQL从8.0开始支持开窗函数，这个功能在大多商业数据库中早已支持，也叫分析函数。
• 开窗函数与分组聚合比较像，分组聚合是通过制定字段将数据分成多份，每一份执行聚合函数，每份数据返回一条结果。
• 开窗函数也是通过指定字段将数据分成多份，也就是多个窗口，对每个窗口的每一行执行函数，每个窗口返回等行数的结果。
• 窗口函数分为静态窗口和滑动窗口，静态窗口的大小是固定的，滑动窗口的大小可以根据设置进行变化，在当前窗口下生成子窗口。

1、窗口函数的定义

窗口函数作用于一个数据集合。窗口函数的一个概念就是当前行，当前行属于某个窗口就是从整个数据集选取一部分数据进行聚合/排名等操作。

2、窗口函数的语法

语法：函数名([参数]) over(partition by [分组字段] order by [排序字段] asc/desc rows/range between 起始位置 and 结束位置)

函数解读：函数分为两个部分，第一部分是函数名称，开窗函数的数量较少，只有11个窗口函数+聚合函数（所有聚合函数都可以用作开窗函数），根据函数性质，有的要写参数，有的不需要写参数；

第二部分是over语句，over()是必须要写的，里面有三个参数，都是非必须参数，根据需求选写：

1.第一个参数是 partition by +分组字段，将数据根据此字段分成多份，如果不加partition by参数，那会把整个数据当做一个窗口。

2.第二个参数是 order by +排序字段，每个窗口的数据要不要进行排序。

3.第三个参数 rows/range between 起始位置 and 结束位置，这个参数仅针对滑动窗口函数有用，是在当前窗口下分出更小的子窗口。其中起始位置和结束位置可写：current row 边界是当前行，unbounded preceding 边界是分区中的第一行，unbounded following 边界是分区中的最后一行，expr preceding 边界是当前行减去expr的值，expr following 边界是当前行加上expr的值。rows是基于行数，range是基于值的大小，到讲解到滑动窗口函数时再详细介绍。

3、窗口函数中的元素

1) 函数名 window_function_name：

静态窗口函数不能用frame子句；滑动窗口函数指加入order by或frame子句后，函数区域变为到当前行的数据集。

静态窗口函数：

排名函数 rank（）、dense_rank()、row_number()；

滑动窗口函数：

聚合函数 sum、 avg、percent_rank()；

取值函数 first_value()、last_value()、nth_value()、lag()、lead()、ntile()

2) 分区 partition_defintion

窗口按照指定字段进行分区，分区语句为"partition by 指定字段"，只有窗口函数功能在分区内执行，并在跨越分区边界时重新初始化。如果没有指定 partition by 语句，且没有后面的frame元素限制，就把所有数据当做一整个区。

3) 排序 order_definition

按照指定字段进行排序，排序语句为"order by 指定字段"，窗口函数将按照排序后的排列数据。和partition by 子句配合使用，就是对分区后的数据进行排序；如果单独使用且没有后面的frame元素的限制，就是对整个区的所有数据进行排序。

4) 框架 frame_definition

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

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

使用BETWEEN frame_start AND frame_end语法来表示行范围，frame_start和frame_end可以支持如下关键字，来确定不同的动态行记录：

CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用

UNBOUNDED PRECEDING 边界是分区中的第一行

UNBOUNDED FOLLOWING 边界是分区中的最后一行

expr PRECEDING 当前行之前的expr(数字或表达式)行

expr FOLLOWING 当前行之后的expr(数字或表达式)行

4、窗口函数的应用

有成绩表sc,字段分别是学生编号s_id，课程编号c_id，成绩score

1）排名函数 rank()，dense_rank()、row_number() 静态窗口（不用frame）

– 查询每位学生的成绩总分并排名

思路：先找每位同学的总成绩，然后再排名。

SELECT s_id,SUM(score)总成绩,RANK()over(ORDER BY SUM(score) DESC )排名 FROM sc
GROUP BY s_id;
1
2

窗口函数的分区，只对窗口函数有作用，而对sum函数是没有作用，求出来只有一个结果总成绩。

SELECT s_id,SUM(score)总成绩,RANK() over(partition by s_id ORDER BY SUM(score) DESC )排名 
FROM sc;
1
2

SELECT s_id,SUM