函数名称 + over(…)
开窗函数(字段) over(partition by 分组字段 order by 排序字段 range between 起始位置 and 结束位置)、
第一部分是函数名称,开窗函数的数量较少,只有11个窗口函数+聚合函数(所有聚合函数都可以用作开窗函数),根据函数性质,有的要写参数,有的不需要写参数第一部分是函数名称,开窗函数的数量较少,只有11个窗口函数+聚合函数(所有聚合函数都可以用作开窗函数),根据函数性质,有的要写参数,有的不需要写参数
第二部分是over语句,over()是必须要写的,里面有三个参数,都是非必须参数,根据需求选写:
1. 第一个参数是 partition by +分组字段,将数据根据此字段分成多份,如果不加partition by参数,那会把整个数据当做一个窗口。
2. 第二个参数是 order by +排序字段,每个窗口的数据要不要进行排序。
第三个参数 rows/range between 起始位置 and 结束位置,这个参数仅针对滑动窗口函数有用,是在当前窗口下分出更小的子窗口。其中起始位置和结束位置可写:current row 边界是当前行,unbounded preceding 边界是分区中的第一行,unbounded following 边界是分区中的最后一行,expr preceding 边界是当前行减去expr的值,expr following 边界是当前行加上expr的值。rows是基于行数,range是基于值的大小,到讲解到滑动窗口函数时再详细介绍
一、排序函数:
1. rank()
按照某字段的排序结果添加排名,但它是跳跃的、间断的排名,例如两个并列第一名后,下一个是第三名。
2. dense_rank
dense_rank()是的排序数字是连续的、不间断。当有相同的分数时,它们的排名结果是并列的,下一个排名紧接着上一个排名,不间断。
3. row_number 无并列排序,相同数据,row_number会无规则排到两个名次
二、偏移函数
- 头尾函数first_value()和last_value()
first_value()求分组后的第一个值
last_value()求分组后的最后一个值,也就是当前行
2.前后函数lead()和lag()
lead表示向下取值,参数表示向下取值的行数,lag表示向上取值。向上或者向下取值没有的话就是null。
三、聚合函数
avg()
sum()
count()
max()和min()
四、分布函数
1. CUME_DIST
如果按升序排列,则统计:小于等于当前值的行数/总行数(number of rows ≤ current row)/(total number of rows)。如果是降序排列,则统计:大于等于当前值的行数/总行数。
2. PERCENT_RANK()
PERCENT_RANK 函数为窗口的 ORDER BY 子句所指定列中的值返回秩,但以介于 0 和 1 之间的小数形式表示,计算方法为 (RANK - 1)/(N- 1),用于表示指定数值在所有数值中的百分比位置
用法:PERCENT_RANK ( ) OVER ( ORDER BY ** DESC/ASC )