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

傷城~ 2024-04-06 09:25 38阅读 0赞

**题目**：  
有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;

参考：  
https://blog.csdn.net/Poolweet\_/article/details/109614982  
https://www.modb.pro/db/79137