SQL 是数据分析和处理最基本的编程语言之一, 因此, 无论是面试数据分析师, 数据科学家, 数据工程师, 还是其他相关工作, 都免不了要过这一关.
实战技术和解决问题的能力是 SQL 面试中考察的重点, 应聘者不仅要基于示例数据编写正确的查询, 还要考虑各种场景和边缘情况, 就如同在处理实际数据集.
笔者曾经帮助求职者设计过 SQL 面试问题, 并模拟了面试, 也多次亲身参加了大型科技公司和初创企业 SQL 求职面试的实战. 本文将对 SQL 面试问题的常见模式进行阐释, 分享在 SQL 查询中灵活处理这些模式的技巧.
快掏出小本本开始学习吧~
提问
要拿下一场 SQL 面试, 最重要的在于尽可能多地提问, 以确保自己掌握了给定任务和数据样本的所有细节. 理解这些需求有助于节省迭代问题的时间, 也有助于更好地处理边缘情况.
许多应聘者会在没有深入理解 SQL 问题或数据集之前, 直接开始解决问题. 在笔者指出解决方案中的问题之后, 他们不得不反复修改查询, 在迭代上浪费了大量时间, 甚至到最后都没找到正确的解决方案.
笔者的建议是将 SQL 面试视为在与业务合作伙伴一起工作, 保持这种心态, 面试者就会在提供解决方案之前努力收集数据请求的所有需求.
示例
从下表中找出薪资最高的三位职员.
样本: 职员薪资表
面试者应该让面试官仔细阐述 "前三名" 的概念 -- 结果中必须只有三名职员吗? 对于并列的处理有何要求? 此外, 面试者应仔细查看示例职员的数据 -- 薪资字段的数据类型是什么? 需要在计算之前清除数据吗?
何种连接
在 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 混淆 -- 许多人都犯过这个错误.
示例
计算每个学生每学年的必修课平均绩点, 并找出每学期中绩点≥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 查询的执行顺序:
- 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 等函数来覆盖这些边缘情况.
沟通
另外很重要的一点在于 -- 在面试过程中保证流畅的沟通.
在笔者面试过的求职者中的很多人, 除非真的有问题, 否则几乎不说话. 如果他们能在最后给出完美的解决方案, 那倒也没什么大问题, 但在技术面试中保持与面试者的沟通通常会有所助益. 例如, 面试者可以谈论自己对问题和数据的理解, 自己是如何计划解决问题的, 使用这个函数而不是另外一个的原因, 或者正在考虑的边缘情况.
总结
先提问, 收集所需的详细信息.
谨慎选择连接方式 -- 自然连接, 左连接还是全连接.
使用 GROUP BY 聚合数据, 合理使用 WHERE 和 HAVING.
了解三个 ranking 函数之间的差异.
了解何时使用 LAG/LEAD 窗口功能.
如果需要创建的查询太过复杂, 尝试按照 SQL 执行顺序编写.
考虑潜在的数据问题, 如重复项和空值.
与面试官沟通思维过程.
面试顺利冲鸭!
来源: http://database.51cto.com/art/202003/613482.htm