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:数量。
posted @ 2024-03-04 00:35  peng_boke  阅读(83)  评论(0编辑  收藏  举报