思维导图
Q1：SQL中有哪些常用的聚合函数？
-
常用的聚合函数
-
注：对表中所有记录进行聚合计算时，无需使用group by语句，可以在select后直接写聚合函数，但是不能出现非聚合字段。
-
代码示例：统计表中2019-08-21的所有记录的数量
Q2：SQL中distinct有什么作用？如何使用distinct？
-
distinct用于去重，它有两种使用场景
1.在select后直接使用
-
对select后的所有字段进行去重
-
可以理解为在所有的语句执行结束之后，对所有的记录整体去重
-
只有在所有字段值都相同的情况下，才会进行去重，不能做到对部分字段进行去重
-
虽然在Hive中只能使用union all，但是如果想达到union的效果，则可以使用distinct
-
注：distinct和group by语句不能在同一个SQL查询中出现（不包含子查询的情况）
-
代码示例：
2.distinct在聚合函数中使用
-
实现分组后去重，然后再进行聚合计算
-
代码示例：（统计每个学生参加考试的次数，以及参加过考试的学科数）
-
count(1)统计的是2019年该学生的考试记录数
-
count(distinct subject)则会基于学号和姓名分组后，对同一个学生所有的学科记录去重后统计记录数，从而计算出参加过考试的学科数
Q3：SQL中case when有什么作用？如何使用？
1.利用现有的字段，结合条件语句，生成新的字段
-
代码示例1：（根据城市名city，生成一个新的字段province，并将除"青岛""济南""南京"之外的值统一命名为“其他”）
-
代码示例2：（另一种实现方式）
-
in表示如果是两者其中之一，则赋值“山东”
-
注：不要漏掉end关键字，实际应用中会直接报错，面试中会大大减分
2.case when可以被使用在分组语句和选择语句中，写在group by之后，提供新的分组字段
-
case when也可以被写在select后，基于现有的字段生成新的字段
-
注：如果将case when写在group by之后，则不可以使用字段别名
-
代码示例：（统计各个省的数据量）
3.case when还被被使用在聚合函数中
-
代码示例：（统计学生参加考试的次数，学生考试通过[>60]的次数以及考试通过的学科数）
-
count(1)：统计学生考试总数
-
count(case when score >= 60 then 1 end)：筛选出成绩大于60分的考试记录
-
count(distinct subject)：统计参加过考试的学科数
-
count(distinct case when score >= 60 then subject end)：统计考试通过的学科数
聚合函数+distinct+case when，基本可以完成SQL分组计算
Q4：什么是窗口函数，如何在SQL中使用窗口函数？
1. 窗口函数
-
窗口函数与聚合函数类似，它也会在记录分组之后进行聚合计算
-
它不会为每组只返回一个值，而是可以为每组返回多个值
-
准确地说，它为分组中的每条记录都会返回特定值
-
窗口函数既可以计算出整体的统计值（平均分、总次数等），也可以计算出每条记录在分组中基于时间或者其他维度的排名或者分位数
-
窗口函数不会出现在group by语句中，也不会出现在聚合函数中，它只能出现在select语句后。并且使用窗口函数后，不会再使用group by语句
2.窗口函数的使用
-
窗口函数的基本结构：函数名() over (partition by c1,c2 order by c3 asc,c4desc)
-
代码示例：（统计每个学生各个学科2019年最新的一次考试记录）
-
子查询中，使用了row_number()窗口函数，其中"partition by"表示对所有的记录按照id、name和subject进行分组
-
具有相同id、name和subject的记录按照pt降序排列，最新的记录回排在最前面。同一分组的所有记录返回row_number()对应的值，最新的记录返回1，次新的记录返回2。最后筛选出rank=1的列，即为最新一次的考试记录。
3.常用的窗口函数
窗口函数一定要掌握，通过窗口函数可以减少表与表之间的连接，同时也可以实现很多功能
Q5：Hive中如何实现动态更新？
-
Hive本身不支持对记录进行insert、update、delete等操作，因此无法直接修改记录
即使在Hive的最新版本中可以通过一些方法来实现，但是在实际工作中也很少这样做
-
动态分区可以解决这个问题，通过对分区的全量更新，实现对数据的修改
1.建立分区表
-
建表语句：
-
分区表按照pt进行分区，col1、col2、col3是非分区字段
2.设置参数
-
如果要对其中的一些分区进行更新，需要设置一些参数
-
代码示例：
-
第一条语句表示开启动态分区；第二条语句表示在动态分区的过程中，可以不用指定任何分区
3.动态更新
-
更新语句：
-
insert overwrite意思是覆盖，在接下来的查询语句中，所有出现的pt对应的分区都会进行全量更新，更新后的数据就是SQL语句查询的结果
-
注：select后字段的顺序需要与建表时字段的顺序一致，并且将分区字段放到最后。如果有多个分区字段，那么也需要按照建表时字段的顺序进行排序
-
常出现的问题：查询语句中字段的顺序和建表时字段的顺序不一致导致数据错位
Q6：SQL中如何实现一行变多行？
案例：比如在数据库中以列表的形式存储了某个学生一段时间的成绩
现在想计算出成绩的平均值
1.字段拆分
-
Hive中并没有处理这种列表数据的函数，因此需要先将grades字段拆分成多行，每行代表一个分数
-
代码示例：
-
regexp_replace()函数：将grades字段中所有的“{”和“}”替换为' '，也就是删除
-
split()函数：将grades字段按照逗号进行分割
-
explode()函数：将grades字段变成多行，再使用“lateral view”将变成多行后的grades字段与原表进行笛卡尔积计算
-
t可以理解为只有一个字段的虚拟表，该字段为grades字段分成多行后的结果，并且将该字段重命名为"grade"，最后将虚拟表t与原表进行笛卡尔积运算
-
最终输出结果：
Q7：SQL中常用的调优方法有哪些？
1.当需要对大表和小表进行join操作时，可以使用MAPJOIN将小表加载到内存中
-
此时将a放到内存中，由b到内存中循环读取a，由于读取内存中数据的速度要远快于读取磁盘中数据的速度，因此效率得到大大提高。
-
通常小表的大小应小于25MB，否则达不到应有的效果
2.当需要对大表和大表进行join操作时
-
可以考虑是否能够将其中一个大表转换成小表，例如只需要一个表中一段时间的数据时，就可以将这段时间的数据取岀，建立一个小的临时表，然后将其与另一个表进行连接
-
即使按照上述操作进行计算，计算速度仍然不快，这时需要考虑数据倾斜的问题
3.数据倾斜
-
理论上，正常的数据分布都有可能是不平衡的。正是由于数据分布的不平衡，导致Hive在计算过程中出现数据倾斜的问题
-
数据倾斜问题涉及到Map&Reduce过程
-
Map过程会将原始数据转换成<key,value>键值对，然后Reduce过程会对相同key的数据进行合并计算
-
在默认情况下，具有相同key的数据会被放在同一个Reduce任务中，因此会出现“一个人累死，其他人闲死”的情况，即数据倾斜问题（执行Hive SQL语句或MapReduce作业时，一直卡在Map100%、Reduce99%）
4.数据倾斜常见问题的解决方法
（1）当使用group by分组时，如果某些key占比非常大，由于相同key的数据会被拉取到相同节点中执行Reduce操作，因此会出现某些节点需要计算的数据量远大于其他节点的情况，造成数据倾斜。
-
明显特征：在Reduce任务执行时，进度停留在99%的时间非常长，此时1%的节点计算量可能超过其余99%节点计算量的总和
-
解决方法：设置“set hive.map.aggr=true”和“set hive.groupby.skewindata=true”参数
-
设置参数后，生成的查询会将此前的一个MapReduce作业拆分成两个任务：
-
第一个任务：Map任务的输出结果集合会随机分布到Reduce任务中，每个Reduce任务进行部分聚合操作，并输出结果，这样相同key的数据会被拉取到不同的节点中，从而达到负载均衡的目的
-
第二个任务：根据第一个任务预处理的数据结果将相同key的数据分发到同一个Reduce任务中，完成最终的聚合操作
-
（2）当Map任务的计算量非常大时（执行count(*),sum(case when)这些语句)
-
解决方法：设置Map任务数量的上限
-
“set mapred.map.tasks”可以设置合理的Map任务数量
（3）如果Hive SQL语句中计算的数据量非常大
-
例如:
-
此时就会因为count(distinct b)函数而出现数据倾斜的问题，可以使用“sum...group by”代替该函数：
（4）当需要执行join操作但是关联字段存在大量空值时
-
解决方法：可以在join操作过程中忽略空值，然后再通过union操作加上空值
参考文献
1.《拿下Offer 数据分析师求职面试指南》徐麟 著
