【DatawhaleSQL学习打卡】Task06:决胜秋招Section C

Section C

练习一：行转列

按要求对比赛结果进行格式转换。

1、创建比赛结果表row_to_col

drop table if exists row_to_col;
create table row_to_col
(cdate DATE,
 result varchar(32) not null);

insert into row_to_col values(20210101,'胜');
insert into row_to_col values(20210101,'负');
insert into row_to_col values(20210103,'胜');
insert into row_to_col values(20210103,'负');
insert into row_to_col values(20210101,'胜');
insert into row_to_col values(20210103,'负');

输出结果：

 

2、解题思路：根据cdate对查询结果进行分组，通过coount、if语句统计各天’胜’、'负’场次

SELECT * FROM exercises.row_to_col;
select cdate as '比赛日期',
	   count(if(result='胜',true,null)) as '胜',
	   count(if(result='负',true,null)) as '负'
from row_to_col
group by cdate;

输出结果：

 

练习二：列转行

1、创建比赛结果表col_to_row

drop table if exists col_to_row;
create table col_to_row
(比赛日期 date,
 胜 integer(4) not null,
 负 integer(4) not null,
 primary key(比赛日期));

insert into col_to_row values(20210101,2,1);
insert into col_to_row values(20210103,1,2);

输出结果：

 

2、列转行

SELECT * FROM exercises.col_to_row;
SELECT cdate,result FROM(

SELECT * FROM
(SELECT 比赛日期 AS cdate, '胜' AS result, 胜 AS times
FROM col_to_row
UNION
SELECT 比赛日期 AS cdate, '负' AS result, 负 AS times
FROM col_to_row
)a

UNION

SELECT * FROM
(SELECT 比赛日期 AS cdate, '胜' AS result, 胜-1 AS times
FROM col_to_row
UNION
SELECT 比赛日期 AS cdate, '负' AS result, 负-1 AS times
FROM col_to_row
)b)c
WHERE times>0
ORDER BY cdate;

输出结果：

 

练习三：连续登录

有用户表行为记录表t_act_records表，包含两个字段：uid（用户ID），imp_date（日期）。要求（1）计算2021年每个月，每个用户连续登录的最多天数；（2）计算2021年每个月，连续2天都有登录的用户名单；（3）计算2021年每个月，连续5天都有登录的用户数

解题思路：注意这个连续登录！！！构造表t_act_records时加入非连续因素（u1004中间断开两天未登录

1、创建t_act_records表

DROP TABLE if EXISTS t_act_records;
CREATE TABLE t_act_records
(uid  VARCHAR(20),
imp_date DATE);

INSERT INTO t_act_records VALUES('u1001', 20210101);
INSERT INTO t_act_records VALUES('u1002', 20210101);
INSERT INTO t_act_records VALUES('u1003', 20210101);
INSERT INTO t_act_records VALUES('u1003', 20210102);
INSERT INTO t_act_records VALUES('u1004', 20210101);
INSERT INTO t_act_records VALUES('u1004', 20210102);
INSERT INTO t_act_records VALUES('u1004', 20210103);
INSERT INTO t_act_records VALUES('u1004', 20210106);
INSERT INTO t_act_records VALUES('u1004', 20210107);

输出结果：

 

 2、计算2021年每个月，每个用户连续登录的最多天数

 

SELECT * FROM exercises.t_act_records;
select distinct uid,max(maxday) over(partition by uid) as consecutive_maxday
from(select uid,count(*) as maxday
    from (select *,date-cum as result from (select *,row_number() over(PARTITION by uid order by date) as cum 
                 from (select DISTINCT imp_date as date,uid 
                              from t_act_records)a)b)c 
GROUP BY uid,result)d;

输出结果： 

 

3、计算2021年每个月，连续2天都有登录的用户名单

SELECT * FROM exercises.t_act_records;
select * from
(select distinct uid,max(maxday) over(partition by uid) as consecutive_maxday
from(select uid,count(*) as maxday
    from (select *,date-cum as result from (select *,row_number() over(PARTITION by uid order by date) as cum 
                 from (select DISTINCT imp_date as date,uid 
                              from t_act_records)a)b)c 
GROUP BY uid,result)d)e
where consecutive_maxday>=2;

