HIVE 窗口及分析函数 应用场景


窗口函数应用场景:
 
(1)用于分区排序
 
(2)动态Group By
 
(3)Top N
 
(4)累计计算
 
(5)层次查询
 
一、分析函数
 
用于等级、百分点、n分片等。
 
函数 说明
RANK() 返回数据项在分组中的排名,排名相等会在名次中留下空位
DENSE_RANK() 返回数据项在分组中的排名,排名相等会在名次中不会留下空位
NTILE() 返回n分片后的值
ROW_NUMBER() 为每条记录返回一个数字
Rank、DENSE_RANK
RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。
 
Eg:某产品类型有两个并列第一
 
RANK():第一二为1,第三位3
 
DENSE_RANK():第一二为1,第三位2
 
SELECTcolumn_name,RANK() OVER (ORDER BY column_name DESC) AS rank,DENSE_RANK() OVER (ORDER BY SUM(column_name) DESC) AS dense_rankFROM table_name
OVER 需要,括号内为编号顺序
 
注意:order by 时,desc NULL 值排在首位,ASC时NULL值排在末尾
 
可以通过NULLS LAST、NULLS FIRST 控制
 
RANK() OVER (ORDER BY column_name DESC NULLS LAST)
PARTITION BY 分组排列顺序
 
RANK() OVER(PARTITION BY month ORDER BY column_name DESC)
这样,就会按照month 来分,即所需要排列的信息先以month 的值来分组,在分组中排序,各个分组间不干涉
 
CUBE,ROLLUP,GROUPING SETS() 详见:HIVE增强的聚合,也可以结合RANK()使用实现具体逻辑。
 
NTILE
按层次查询,如一年中,统计出工资前1/5之的人员的名单,使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果:
select empno,ename,sum(sal),ntile(5) over (order by sum(sal) desc nulls last) til from emp group by empno,ename;
ROW_NUMBER
ROW_NUMBER()从1开始,为每条记录返回一个数字
 
SELECTROW_NUMBER() OVER (ORDER BY column_name DESC)AS row_nameFROM table_name;
二、窗口函数
 
可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
 
可以结合聚集函数SUM() 、AVG() 等使用。
 
可以结合FIRST_VALUE() 和LAST_VALUE(),返回窗口的第一个和最后一个值
 
(1)计算累计和
 
eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和
 
SELECTmonth,SUM(amount) month_amount,SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amountFROM table_nameGROUP BY monthORDER BY month;
其中:
 
SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount
 
ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序
 
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:
 
ROWS UNBOUNDED PRECEDING
 
PRECEDING:在前 N 行的意思。
 
FOLLOWING:在后 N 行的意思。
 
计算前3个月之间的和
 
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount
也可以
 
SUM( SUM(amount)) OVER (ORDER BY month 3 PRECENDING) AS cumulative_amount
前后一个月之间的和
 
SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount
窗体第一条和最后一条的值
 
FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS xxxx;
三、LAG、LEAD
 
获得相对于当前记录指定距离的那条记录的数据
 
LAG()为向前、LEAD()为向后
 
LAG(column_name1,1) OVER(ORDER BY column_name2)LEAG(column_name1,1) OVER(ORDER BY column_name2)
这样就获得前一条、后一条的数据
 
四、FIRST、LAST
 
获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合
 
SELECTMIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_monthFROM table_nameGROUP BY monthORDER BY month;
这样就可以求得一年中销量最高和最低的月份。
 
输出的是月份,但是用SUM(amount)来判断。

相关内容