一、hive 窗口函数语法
在SQL中,avg()、sum()、max()、min()是主要的分析函数,而 over()是窗口函数,下面我们来看看over()窗口函数的语法结构、及常与 over()一起使用的分析函数
1、over()窗口函数的语法结构
2、常与 over()一起使用的分析函数
3、窗口函数总结
1、over()窗口函数的语法结构
分析函数 over(partition by 列名 order by 列名 rows between 开始位置 and 结束位置)
over()函数中包括三个函数:包括分区 partition by 列名、排序 order by 列名、指定窗口范围 rows between 开始位置 and 结束位置。我们在使用 over()窗口函数时,over()函数中的这三个函数可组合使用也可以不使用。
over()函数中如果不使用这三个函数,窗口大小是针对查询产生的所有数据,如果指定了分区,窗口大小是针对每个分区的数据。
1.1、over()函数中的三个函数讲解
order by
order by 是排序的意思,是该窗口中的
A、partition by
partition by 可理解为 group by 分组。over(partition by 列名)搭配分析函数时,分析函数按照每一组每一组的数据进行计算的。
B、rows between 开始位置 and 结束位置
是指定窗口范围,比如第一行到当前行。而这个范围是随着数据变化的。over(rows between 开始位置 and 结束位置)搭配分析函数时,分析函数按照这个范围进行计算的。
窗口范围说明:
我们常使用的窗口范围是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(表示从起点到当前行),常用该窗口来计算累加。
PRECEDING:往前
FOLLOWING:往后
CURRENT ROW:当前行
UNBOUNDED:起点(一般结合 PRECEDING,FOLLOWING 使用)
UNBOUNDED PRECEDING 表示该窗口最前面的行(起点)
UNBOUNDED FOLLOWING:表示该窗口最后面的行(终点)
比如说:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(表示从起点到当前行)
ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING(表示往前 2 行到往后 1 行)
ROWS BETWEEN 2 PRECEDING AND 1 CURRENT ROW(表示往前 2 行到当前行)
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING(表示当前行到终点)
常与 over()一起使用的分析函数:
2.1、聚合类
avg()、sum()、max()、min()
2.1、排名类
row_number()按照值排序时产生一个自增编号,不会重复(如:1、2、3、4、5、6)
rank() 按照值排序时产生一个自增编号,值相等时会重复,会产生空位(如:1、2、3、3、3、6)
dense_rank() 按照值排序时产生一个自增编号,值相等时会重复,不会产生空位(如:1、2、3、3、3、4)
row_number 不存在并列
dense_rank 和 rank 存在并列,但 rank 很跳。
口诀的意思是:
row_number:不考虑并列的情况,哪怕分数相同,排名都是一溜下来的自然数。
dense_rank 和 rank 考虑并列的情况,区别在于 rank 很跳,并列排名的个数会影响接下来的排名,表现为数字的中断。而 dense_rank 不管有几个并列的第 5 名,接下来都是从 6 开始排。
如下图所示
2.1、其他类
lag(列名,往前的行数,[行数为 null 时的默认值,不指定为 null]),可以计算用户上次购买时间,或者用户下次购买时间。
lead(列名,往后的行数,[行数为 null 时的默认值,不指定为 null])
ntile(n) 把有序分区中的行分发到指定数据的组中,各个组有编号,编号从 1 开始,对于每一行,ntile 返回此行所属的组的编号。
最后,祝大家端午安康,假期愉快 ~