已知顾客拜访店铺的拜访日志

建表、导表

create table visit(user_id string,shop string) row format delimited fields terminated by '\t';
load data local inpath '/data/visit.dat' into table visit;

求每个店铺的 UV（访客数）

select shop , count(distinct user_id) as uv from visit group by shop;

求每个店铺拜访次数 top3 的访客信息。输入店铺名称、访客 id、拜访次数

1. 先求出每个店铺，每个访客的拜访次数

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

2. 计算每个店铺被用户拜访次数排名

select shop,user_id,ct,rank() over(partition by shop order by ct) rk
from (select shop, user_id, count(*) as ct from visit group by shop, user_id) as t1;

3. 取每个店铺的前三名

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

The End