Hive实现topN

一、需求

查询每个产品top3的用户信息，初始数据表如下

uid			pid
user9		e
user2		a
user14		e
user6		b
user12		a
...			...
...			...

二、实现

1. #每个产品对应的每个用户的浏览量

select pid,uid,count(uid) as cnt from visit2 group by pid,uid order by pid,cnt desc;

#结果
pid	uid		cnt
a	user4	4
a	user9	3
a	user12	3
a	user5	2
a	user7	2
a	user0	2
a	user2	1
a	user6	1
b	user0	4
b	user14	3
b	user13	3
b	user8	2
b	user7	2
b	user3	2
b	user4	2
b	user6	1
b	user10	1
b	user1	1
b	user12	1
c	user10	4
c	user0	3
c	user9	3
c	user8	3
c	user5	3
c	user13	3
c	user7	2
c	user1	2
c	user11	1
c	user2	1
d	user5	3
d	user0	2
d	user1	2
d	user7	2
d	user9	2
d	user10	1
e	user13	3
e	user14	3
e	user4	3
e	user9	3
e	user3	2
e	user5	2
e	user12	2
e	user6	2
e	user2	1
e	user8	1
e	user1	1

2. 使用窗口函数实现排名

select pid,uid,cnt,row_number() over (partition by pid order by cnt desc ) as rank from 
(
select pid,uid,count(uid) as cnt from visit2 group by pid,uid order by pid,cnt desc
) as t1;

# 结果
pid	uid		cnt	rank
a	user4	4	1
a	user9	3	2
a	user12	3	3
a	user5	2	4
a	user7	2	5
a	user0	2	6
a	user2	1	7
a	user6	1	8
b	user0	4	1
b	user14	3	2
b	user13	3	3
b	user8	2	4
b	user7	2	5
b	user3	2	6
b	user4	2	7
b	user6	1	8
b	user10	1	9
b	user1	1	10
b	user12	1	11
c	user10	4	1
c	user0	3	2
c	user9	3	3
c	user8	3	4
c	user5	3	5
c	user13	3	6
c	user7	2	7
c	user1	2	8
c	user11	1	9
c	user2	1	10
d	user5	3	1
d	user0	2	2
d	user1	2	3
d	user7	2	4
d	user9	2	5
d	user10	1	6
e	user13	3	1
e	user14	3	2
e	user4	3	3
e	user9	3	4
e	user3	2	5
e	user5	2	6
e	user12	2	7
e	user6	2	8
e	user2	1	9
e	user8	1	10
e	user1	1	11

3. 查询结果

• 相同的值不并列名次，严格的1,2,3…

select t2.* from(
select pid,uid,cnt,row_number() over (partition by pid order by cnt desc ) as rank from 
(
select pid,uid,count(uid) as cnt from visit2 group by pid,uid order by pid,cnt desc
) as t1
) as t2
where t2.rank <= 3;

#结果
pid	uid		cnt	rank
a	user4	4	1
a	user9	3	2
a	user12	3	3
b	user0	4	1
b	user14	3	2
b	user13	3	3
c	user10	4	1
c	user0	3	2
c	user9	3	3
d	user5	3	1
d	user0	2	2
d	user1	2	3
e	user13	3	1
e	user14	3	2
e	user4	3	3

• 相同的值并列名次，

select t2.* from(
select pid,uid,cnt,dense_rank() over (partition by pid order by cnt desc ) as rank from 
(
select pid,uid,count(uid) as cnt from visit2 group by pid,uid order by pid,cnt desc
) as t1
) as t2
where t2.rank <= 3;

# 结果
pid	uid		cnt	rank
a	user4	4	1
a	user9	3	2
a	user12	3	2
a	user5	2	3
a	user7	2	3
a	user0	2	3
b	user0	4	1
b	user14	3	2
b	user13	3	2
b	user8	2	3
b	user7	2	3
b	user3	2	3
b	user4	2	3
c	user10	4	1
c	user0	3	2
c	user9	3	2
c	user8	3	2
c	user5	3	2
c	user13	3	2
c	user7	2	3
c	user1	2	3
d	user5	3	1
d	user0	2	2
d	user1	2	2
d	user7	2	2
d	user9	2	2
d	user10	1	3
e	user13	3	1
e	user14	3	1
e	user4	3	1
e	user9	3	1
e	user3	2	2
e	user5	2	2
e	user12	2	2
e	user6	2	2
e	user2	1	3
e	user8	1	3
e	user1	1	3