数据科学面试中你应该知道的10个高级SQL概念

公众号新闻

2022-10-12 19:10

今日份知识你摄入了么？

随着数据量的不断增长，对专业数据人员的需求也在增长。具体来说，对精通SQL而非初学者水平的专业人员的需求日益增长。

因此，我和StrataScratch的创始人Nathan Rosidi回顾了我认为最重要的10个中高级SQL概念。

公用表表达式 (CTE)

如果你想要在查询中查询，这时候就可以使用CTE——CTE本质上是创建一个临时表。

使用公用表表达式(CTE)可以模块化代码和分解代码，就像你将一篇文章分解成几个段落一样。

考虑以下查询，其中包含where子句中的子查询。

SELECT name       ，salaryFROM PeopleWHERE name in (SELECT DISTINCT name               FROM population               WHERE country = "Canada"                      AND city = "Toronto")       AND Salary >= (SELECT AVG(salary)                      FROM photos                     where gender = "Female")

这似乎不难理解，但是如果子查询中有很多子查询或者子查询中嵌套子查询呢？这就是CTE发挥作用的地方。

with toronto_ppl as (   SELECT DISTINCT name   FROM population   WHERE country = "Canada"         AND city = "Toronto"), avg_female_salary as (   SELECT AVG(salary) as avgSalary   FROM salaries   WHERE gender = "Female")SELECT name       , salaryFROM PeopleWHERE name in (SELECT DISTINCT FROM toronto_ppl)      AND salary >= (SELECT avgSalary FROM avg_female_salary)

现在很明显，WHERE子句正在筛选在Toronto的姓名。CTE很有用，因为你可以将代码分解成更小的块，它们允许你为每个CTE分配一个变量名（即toronto_ppl 和avg_female_salary）

同样，CTE可以使用更高级的技术，例如创建递归表：

递归CTE

递归CTE是引用自身的CTE，就像Python中的递归函数一样。递归CTE在查询组织结构图、文件系统、网页之间的链接图等分层数据时特别有用。

递归CTE有3个部分：

定位成员（Anchor Member）：返回CTE基本结果的初始查询
递归成员（Recursive Member）：递归查询引用CTE.this与定位成员UNION ALL
停止递归成员的终止条件

下面是一个递归CTE示例，它获取每个员工ID的经理ID：

with org_structure as (   SELECT id          , manager_id   FROM staff_members   WHERE manager_id IS NULL   UNION ALL   SELECT sm.id          , sm.manager_id   FROM staff_members sm   INNER JOIN org_structure os      ON os.id = sm.manager_id

临时函数

如果你想了解更多关于临时函数的信息，请查看此内容，但如何编写临时函数很重要，原因如下：

它允许你将代码块分解为更小的代码块
这对于编写更简洁的代码很有用
它可以防止重复并允许代码重用，这类似于在Python中使用的函数。

考虑以下示例：

SELECT name       , CASE WHEN tenure < 1 THEN "analyst"              WHEN tenure BETWEEN 1 and 3 THEN "associate"              WHEN tenure BETWEEN 3 and 5 THEN "senior"              WHEN tenure > 5 THEN "vp"              ELSE "n/a"         END AS seniorityFROM employees

相反，你可以利用临时函数来捕获CASE子句。

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) AS (   CASE WHEN tenure < 1 THEN "analyst"        WHEN tenure BETWEEN 1 and 3 THEN "associate"        WHEN tenure BETWEEN 3 and 5 THEN "senior"        WHEN tenure > 5 THEN "vp"        ELSE "n/a"   END);SELECT name       , get_seniority(tenure) as seniorityFROM employees

有了临时函数，查询就简单多了，可读性更强，而且还可以重用seniority函数！

使用CASE WHEN透视数据

你可能会看到许多使用CASE WHEN语句的问题，因为它用途广泛。如果你想根据其他变量分配某个值或类，它允许你编写复杂的条件语句。

鲜为人知的是，它还允许你透视数据。例如，如果你有一个月份列，并且想为每个月创建一个单独的列，你可以使用CASE WHEN语句来透视数据。

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        |+------+-------------+-------------+-------------+-----+-----------+

示例问题：编写一个SQL查询来重新格式化表，以便每个月都有一个收入列。

EXCEPT与NOT IN

EXCEPT和NOT IN的操作几乎相同。它们都用于比较两个查询/表之间的行。虽然如此，你应该知道两者之间的差别。

首先，EXCEPT过滤掉重复项并返回不同于NOT IN的不同行。

其次，EXCEPT要求两个查询/表中的列数相同，其中NOT IN比较每个查询/表中的单个列。

Self Joins

