sql面试题03

有 50W 个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，

访问日志存储的表名为 Visit ，访客的用户 id 为 user_id ，被访问的店铺名称为 shop ，数据如下：

(1) 每个店铺的 UV （访客数）

(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数

假如后台数据客户访问网店日志如下：

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

创建表：

create table shopping_log(
    user_id   varchar(20)
   ,shop      varchar(20)
);

insert into shopping_log values
 ('u1','a')
,('u1','b')
,('u1','a')
,('u1','a')
,('u1','b')
,('u2','b')
,('u2','c')
,('u2','c')
,('u2','a')
,('u2','a')
,('u3','c')
,('u3','a')
,('u4','b')
,('u4','b')
,('u5','b')
,('u5','a')
,('u5','a')
,('u5','a')
,('u6','c');

sql实现：

select t2.shop,t2.user_id,t2.c from(
select shop,user_id,t1.c as c,dense_rank() over(partition by shop order by c desc) rn from(
select shop,user_id,count(1) c from shopping_log group by 1,2 )t1
)t2
where t2.rn<=3

实现效果如下：