秋招秘籍C

• • 练习一：行转列
  • 练习二：列转行
  • 练习三：连续登录
  • 练习四：hive 数据倾斜的产生原因及优化策略？
  • 练习五：LEFT JOIN 是否可能会出现多出的行？为什么？

假设有如下比赛结果
创建比赛结果表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,'负');

请使用 SQL 将比赛结果转换为如下形式：

解题思路：

1. 根据cdate对查询结果进行分组
2. 通过coount、if语句统计各天’胜’、'负’场次

SQL语句如下：

select cdate as '比赛日期',
	   count(if(result='胜',true,null)) as '胜',
	   count(if(result='负',true,null)) as '负'
from row_to_col
group by cdate

运行结果如下：

假设有如下比赛结果:
创建比赛结果表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);

解题思路：
时间有限，此题想了很久还是没有很好的想法，后续有机会再做整理吧，这里先留一个坑。

问题:
有用户表行为记录表t_act_records表，包含两个字段：uid（用户ID），imp_date（日期）

1. 计算2021年每个月，每个用户连续登录的最多天数
2. 计算2021年每个月，连续2天都有登录的用户名单
3. 计算2021年每个月，连续5天都有登录的用户数
   创建表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', 20210104);
INSERT INTO t_act_records VALUES('u1004', 20210105);

解题思路：

1. 选取任意小于表中日期的初始日期作为参考日期，并利用datediff函数计算用户登录日期与参考日期间的间隔天数
2. 针对不同用户，对其登录日期依次进行编号排序， 并计算步骤1中间隔天数与此排序编号的差值，记作ranking。
3. 不难发现，某一用户的登录日期连续时，差值ranking也会相同。
4. 根据月份、用户名(uid)、与ranking进行分组，找出每月所有连续天数。
5. 根据连续天数使用order by进行降序排序，找出最大连续登录天数。
   此处解题思路借鉴文章：mysql 连续日期统计_MYSQL – 计算连续日期天数

SQL语句：

select month(imp_date) as '月份',
			 uid,
			 min(imp_date)as '起始日期',
			 max(imp_date)as '终止日期',
			 count(*) as '连续天数'
from (select uid,imp_date,
			datediff(imp_date,'2020-01-01')-rank()over(partition by uid order by imp_date) as ranking
			from t_act_records) as r
group by uid,month(imp_date),r.ranking
order by 连续天数 desc

运行结果，得到每月所有用户的连续登录天数：

问题2和3只需要加入如下where条件即可：
需要注意的是，此处需要把上述查询到的结果单独作为一个新表进行查询，不然由于sql语句执行顺序from–where–select的原因，会导致找不到字段‘连续天数’。

where p.连续天数 = 5
--
where p.连续天数 = 2

原因：
1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜
具体细节参考文章：Hive数据倾斜产生原因及解决办法

可能会导致数据量的增加。

运行SQL语句：

SELECT * 
FROM A 
LEFT JOIN B 
on A.name = B.name

结果如下：

本文参考：
mysql 连续日期统计_MYSQL – 计算连续日期天数
Hive数据倾斜产生原因及解决办法
详细题目参考：
DataWhale组队学习