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

举报
野猪佩奇996 发表于 2022/01/22 23:31:04 2022/01/22
【摘要】 学习总结 (1)还欠练习2和连续登陆的补充。 (2)hive数据倾斜的产生原因: 1)、key分布不均匀 2)、业务数据本身的特性 3)、建表时考虑不周 4)、某些SQL语句本身就有数据倾斜 文...

学习总结

(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(日期)

  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
  • 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

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。