数据分析面试手册《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类型：查询类、合并类、排序类、字符串提取类。小伙伴们可以根据题目总结类似题目的解题思想。

注：写SQL代码是多数公司必不可少的一环，毕竟实践是检验真理的唯一标准。

Q1：第二高的薪水

考频：🔥🔥🔥
难度：🔥🔥🔥

题目

给定一个如下定义的数据表，编写查询语句获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

字段名	数据类型
id	int
salary	int

示例：

输入：Employee表

id	salary
1	100
2	200
3	300

输出：

SecondHighestSalary
200

答案

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 。

字段名	数据类型
id	int
recordDate	date
temperature	Int

示例

id	recordDate	temperature
1	2015-01-01	10
2	2015-01-02	25
3	2015-01-02	20
4	2015-01-04	30

输出

id
2
4

答案

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最小的唯一电子邮件。

字段名	数据类型
id	int
email	Archer

示例

id	email
1	john@example.com
2	bob@example.com
3	john@example.com

输出

id	email
1	john@example.com
2	bob@example.com

答案

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.email 

Q4：分数排名

考频：🔥🔥🔥
难度：🔥🔥🔥

题目

给定如下的表格，编写SQL查询对分数进行排序。排名按以下规则计算:

• 分数应按从高到低排列。
• 如果两个分数相等，那么两个分数的排名应该相同。
• 在排名相同的分数后，排名数应该是下一个连续的整数。换句话说，排名之间不应该有空缺的数字。

按 score 降序返回结果表。

字段名	数据类型
id	int
score	decimal

示例

id	score
1	3.50
2	3.65
3	4.00
4	3.85
5	4.00
6	3.65

输出

Score	Rank
4.00	1
4.00	1
3.85	2
3.65	3
3.65	3
3.50	4

答案

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 。

按任意顺序返回结果表。

字段名	数据类型
patient_id	int
patient_name	varchar
conditions	varcher

示例

patient_id	patient_name	conditions
1	Daniel	YFEV COUGH
2	Alice
3	Bob	DIAB100 MYOP
4	George	ACNE DIAB100
5	Alain	DIAB201

输出

patient_id	patient_name	conditions
3	Bob	DIAB100 MYOP
4	George	ACNE DIAB100

答案

select *
from Patients where conditions like 'DIAB1%' or conditions like '% DIAB1%';

解析

该题是一道典型的字符串提取类题目，对于字符串我们需要掌握字符串的截取、模糊查询、位置查找等操作，对于本题我们使用连续的模糊查询进行筛选即可。