HIVE 窗口及分析函数 应用场景
评:
窗口函数应用场景:
(1)用于分区排序
(2)动态GroupBy
(3)TopN
(4)累计计算
(5)层次查询
一、分析函数
用于等级、百分点、n分片等。
函数说明
RANK()返回数据项在分组中的排名,排名相等会在名次中留下空位
DENSE_RANK()返回数据项在分组中的排名,排名相等会在名次中不会留下空位
NTILE()返回n分片后的值
ROW_NUMBER()为每条记录返回一个数字
Rank、DENSE_RANK
RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。
Eg:某产品类型有两个并列第一
RANK():第一二为1,第三位3
DENSE_RANK():第一二为1,第三位2
Sql代码收藏代码
SELECT
column_name,
RANK()OVER(ORDERBYcolumn_nameDESC)ASrank,
DENSE_RANK()OVER(ORDERBYSUM(column_name)DESC)ASdense_rank
FROMtable_name
OVER需要,括号内为编号顺序
注意:orderby时,descNULL值排在首位,ASC时NULL值排在末尾
可以通过NULLSLAST、NULLSFIRST控制
Java代码收藏代码
RANK()OVER(ORDERBYcolumn_nameDESCNULLSLAST)
PARTITIONBY分组排列顺序
Java代码收藏代码
RANK()OVER(PARTITIONBYmonthORDERBYcolumn_nameDESC)
这样,就会按照month来分,即所需要排列的信息先以month的值来分组,在分组中排序,各个分组间不干涉
CUBE,ROLLUP,GROUPINGSETS()详见:HIVE增强的聚合,也可以结合RANK()使用实现具体逻辑。
NTILE
按层次查询,如一年中,统计出工资前1/5之的人员的名单,使用NTILE分析函数,把所有工资分为5份,为1的哪一份就是我们想要的结果:
Sql代码收藏代码
selectempno,ename,sum(sal),ntile(5)over(orderbysum(sal)descnullslast)tilfromempgroupbyempno,ename;
ROW_NUMBER
ROW_NUMBER()从1开始,为每条记录返回一个数字
Sql代码收藏代码
SELECT
ROW_NUMBER()OVER(ORDERBYcolumn_nameDESC)ASrow_name
FROMtable_name;
二、窗口函数
可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。
可以结合聚集函数SUM()、AVG()等使用。
可以结合FIRST_VALUE()和LAST_VALUE(),返回窗口的第一个和最后一个值
(1)计算累计和
eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和
Java代码收藏代码
SELECT
month,SUM(amount)month_amount,
SUM(SUM(amount))OVER(ORDERBYmonthROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AScumulative_amount
FROMtable_name
GROUPBYmonth
ORDERBYmonth;
其中:
SUM(SUM(amount))内部的SUM(amount)为需要累加的值,在上述可以换为month_amount
ORDERBYmonth按月份对查询读取的记录进行排序,就是窗口范围内的排序
ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW定义起点和终点,UNBOUNDEDPRECEDING为起点,表明从第一行开始,CURRENTROW为默认值,就是这一句等价于:
ROWSUNBOUNDEDPRECEDING
PRECEDING:在前N行的意思。
FOLLOWING:在后N行的意思。
计算前3个月之间的和
Sql代码收藏代码
SUM(SUM(amount))OVER(ORDERBYmonthROWSBETWEEN3PRECEDINGANDCURRENTROW)AScumulative_amount
也可以
Java代码收藏代码
SUM(SUM(amount))OVER(ORDERBYmonth3PRECENDING)AScumulative_amount
前后一个月之间的和
Sql代码收藏代码
SUM(SUM(amount))OVER(ORDERBYmonthROWSBETWEEN1PRECEDINGAND1FOLLOWING)AScumulative_amount
窗体第一条和最后一条的值
Java代码收藏代码
FIRST_VALUE(SUM(amount))OVER(ORDERBYmonthROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASxxxx;
LAST_VALUE(SUM(amount))OVER(ORDERBYmonthROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASxxxx;
三、LAG、LEAD
获得相对于当前记录指定距离的那条记录的数据
LAG()为向前、LEAD()为向后
Sql代码收藏代码
LAG(column_name1,1)OVER(ORDERBYcolumn_name2)
LEAG(column_name1,1)OVER(ORDERBYcolumn_name2)
这样就获得前一条、后一条的数据
四、FIRST、LAST
获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合
Java代码收藏代码
SELECT
MIN(month)KEEP(DENSE_RANKFIRSTORDERBYSUM(amount))AShighest_sales_month,
MIN(month)KEEP(DENSE_RANKLASTORDERBYSUM(amount))ASlows_sales_month
FROMtable_name
GROUPBYmonth
ORDERBYmonth;
这样就可以求得一年中销量最高和最低的月份。
输出的是月份,但是用SUM(amount)来判断。