SQL自连接将表与其自身连接。你可能会认为这没有任何意义，但这种情况很普遍。在许多现实生活中，数据存储在一个大表中，而不是许多较小的表中。在这种情况下，可能需要自连接来解决特殊的问题。

让我们看一个例子。

示例问题：根据下面的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 EmployeeFROM    Employee as a    JOIN Employee as b on a.ManagerID = b.IdWHERE a.Salary > b.Salary

如果你想尝试这样的练习题，请在此处查看StrataScratch ！

链接：https://platform.stratascratch.com/coding?code_type=1

Rank vs Dense Rank vs Row Number

对行和值进行排名是一个非常常见的应用。以下是一些公司经常使用排名的例子：

按购买次数、利润等对最有价值的客户进行排名。
按销售量排名前几位的产品
对销售额最高的国家进行排名
根据观看的分钟数、不同的观众的数量等对观看次数最多的视频进行排名。

在SQL中，你可以通过多种方式为行分配“排名”，我们将通过一个示例对其进行深入研究。考虑以下查询和结果：

SELECT Name       , GPA       , ROW_NUMBER() OVER (ORDER BY GPA desc)       , RANK() OVER (ORDER BY GPA desc)       , DENSE_RANK() OVER (ORDER BY GPA desc)FROM student_grades

ROW_NUMBER()为从1开始的每一行返回一个唯一编号。当排名相同时（例如 Bob vs Carrie），如果未定义第二个标准，ROW_NUMBER()将任意分配一个数字。

RANK()为从1开始的每一行返回一个唯一编号，除非排名不同，否则RANK()将分配相同的编号。同样，后面将跳过重复排名。

DENSE_RANK()与RANK()类似，只是在重复排名之后没有跳过。请注意，使用 DENSE_RANK()，Daniel排名第三，而使用RANK()时排名第四。

计算增量值

另一个常见的应用是比较不同时期的值。例如，本月和上月的销售额之间的差值是多少？或者这个月和去年这个月之间的增量是多少？

比较不同时期的值以计算增量时，就是用到LEAD()和LAG()的时候了。

这里有一些例子：

# Comparing each month's sales to last monthSELECT month       , sales       , sales - LAG(sales, 1) OVER (ORDER BY month)FROM monthly_sales# Comparing each month's sales to the same month last yearSELECT month       , sales       , sales - LAG(sales, 12) OVER (ORDER BY month)FROM monthly_sales

计算累加值

如果你知道ROW_NUMBER()和LAG()/LEAD()，这对你来说可能不会有太大的惊喜。但是，如果你不这样做，这可能是最有用的窗口函数之一，尤其是当你想要可视化增长时！

使用带有SUM()的窗口函数，我们可以计算累加值。请参见下面的示例：

SELECT Month       , Revenue       , SUM(Revenue) OVER (ORDER BY Month) AS CumulativeFROM monthly_revenue

日期时间操作

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

你应该知道的一些功能：

EXTRACT
DATEDIFF
DATE_ADD, DATE_SUB
DATE_TRUNC

示例问题：给定一个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.IdFROM    Weather a,    Weather bWHERE    a.Temperature > b.Temperature    AND DATEDIFF(a.RecordDate, b.RecordDate) = 1

谢谢阅读！

我希望这对你的面试准备有所帮助——我敢肯定，如果你彻底掌握了这10个概念，那么当涉及到大多数SQL问题时，你会做得很好。

原文作者：Terence Shin

翻译作者：明慧

美工编辑：过儿

校对审稿：过儿

原文链接：https://towardsdatascience.com/ten-advanced-sql-concepts-you-should-know-for-data-science-interviews-4d7015ec74b0

AI聘

找到工作再付费的 Career VIP求职项目是AI聘旗下面向广大北美数据类、软件开发类求职者的一站式求职解决方案，作为美国数据应用学院的独家求职服务合作伙伴，以及国际数据工程和科学协会的会员单位，已经成功帮助数百求职者找到了理想的工作岗位。

服务内容涵盖了全方位背景评估、求职方案设计、背景和能力提升、个人简历修改、全程面试辅导、岗位内推在内的求职整体环节。应用人工智能和大数据技术，精准进行岗位匹配和竞争力分析，让申请人在最短时间内完成短板补充，达到面试要求的能力水平，并通过内推等资源网络为申请人提供职场机会扩展，最终实现成功求职。

欢迎扫描下方二维码，添加“北美求职导师”微信，预约免费咨询。

扫码免费注册本周公开课

微信扫码关注该文公众号作者

戳这里提交新闻线索和高质量文章给我们。

来源: qq

点击查看作者最近其他文章