SQL面试的技巧和陷阱有哪些

63次阅读
没有评论

共计 3702 个字符,预计需要花费 10 分钟才能阅读完成。

本篇内容介绍了“SQL 面试的技巧和陷阱有哪些”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!

提问

要拿下一场 SQL 面试,最重要的在于尽可能多地提问,以确保自己掌握了给定任务和数据样本的所有细节。理解这些需求有助于节省迭代问题的时间,也有助于更好地处理边缘情况。

许多应聘者会在没有深入理解 SQL 问题或数据集之前,直接开始解决问题。在笔者指出解决方案中的问题之后,他们不得不反复修改查询,在迭代上浪费了大量时间,甚至到最后都没找到正确的解决方案。

笔者的建议是将 SQL 面试视为在与业务合作伙伴一起工作,保持这种心态,面试者就会在提供解决方案之前努力收集数据请求的所有需求。

示例

从下表中找出薪资最高的三位职员。

样本:职员薪资表

面试者应该让面试官仔细阐述“前三名”的概念 mdash; mdash; 结果中必须只有三名职员吗? 对于并列的处理有何要求? 此外,面试者应仔细查看示例职员的数据 mdash; mdash; 薪资字段的数据类型是什么? 需要在计算之前清除数据吗?

何种连接

在 SQL 中,连接经常用于组合来自多个表的信息。共有四种不同类型的连接, 但是在大多数情况下,我们只使用自然连接、左连接和全连接,因为右连接并不直观,而且使用左连接很容易重写。在 SQL 面试中,面试者需要根据给定问题的特定要求,选择正确的连接。

示例

找出每位学生上课的总节数。(已知学生证、姓名和上课次数。)

样本:学生名单和课程数据表

可以注意到,并非所有出现在课程数据表中的学生都存在于学生名单中,这可能是因为这些学生已经毕业 (这在事务数据库中非常典型,数据不活跃时就会被删除)。在了解清楚面试官是否希望将不活跃的学生包括在内之后,可以根据情况使用左连接和自然连接两种方式来合并表格。

WITHclass_count AS ( SELECT student_id, COUNT(*) ASnum_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

GROUP  BY 是 SQL 中最基本的函数,广泛用于数据聚合。如果在一个 SQL 问题中出现了 sum、average、minimum 或 maximum 等关键字,则极有可能应该在查询中使用 GROUP  BY。一个常见的陷阱是,在用 GROUP BY 过滤数据时将 WHERE 和 HAVING 混淆 mdash; mdash; 许多人都犯过这个错误。

示例

计算每个学生每学年的必修课平均绩点,并找出每学期中绩点 3.5 的学生。

样本:GPA 数据表

在计算 GPA 时只考虑必修课,因此需要使用 WHERE is_required =  TRUE 来排除选修课。需要计算每个学生每学年的平均绩点,因此需要用 GROUP BY 命令按 student_id 和 school_year   两列来进行分组,并取 gpa 的平均值。最后,只保留平均 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 之间有什么区别,或者想知道为什么不直接用 avg_gpa =  3.5,而是指定函数。下一节将会给出详细解释。

SQL 查询语句执行顺序

在写 SQL 查询时,大多数人是按照自上而下的顺序,但他们可能并不知道 SELECT 是 SQL 引擎最后执行的函数之一。以下是 SQL 查询的执行顺序:

鸿蒙官方战略合作共建——HarmonyOS 技术社区

FROM, JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

LIMIT, OFFSET

回头再看前面的示例。因为需要在计算平均绩点之前过滤掉选修课,所以可以用 WHERE is_required =  TRUE 来代替 HAVING,因为 WHERE 在 GROUP BY 和 HAVING 之前执行。不用 HAVINGavg_gpa =  3.5 的原因是 avg_gpa 被定义为 SELECT 的一部分,所以不能在 SELECT 之前执行的步骤中引用。

图源:unsplash

笔者建议在编写查询时按照执行顺序编写,这在编写复杂查询时非常有用。

窗口函数

窗口函数也经常出现在 SQL 面试中。五种常见的窗口函数如下:

RANK /DENSE_RANK  /ROW_NUMBER:通过对特定列排序,为每行分配一个秩。如果给定了任何分区列,则行将在其所属的分区组中排列。

LAG /LEAD:根据指定的顺序和分区组从前一行或后一行检索列值。

在 SQL 面试中,面试者必须知道排名函数之间的差异,以及何时使用 LAG/LEAD。

示例

找出每个部门中薪资最高的 3 名职员。

样本:职员薪资表 2

当 SQL 问题要求找出“前 N 名”时,可以使用 ORDER BY 或 ranking 函数来回答。但以上示例要求计算“每个 Y 中的前 N   个 X”,这代表着面试者应该使用排 ranking 函数,因为需要对每个分区组中的行进行排列。

下面的查询能准确找到 3 名薪资最高的职员,不考虑并列:

WITH TAS ( SELECT *, ROW_NUMBER() OVER (PARTITION BYdepartment_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 aunique rank number and ranks for tied records are assigned randomly. Forexmaple, Rimsha and Tiah may be rank 2 or 3 in different query runs.

另外,根据面试官对并列情况处理的要求,面试者也可选择不同的 ranking 函数。再次提醒大家,细节很重要!

ROW_NUMBER、RANK 和 DENSE_RANK 三种函数的对比。

重复项

SQL 面试中的另一个常见陷阱是忽略重复项。尽管有些列在示例数据中似乎具有不同的值,但面试者还是应该考虑所有可能的情况,就像在处理真实的数据集一样。例如,在上例的员工薪资表中,不同职员可能出现同名情况。

想要避免重复项引起的潜在问题,一个简单的方法是始终使用 ID 列来标识不同的记录,避免重复。

示例

根据职员薪资表,找出所有部门每个职员的总工资。

正确的解决方案是按 employee_id   来分组,使用 SUM(employee_salary) 来计算总薪资。如果需要员工姓名,可在末尾加入职员表格来检索职员的姓名信息。

用 employee_name 来分组是错误的。

NULL

在 SQL 中,任何谓词都可能产生以下三个值之一:true、false 和 NULL。NULL 这一关键词用于指代未知或空缺数据。处理 NULL 可能会非常棘手。在 SQL 面试中,面试官会特别注意面试者在解决过程中是否处理了 NULL。在一些情况下,很明显某列数据不能为空值 (例如 ID 列),但大多数其他的列很可能会出现 NULL。

笔者建议面试者确认示例数据中的关键列是否可以为空值,如果可以,则可以使用 IS (NOT) NULL、IFNULL 和 COALESCE   等函数来覆盖这些边缘情况。

沟通

另外很重要的一点在于 mdash; mdash; 在面试过程中保证流畅的沟通。

在笔者面试过的求职者中的很多人,除非真的有问题,否则几乎不说话。如果他们能在最后给出完美的解决方案,那倒也没什么大问题,但在技术面试中保持与面试者的沟通通常会有所助益。例如,面试者可以谈论自己对问题和数据的理解、自己是如何计划解决问题的、使用这个函数而不是另外一个的原因、或者正在考虑的边缘情况。

“SQL 面试的技巧和陷阱有哪些”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站,丸趣 TV 小编将为大家输出更多高质量的实用文章!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-15发表,共计3702字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)