数据科学的 SQL 问题

引言

SQL 在数据科学面试中受到如此频繁的考察，是因为全职数据科学家每天都使用它来进行研究和从数据中提取洞察。

SQL 数据科学的面试问题与数据科学家实际执行的任务非常相似。候选人需要根据这些查询收集数据、进行分析并提取指标。在大多数面试中，您都会被要求快速写出或在白板上写出 SQL 查询。

数据科学 SQL 问题可以分为三个主要类别：

• 初级 SQL 数据科学问题： 这些问题基于定义、提供用例示例或比较不同的 SQL 函数。一个基本的 SQL 数据科学问题是：WHERE 和 HAVING 有什么区别？
• 中级数据科学 SQL 问题： 数据科学面试中的大多数问题都是中级的查询编写问题。为了生成所需的指标，您将获得数据或表结构，并被要求创建 SQL 查询。这些问题将涵盖基本连接、聚合和日期操作。
• 高级 SQL 数据科学问题： 子查询、窗口函数和高级连接只是通过挑战性 SQL 问题进行测试的一些 SQL 原理。此外，您还应准备好应对需要您研究业务问题的多步 SQL 案例研究。

SQL 在数据科学中的角色应用

并非所有数据科学职位都采用相同的 SQL 概念。虽然某些职位更常涉及数据架构和 ETL 过程，但对于其他职位，查询和查询优化将是重点。以下类型用于对数据科学面试中提出的 SQL 命令进行分组。

• 数据定义语言 (DDL)
  • CREATE
  • ALTER
  • DROP
  • 重命名
  • TRUNCATE
  • 注释
• 数据操纵语言 (DML)
  • INSERT
  • UPDATE
  • DELETE
  • MERGE (合并)
  • CALL
  • 解释计划
  • 锁定表
• 数据查询语言 (DQL)
  • SELECT
• 数据控制语言 (DCL)
  • 授予
  • 撤销

大多数时候，数据科学家或分析师将处理 SELECT 语句及其相关的 CTE、分组、汇总、窗口函数和子查询等高级主题。如果您是分析师，您很可能已经在使用 SQL。

数据科学家、数据工程师和数据分析师是协作的。构建和维护数据架构，以及开发促进数据科学家和分析师数据访问的方法是他们的主要职责。他们通过履行职责来协助数据科学家工作。作为一名数据工程师，您不可避免地需要了解 SQL，其熟练程度通常比数据科学家和分析师要求更高。因此，您将被问到的问题将与问到科学家和数据分析师的问题相似。您除了 DQL 命令外，还必须精通数据库建模。因此，您还应该精通 DDL、DML 和 DCL 命令。

混合领域和机器学习工程师的专家填补了软件开发人员和数据科学家之间的知识鸿沟。他们必须在这两个领域都拥有特定的技能集，因为他们充当这两个角色之间的桥梁。他们通常会运用多种技能集来实现这一目标，包括：

• 统计量
• 数学
• 数据挖掘
• 数据分析
• 预测分析

软件开发人员经常面临 SQL 面试，这些面试结合了计算机科学、工程学和数学等各种技能领域。他们使用多种学科来设计、编写、测试和维护程序。与机器学习工程师类似，他们将不得不与不同的部门和客户合作。因此，即使他们的主要职责不是数据分析，他们也需要高水平的业务和技术能力。他们必须依赖在后台运行的数据库来开发用户界面。在实现新软件时，利用这些数据库并进行数据分析是必要的。

初级 SQL 数据科学问题

对于数据科学家，以下是一些经常被问到的基本 SQL 查询：

1. SQL 中的 JOIN 是什么？

JOIN 是一个关键字，用于在相同的键上组合两个或多个表。

2. 使用哪个 SQL 命令将记录添加到表中？

INSERT 命令用于向已存在的表中添加行。

3. OLAP 和 OLTP 是什么？何时对数据进行反规范化？

OLTP 数据库用于在线事务处理，OLAP 数据库用于在线分析处理。在执行 OLAP 操作时，对数据进行反规范化；在执行 OLTP 操作时，对其进行规范化。

4. 区分 WHERE 和 HAVING

在分组之前，可以使用 WHERE 子句过滤行，并且可以使用 HAVING 子句在分组之后排除记录。

5. CASE WHEN 函数何时使用？

您可以使用 CASE WHEN 将数据从宽格式转换为长格式，并且可以在 SELECT 子句上构建复杂的条件语句。

6. 区分 LEFT JOIN 和 INNER JOIN？

简单来说，JOIN 用于组合来自多个数据源的数据。即使右表没有匹配项，LEFT JOIN 也会聚合并返回左表中的所有条目。INNER JOIN 仅在两个表中都存在匹配项时才返回行。

7. UNION 和 UNION ALL 何时使用？

UNION 可用于连接多个数据集。因此，根据查询的参数，UNION 用于将两个搜索合并为一个结果。另一方面，UNION ALL 会包含来自两个表的每个行，包括重复项。

8. 区分 COUNT 和 COUNT DISTINCT？

COUNT 函数返回查询提供的行数，或由 WHERE 条件指示的行数。COUNT DISTINCT 返回表中唯一行的数量，或者在应用计数之前删除重复值。它也不计算包含 NULL 值的行。

9. HAVING 子句和 WHERE 子句何时使用？

如果您将 GROUP BY 子句与 WHERE 和 HAVING 一起使用，您将同时使用它们。例如，WHERE 将在 GROUP BY 子句之前过滤行，HAVING 将在 GROUP BY 子句之后过滤记录。

