Twitch数据科学家手把手教你拿下面试必考的SQL

原创： Carson Forter MarTechCareer

数据科学方面的面试比较难找到准确的应对方法。它涉及多个学科领域的内容，意味着需要覆盖和练习众多方面才能准备妥当，不然面试的时候就会不知所措。

不管你是第一次面试数据科学的职位，还是即便已经有过一些经验了，这篇由Twitch（视频游戏实时流媒体视频平台，2014年被亚马逊收购）数据科学家亲撰的《拿下数据科学中的SQL面试》文章都值得一看。其中不仅包括了SQL基础知识介绍、常考内容示例，还有关于面试数据科学SQL测试的一些注意事项。

SQL基础知识

我参加过的每一场数据科学面试，都会以某种方式涉及到SQL。我几乎可以保证，在你找工作的过程中，一定会被要求写一些SQL语句给面试官，考察你的基本数据分析能力和数据处理逻辑。尽管一些数据科学家认为他们现在在处理大数据的时候要用Hadoop这样的分布式系统，但事实是，大部分现代数据存储仍然依赖（或允许）用SQL语法来检索和处理数据。

SQL的基本语句都很直截了当，但是在数据科学面试中遇到的SQL问题可能非常复杂。我猜读这篇文章的大多数人都有一些关于这门语言的经验，本文的其余部分假设你对以下内容已有所了解（若非如此，推荐你报名MarTechApe的《SQL基础班特训课程》，3小时掌握企业内最常用sql语句，可划到文末了解）：

SELECT
FROM
JOIN
WHERE
(AND)
GROUP BY
ORDER BY
LIMIT

现举个最基本的例子，一个SELECT…FROM语句将从指定的表中返回一组行和列，这些行和列的详细信息由放在关键字之后的内容决定。例如，要根据用户名的字母顺序来计算今天的网页浏览量，你可以这样写：

SELECT username, count(1) as number_of_views
FROM pageviews
WHERE day = '2017-09-08'
GROUP BY username
ORDER BY username;

明白了的话我们就可以继续往下看了。

子查询(Subqueries)和公共表表达式(Common Table Expressions)

现在你知道如何检索一组行和列，这可能会让你通过面试官的第一个问题。但是更高级的问题将涉及子查询或公共表表达式(CTEs)。让我们讨论一下这些是什么以及如何使用它们。

CTEs和子查询允许获取数据的子集并使用名称存储数据的子集，然后从中选择并执行更多操作。这两个方法的功能几乎相同。子查询是将一个查询块嵌套在另一个查询块的WHERE或HAVING字句的条件中查询块。比如想得到营收额高于或等于在同一地区所有商店平均营收额的店铺ID。这张店铺表的别名为“s“：

SELECT store_id, zipcode, revenue

FROM stores as s
WHERE revenue >=

