Hive分析函数和窗口函数

1 分析函数：用于等级、百分点、n分片等

Ntile 是Hive很强大的一个分析函数。

• 可以看成是：它把有序的数据集合 平均分配 到 指定的数量（num）个桶中, 将桶号分配给每一行。如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
• 语法是：

     ntile (num)  over ([partition_clause]  order_by_clause)  as your_bucket_num

•    然后可以根据桶号，选取前或后 n分之几的数据。

例子：

    给了用户和每个用户对应的消费信息表， 计算花费前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

SQL很熟悉的3个组内排序函数了。语法一样：

R()  over  (partion  by  col1...  order  by  col2...  desc/asc)

select 
   class1,
   score,
   rank() over(partition by class1 order by score desc) rk1,
   dense_rank() over(partition by class1 order by score desc) rk2,
   row_number() over(partition by class1 order by score desc) rk3
from zyy_test1;

如上图所示，rank  会对相同数值，输出相同的序号，而且下一个序号不间断；

       dense_rank  会对相同数值，输出相同的序号，但下一个序号，间断

       row_number 会对所有数值输出不同的序号，序号唯一连续；

2. 窗口函数 Lag, Lead, First_value,Last_value

Lag, Lead

LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值

LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值, 与LAG相反

-- 组内排序后，向后或向前偏移
-- 如果省略掉第三个参数，默认为NULL，否则补上。

select
    dp_id,
    mt,
    payment,
    LAG(mt,2) over(partition by dp_id order by mt) mt_new
from test2;

 

-- 组内排序后，向后或向前偏移
-- 如果省略掉第三个参数，默认为NULL，否则补上。

select
   dp_id,
   mt,
   payment,
   LEAD(mt,2,'1111-11') over(partition by dp_id order by mt) mt_new
from test2;

 

FIRST_VALUE, LAST_VALUE

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;