数据分析面试手册《SQL篇》
数据分析面试手册《SQL篇》
前言：在当前的数据分析岗位中，多数人在做着SQL-Boy\SQL-Girl的工作，在数据分析面试中，SQL是必不可少的一环，对于SQL不仅有常见函数用法的考察，更多时候面试官喜欢出一些编程类题目，本文我们来了解一下那些典型的SQL面试题。(文中的问题均以MySQL为例)
简述类题
Q1 :
MySQL排序窗口函数的区别？
考频：🔥🔥🔥🔥
难度：🔥🔥🔥🔥
- ROW_NUMBER()：按照顺序进行排序（1、2、3...）
- RANK()：并列排序，会跳过重复的序号（1、1、3...）
- DENSE_RANK()：并列排序，不会跳过重复的序号（1、1、2...）
Q2 :
如何进行MySQL优化？
考频：🔥🔥🔥🔥🔥
难度：🔥🔥🔥🔥
SQL进行优化的方式多种多样，这里列出10种常见方法：
1. 使用select具体字段代替select*
2. 查询结果数量已知时，使用limit限定
3. 尽量避免使用in和not in（可以使用between和exists）
4. 尽量避免使用or（可用union代替）
5. 尽量避免进行null值判断（可用0去填充然后判断）
6. 大表驱动小表（in的时候左大右小，exists左小右大）
7. join的表不宜过多（一般不超过3个）
8. 先缩小数据范围，再进行其他操作
9. 针对条件筛选列添加索引
10. 使用group by代替distinct进行去重
Q3 :
MySQL中left join\right join\inner join的区别？
考频：🔥🔥🔥
难度：🔥🔥🔥
- 左外连接（left join）：将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。
- 右外连接（right join）：将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。
- 内连接（inner join）：两表同时满足ON后的条件的部分才会列出。
编程类题
完成编程题的时候，不要被SQL优化的思维固化，这种题目在保证速度和准确率的基础上再去考虑优化方案。
下面选出的5道题目对应着4种常考的SQL类型：查询类、合并类、排序类、字符串提取类。小伙伴们可以根据题目总结类似题目的解题思想。（更多的题目可以去leetcode了解～）
注：写SQL代码是多数公司必不可少的一环，毕竟实践是检验真理的唯一标准。
Q1 :
第二高的薪水
考频：🔥🔥🔥
难度：🔥🔥🔥
题目
- 给定一个如下定义的数据表，编写查询语句获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null。
示例
- 输入：Employee表
输出
答案
select ifnull((
select distinct salary
from Employee
order by salary Desc limit 1,1),null)
as
SecondHighestSalary;解析
- 该题是一道经典的查询类问题，很多的场景下我们需要查找第n高的数据，较为简便的方式就是使用limit(x,y)进行查询，x是定位到第n个数据，y是从x的位置开始显示多少数据。因此本题需要对数据进行从大到小的排序，然后进行limit(1,1)限制，也就表示从第2大的数据开始显示一个数据。
- 因为题目中给出查不到需要显示null因此使用ifNull(查询,null)的方式完成。
Q2 :
上升的温度
考频：🔥🔥🔥
难度：🔥🔥🔥
题目
- 给定一个如下定义的数据表，编写一个SQL查询，来查找与之前（昨天的）日期相比温度更高的所有日期的id 。
示例
输出
答案
select u.id
from Weather u, Weather v
where
datediff(u.recordDate,v.recordDate)=1
and
u.Temperature > v.Temperature;解析
- 本题是一个合并类的题目，我们需要进行前后日期的比较，对于该类比较我们可以对日期做差来完成，对于给定的数据表赋予两个别名得到两个相同的表u和v，对u和v的日期进行做差，如果差值为1则证明正在比较'今天和明天'的数据，此时再对温度做差得到结果即可。
Q3 :
删除重复的电子邮箱
考频：🔥🔥🔥
难度：🔥🔥🔥🔥
题目
- 给定一个如下定义的数据表，编写一个SQL删除语句来删除所有重复的电子邮件，只保留一个id最小的唯一电子邮件。
示例
输出
答案
delete from person
where id not in (
select id
from
(select min(id) as id
from
person group by email)
as t
)解析
- 本题是一道排序类题目，我们要进行重复值的删除并且保留ID最小的那一条数据，此时我们只需要找到每一个最小的ID进行保留即可，因此使用min(id)找到每条数据最小的id，将所有的最小id作为id池，后续只要id不在里面就进行删除即可。
- 除了上述方法，还有比较简单的建立双表，直接找到email相同且id较大的数据进行删除，代码如下：
delete u
from Person u , Person v
where v.id < u.id and u.email = v.emailQ4 :
分数排名
考频：🔥🔥🔥
难度：🔥🔥
题目
- 给定如下的表格，编写SQL查询对分数进行排序。排名按以下规则计算:
- 分数应按从高到低排列。
- 如果两个分数相等，那么两个分数的排名应该相同。
- 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。
- 按 score 降序返回结果表。
示例
输出
答案
select
score,
dense_rank() over(order by Score desc)
as 'rank'
from Scores;解析
- 这是一个考察排序的题目，mysql出现窗口函数之后对于此类问题的解答就简单了许多，不难理解上述答案。但是需要思考的是如果在不使用窗口函数的情况下我们如何完成呢？
Q5 :
患某种疾病的患者
考频：🔥🔥🔥🔥🔥
难度：🔥🔥🔥
题目
- 给定如下的数据表，写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。
示例
输出
答案
select *
from Patients
where conditions like 'DIAB1%'
or
conditions like '% DIAB1%';解析
- 该题是一道典型的字符串提取类题目，对于字符串我们需要掌握字符串的截取、模糊查询、位置查找等操作，对于本题我们使用连续的模糊查询进行筛选即可。