(SELECT Avg(revenue)

FROM stores

WHERE s.zipcode = stores.zipcode）

ORDER BY zipcode;

上面是一个较为简单的例子，而更复杂的子查询可以层层嵌套。这里我主要关注CTEs，因为我认为它的语法更易于阅读。此外，CTEs会预存子查询的结果，所以运行速度通常比子查询要快。尤其是当某个子查询的表被重复使用的时候，效率会显著提升。

假设你被问到如何计算某个用户下达订单之间的平均时间。你有一个名为transactions的表，该表包含用户名和交易时间。要解决这个问题，你需要将每个交易时间与该用户的下一个交易时间放在一行中。一个查询并不能解决所有问题：你需要先提取一个用户的交易，将其存储在CTEs中，然后将它与此用户之后的交易连结，以便计算平均时间。这可以是这样的:

-- 首先，找出2017年9月8日所有user_a的交易记录

with user_a_trans as (
     SELECT username, time
     FROM transactions
     WHERE day = '2017-09-08'
     AND username = 'user_a'),

-- 将每一条交易与在它之后发生的所有交易合并在一张表格里

joined_trans as (
     SELECT username, time, future_times
     FROM user_a_trans a
     INNER JOIN user_a_trans b
     ON b.time > a.time),

-- 使用MIN()函数找出最近的下一次交易

next_trans as (
     SELECT username, time, MIN(future_times) as next_time
     FROM joined_trans
     GROUP BY username, time)

-- 将前一次交易与下一次交易的时间差取平均值

SELECT AVG(next_time - time) as avg_time_to_next_transactionfrom next_trans;

如果你不能很好的理解这个查询的所有细节，完全没关系——熟能生巧。重要的一点是，复杂的问题需要分解成多个部分，并通过一系列CTEs来解决。

筛选(Filter)，聚合(Aggregate)，合并(Join)

当你遇到像上面这样的难题时，先花一分钟问问自己理想的表应该是什么样的，才能用一个SELECT语句来解决这个问题。在上面的例子中，理想的表是有一条包含每个交易的记录，以及还包含下一个交易时间的一列。

一旦你知道了你的最终表格是什么样的，你就可以逆向思考，一步一步地决定如何使用一系列CTEs将你的原始表格转换成面试官要求的最终输出。

通常，你可以对你的CTEs字符串执行以下步骤：使用WHERE进行筛选，使用GROUP BY进行聚合，使用JOIN进行合并，重复上述步骤。

在合并之前先进行筛选和聚合数据，可以编写最高效的SQL。合并的过程需要花费一定时间，因此在将两个表联结在一起之前，尽可能删减掉不需要的行，只保留有需要的行。有时，会无法先进行聚合，但是你通常可以通过至少一两个WHERE子句来限制最后要合并的表的大小。

需要注意的是，如果在同一个CTE中有一个JOIN句和一个WHERE句，那么SQL首先处理JOIN。换句话说，以下是非常低效的，因为它将先合并两个表，之后才对“2017年1月9日”之后的时间进行筛选:

SELECT *
FROM table_a a
INNER JOIN table_b b
ON a.username = b.username
WHERE a.day >= '2017-09-01'

正确的表达方式是在合并表之前使用CTEs进行筛选，如下:

with a as (
     SELECT *
     FROM table_a
     WHERE day >= '2017-09-01')，

b as (
     SELECT *
     FROM table_b
     WHERE day >= '2017-09-01')

SELECT *
FROM a
INNER JOIN b
ON a.username=b.username;

再强调一次，这里的效率提高是由于先筛选到尽可能少的行，之后执行两个表的合并。

窗口函数

使用上述筛选Filter、聚合Aggregate、合并Join的过程可以解决大多数问题，不过有时候也会遇到棘手的SQL问题，需要使用窗口函数（Window Functions）来解决。

类似GROUP BY语句，窗口函数会将数据分成几个模块，并分别对每个模块进行操作。但与GROUP BY语句不同的是，行不会被合并。以下为示例。

假设你需要处理一个表格，该表格每行代表一个观测项，观测量包括营收情况及其所在的州，你需要计算每个观测项的营收占该州总收入的百分比。

此问题棘手之处就在于你需要将单个值（具体某一行的营收）与汇总值（属于某一州所有行的营收汇总）进行比较。本例中，单个值是指特定观测项的营收，汇总值指某个州所有观测项营收的总和。在这种情况下，解决类似问题的好方法就是使用窗口函数。代码如下：

with state_totals as (
     SELECT state, revenue,
         SUM(revenue) OVER (PARTITION BY state) as state_revenue
     FROM state_line_items)

SELECT state,
revenue/state_revenue as percent_of_state_revenue
FROM state_totals;

窗口函数包含OVER子句。本例中，通过按州划分（PARTITION BY），可得到与每个行观测项关联的各州的汇总值，后续通过简单的除法，上述营收占比问题也就迎刃而解。

窗口函数支持大多数聚合(Aggregate)函数，包括SUM、COUNT或AVG等，但也有一些特殊语句只能用作窗口函数。例如RANK、FIRST_VALUE和LAG。在准备面试时，也应重点准备以上六类函数。

Union及Case语句

此外，还有一些SQL语句需要重点准备。一是Union语句，相对而言较简单。Union语句可以看作是Join函数的垂直形式，也就是说，Join函数是将数据表或CTE水平组合在一起，而Union语句则是将两个表堆叠在一起，形成一个包含原始表中所有行数据的表。Union语句需满足的前提是被组合的两个表具有相同的列，否则将无法按逻辑进行组合。

例如在下面这种情况下，使用union会很有用——已知两张分别对应两类交易类型的数据表格，需要通过一个查询来确定每种交易类型的数量。代码如下：

with sales as (
     SELECT 'sale' as type
     FROM sale_transactions
     WHERE day >= '2017-09-01'),

buys as (
     SELECT 'buy' as type
     FROM buy_transactions
     WHERE day >= '2017-09-01'),

unioned as (
     SELECT type
     FROM buys
     UNION ALL
     SELECT type
     FROM sales)

SELECT type, count(1) as num_transactions
FROM unioned
GROUP BY type;

上述操作首先从两个表中分别选择一个常数字段“type”（在本例中为“ sale”或“ buy”），然后对其进行union合并，最终得到一个大表，从而可通过单个查询进行分组及计数。

总体而言，当需要将两个表组合为一个总表时，Union语句就是答案。

Case语句则是另一个相当简单的概念，与R和Excel等软件中的ifelse函数完全相同，通常应用于将一组预定义值映射到另一组预定义值的情况。

例如，你可能希望将星期几所在的列转换为另一个变量，代表该天是否为周末。

SELECT
CASE WHEN day_of_week in ('Sat', 'Sun')
then 'Weekend' else 'Weekday' end as day_type
FROM table_a;

如上所述，可以将字符串所在列（如“星期几”）转换为二进制变量，然后对其求和以计算表中周末的天数。

SELECT
     SUM(
         CASE WHEN day_of_week in ('Sat', 'Sun')
         THEN 1 ELSE 0 END) as num_weekend_days
FROM table_a;

Case语句的应用非常灵活，可以使用一系列WHEN语句表示映射条件，将对应值映射到目标值，再使用ELSE语句描述剩余的情况。

结语

现在你已经初步了解了成功通过数据科学面试的SQL部分所需的所有知识。当然，练习是关键。试着给自己设置一些问题，然后使用上面描述的工具来解决它们。更好的办法是，找一块白板，在上面练习，这样在面试的时候你会感觉更舒服。

我还想就面试本身给大家提几条建议。

把每个问题分解成尽可能小的部分。这是清晰思考SQL问题的最佳方式，将帮助你一步一步地思考要实现每个步骤所需要的SQL命令。

大声说出你的解题过程。就像在学校里一样，你会因为展示你的作品而得到赞扬。如果你只是独自埋头在白板上写东西，没有与面试官交流让他知道你在做什么，他们将很难评估你的技能，尤其是当你没有得到最终答案的时候。

寻求帮助。无论你是否相信，但大多数面试官都希望面试者能通过面试，也乐于提供一些帮助，只要你能清楚地表达出自己的问题所在。换句话说，可以问一些诸如将整数转换为浮点数的正确语法是什么之类的问题，但是要避免问一些含糊不清的问题，这些问题可能表明您不知道如何处理您试图解决的问题。

最后，如果你想在学习SQL的过程中不是靠自己花费大量时间一点点摸索与判断，而是让专业的SQL面试官来指导你答案究竟是否正确及高效；如果你想跳出“自学的魔咒”，短时间内快速掌握SQL、应对面试，就来报名MarTechApe推出的《SQL面试冲刺课》吧——

Amazon专家授课 + 在线集中答疑 = 拿下你的下一场SQL面试！

MarTechApe联合亚马逊商务智能工程师（Amazon Business Intelligence Engineer）—— Cindy老师，开设了SQL特训课，将行业内所需SQL技能和面试考点浓缩成6个小时的课程内容，让你短时间就能实现SQL能力质的飞跃！

前3小时全方面提升你的SQL能力，用SQL完成一系列的数据分析操练，真正学会SQL编程语言，而不是“只知道个大概”。
后3小时深入辅导Google、Facebook、Amazon、Apple、Uber、Airbnb等知名公司的SQL面试真题，总结不同公司的SQL出题风格。让你从此不再胆怯SQL面试题！
2小时在线集中答疑、模拟面试，并分享SQL在线面试（Live SQL Interview）与白板编程（white board coding）面试技巧。

报名成功的同学还可获赠一场Cindy老师的《如何成为亚马逊Business Intelligence商务智能分析师》线上讲座。

课程大纲

Introduction to SQL | SQL基本概念

SQL introduction, basic concept of relational database
SQL usage in work, transactional database, analysis database
SQL generations:

Oracle
SQL Server
MySQL
Postgres
Redshift

Basic SQL | 1小时SQL基本语句与功能应用

Basic SQL -1:

SELECT
LIMIT
WHERE

Basic SQL -2: Comparison

LIKE
IN
BETWEEN
IS NULL

Basic SQL -3:

Boolean ( AND, OR, NOT)
ORDER BY

Basic SQL -4:

Aggregation ( COUNT, SUM, MIN, MAX, AVG),
GROUP BY

Basic SQL -5:

HAVING
DISTINCT

Basic SQL -6: Case

Intermediate SQL | 2小时SQL进阶语句与功能应用

Intermediate SQL -1: Joins

NNER
OUTER
LEFT
RIGHT
JOIN USING WHERE or ON

Intermediate SQL -2:

UNION
JOIN with comparison
JOIN on multi-key, self join

Intermediate SQL -3:

Data types
Data format
String function
Window function

Intermediate SQL -4:

Primary key
Index
Sort key
Distribution key

SQL Interview Questions | 3小时SQL面试真题详解

Deep dive into SQL interview questions from major Tech companies including but not limited to:

Google
Amazon
Apple
Facebook

Cracking the SQL Interviews | 2小时SQL模拟面试、答疑、面试经验分享

Live Q&A
Interview tips on SQL online Test (e.g., Live interview, CoderPad)
SQL challenges in the real business world

授课老师

Cindy老师

Amazon亚马逊美国担任商务智能工程师（Business Intelligence Engineer）
亚马逊SQL面试官

课程形式

3小时SQL系统性知识培训 + 3小时SQL真题详解+2小时线上答疑模拟面试
录播课程+直播答疑，所有内容均有视频回放，一经购买，可永久回看。
专属课程学习小组，持续SQL真题讨论、学习经验分享。

课程价格

只购买3小时SQL系统性知识培训：149美元/人
只购买3小时SQL真题特训：149美元/人
购买6小时全套课程：249美元/人

注：所有购买者均可参与直播答疑。

官网美元购买通道：

*我们提供标准Invoice，可用于企业报销

如果需要人民币支付，请扫描下方二维码，联系小助手进行购课。

购买后，请联系小助手，加入SQL特训班专属课程群：

小助手

想要快速提高你的SQL能力，就快来报名吧！

坚持学习，保持职场竞争力，选择MarTechApe！

Twitch数据科学家手把手教你拿下面试必考的SQL

Union及Case语句

发布到看一看