数据科学面试中你应该知道的10个高级SQL概念
随着数据量的不断增长,对专业数据人员的需求也在增长。具体来说,对精通SQL而非初学者水平的专业人员的需求日益增长。
在本文中,我和StrataScratch的创始人Nathan Rosidi回顾了我们认为最重要的10个高级SQL概念。如果你想了解更多关于SQL的相关内容,可以阅读以下这些文章:
SQL & NoSQL,掌握这7点就够了
SQL数据清理及准备 – 看这一篇就够了
用 SQL和Tableau做客户流失分析
2022年你需要知道的5个高级SQL技巧
1 公用表表达式 (CTE)
如果你想要在查询中查询,这时候就可以使用CTE——CTE本质上是创建一个临时表。
使用公用表表达式(CTE)可以模块化代码和分解代码,就像你将一篇文章分解成几个段落一样。
考虑以下查询,其中包含where子句中的子查询。
SELECT name
,salary
FROM People
WHERE 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
, salary
FROM People
WHERE 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可以使用更高级的技术,例如创建递归表:
2 递归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
3 临时函数
如果你想了解更多关于临时函数的信息,请查看此内容,但如何编写临时函数很重要,原因如下:
- 它允许你将代码块分解为更小的代码块
- 这对于编写更简洁的代码很有用
- 它可以防止重复并允许代码重用,这类似于在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 seniority
FROM 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 seniority
FROM employees
有了临时函数,查询就简单多了,可读性更强,而且还可以重用seniority函数!
4 使用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查询来重新格式化表,以便每个月都有一个收入列。
5 EXCEPT与NOT IN
EXCEPT和NOT IN的操作几乎相同。它们都用于比较两个查询/表之间的行。虽然如此,你应该知道两者之间的差别。
首先,EXCEPT过滤掉重复项并返回不同于NOT IN的不同行。
其次,EXCEPT要求两个查询/表中的列数相同,其中NOT IN比较每个查询/表中的单个列。
6 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 Employee
FROM
Employee as a
JOIN Employee as b on a.ManagerID = b.Id
WHERE a.Salary > b.Salary
如果你想尝试这样的练习题,请在此处查看StrataScratch !
链接:https://platform.stratascratch.com/coding?code_type=1
7 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()时排名第四。
8 计算增量值
另一个常见的应用是比较不同时期的值。例如,本月和上月的销售额之间的差值是多少?或者这个月和去年这个月之间的增量是多少?
比较不同时期的值以计算增量时,就是用到LEAD()和LAG()的时候了。
这里有一些例子:
# Comparing each month's sales to last month
SELECT month
, sales
, sales - LAG(sales, 1) OVER (ORDER BY month)
FROM monthly_sales# Comparing each month's sales to the same month last year
SELECT month
, sales
, sales - LAG(sales, 12) OVER (ORDER BY month)
FROM monthly_sales
9 计算累加值
如果你知道ROW_NUMBER()和LAG()/LEAD(),这对你来说可能不会有太大的惊喜。但是,如果你不这样做,这可能是最有用的窗口函数之一,尤其是当你想要可视化增长时!
使用带有SUM()的窗口函数,我们可以计算累加值。请参见下面的示例:
SELECT Month
, Revenue
, SUM(Revenue) OVER (ORDER BY Month) AS Cumulative
FROM monthly_revenue
10 日期时间操作
你可能会遇到一些涉及日期时间数据的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.Id
FROM
Weather a,
Weather b
WHERE
a.Temperature > b.Temperature
AND DATEDIFF(a.RecordDate, b.RecordDate) = 1
谢谢阅读!
我希望这对你的面试准备有所帮助——我敢肯定,如果你彻底掌握了这10个概念,那么当涉及到大多数SQL问题时,你会做得很好。你还可以订阅我们的YouTube频道,观看大量大数据行业相关公开课:https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ;在LinkedIn上关注我们,扩展你的人际网络!https://www.linkedin.com/company/dataapplab/
原文作者:Terence Shin
翻译作者:明慧
美工编辑:过儿
校对审稿:过儿
原文链接:https://towardsdatascience.com/ten-advanced-sql-concepts-you-should-know-for-data-science-interviews-4d7015ec74b0