1.9 好友关系表
1）表结构
user1_id（用户1 id） |
user2_id（用户2 id） |
101 |
1010 |
101 |
108 |
101 |
106 |
注：表中一行数据中的两个user_id，表示两个用户互为好友。
2）建表语句
hive>
DROP TABLE IF EXISTS friendship_info; CREATE TABLE friendship_info( `user1_id` string comment '用户1id', `user2_id` string comment '用户2id' ) COMMENT '用户关系表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
3）数据装载
hive>
insert overwrite table friendship_info values ('101', '1010'), ('101', '108'), ('101', '106'), ('101', '104'), ('101', '102'), ('102', '1010'), ('102', '108'), ('102', '106'), ('102', '104'), ('102', '102'), ('103', '1010'), ('103', '108'), ('103', '106'), ('103', '104'), ('103', '102'), ('104', '1010'), ('104', '108'), ('104', '106'), ('104', '104'), ('104', '102'), ('105', '1010'), ('105', '108'), ('105', '106'), ('105', '104'), ('105', '102'), ('106', '1010'), ('106', '108'), ('106', '106'), ('106', '104'), ('106', '102'), ('107', '1010'), ('107', '108'), ('107', '106'), ('107', '104'), ('107', '102'), ('108', '1010'), ('108', '108'), ('108', '106'), ('108', '104'), ('108', '102'), ('109', '1010'), ('109', '108'), ('109', '106'), ('109', '104'), ('109', '102'), ('1010', '1010'), ('1010', '108'), ('1010', '106'), ('1010', '104'), ('1010', '102');
1.10 收藏信息表
1）表结构
user_id(用户id) |
sku_id(商品id) |
create_date(收藏日期) |
101 |
3 |
2021-09-23 |
101 |
12 |
2021-09-23 |
101 |
6 |
2021-09-25 |
2）建表语句
hive>
DROP TABLE IF EXISTS favor_info; CREATE TABLE favor_info ( `user_id` string comment '用户id', `sku_id` string comment '商品id', `create_date` string comment '收藏日期' ) COMMENT '用户收藏表' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
3）数据装载
hive>
insert overwrite table favor_info values ('101', '3', '2021-09-23'), ('101', '12', '2021-09-23'), ('101', '6', '2021-09-25'), ('101', '10', '2021-09-21'), ('101', '5', '2021-09-25'), ('102', '1', '2021-09-24'), ('102', '2', '2021-09-24'), ('102', '8', '2021-09-23'), ('102', '12', '2021-09-22'), ('102', '11', '2021-09-23'), ('102', '9', '2021-09-25'), ('102', '4', '2021-09-25'), ('102', '6', '2021-09-23'), ('102', '7', '2021-09-26'), ('103', '8', '2021-09-24'), ('103', '5', '2021-09-25'), ('103', '6', '2021-09-26'), ('103', '12', '2021-09-27'), ('103', '7', '2021-09-25'), ('103', '10', '2021-09-25'), ('103', '4', '2021-09-24'), ('103', '11', '2021-09-25'), ('103', '3', '2021-09-27'), ('104', '9', '2021-09-28'), ('104', '7', '2021-09-28'), ('104', '8', '2021-09-25'), ('104', '3', '2021-09-28'), ('104', '11', '2021-09-25'), ('104', '6', '2021-09-25'), ('104', '12', '2021-09-28'), ('105', '8', '2021-10-08'), ('105', '9', '2021-10-07'), ('105', '7', '2021-10-07'), ('105', '11', '2021-10-06'), ('105', '5', '2021-10-07'), ('105', '4', '2021-10-05'), ('105', '10', '2021-10-07'), ('106', '12', '2021-10-08'), ('106', '1', '2021-10-08'), ('106', '4', '2021-10-04'), ('106', '5', '2021-10-08'), ('106', '2', '2021-10-04'), ('106', '6', '2021-10-04'), ('106', '7', '2021-10-08'), ('107', '5', '2021-09-29'), ('107', '3', '2021-09-28'), ('107', '10', '2021-09-27'), ('108', '9', '2021-10-08'), ('108', '3', '2021-10-10'), ('108', '8', '2021-10-10'), ('108', '10', '2021-10-07'), ('108', '11', '2021-10-07'), ('109', '2', '2021-09-27'), ('109', '4', '2021-09-29'), ('109', '5', '2021-09-29'), ('109', '9', '2021-09-30'), ('109', '8', '2021-09-26'), ('1010', '2', '2021-09-29'), ('1010', '9', '2021-09-29'), ('1010', '1', '2021-10-01');
第2章 练习题
2.1 查询累积销量排名第二的商品
2.1.1 题目需求
查询订单明细表（order_detail）中销量（下单件数）排名第二的商品id，如果不存在返回null，如果存在多个排名第二的商品则需要全部返回。期望结果如下：
sku_id |
2 |
2.1.2 代码实现
hive>
select sku_id from ( select sku_id from ( select sku_id, order_num, dense_rank() over (order by order_num desc) rk from ( select sku_id, sum(sku_num) order_num from order_detail group by sku_id ) t1 ) t2 where rk = 2 ) t3 right join --为保证，没有第二名的情况下，返回null ( select 1 ) t4;
需要注意的几个点：
1.group by sku_id这句我一开始都没想到，因为同一个商品在同一天可能会被下单很多次，所以这里需要分组
2.dense_rank() over (order by order_num desc) rk这里我尝试写成下面这样，但是报错！说明这样写不行，我还想着把这两个select合成一个，结果也不行！所以需要在写一个select去用where筛选
3.select 1的返回结果就是1，就一条记录，这里通过和select 1进行right join来实现“如果不存在返回null，如果存在多个排名第二的商品则需要全部返回”这个效果，假如左边查询的sku_id是null，那么null和select 1去join的结果就是null，因为最终查询的是sku_id
4.下面的t1-t4我一开始是没有加的，因为我觉得都没有用到，不需要加也可以，结果报错！
查漏补缺：dense_rank() over是用来排名的，有三种排名的方式，分别是rank() over()、row_number() over()、dense_rank() over() 。
例如下面用的rank() over()函数，在列值重复的情况下，也就是score有两个65，这里两个65并列排在第3名，然后57排在第5名了，没有第4名
例如下面使用dense_rank() over函数，score有两个65，这里两个65并列排在第3名，然后57排在第4名了，跟rank() over()对比发现rank() over()只要重复了排名就会在下一个排名那里跳过去，而rank() over()不会跳，不管重复多少都会按顺序来排名，也就是不会有缺少某个排名的情况
例如下面使用row_number() over()函数，score有两个80，这里两个80分别排名为2和3名，跟上面两个对比发现，上面两个函数是有重复就并列多少名，这个函数就重复也会继续排序，很明显，这不公平哈哈
2.2 查询至少连续三天下单的用户
2.2.1 题目需求
查询订单信息表(order_info)中最少连续3天下单的用户id，期望结果如下：
user_id |
10158分45秒 |
2.2.2 代码实现
hive> select distinct user_id from ( select user_id from ( select user_id, create_date, date_sub(create_date, row_number() over (partition by user_id order by create_date)) flag from ( select user_id, create_date from order_info group by user_id, create_date ) t1 --同一天同一个用户可以下单多次，所以要分组 同一天可能多个用户下单，进行去重 ) t2 -- 判断一串日期是否连续：若连续，用这个日期减去它的排名，会得到一个相同的结果 group by user_id, flag having count(flag) >= 3 -- 连续下单大于等于三天 ) t3; 骚戴解法 SELECT user_id FROM ( SELECT user_id , create_date, date_sub(create_date,row_number() over (partition by user_id order by create_date)) flag FROM ( SELECT user_id , create_date FROM order_info GROUP by user_id,create_date )t1 )t2 GROUP by user_id,flag HAVING COUNT(flag) >=3;1.
骚戴理解：最里面的select的group by是为了去重，因为同一个用户同一天可能下单多次，date_sub(create_date,row_number() over (partition by user_id order by create_date)) flag这个语句最难理解，这里的date_sub函数是日期相减的意思，row_number() over (partition by user_id order by create_date)是一个开窗函数，作用就是按照user_id分组，按照create_date进行排序并且排名，拆开理解是很容易理解的，但是它是怎么实现“最少连续3天”这个效果的呢？
select user_id, create_date, row_number() over (partition by user_id order by create_date) rn, date_sub(create_date, row_number() over (partition by user_id order by create_date)) flag from ( select user_id, create_date from order_info group by user_id, create_date )t1;
这里为了更好的理解，我把它拆开并且查询出来，看看到底是什么东西？下面就是上面的hql运行的结果！下面的rn列就是一个排名
这里理解date_sub(create_date,row_number() over (partition by user_id order by create_date)) flag的核心在于要明白如果create_date是连续的，那么create_date减掉rn的值就是一样的！例如上面的101可以看出flag都是一样的。所以只要flag是相同的那么就说明create_date是连续的日期，然后再写一个select去子查询，最后通过下面两句来筛选出最少连续3天下单的用户。注意这里的分组是有flag的！
GROUP by user_id,flag
HAVING COUNT(flag) >=3
2.3 查询各品类销售商品的种类数及销量最高的商品
2.3.1 题目需求
从订单明细表(order_detail)统计各品类销售出的商品种类数及累积销量最好的商品，期望结果如下：
2.3.2 代码实现
hive>
select category_id, category_name, sku_id, name, order_num, sku_cnt from ( select od.sku_id, sku.name, sku.category_id, cate.category_name, order_num, rank() over (partition by sku.category_id order by order_num desc) rk, count(od.sku_id) over (partition by sku.category_id) sku_cnt from ( select sku_id, sum(sku_num) order_num from order_detail group by sku_id ) od left join sku_info sku on od.sku_id = sku.sku_id left join category_info cate on sku.category_id = cate.category_id ) t1 where rk = 1;
骚戴理解：
1.这个题目用到了sku_info表、category_info表、order_detail表
2.特别注意在多表联查的时候select后面的字段如果是在多个表里出现过的那就必须要通过表名.字段名的方式，例如od.sku_id，不然它不知道你要用哪个表的sku_id
3.这里sum(sku_num) order_num我一开始写错了，用的count函数而不是sum函数
4.rank() over (partition by sku.category_id order by order_num desc) rk这里用的rank()over()，所以会跳，具体的看上面的查漏补缺介绍
5.count( od.sku_id) over (partition by sku.category_id) sku_cnt这语句相当于下面的
select count(sku_id) from sku_info group by category_id;
其实就是实现“统计各品类销售出的商品种类数”这个效果，这种写法很妙，特别是在group by的限制情况下使用，非常nice！
2.4 查询用户的累计消费金额及VIP等级
2.4.1 题目需求
从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额，以及每个用户在其每个下单日期的VIP等级。
用户vip等级根据累积消费金额计算，计算规则如下：
设累积消费总额为X，
若0=<X<10000,则vip等级为普通会员
若10000<=X<30000,则vip等级为青铜会员
若30000<=X<50000,则vip等级为白银会员
若50000<=X<80000,则vip为黄金会员
若80000<=X<100000,则vip等级为白金会员
若X>=100000,则vip等级为钻石会员
期望结果如下：
