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

建表、导表

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

Hive基础知识之顾客访问店铺数据分析（UV、Top3）