MySQL8窗口函数

前言

总结一下窗口函数呗

1.含义

窗口函数又名开窗函数，属于分析函数的一种。用于解决复杂报表统计需求的功能强大的函数。窗口函数用于计算基于组（GROUP BY）**的某种聚合值，它和聚合函数的不同之处是：窗口函数可以在分组之后的返回多行结果，而聚合函数对于每个组只返回一行。开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。

2.窗口函数的格式

Function() over(partition by query_patition_clause order by order_by_clause Window_clause )

2.1Function：

• 排名函数 ROW_NUMBER()；
• 排名函数 RANK() 和 DENSE_RANK()；
• 错行函数 lead()、lag()；
• 取值函数 First_value()和last_value()；
• 分箱函数 NTILE()；
• 统计函数，也就是我们常用的聚合函数 MAX()、MIN()、AVG()、SUM()、COUNT()

2.2开窗函数over()

partition by query_patition_clause：即分组，通过query_patition_clause进行分组，一般是表中的某一个字段，所以可以把partition by 看作与GROUP BY具有相同功能的语法。

order by order_by_clause：即排序，通过order_by_clause进行排序，一般是在分组（partition by）之后再进行排序，如此一来，就是在组内进行排序。如果没有前面的分组子句（partition by），那么就是全部数据进行排序。和普通MySQL中的查询语句一样，排序从句也支持ASC和DESC的用法。

Window_clause：窗口从句，它是排序之后的功能扩展，它标识了在排序之后的一个范围，它的格式是

rows | range between start_expr and end_expr

其中rows和range为二选其一：

• rows是物理范围，即根据order by子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关）；

• range是逻辑范围，根据order by子句排序后，指定当前行对应值的范围取值，行数不固定，只要行值在范围内，对应行都包含在内

between…and...用来指定范围的起始点和终结点，start_expr为起始点，end_expr为终结点

Start_expr为起始点，起始点有下面几种选项：

• unbounded preceding：指明窗口开始于分组的第一行，以排序之后的第一行为起点；

• current row：以当前行为起点；

• n preceding：以当前行的前面第n行为起点；

• n following：以当前行的后面第n行为起点；

end_expr为终结点，终结点有下面几种选项：

• unbounded following：以排序之后的最后一行为终点；
• current row：以当前行为终点；
• n preceding：以当前行的前面第n行为终点；
• n following：以当前行的后面第n行为终点；

3.窗口函数

3.1ROW_NUMBER()

row_number() over(partition by col1 order by col2)

row_number函数根据字段col1进行分组，在分组内部根据字段col2进行排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内的排序是连续且唯一的）

3.2RANK()与DENSE_RANK()

rank：

rank() over(partition by col1 order by col2)

rank函数根据字段col1进行分组，在分组内部根据字段col2进行跳跃排序，有相同的排名时，相同排名的数据有相同的序号，排序序号不连续。

dense_rank：

dense_rank() over(partition by col1 order by col2)

dense_rank函数根据字段col1进行分组，在分组内部根据字段col2进行连续排序，有相同的排名时，相同排名的数据有相同的序号，但是排序序号连续。

排名函数：

row_number是没有重复的一种排序，即使对于两行相同的数据，也会根据查询到的顺序进行排名；而rank函数和dense_rank函数对相同的数据会有一个相同的次序

3.3LEAD()与LAG()

lead函数与lag函数是两个偏移量函数，主要用于查找当前行字段的上一个值或者下一个值。lead函数是向下取值，lag函数是向上取值，如果向上取值或向下取值没有数据的时候显示为NULL，这两个函数的格式为：

lead(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 order by col2）
lag(EXPR,<OFFSET>,<DEFAULT>) over（partition by col1 order by col2）

• EXPR通常是直接是列名，也可以是从其他行返回的表达式；
• OFFSET是默认为1，表示在当前分区内基于当前行的偏移行数；
• DEFAULT是在OFFSET指定的偏移行数超出了分组的范围时(因为默认会返回null)，可以通过设置这个字段来返回一个默认值来替代null。

3.4FIRST_VALUE()与LAST_VALUE()

first_value函数返回一组排序值后的第一个值。

last_value返回一组排序值后的最后一个值。

first_value( EXPR ) over( partition by col1 order by col2 )
last_value( EXPR ) over( partition by col1 order by col2 )

其中EXPR通常是直接是列名，也可以是从其他行返回的表达式，根据字段col1进行分组，在分组内部根据字段col2进行排序，

3.5NTILE

NTILE函数对一个数据分区中的有序结果集进行划分。

ntile(ntile_num） OVER ( partition by col1 order by col2 )

ntile_num是一个整数，用于创建分组的数量，不能小于等于0。其次需要注意的是，在over函数内，尽量要有排序ORDER BY子句。

3.6MAX()、MIN()、AVG()、SUM()与COUNT()

在分组范围内进行聚合运算。

• MAX：最大值。
• MIN：最小值。
• AVG：平均值。
• SUM：和。
• COUNT：数量。