需求：
一.每个店铺的uv(访客数)
二.每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
建表:
create table visit(user_id string,shop string)
row format delimited fields terminated by '\t';
答案1
方法一：使用distribute by(不建议使用不够优化)
select shop,count(distinct user_id) uv
from
visit
group by shop;
方法二：
1.去重
select shop,user_id
from visit
group by shop,user_id;
2.计数
select shop,count(*) uv
from (select shop,count(distinct user_id) uv
from
visit
group by shop,user_id)t1
group by shop;
二、
1.计算每个人访问每个店铺的总次数
select shop,user_id,count(*) ct
from visit
group by shop,user_id;
2.针对同一店铺，对访问次数进行逆序排序，并添加rank值
select shop,user_id,ct,
row_number() over(partition by shop order by ct desc) rk
from(select shop,user_id,count(*) ct
from visit
group by shop,user_id)t1;
3.去店铺访问前三名的用户
select shop,user_id,ct
from(select shop,user_id,ct,
row_number() over(partition by shop order by ct desc) rk
from(select shop,user_id,count(*) ct
from visit
group by shop,user_id)t1)t2
where rk<=3;
