练习一：行转列
假设有如下比赛结果
+--------------+-----------+ | 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);
表如图：
（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 操作：
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有可能行数增加的，最多是笛卡尔积，即两表的行数相乘。
六、找出每个部门前k大的员工
