【教奶奶学SQL】(task6)秋招秘籍C

【教奶奶学SQL】(task6)秋招秘籍C 学习总结

（1）还欠练习2和连续登陆的补充。
（2）hive数据倾斜的产生原因：
1)、key分布不均匀
2)、业务数据本身的特性
3)、建表时考虑不周
4)、某些SQL语句本身就有数据倾斜

文章目录

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

练习一：行转列

假设有如下比赛结果

+--------------+-----------+
|    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（日期）

1. 计算2021年每个月，每个用户连续登录的最多天数
2. 计算2021年每个月，连续2天都有登录的用户名单
3. 计算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）计算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;

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

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

练习四：hive 数据倾斜的产生原因及优化策略？ 1.1 *** 作： 关键词情形后果Join其中一个表较小，但是key集中分发到某一个或几个Reduce上的数据远高于平均值大表与大表，但是分桶的判断字段0值或空值过多这些空值都由一个reduce处理，灰常慢-group bygroup 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;

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;

B表：

而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有可能行数增加的，最多是笛卡尔积，即两表的行数相乘。

Reference

（1）datawhale notebook
（2）Hive数据倾斜产生原因及解决办法
（3）在SQL中，两个表格left join之后，最多能产生多少行数据？