10. 编写一个 SQL 查询来选择部门中的第二高薪水。

向您展示了一个员工表（e_id, name, salary, d_id）和一个部门表（id, name）。部门表中的“id”字段链接到员工表中的 d_id 字段。

了解这一点，我们可以说 d_id 是一个“外键”，因为它在列中引用了另一个表的主键。我们可以使用 INNER JOIN 在此公共字段上连接这两个表，并将属于这些部门的员工与其部门名称关联起来。

11. 编写一个查询，找出在 2022 年注册的用户在“purchases”表中总共花费了多少金额。

假设您的数据库中有两个表。用户详细信息（包括注册日期）列在该表中。所有用户的购买历史（如果有）都包含在第二个表中，该表专用于购买。

可以通过以下步骤解决此问题：

• 使用 user_id 列连接用户表和 purchases 表，方法是使用 INNER JOIN 或 JOIN。
• 使用 WHERE 子句过滤结果。
• 使用 GROUP BY 聚合项目，然后使用 SUM 计算总花费金额。

中级数据科学 SQL 问题

中级 SQL 数据科学问题包括最复杂的连接、聚合、窗口函数和其他 SQL 代码挑战。通常，这些是更复杂的报告和分析查询，需要复杂的解决问题技术和多步聚合才能解决。

12. 给定一个交易表，编写一个查询来识别每个用户的第三次购买。

为了确定哪个交易是第三次，我们需要一个指示。如果我们考虑对我们的集合进行评级，那么想到一个特定的窗口函数就至关重要。

要处理交易表，我们必须使用 RANK 函数。通过窗口函数 RANK，将为结果集分区中的每一行分配一个指定的排名。

13. 编写一个查询，根据包含工作发布的表，区分发布工作一次的用户数和发布至少一次工作多次的用户数。

如果一个用户有 5 个工作但每个工作只发布了一次，那么该用户就被包含在 single_post 用户群中。如果一个人有 5 个工作机会，发布了 7 次，那么其中至少一个工作应该有重复发布。

鸽巢原理通常指出，如果一个用户的总帖子数多于不同的工作数，那么至少有一项工作必须被发布不止一次。

为了获得工作帖子的结构化版本，我们首先创建一个我们称为 user_job 的子查询。

我们希望按用户和工作细分总工作帖子数。我们创建子查询以计算每个用户的帖子 ID 和不同的工作 ID，因为每个工作帖子都有一个唯一的 ID。

由于每个工作 ID 已经唯一，我们对 j_id 使用 COUNT DISTINCT 来获得每个工作的唯一行，并对 id 使用 COUNT。然后，通过按 user_id 分组，将每个用户的唯一工作数量（表示为 num_jobs）与每个用户的总帖子数（表示为 n_posts）进行比较。

14. 编写一个查询，根据包含学生分数的表，检索 SAT 分数最接近的两个学生。

注意：在不止一名学生具有相同的最低分数差的情况下，选择具有更高字母顺序的学生姓名组合。

问题陈述涉及一个只有两列的表，因此我们必须多次自引用相同的数据。将此问题视为两个具有相同值的不同表很有帮助。

此问题包含两个部分：

• 将每个学生的组合与其各自的 SAT 分数进行比较。
• 确定最接近的两个学生的得分。

两个相同的表 s1 和 s2 用于比较的第一部分。为了将每个学生与每个其他学生进行比较，我们可以使用交叉连接技术的一个变体，将每个用户与每个其他用户进行链接。

高级 SQL 数据科学问题

在数据科学面试中，高级 SQL 问题可能包括使用子查询、窗口函数和复杂连接的复杂查询。另一方面，SQL 案例研究是具有挑战性的 SQL 问题中最常见的类型之一。

15. 编写一个查询来显示 2020 年当月用户数、交易数和总订单金额。

假设我们只对特定年份的月度报告感兴趣。向您展示了三个表：users、products 和 transactions。

我们只能使用 transactions 和 products 表来确定我们的客户数量和他们每月的总购买金额，因为用户表中的任何信息都与我们的查询结果没有直接关系。

16. 计算已转化用户的每个 user_id 的首次触达归因。

以下模式中的两个表，user_sessions 和 attribution，属于一家在线零售公司。两个表的详细信息如下：

• attribution 表中的每一行都记录了一次会话访问。
• 如果该会话中的转化是合法的，则用户成为买家。
• 用户在特定会话期间被分配的广告平台在 channel 列中指示。
• 最后，user_sessions 表将一天内的一次或多次会话访问链接回特定用户。

用户首次访问网站时与之关联的渠道称为首次触达归因。绘制出用于转化用户的归因模型很有用。

第一次会话：用户看到 Facebook 广告 -> 点击订单 -> 离开

第二次会话：用户看到 Google 广告 -> 离开

第三次会话：用户输入网站 -> 点击订单 -> 购买

结论

SQL 问题在数据科学中至关重要，因为它们能够在关系数据库中提取、修改和分析数据。有效使用 SQL 有助于数据科学家管理大数据和查询，并能够提取相关信息。SQL 支持数据清洗、聚合和表连接，这些都是数据分析和机器学习活动中最基本和最重要的活动。因此，了解如何编写高效的 SQL 查询以执行数据操作可以优化操作性能。因此，SQL 对于数据科学家来说是一项至关重要的技能，因为它提供了探索数据的可能性，并且是数据驱动决策制定的关键因素。