作者 | Xinran Waibel
译者 | 天道酬勤 责编 | 徐威龙
封图| CSDN 下载于视觉中国
SQL是用于数据分析和数据处理的最重要的编程语言之一，因此SQL问题始终是与数据科学相关工作（例如数据分析师、数据科学家和数据工程师）面试过程中的一部分。 SQL面试旨在评估应聘者的技术和解决问题的能力。因此，至关重要的是，不仅要根据样本数据编写正确的查询语句，而且还要像对待现实数据集一样考虑各种情况和极端情况。作者曾帮助设计和执行针对数据科学面试者的SQL面试问题，并亲自为大型技术公司和初创公司进行了许多SQL面试。在这篇文章中，作者将解释SQL面试问题中常见的模式，并提供有关如何在SQL查询中巧妙处理它们的技巧。
要搞定SQL面试，最重要的是通过询问所需的尽可能多的问题来确保拥有给定任务和数据样本的所有详细信息。理解需求可以节省你以后迭代问题的时间，并使你能够很好地处理极端情况。 我注意到许多面试候选者往往不了解SQL问题或数据集就直接进入解决方案。后来，在我指出解决方案中的问题之后，他们不得不重复修改查询。最后，他们在迭代中浪费了很多面试时间，甚至可能还没有找到正确的解决方案。我建议将SQL面试视作你正在与业务合作伙伴一起工作。在提供解决方案之前，你希望收集对数据请求的所有要求。你应要求面试官解释清楚“前三名”的含义。我应该在结果中包括3名员工吗？你要我如何处理关系？此外，请仔细查看样本员工数据。薪水字段的数据类型是什么？在计算之前是否需要清除数据？
哪个JOIN？
在SQL中，JOIN通常用于合并来自多个表的信息。有四种不同类型的JOIN，但是在大多数情况下，我们仅使用INNER、LEFT和FULL JOIN，因为RIGHT JOIN不是很直观，可以使用LEFT JOIN轻松地重写。在SQL面试中，你需要根据给定问题的特定要求选择要使用的正确JOIN。 例如：查找每个学生参加的课程总数。（提供学生证、姓名和上课的数量。）
你可能已经注意到，并非所有出现在class_history表中的学生都出现在学生表中，这可能是因为这些学生已不再注册。 （这在事务数据库中实际上是非常典型的，因为记录通常在不活动时会被删除。）根据面试官是否希望结果中的不活动学生，我们需要使用LEFT JOIN或INNER JOIN来组合两个表：WITH class_count AS (
SELECT student_id, COUNT(*) AS num_of_class
FROM class_history
GROUP BY student_id
)
SELECT
c.student_id,
s.student_name,
c.num_of_class
FROM class_count c
-- CASE 1: include only active students
JOIN student s ON c.student_id = s.student_id
-- CASE 2: include all students
-- LEFT JOIN student s ON c.student_id = s.student_id
GROUP BY是SQL中最重要的功能，因为它广泛用于数据聚合。如果在SQL问题中看到诸如求和、平均值、最小值或最大值之类的关键字，则表明你可能应该在查询中使用GROUP BY。一个常见的陷阱是在过滤数据和GROUP BY时混合使用WHERE和HAVING，我见过很多人会犯此错误。例如：计算每个学生在每个学年中平均所需的课程GPA，并找到每个学期符合Dean's List（GPA≥3.5）资格的学生。
由于我们在GPA计算中仅考虑必修课程，因此需要使用WHERE is_required = TRUE排除可选课程。我们需要每位学生每年的平均GPA，因此我们将按GROUPS的thestudent_id和theschool_year列并取thegpa列的平均值。最后，我们仅保留学生平均GPA高于3.5的行，可以使用HAVING来实现。让我们将所有内容放在一起：SELECT
student_id,
school_year,
AVG(gpa) AS avg_gpa
FROM gpa_history
WHERE is_required = TRUE
GROUP BY student_id, school_year
HAVING AVG(gpa) >= 3.5
注意，每当在查询中使用GROUP BY时，你都只能选择“分组依据”列和聚合列，因为其他列中的行级信息已被舍弃。有些人可能想知道WHERE和HAVING之间有什么区别，或者为什么我们不只是写HAVING avg_gpa> = 3.5而未指定函数。我将在下一节中详细解释。
多数人从SELECT开始从上到下编写SQL查询，但是你知道SELECT是SQL引擎执行的最后一个函数之一吗？以下是SQL查询的执行顺序：
再次考虑前面的示例。因为我们想在计算平均GPA之前过滤掉可选课程，所以我使用WHERE is_required = TRUE代替HAVING，因为WHERE在GROUP BY和HAVING之前执行。我无法写HAVING avg_gpa> = 3.5的原因是，avg_gpa被定义为SELECT的一部分，因此无法在SELECT之前执行的步骤中引用它。我建议在编写查询时遵循执行顺序，这在你编写复杂查询时会很有帮助。
窗口函数也经常出现在SQL面试中。共有五种常见的窗口函数：- RANK / DENSE_RANK / ROW_NUMBER：这些通过排序特定列为每行分配一个等级。如果给出了任何分区列，则行将在其所属的分区组中排名。
- LAG / LEAD：它根据指定的顺序和分区组从前一行或后一行检索列值。
在SQL面试中，了解排名函数之间的区别以及知道何时使用LAG / LEAD至关重要。
当一个SQL问题要求输入“ TOP N”时，我们可以使用ORDER BY或等级函数来回答该问题。但是，在此示例中，它要求计算“每个Y中的TOP N X”，这强烈暗示我们应该使用排名函数，因为我们需要对每个分区组中的行进行排名。以下查询恰好找到3个薪水最高的员工，而不论他们的关系如何：WITH T AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_salary DESC) AS rank_in_dep
FROM employee_salary)
SELECT * FROM T
WHERE rank_in_dep <= 3
-- Note: When using ROW_NUMBER, each row will have a unique rank number and ranks for tied records are assigned randomly. For exmaple, Rimsha and Tiah may be rank 2 or 3 in different query runs.此外，根据应如何处理关系，我们可以选择其他排名函数。同样，细节很重要！ROW_NUMBER，RANK和DENSE_RANK函数的结果比较SQL面试中的另一个常见陷阱是忽略数据重复。尽管样本数据中的某些列似乎具有不同的值，但还是希望面试者考虑所有可能性，就像他们在处理真实数据集一样。例如，在上一个示例的employee_salary表中，可以让雇员共享相同的名称。避免重复引起的潜在问题的一种简单方法是始终使用ID列唯一地标识不同的记录。例如：使用employee_salary表查找每个部门的所有部门的总薪水。正确的解决方案是GROUP BY employee_id，然后使用SUM（employee_salary）计算总工资。如果需要雇员姓名，请在末尾与雇员表联接以检索雇员姓名信息。错误的方法是使用GROUP BY employee_name。
在SQL中，任何谓词都可以产生三个值之一：true，false和NULL，这是用于未知或缺失数据值的保留关键字。处理NULL数据集可能会出乎意料的棘手。在SQL面试中，面试官可能会特别注意你的解决方案是否处理了NULL值。有时很明显，如果一列不可为空（例如ID列），但是对于大多数其他列，很有可能会有NULL值。我建议确认示例数据中的键列是否可为空，如果可以，请利用IS（NOT）NULL，IFNULL和COALESCE之类的功能来覆盖这些极端情况。
最后一条但也很重要的一点是：在SQL面试期间保持交流。 我面试了许多面试者，他们几乎没有说话，除非他们有疑问，如果他们最终提出了完美的解决方案，那就没问题了。但是，在技术面试过程中保持交流通常是一个方法。例如，你可以谈论你对问题和数据的理解，计划如何解决问题，为什么使用某些功能而不是其他替代方法以及正在考虑哪些极端情况。- 在INNER，LEFT和FULL JOIN之间进行仔细选择。
- 使用GROUP BY聚合数据并正确使用WHERE和HAVING。
- 如果您在创建复杂的查询时遇到困难，请尝试遵循SQL执行顺序。
为了帮助您了解如何在实际的SQL采访中使用这些策略，我将在下面的视频中从头到尾逐步介绍一个示例SQL采访问题：
视频连接：https://youtu.be/EjETvPfiwYw
原文：https://towardsdatascience.com/crack-sql-interviews-6a5fc90ec763