Hive函数——分析函数

  |   0 评论   |   1,968 浏览


准备数据

ROW_NUMBER

ROW_NUMBER() –从1开始,按照顺序,生成分组内记录的序列
ROW_NUMBER() 的应用场景非常多,再比如,获取分组内排序第一的记录;

SELECT id,
order_date,update_date,
state,
fee,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY update_date) AS num
FROM tmp_order


 

NTILE

NTILE(n),用于将分组数据按照顺序切分成n片,返回当前切片值
NTILE不支持ROWS BETWEEN,比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
如果切片不均匀,默认增加第一个切片的分布

窗口内分为2组

SELECT id,
order_date,
update_date,
state,
fee,
NTILE(2) OVER(PARTITION BY id ORDER BY update_date) AS rn1
FROM tmp_order

 

 

id

order_date

update_date

state

fee

rn1

110012017010120170101创建1501
210012017010120170104支付1501
310012017010120170106支付1501
410012017010120170107完成1502
510012017010120170108完成1502
610052017010120170101创建2701
710052017010120170102支付2701
810052017010120170103支付2701
910052017010120170104支付2702
1010052017010120170105取消2702
1110052017010120170109取消2702
1210082017010220170102创建3001
1310082017010220170105取消3002

所有数据在一个窗口内,内分为3组

SELECT id,
order_date,update_date,
state,
fee,
NTILE(3) OVER(ORDER BY update_date) AS rn2
FROM tmp_order

 

 

id

order_date

update_date

state

fee

rn2

110012017010120170101创建1501
210052017010120170101创建2701
310052017010120170102支付2701
410082017010220170102创建3001
510052017010120170103支付2701
610012017010120170104支付1502
710052017010120170104支付2702
810052017010120170105取消2702
910082017010220170105取消3002
1010012017010120170106支付1503
1110012017010120170107完成1503
1210012017010120170108完成1503
1310052017010120170109取消2703

RANK、DENSE_RANK

—RANK() 生成数据项在分组中的排名,排名相等会在名次中留下空位
—DENSE_RANK() 生成数据项在分组中的排名,排名相等会在名次中不会留下空位

SELECT id,
order_date,
update_date,
state,
fee,
RANK() OVER(PARTITION BY id ORDER BY state) AS rn1,
DENSE_RANK() OVER(PARTITION BY id ORDER BY state) AS rn2,
ROW_NUMBER() OVER(PARTITION BY id ORDER BY state) AS rn3
FROM tmp_order
ORDER BY id, rn1

idorder_dateupdate_datestatefeern1rn2rn3
110012017010120170101创建150111
210012017010120170107完成150222
310012017010120170108完成150223
410012017010120170104支付150434
510012017010120170106支付150435
610052017010120170101创建270111
710052017010120170105取消270222
810052017010120170109取消270223
910052017010120170102支付270434
1010052017010120170103支付270435
1110052017010120170104支付270436
1210082017010220170102创建300111
1310082017010220170105取消300222


读后有收获可以支付宝请作者喝咖啡