在 Hive 中做数据分析或 OLAP 查询,除了使用上一篇提到的窗口函数之外,还有一批分析函数,主要用于排名、排序、分组等。当然,在不同的实际业务场景下,分析函数有着不同的表达,功能更加丰富。

0x00 常用的分析函数

下表列出了一些分析函数以及描述信息:

分析函数 描述
RANK 返回数据项在分区中的排名。排名值序列可能会有间隔
DENSE_RANK 返回数据项在分区中的排名。排名值序列是连续的,不会有间隔
PERCENT_RANK 计算当前行的百分比排名
CUME_DIST 计算分区中当前行的相对排名
ROW_NUMBER 确定分区中当前行的序号
NTILE 将每个分区的行尽可能均匀地划分为指定数量的分组

0x01 分析函数的使用要点

对比上一篇的窗口函数,分析函数的使用要点有:

  • 必须结合 over + order by 一起使用
  • 不能使用 window 子句
1
2
3
4
5
6
7
8
9
select gid, dt, pv
,rank() over(partition by gid order by pv) as rank_1
,dense_rank() over(partition by gid order by pv) as rank_2
,percent_rank() over(partition by gid order by pv) as rank_3
,cume_dist() over(partition by gid order by pv) as rank_4
,row_number() over(partition by gid order by pv) as row_no
,ntile(2) over(partition by gid order by pv) as bucket_no
from page_analytic
;

0x02 分析函数的业务场景

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
---把用户和消费表,按消费下降顺序平均分成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
;

参考文献

Windowing and Analytics Functions
分析函数 RANK ROW_NUMBER CUME_DIST CUME_DIST
Hive分析函数–Ntile