数据分析面试手册《SQL篇》

2024-04-28 21:36| 来源: 网络整理| 查看: 265

前言最近互联网行业进入了工作变动的高峰期，很多读者对于数据分析的面试题不知道如何进行解答，本文开始二师兄将连载《数据分析面试手册》来帮助大家！

在当前的数据分析岗位中，多数人在做着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种常见方法：

使用select具体字段代替select* 查询结果数量已知时，使用limit限定尽量避免使用in和not in（可以使用between和exists）尽量避免使用or（可用union代替）尽量避免进行null值判断（可用0去填充然后判断）大表驱动小表（in的时候左大右小，exists左小右大） join的表不宜过多（一般不超过3个）先缩小数据范围，再进行其他操作针对条件筛选列添加索引使用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 [email protected] 2 [email protected] 3 [email protected]

输出

id email 1 [email protected] 2 [email protected]

答案

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%';

解析

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

【本文地址】

数据分析面试手册《SQL篇》

数据分析面试手册《SQL篇》

今日新闻

推荐新闻