什么是窗口函数和窗口?
窗口函数:窗口、函数(应用在窗口内的函数)—窗口类似于窗户,限定一个空间范围
那什么叫窗口呢?
窗口的概念非常重要,它可以理解为记录集合,窗口函数也就是在满足某种条件的记录集合上执行的特殊函数。对于每条记录都要在此窗口内执行函数,窗口大小都是固定的,这种属于静态窗口;不同的记录对应着不同的窗口,这种动态变化的窗口叫滑动窗口。
结构
函数() over()
其中,over是关键字,用来指定函数执行的窗口范围,包含三个分析子句:分组(partition by)子句,排序(orderby)子句,窗口(rows)子句,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下语法来设置窗口:
函数名([expr]) over(partition by <要分列的组>order by <要排序的列>rows between<数据范围>)
注意
rows between 2 preceding and current row#取当前行和前面两行
rows between unbounded preceding and current row#包括本行和之前所有的行
rows between current row and unbounded fo11owing#包括本行和之后所有的行
rows between 3 preceding and current row#包括本行和前面三行
I
rows between 3 preceding and 1 fo7lowing #从前面三行和下面一行,总共五行
#当order by后面缺少窗口从句条件,窗口规范默认是rows between unbounded preceding and currentrow.
#当order by和窗口从句都缺失,窗口规范默认是 rows between unbounded preceding and unboundedfo11owing
一般,我们可以把窗口函数分为两种:
专有窗口函数:
rank()
dense_rank()
row_number()·
聚合类窗口函数:
sum(),count(),avg(),max(),min()
例子
一、ROW_NUMBER()
Row_number() 在排名是序号 连续 不重复,即使遇到表中的两个一样的数值亦是如此
select *,row_number()
OVER(order by number )
as row_num from num
注意:在使用row_number() 实现分页时需要特别注意一点,over子句中的order by 要与SQL排序记录中的order by保持一致,否则得到的序号可能不是连续的
select *,row_number()
OVER(order by number )
as row_num from num
ORDER BY id
二、rank()
Rank() 函数会把要求排序的值相同的归为一组且每组序号一样,排序不会连续执行
select *,rank()
OVER(order by number )
as row_num from num
三、dense_rank()
Dense_rank() 排序是连续的,也会把相同的值分为一组且每组排序号一样
select *,dense_rank()
OVER(order by number )
as row_num from num
四、ntile()
Ntile(group_num) 将所有记录分成group_num个组,每组序号一样
select *,ntile(2)
OVER(order by number )
as row_num from num