Hive-SQL基础练习一

红太狼 2022-11-25 00:55 125阅读 0赞

### 文章目录 ###

*  练习一
 *  练习二

# 练习一 #

有如下的用户访问数据

table_name：action
    
    userId (string), visitDate (string), visitCount (int)
    用户ID		  	  访问时间			  访问次数
    
    userid|visitdate|visitcount|
    ------|---------|----------|
    u01   |2017/1/21|         5|
    u02   |2017/1/23|         6|
    u03   |2017/1/22|         8|
    u04   |2017/1/20|         3|
    u01   |2017/1/23|         6|
    u01   |2017/2/21|         8|
    u02   |2017/1/23|         6|
    u01   |2017/2/22|         4|

要求统计出每个用户的累积访问次数，如下所示：

用户id     月份     小计    累积
    u01      2017-01   	11     11
    u01      2017-02   	12     23
    u02      2017-01   	12     12
    u03      2017-01   	 8      8
    u04      2017-01  	 3      3

解法不唯一：

-- 1.先把visitdate转成符合需求的格式，然后把每个用户相同月份的数据统计出来，可以通过userid和visitdate一起分组，再统计分组后的visitcount总和
    SELECT
    	userid,
    	date_format(regexp_replace (visitdate, '/', '-'),'yyyy-MM') visitdate,
    	SUM(visitcount) AS vc_sum
    FROM action
    GROUP BY userid, date_format(regexp_replace (visitdate, '/', '-'),'yyyy-MM');

![在这里插入图片描述][2020080623171192.png]

-- 2.使用窗口函数把vc_sum，根据userid分区，visitdate升序排序，进行上无边界到当前行累加
    SELECT
    	t1.userid AS `用户id`,
    	t1.visitdate AS `月份`, 
    	t1.vc_sum AS `小计`,
    	SUM(t1.vc_sum) OVER (PARTITION BY t1.userid ORDER BY t1.visitdate) AS `累计`
    FROM
    	(
    		SELECT
                userid,
                date_format(regexp_replace (visitdate, '/', '-'),'yyyy-MM') visitdate,
                SUM(visitcount) vc_sum
            FROM action
            GROUP BY userid, date_format(regexp_replace (visitdate, '/', '-'),'yyyy-MM')
    	) t1;

![在这里插入图片描述][20200806231814972.png]

# 练习二 #

有10W个店铺，每个顾客访问任何一个店铺的任何一个商品时都会产生一条访问日志，访问日志存储的表名为Visit

table_name：Visit
    
    user_id (string), shop (string), visit_time (string)
    用户id		  	 店铺名称		  访问时间
    
    user_id|shop|visit_time|
    -------|----|----------|
    huawei |1005|2017-02-10|
    huawei |1005|2017-02-10|
    huawei |1005|2017-02-10|
    huawei |1005|2017-02-10|
    huawei |1004|2017-02-10|
    huawei |1004|2017-02-10|
    huawei |1003|2017-02-10|
    huawei |1003|2017-02-10|
    huawei |1001|2017-02-10|
    huawei |1002|2017-02-10|
    huawei |1006|2017-02-10|
    apple  |1001|2017-02-10|
    apple  |1001|2017-02-10|
    apple  |1001|2017-02-10|
    apple  |1001|2017-02-10|
    apple  |1002|2017-02-10|
    apple  |1002|2017-02-10|
    apple  |1005|2017-02-10|
    apple  |1005|2017-02-10|
    apple  |1006|2017-02-10|
    apple  |1004|2017-02-10|
    meizu  |1006|2017-02-10|
    meizu  |1006|2017-02-10|
    meizu  |1006|2017-02-10|
    meizu  |1006|2017-02-10|
    meizu  |1003|2017-02-10|
    meizu  |1003|2017-02-10|
    meizu  |1003|2017-02-10|
    meizu  |1002|2017-02-10|
    meizu  |1002|2017-02-10|
    meizu  |1004|2017-02-10|

请统计：

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

-- 一.每个店铺的UV（访客数)
    SELECT
    	shop,
    	count(shop) AS UV
    FROM Visit
    GROUP BY shop;

![在这里插入图片描述][2020080623194739.png]

-- 解法不唯一，不用窗口函数，需要多套个子查询
    -- 二.每个店铺访问次数top3的访客信息。输出店铺名称、访客id、访问次数
    -- 1.先要根据 店铺 和 访客id 分组，统计出每个店铺的，每个访客id的，访问次数，
    -- 2.再用窗口函数rank，根据 店铺 分区，区内根据 访问次数 降序排序，就可以得到 每个店铺访问次数的排行，
    -- 3.最后过滤出 rank<=3（就是top3）的数据
    SELECT
    	t2.shop,
    	t2.user_id,
    	t2.UV
    FROM
    	(
    		SELECT
    			t1.shop,
    			t1.user_id,
    			t1.UV,
    			rank() over(PARTITION BY t1.shop ORDER BY t1.UV DESC) rk
    		FROM
    			(
    				SELECT
    					shop,
    					user_id,
    					count(*) AS UV
    				FROM Visit
    				GROUP BY shop, user_id
    			) t1
    	) t2
    WHERE t2.rk <= 3;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70]

[2020080623171192.png]: /images/20221124/298891c3a1174dd3805d605f6814a49b.png
[20200806231814972.png]: /images/20221124/90fe1d12aecf4872bd772c291970eac3.png
[2020080623194739.png]: /images/20221124/4c7a21d1e1614dcd889919392bdd8183.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzMyNzI3MDk1_size_16_color_FFFFFF_t_70]: /images/20221124/5c6b5a2916154853adc4b3919e73b968.png