数据科学家面试必须掌握的十个SQL概念

May 17, 2021 by Zhang in Blog

SQL是非常强大而且多功能的工具。但是实际上，在数据科学面试时，大多数公司只会考少数几个核心概念。这10个概念是最常出现的，在现实生活中也是应用是最多的。

在本文中，我将介绍我认为最重要的10个SQL概念，在准备面试时，你应将大部分时间集中在这些概念上。

我最近还遇到了一个很棒的网站，称为StrataScratch，上面有100多道SQL的练习题，在这里推荐给你。

话不多说，我们开始吧！

1 CASE WHEN

你很可能会遇到许多需要使用CASE WHEN的问题，这是因为它是一个非常万能的概念。

你可以使用它来编写复杂的条件语句，来根据某些变量来找到某个值或者类。

鲜为人知的是，它还能让你做数据透视表。例如，如果你有一个month列，而且想要为每个月创建一个单独的列，则可以使用CASE WHEN语句来做数据透视。

示例问题：写一个SQL查询操作，来重新设定表的格式，让每个月都有一个Revenue（收益）列。

Initial table:
+——+———+——-+
| id   | revenue | month |
+——+———+——-+
| 1    | 8000    | Jan   |
| 2   | 9000    | Jan   |
| 3    | 10000   | Feb   |
| 1    | 7000    | Feb   |
| 1    | 6000    | Mar   |
+——+———+——-+

Result table:
+——+————-+————-+————-+—–+———–+
| id   | Jan_Revenue | Feb_Revenue | Mar_Revenue | … | Dec_Revenue |
+——+————-+————-+————-+—–+———–+
| 1    | 8000        | 7000        | 6000        | … | null        |
| 2    | 9000        | null        | null        | … | null        |
| 3    | null        | 10000       | null        | … | null        |
+——+————-+————-+————-+—–+———–+

2 SELECT DISTINCT

SELECT DISTINCT是一个你始终应该记在脑子里的东西。将SELECT DISTINCT语句与聚合函数（第3点概念会讲到）一起使用是非常普遍的。

比如，如果你有一个显示客户订单的表，则可能会要求你计算每个客户的平均订单数。在这种情况下，你需要用订单总数除以客户总数。它应该是这样的：

SELECT
COUNT(order_id) / COUNT(DISTINCT customer_id) as orders_per_cust
FROM
customer_orders

3 聚合函数

与第2点概念一样，你应该对聚合函数（如最小值，最大值，总和，计数等）有深刻的认知与了解。这也意味着你要对GROUP BY和HAVING语句有深入的了解。我强烈建议你花一些时间做一些练习题目，因为你可以发现一些使用聚合函数的创造性方法。

示例问题：写一个SQL查询，在一个叫Person的表格中查找所有重复的电子邮件地址。

+—-+———+
| Id | Email   |
+—-+———+
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
+—-+———+ANSWER:
SELECT
    Email
FROM
    Person
GROUP BY
    Email
HAVING
    count(Email) > 1

4 Left Joins vs Inner Joins

对于那些刚接触SQL或一段时间不使用SQL的人来说，很容易将Left Join和Inner Join混在一起。你需要确保你能清楚地了解每种join是如何得出不同的结果的。在许多面试问题中，都要求你使用某种形式的join，而在某些情况下，选择一种join与另一个join是选择正确答案与错误答案之间的区别。

5 什么是人口普查？

现在我们来看看更多有趣的概念！SQL的self join会把一个表与它自身联接起来。你可能会觉得这毫无用处，但是你会惊讶于使用这种语句的普遍性。在许多实际操作中，数据会存储在一个大表中，而不是许多较小的表中。在这种情况下，可能需要self join来解决一些独特的问题。

让我们看一个例子。

示例问题：给定下面的Employee表，写一个SQL查询，找出收入比他经理更高的员工。

从这个表看，Joe是唯一一个收入高于他的经理的员工。

+—-+——-+——–+———–+
| Id | Name | Salary | ManagerId |
+—-+——-+——–+———–+
| 1 | Joe   | 70000 | 3         |
| 2 | Henry | 80000 | 4         |
| 3 | Sam   | 60000 | NULL      |
| 4 | Max   | 90000 | NULL      |
+—-+——-+——–+———–+Answer:
SELECT
    a.Name as Employee
FROM
    Employee as a
        JOIN Employee as b on a.ManagerID = b.Id
WHERE a.Salary > b.Salary

6 子查询Subqueries

子查询（Subqueries），也称为内部查询（inner query）或嵌套查询（nested query），是存在于查询中的查询，并且嵌入在WHERE语句中。这是用来解决需要按顺序进行多个查询才能产生指定结果的问题的好方法。子查询和WITH AS语句都非常有用，因此你应该确保自己知道怎么使用它们。

示例问题：假设一个网站包含两个表，Customers表和Orders表。编写一个SQL查询来查找所有从来没有购买过任何商品的客户。

Table: Customers.+—-+——-+
| Id | Name |
+—-+——-+
| 1 | Joe   |
| 2 | Henry |
| 3 | Sam   |
| 4 | Max   |
+—-+——-+Table: Orders.
+—-+————+
| Id | CustomerId |
+—-+————+
| 1 | 3          |
| 2 | 1          |
+—-+————+Answer:
SELECT
    Name as Customers
FROM
    Customers
WHERE
    Id NOT IN (
        SELECT
            CustomerId
        FROM Orders
    )

7 String 格式

字符串的函数非常重要，尤其是在处理杂乱的数据时。 因此，公司可能会在字符串格式和操作方面对你进行测试，来确认你是否知道如何操作数据。

常见String格式有以下内容：

LEFT, RIGHT
TRIM
POSITION
SUBSTR
CONCAT
UPPER, LOWER
COALESCE

8 时间日期格式

你多半会遇到某些涉及日期时间数据的SQL问题。例如，你可能需要按月对数据进行分组，或把变量格式从DD-MM-YYYY转换为简单的月。

你应该知道的一些函数有：

EXTRACT
DATEDIFF

示例问题：给定一个Weather表，编写一个SQL查询，查找与其之前（昨天）日期相比温度更高的所有日期的ID。

+———+——————+——————+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+———+——————+——————+
|       1 |       2015-01-01 |              10 |
|       2 |       2015-01-02 |               25 |
|       3 |       2015-01-03 |               20 |
|       4 |       2015-01-04 |               30 |
+———+——————+——————+Answer:
SELECT
    a.Id
FROM
    Weather a,
    Weather b
WHERE
    a.Temperature > b.Temperature
    AND DATEDIFF(a.RecordDate, b.RecordDate) = 1

9 窗口函数（Window Functions）

窗口函数让你可以在所有行上计算总值，而不是仅返回一行（这是GROUP BY语句的作用）。 如果你想对行进行一个排名，或者计算累计总和等等，这个函数将非常有用。

示例问题：编写查询来找到薪水最高的empno，确保你的答案能正确处理重复的值！

depname | empno | salary |
———–+——-+——–+
develop   |    11 |   5200 |
develop   |     7 |   4200 |
develop   |     9 |   4500 |
develop   |     8 |   6000 |
develop   |    10 |   5200 |
personnel |     5 |   3500 |
personnel |     2 |   3900 |
sales     |     3 |   4800 |
sales     |     1 |   5000 |
sales     |     4 |   4800 |Answer:
WITH sal_rank AS
(SELECT
    empno,
    RANK() OVER(ORDER BY salary DESC) rnk
FROM
    salaries)
SELECT
empno
FROM
sal_rank
WHERE
rnk = 1;