练习一:行转列
假设有如下比赛结果
创建比赛结果表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 将比赛结果转换为如下形式:
解题思路:
- 根据cdate对查询结果进行分组
- 通过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(日期)
- 计算2021年每个月,每个用户连续登录的最多天数
- 计算2021年每个月,连续2天都有登录的用户名单
- 计算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);
解题思路:
- 选取任意小于表中日期的初始日期作为参考日期,并利用datediff函数计算用户登录日期与参考日期间的间隔天数
- 针对不同用户,对其登录日期依次进行编号排序, 并计算步骤1中间隔天数与此排序编号的差值,记作ranking。
- 不难发现,某一用户的登录日期连续时,差值ranking也会相同。
- 根据月份、用户名(uid)、与ranking进行分组,找出每月所有连续天数。
- 根据连续天数使用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
练习四:hive 数据倾斜的产生原因及优化策略?
原因:
1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜
具体细节参考文章:Hive数据倾斜产生原因及解决办法
练习五:LEFT JOIN 是否可能会出现多出的行?为什么?
可能会导致数据量的增加。
运行SQL语句:
SELECT *
FROM A
LEFT JOIN B
on A.name = B.name
结果如下:
本文参考:
mysql 连续日期统计_MYSQL – 计算连续日期天数
Hive数据倾斜产生原因及解决办法
详细题目参考:
DataWhale组队学习