hive练手03

visit.user_id visit.shop
u1 a
u2 b
u1 b
u1 a
u3 c
u4 b
u1 a
u2 c
u5 b
u4 b
u6 c
u2 c
u1 b
u2 a
u2 a
u3 a
u5 a
u5 a
u5 a

select
user_id,
shop,
count(*)ct
from
visit
group by
user_id,shop;

user_id shop ct
u1 a 3
u1 b 2
u2 a 2
u2 b 1
u2 c 2
u3 a 1
u3 c 1
u4 b 2
u5 a 3
u5 b 1
u6 c 1

select
user_id,shop,ct,
row_number() over(partition by shop order by ct desc)rk
from(
select
user_id,
shop,
count(*) ct
from
visit
group by
user_id,shop
)t1;//这里要起别名

user_id shop ct rk
u5 a 3 1
u1 a 3 2
u2 a 2 3
u3 a 1 4
u4 b 2 1
u1 b 2 2
u5 b 1 3
u2 b 1 4
u2 c 2 1
u6 c 1 2
u3 c 1 3

select
shop,user_id,ct
from(select
user_id,shop,ct,
row_number() over(partition by shop order by ct desc)rk
from(
select
user_id,
shop,
count(*) ct
from
visit
group by
user_id,shop
)t1)t2
where
rk<=3;

shop user_id ct
a u5 3
a u1 3
a u2 2
b u4 2
b u1 2
b u5 1
c u2 2
c u6 1
c u3 1