求每个店铺访问次数top3的访客信息

小半 • 2023年2月3日上午10:46 • 技术随笔 • 阅读 211

题目：
有50W个京东店铺，每个顾客访客访问任何一个店铺的任何一个商品时都会产生一条访问日志，
访问日志存储的表名为Visit，访客的用户id为user_id，被访问的店铺名称为shop，数据如下：

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

请统计：
(1)每个店铺的UV（访客数）
(2)每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数；

实现：

数据准备：

CREATE TABLE visit( 
                         user_id string, 
                         shop string )
            ROW format delimited FIELDS TERMINATED BY '\t'; 
            INSERT INTO TABLE visit VALUES
            ( 'u1', 'a' ),
            ( 'u2', 'b' ),
            ( 'u1', 'b' ),
            ( 'u1', 'a' ),
            ( 'u3', 'c' ),
            ( 'u4', 'b' ),
            ( 'u1', 'a' ),
            ( 'u2', 'c' ),
            ( 'u5', 'b' ),
            ( 'u4', 'b' ),
            ( 'u6', 'c' ),
            ( 'u2', 'c' ),
            ( 'u1', 'b' ),
            ( 'u2', 'a' ),
            ( 'u2', 'a' ),
            ( 'u3', 'a' ),
            ( 'u5', 'a' ),
            ( 'u5', 'a' ),
            ( 'u5', 'a' );

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

方法一：
每个店，即以shop来group by 分组，count计算访客数量同时去重即可；

        SELECT shop,
               count(DISTINCT user_id)
        FROM visit
        GROUP BY shop;

方法二：
使用shop和user_id 联合去重，即可去重！

        SELECT t.shop,
               count(*)
        FROM
          (SELECT user_id,
                  shop
           FROM visit
           GROUP BY shop,
		            user_id,
                    ) t
        GROUP BY t.shop

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

思路：
① 先统计 每个shop中、每个用户的访问次数，用联合分组，

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

②对shop开窗，用rank() 计算用户的访问次数排名

select
     shop,
     user_id,
     ct, 
     rank() over(partition by shop order by ct DESC) rk
from t1;---t2

③ 用where 限制rk排名，筛选出每个shop访问次数排名前三的用户；

select
    shop,
    user_id,
    ct
from t2
where rk<=3

最终：

	select 
	   shop,
	   user_id,
	   ct
	from
		 (select 
		   shop,
		   user_id,
		   ct,
		   rank() 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;