【教奶奶学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(日期)

  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);

表如图:
在这里插入图片描述

练习四: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
分享
二维码
< <上一篇
下一篇>>