SQL练习题——店铺UV、访客信息

数据表

问题：

1. 每个店铺的UV（访客数）
2. 每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

问题1解答：

select 
	shop, count(distinct user_id) as count_user
from jd
group by shop

问题2解答：
根据题意，我们可以先将问题拆分：
1）获取每个店铺的访客次数信息

select 
	shop, user_id, count(user_id) as count_user
from jd
group by shop, user_id

2）分别对每个店铺中访客的次数进行排序

set @i :=0, @type :='';
select
	@i := case when @type = shop then @i+1 else 1 end as num,
	@type := shop as type,
	a.*
from(
	select 
		shop, user_id, count(user_id) as count_user
	from jd
	group by shop, user_id
	order by shop, count_user desc
) a

3）获取访问次数前三的访客信息

set @i :=0, @type :='';
select
	b.num, b.shop, b.user_id, b.count_user
from(
	select
		@i := case when @type = shop then @i+1 else 1 end as num,
		@type := shop as type,
		a.*
	from(
		select 
			shop, user_id, count(user_id) as count_user
		from jd
		group by shop, user_id
		order by shop, count_user desc
	) a
) b
where b.num<=3

如果使用MySQL8.0版本，可以通过row_number（） over（）方法进行排序

代码如下：

select 
	b.num, b.shop, b.user_id, b.count_user
from(
	select
		shop, user_id, count_user,
		row_number() over(partition by shop order by count_user desc) as num
	from(
		select shop, user_id, count(user_id) as count_user
		from jd
		group by shop, user_id
	) a
) b
where b.num <=3