【教奶奶学SQL】(task6)秋招秘籍C(更新ing)
学习总结
文章目录
练习一:行转列
假设有如下比赛结果
+--------------+-----------+
| cdate | result |
+--------------+-----------+
| 2021-01-01 | 胜 |
| 2021-01-01 | 负 |
| 2021-01-03 | 胜 |
| 2021-01-03 | 负 |
| 2021-01-01 | 胜 |
| 2021-01-03 | 负 |
+------------+-----------+
方法一:
通过日期cdate
进行分组,可以通过SUM(CASE WHEN)
或者COUNT(IF)
统计各天的胜负次数。
SELECT
cdate,
SUM(CASE WHEN result = '胜' then 1 else 0 end) AS '胜',
SUM(CASE WHEN result = '负' then 1 else 0 end) AS '负'
FROM score3
GROUP BY cdate;
方法二:
# 方法二
SELECT cdate,
COUNT(IF(result = '胜', true, NULL)) AS '胜',
COUNT(IF(result = '负', true, NULL)) AS '负'
FROM score3
GROUP BY cdate;
比赛结果转换为如下形式:
+--------------+-----+-----|
| 比赛日期 | 胜 | 负 |
+--------------+-----------+
| 2021-01-01 | 2 | 1 |
| 2021-01-03 | 1 | 2 |
+------------+-----------+
练习二:列转行
假设有如下比赛结果
+--------------+-----+-----|
| 比赛日期 | 胜 | 负 |
+--------------+-----------+
| 2021-01-01 | 2 | 1 |
| 2021-01-03 | 1 | 2 |
+------------+-----------+
将比赛结果转换为如下形式:
+--------------+-----------+
| cdate | result |
+--------------+-----------+
| 2021-01-01 | 胜 |
| 2021-01-01 | 负 |
| 2021-01-03 | 胜 |
| 2021-01-03 | 负 |
| 2021-01-01 | 胜 |
| 2021-01-03 | 负 |
+------------+-----------+
练习三:连续登录
有用户表行为记录表t_act_records
表,包含两个字段:uid
(用户ID),imp_date
(日期)
- 计算2021年每个月,每个用户连续登录的最多天数
- 计算2021年每个月,连续2天都有登录的用户名单
- 计算2021年每个月,连续5天都有登录的用户数
构造表mysql如下:
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);
表如图:
练习四:hive 数据倾斜的产生原因及优化策略?
1.1操作:
关键词 | 情形 | 后果 |
---|---|---|
Join | 其中一个表较小,但是key集中 | 分发到某一个或几个Reduce上的数据远高于平均值 |
大表与大表,但是分桶的判断字段0值或空值过多 | 这些空值都由一个reduce处理,灰常慢 | - |
group by | group by 维度过小,某值的数量过多 | 处理某值的reduce灰常耗时 |
Count Distinct | 某特殊值过多 | 处理此特殊值的reduce耗时 |
1.2原因:
1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜
练习五:LEFT JOIN 是否可能会出现多出的行?为什么?
假设 A表有6行(关联列 name 有2行为空),B表有6行(关联列 name 有3行为空),
那么 SELECT * FROM A LEFT JOIN B on A.name = B.name
会返回多少行结果?
可以参考下图
A表:
B表:
Reference
(1)datawhale notebook
(2)Hive数据倾斜产生原因及解决办法
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
THE END
二维码