【数据分析】大厂SQL考点拆解(精华帖)

关于 【SQL】，是数据分析师岗必考技能之一。

其中，包含DROP、INSERT、WHERE、CASE WHEN、窗口函数等众多的知识点，

其实，对于应试没必要样样精通。

大部分考点主要是围绕JOIN链接、聚合函数、窗口函数以及列转换进行命题，

一、JOIN连接

目前Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接。

• INNER JOIN： 内连接（刑NERJOIN ）中，只有进行连接的两个表中都存在与连接标准相匹配的数据才会被保留下来。
• LEFT JOIN: 左连接操作符左边表中符合 WHERE 子句的所有记录将会被 返回，操作符右边表中如果没有符合 ON 后面连接条件的记录时，那么从右边表 指定选择的列的值将会是 NULL。
• RIGHT JOIN: 右连接会返回右边表所有符合 WHERE 语句的记录。左表 中匹配不上的宇段值用 NULL 代替。
• FULL JOIN：最后介绍的完全外连接将会返回所有表中符合 WHERE 语句条 件的所有记录。如果任一表的指定宇段没有符合条件的值的话，那么就使用 NULL 替代。

考点：重点掌握LEFT JOIN 和 INNER JOIN，这也是众多资深数据分析师使用率最高的两个语法。一般的大厂笔试题，掌握这两个语法即可轻松应对所有JOIN连接考点，故需熟练掌握该两个语法。

经典问题解析：

编写一个 SQL 查询，查找所有至少连续出现三次的数字，表名为：log_table，表结构如下：

例如，给定上面的log_table 表， 1 是唯一连续出现至少三次的数字。

解题思路：通过ID+1的方式查找ID下一次出现的数字，通过ID+2的方式查找下下次出现的数字，只要下一次及下下次出现的数字和当前出现的数字相同，即为满足连续出现至少三次的数字。

注意：以上代码结果没有进行去重处理，可能会出现重复，为了避免重复，我们可以通过GROUP BY 或者DISTINCT进行去重。

二、聚合函数

• GROUP BY 语句通常会和聚合函数一起使用，按照一个或者多个列对结果进行分组， 然后对每个组执行聚合操作。
• HAVING子句允许用户通过一个简单的语法完成原本需要通过子查询才能对GROUP BY语句产 生的分组进行条件过滤的任务。
• 常用聚合函数：见：【数据分析岗】SQL类高频考点归纳

经典问题解析：

编写一个SQL查询，查找 Person 表中所有重复的电子邮箱。

根据以上输入，你的查询应返回以下结果：

解题思路：通过GROUP BY 对Email进行分组，使用COUNT聚合函数进行计数，并通过having进行过滤，将有重复（计数大于2）的邮箱汇总出来。

三、窗口函数

窗口函数，也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据库数据进行实时分析处理。窗口函数的基本语法如下：

						
						

							<窗口函数> over (partition by <用于分组的列名>
						

						

							order by <用于排序的列名>)
						

<窗口函数>的位置，可以放以下两种函数：

• 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数。
• 聚合函数，如sum. avg, count, max, min等

考点：窗口函数是必考之一，各大互联网都会对窗口函数进行考点测试，这也是作为数据分析师超级常用的语法函数，掌握该函数至关重要。

经典问题解析：

Employee 表包含所有员工信息，每个员工有其对应的工号 Id，姓名 Name , 工资 Salary 和部门编号 DepartmentId 。

Department 表包含公司所有部门的信息。

编写一个SQL查询，找出每个部门获得前三高工资的所有员工。例如，根据上述给定的表，查询结果应返回：

解题思路：我们借助窗口函数中的partition by置顶窗口的分类方式，然后通过RANK()函数进行排序，最后输出排名前三的员工信息：

四、列转行

• Lateral View 用于和UDTF函数【explode,split】结合来使用。
• 首先通过UDTF函数将数据拆分成多行，再将多行结果组合成一个支持别名的虚拟表。
• 主要解决在select使用UDTF做查询的过程中查询只能包含单个UDTF，不能包含其它字段以及多个UDTF的情况。
• 语法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)

经典问题解析：

Movie表记录了各大电影的类型情况，数据如下：

需要转换成以下格式：

解题思路：本题只需要借助Lateral View进行拆解重组即可：