输出结果： 

4、计算2021年每个月，连续5天都有登录的用户数

SELECT * FROM exercises.t_act_records;
select count(*) as user_num from
(select distinct uid,max(maxday) over(partition by uid) as consecutive_maxday
from(select uid,count(*) as maxday
    from (select *,date-cum as result from (select *,row_number() over(PARTITION by uid order by date) as cum 
                 from (select DISTINCT imp_date as date,uid 
                              from t_act_records)a)b)c 
GROUP BY uid,result)d)e
where consecutive_maxday>=5;

输出结果： 

 

练习四：用户购买商品推荐

假设现在需要根据算法给每个 user_id 推荐购买商品，推荐算法比较简单，推荐和他相似的用户购买过的 product 即可，说明如下：排除用户自己购买过的商品；相似用户定义：曾经购买过2 种或2 种以上的相同的商品。

练习五：hive 数据倾斜的产生原因及优化策略？

1、什么是hive？

hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供完整的sql查询功能，可以将sql语句转换为MapReduce任务进行运行。

2、什么是数据倾斜？

由于数据分布不均匀，造成数据大量的集中到一点，造成数据热点

3、数据倾斜产生原因？

1）key分布不均匀

2）业务数据本身的特性

3）建表考虑不周全

4）某些HQL语句本身就存在数据倾斜

4、常见场景

 

5、优化策略
针对不同业务场景，处理方式也不同。可参考：Hive的数据倾斜 - BBBone - 博客园

练习六：LEFT JOIN 是否可能会出现多出的行？为什么？

假设t1 表有6行（关联列name 有2行为空），t2 表有6行（关联列name 有3行为空），那么 SELECT * FROM t1 LEFT JOIN t2 on t1.name = t2.name 会返回多少行结果？

1、创建A表

drop table if exists A;
CREATE TABLE A
(id VARCHAR(8) NOT NULL,
 name VARCHAR(8) ,
 score INTEGER);
INSERT INTO A VALUES('1', 'aaa', 90);
INSERT INTO A VALUES('2', 'bbb', 80);
INSERT INTO A VALUES('3', 'ccc', 70);
INSERT INTO A VALUES('4', 'ddd', 60);
INSERT INTO A VALUES('5', '', 90);
INSERT INTO A VALUES('6', '', 100);

输出结果：

 

2、创建B表

drop table if exists B;
CREATE TABLE B 
(id VARCHAR(8) NOT NULL,
 name VARCHAR(8) ,
 city VARCHAR(16));
INSERT INTO B VALUES('1', 'aaa', 'beijing');
INSERT INTO B VALUES('2', 'bbb', 'tianjin');
INSERT INTO B VALUES('3', 'ccc', 'chengdu');
INSERT INTO B VALUES('4', '', 'shenzhen');
INSERT INTO B VALUES('5', '', 'qingdao');
INSERT INTO B VALUES('6', '', 'guangzhou');

输出结果：

 

 3、执行LEFT JOIN语句结果

SELECT * 
FROM A 
LEFT JOIN B 
ON A.name = B.name;

输出结果：

 

左表关联列为NULL的行会与右表关联列为NULL的行去关联，条件就是NULL= NULL，所以由NULL产生的行数是左表NULL的行数m 乘以右表NULL的行数n

总行数= 左表的非空行数+ m * n

所以通过LEFT JOIN有可能行数增加的，最多是笛卡尔积，即两表的行数相乘。

---

 为期2周的Datawhale组队学习SQL编程学习打卡完结。很高兴加入Datawhale学习团队，从课程的编排介绍，到报名招募，学习安排，进度管理和反馈评价，整个学习体验过程很流畅充实，期待下一次学习打卡任务！

在抗疫的特殊时期，Push自己坚持学习，每天努力一点点，DDUp！同时也祝愿疫情早日消散，静待花开。