【教奶奶学SQL】(task6)秋招秘籍C
学习总结
(1)还欠练习2和连续登陆的补充。
(2)hive数据倾斜的产生原因:
1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜
练习一:行转列
假设有如下比赛结果
+--------------+-----------+
| cdate | result |
+--------------+-----------+
| 2021-01-01 | 胜 |
| 2021-01-01 | 负 |
| 2021-01-03 | 胜 |
| 2021-01-03 | 负 |
| 2021-01-01 | 胜 |
| 2021-01-03 | 负 |
+------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
方法一:
通过日期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;
- 1
- 2
- 3
- 4
- 5
- 6
方法二:
# 方法二
SELECT cdate,
COUNT(IF(result = '胜', true, NULL)) AS '胜',
COUNT(IF(result = '负', true, NULL)) AS '负'
FROM score3
GROUP BY cdate;
- 1
- 2
- 3
- 4
- 5
- 6
比赛结果转换为如下形式:
+--------------+-----+-----|
| 比赛日期 | 胜 | 负 |
+--------------+-----------+
| 2021-01-01 | 2 | 1 |
| 2021-01-03 | 1 | 2 |
+------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
练习二:列转行
假设有如下比赛结果
+--------------+-----+-----|
| 比赛日期 | 胜 | 负 |
+--------------+-----------+
| 2021-01-01 | 2 | 1 |
| 2021-01-03 | 1 | 2 |
+------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
将比赛结果转换为如下形式:
+--------------+-----------+
| cdate | result |
+--------------+-----------+
| 2021-01-01 | 胜 |
| 2021-01-01 | 负 |
| 2021-01-03 | 胜 |
| 2021-01-03 | 负 |
| 2021-01-01 | 胜 |
| 2021-01-03 | 负 |
+------------+-----------+
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
练习三:连续登录
有用户表行为记录表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);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
表如图:
(1)计算2021年每个月,每个用户连续登录的最多天数
方法一:
datediff
函数返回前一个日期减去后一个日期的差值(可正可负),这里的日期格式是2021-04-14这种的。
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;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
(2)计算2021年每个月,连续2天都有登录的用户名单
(3)计算2021年每个月,连续5天都有登录的用户数
练习四: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表的创表语句:
USE autumn;
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);
SELECT * FROM A;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
A表:
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');
SELECT * FROM B;
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
B表:
而LEFT JOIN
的结果会发现,行数增加:
SELECT *
FROM A
LEFT JOIN B
ON A.name = B.name;
- 1
- 2
- 3
- 4
左表关联列为NULL的行会与右表关联列为NULL的行去关联,条件就是 NULL= NULL,所以由 NULL产生的行数是左表 NULL的行数 m 乘以 右表 NULL的行数 n
总行数 = 左表的非空行数 + m * n
所以通过LEFT JOIN
有可能行数增加的,最多是笛卡尔积,即两表的行数相乘。
Reference
(1)datawhale notebook
(2)Hive数据倾斜产生原因及解决办法
(3)在SQL中,两个表格left join之后,最多能产生多少行数据?
文章来源: andyguo.blog.csdn.net,作者:山顶夕景,版权归原作者所有,如需转载,请联系作者。
原文链接:andyguo.blog.csdn.net/article/details/121489107
- 点赞
- 收藏
- 关注作者
评论(0)