为心灵开扇窗 --窗口函数

目录

1.概念

语法结构：

2.和窗口函数一起使用的函数

2.1 序号函数

2.2  聚合函数

2.3  分布函数

2.4  前后函数

2.5  头尾函数

2.6  其他函数

3.总结

思考：group by 和 partition by的区别

---

1.概念

        窗口函数是mysql8.0版本以上提供的功能，也称为开窗函数.是对窗口内数据的分组统计，并且可以把结果单独展开一列，不影响其它列的查询效果。

语法结构：

window_function ( expr ) OVER ( 
  PARTITION BY 分组的列... 
  ORDER BY 排序列... 
  [ rows between 起始行 and 结束行
    unbound preceding    表示第1行
    n preceding	     n表示数字, 表示向上n行, 例如: 3 preceding 表示 向上 3行.
    current row          表示 当前行
    n following          n表示数字, 表示向下n行, 例如: 3 following 表示 向下 3行.
    unbound following    表示最后1行
  ]
)

其中，window_function 是窗口函数的名称；expr 是参数，有些函数不需要参数；

over子句包含三个选项：

1)  分区（partition by）: partition by选项用于将数据行拆分成多个分区（组），它的作用类似于group by分组。如果省略了 partition by，所有的数据作为一个组进行计算

2)  排序（order by）: over 子句中的order by选项用于指定分区内的排序方式，与 order by 子句的作用类似

3)  以及窗口大小（frame_clause）:frame_clause选项用于在当前分区内指定一个计算窗口，也就是一个与当前行相关的数据子集。

2.和窗口函数一起使用的函数

2.1 序号函数

序号函数有三个：row_number()、rank()、dense_rank()，可以用来实现分组排序，并添加序号。

• rank()：有并列的情况出现时序号会重复但不连续。

• dense_rank()：有并列的情况时序号会重复但连续。

• row_number()：返回连续唯一的行号，序号不会重复且连续

2.2  聚合函数

        在窗口中每条记录动态地应用聚合函数（SUM()、AVG()、MAX()、MIN()、COUNT()），可以动态计算在指定的窗口内的各种聚合函数值。

sum():  求和

avg():  求平均

max():  求最大

min():  求最小

count():  计数

2.3  分布函数

（1）cume_dist ():    累积分布值

  用途：分组内小于、等于当前rank值的行数 / 分组内总行数  

  适例：查询小于等于当前薪资（salary）的比例

（2）percent_rank():    等级值，每行按照公式(rank-1) / (rows-1)进行计算

  其中，rank为rank()函数产生的序号，rows为当前窗口的记录总行数

2.4  前后函数

（1）lag(expr,n)：  返回位于当前行的前n行

（2）lead(expr,n)：  返回位于当前行的后n行的expr的值

  适例：查询前1名同学的成绩和当前同学成绩的差值

2.5  头尾函数

（1）first_value(expr)：  返回第一个expr的值

（2）last_value(expr)：  返回最后一个expr的值

  适例：截止到当前，按照日期排序查询第1个入职和最后1个入职员工的薪资

2.6  其他函数

（1）nth_value(expr,n)： 返回窗口中第n个expr的值。expr可以是表达式，也可以是列名

  适例：截止到当前薪资，显示每个员工的薪资中排名第2或者第3的薪资

（2）ntile()：  将分区中的有序数据分为n个等级，记录等级数

  适例：将每个部门员工分成3组并按照入职日期排序

3.总结

概述:      

         窗口函数指的是 over()函数, 它相当于给表新增一列, 至于新增的内容是什么, 取决于你的 窗口函数 结合了 什么函数一起使用.

问题: 能和窗口函数一起使用的函数有哪些呢?    

答:                

情境1: 窗口函数 + 聚合函数(count, sum, max, min, avg)        

情境2: 窗口函数 + 排序函数(row_number, rank, dense_rank, ntile)        

情境3: 窗口函数 + 其他函数(lag, lead, first_value, last_value)    

格式:        

        可以结合窗口函数一起使用的函数  over(partition by 分组的列 order by 排序列 rows between 起始行 and 结束行)    

打起精神，注意啦:        

        1. 窗口函数是MySQL8.0的特性, 你的MySQL必须是8.X及其以上版本才可以使用.        

        2. 窗口函数相当于给表新增一列, 至于加的内容是什么, 取决于 结合了什么函数一起使用.      

        3. 如果over()里边什么都不写, 默认操作的是: 表中 该列所有的数据.      

        4. 如果写了partition by(表示分组): 则默认操作 组内所有的数据.        

        5. 如果写了order by(表示排序):     则默认操作 组内第一行 至 当前行的数据.        

        6. 如果要执行操作数据的范围, 即: 从哪一行开始, 到哪一行结束, 可以使用 rows between 起始行 and 结束行,

它主要涉及的参数如下:            

        unbound preceding： 表示第1行            

        n  preceding：n表示数字, 表示向上n行,    例:     3 preceding： 表示 向上 3行.           

        current row： 表示 当前行            

        n  following：n表示数字, 表示向下n行,      例:     3 following ：表示 向下 3行.          

        unbound following :  表示最后1行

思考：group by 和 partition by的区别

• 使用场景不同

  • group by 分组是为了聚合，分组聚合属于：多进一出

  • partition by分区是为了配合窗口函数做运算，窗口函数属于：一进一出