Hive分析函数

Hive分析函数

image.png

Example:

Ntile（分片）

使用场景：计算百分之几的用户的结果

• 给了用户和每个用户对应的消费信息表， 计算花费前50%的用户的平均消费；

-- 把用户和消费表，按消费下降顺序平均分成2份
drop table if exists test_by_payment_ntile;
create table test_by_payment_ntile as
select 
      nick, 
      payment ,
      NTILE(2) OVER(ORDER BY payment desc) AS rn 
from test_nick_payment;

-- 分别对每一份计算平均值，就可以得到消费靠前50%和后50%的平均消费
select 
   'avg_payment' as inf,
   t1.avg_payment_up_50 as avg_payment_up_50,
   t2.avg_payment_down_50 as avg_payment_down_50
from
 (select
         avg(payment) as avg_payment_up_50 
  from test_by_payment_ntile 
  where rn=1
)t1
   join
(select 
          avg(payment) as avg_payment_down_50 
 from test_by_payment_ntile 
 where rn=2
)t2
on (t1.dp_id=t2.dp_id);

Rank,Dense_Rank,Row_Number

使用场景：Top N

• Rank ： 相同的排名会留下空缺，1，2，2，4
• Dense_Rank: 相同的排名不会留下空缺，1，2，2，3
• Row_Number:不会重复

Lag,Lead

使用场景：计算用户页面的停留时间

统计窗口内往上（往下）第n行值,当前行不算

-- 组内排序后，向后或向前偏移
-- 如果省略掉第三个参数，默认为NULL，否则补上。
select
    dp_id,
    mt,
    payment,
    LAG(mt,2) over(partition by dp_id order by mt) mt_new
from test2;

image.png

-- 组内排序后，向后或向前偏移
-- 如果省略掉第三个参数，默认为NULL，否则补上。
select
   dp_id,
   mt,
   payment,
   LEAD(mt,2,'1111-11') over(partition by dp_id order by mt) mt_new
from test2;

image.png

FIRST_VALUE, LAST_VALUE

使用场景：计算每个部门的最高工资与最低工资

-- FIRST_VALUE      获得组内当前行往前的首个值
-- LAST_VALUE       获得组内当前行往前的最后一个值
-- FIRST_VALUE(DESC) 获得组内全局的最后一个值
select
   dp_id,
   mt,
   payment,
   FIRST_VALUE(payment) over(partition by dp_id order by mt) payment_g_first,
   LAST_VALUE(payment) over(partition by dp_id order by mt) payment_g_last,
  FIRST_VALUE(payment) over(partition by dp_id order by mt desc) payment_g_last_global
from test2
ORDER BY dp_id,mt;

image.png

多维度

使用场景：计算每一类圈子的观看量，和每一类圈子下每一个标签视频的观看量

-- grouping sets 
select 
       order_id,
       departure_date,
       count(*) as cnt
  from ord_test
 where order_id=410341346
 group by order_id,
       departure_date
 grouping sets (order_id,(order_id,departure_date))
;

---- 等价于以下
group by order_id
union all
group by order_id,departure_date

-- cube
select 
       order_id,
       departure_date,
       count(*) as cnt
  from ord_test
 where order_id=410341346
 group by order_id,
       departure_date
 with cube
 ;

---- 等价于以下
select count(*) as cnt from ord_test where order_id=410341346
union all
group by order_id
union all
group by departure_date
union all
group by order_id,departure_date

-- rollup
select 
       order_id,
       departure_date,
       count(*) as cnt
  from ord_test
 where order_id=410341346
 group by order_id,
       departure_date
 with rollup
 ;

---- 等价于以下
select count(*) as cnt from ord_test where order_id=410341346
union all
group by order_id
union all
group by order_id,departure_date

计算比当前小的百分比

使用场景：计算当前组内比你小的人数比例

cume_list: 小于等于当前值的行数/分组内总行数

percent_rank:分组内当前行的RANK值-1/分组内总行数-1

根窗口聚合函数使用相同