
2022年你需要知道的5个高级SQL技巧
立即掌握这些省时的高级SQL查询,成为 SQL 专家!这篇文章很短,你可以快速完成并掌握必知的SQL技巧。如果你想了解更多关于数据科学的相关内容,可以阅读以下这些文章:
2022年,数据科学家文凭能让你赚多少钱?
谷歌数据科学家面试真题
基于云技术的数据仓库给数据科学带来的优势
职场转型与进阶:多年非Data相关工作经验,如何转行数据科学家?
随着数据量的不断增加,数据专业人员的需求也在增加。只了解这些高级SQL概念是不够的,你还应该能够在工作中高效地实现它们,这是数据科学岗位面试中必需的!

因此,我在这里列出了5个高级SQL概念以及2022年你应该知道的查询示例。
这篇文章很短,你可以快速完成并掌握必知的SQL技巧。
目录
- Common Table Expressions (CTEs)
- ROW_NUMBER() vs RANK() vs DENSE_RANK()
- CASE WHEN Statement
- Extract Data From Date — Time Columns
- SELF JOIN
注意:我使用的是SQLite DB浏览器和在Faker上自行创建的Dummy_Sales_Data,你可以在我的Github存储库上免费获得它!
Common Table Expressions (CTE)
在处理现实生活中的数据时,有时需要查询另一个查询的结果。实现此目的的一种简单方法是使用子查询。
然而,随着复杂性的增加,计算子查询变得难以读取和调试。
这时,CTE应运而生。它使编写和维护复杂查询更容易。
例如,考虑使用子查询进行数据提取:
SELECT Sales_Manager, Product_Category, UnitPrice FROM Dummy_Sales_Data_v1 WHERE Sales_Manager IN (SELECT DISTINCT Sales_Manager FROM Dummy_Sales_Data_v1 WHERE Shipping_Address = 'Germany' AND UnitPrice > 150) AND Product_Category IN (SELECT DISTINCT Product_Category FROM Dummy_Sales_Data_v1 WHERE Product_Category = 'Healthcare' AND UnitPrice > 150) ORDER BY UnitPrice DESC
在这里,只使用了两个子查询。
这仍然很难理解,当你在子查询中添加更多计算,甚至再添加几个子查询时,情况会怎样?复杂性增加使代码可读性降低,并且难以维护。
现在,让我们看一下上述子查询使用CTE的简化版本,如下所示。
WITH SM AS ( SELECT DISTINCT Sales_Manager FROM Dummy_Sales_Data_v1 WHERE Shipping_Address = 'Germany' AND UnitPrice > 150 ), PC AS ( SELECT DISTINCT Product_Category FROM Dummy_Sales_Data_v1 WHERE Product_Category = 'Healthcare' AND UnitPrice > 150 ) SELECT Sales_Manager, Product_Category, UnitPrice FROM Dummy_Sales_Data_v1 WHERE Product_Category IN (SELECT Product_Category FROM PC) AND Sales_Manager IN (SELECT Sales_Manager FROM SM) ORDER BY UnitPrice DESC
将复杂的子查询分解成更简单的代码块。
通过这种方式,将复杂的子查询重新写入两个更易于理解和修改的CTEs SM和PC。
上述两个查询都需要相同的时间来执行,结果与下面的输出相同。

CTE 本质上允许你根据查询结果创建临时表。这提高了代码的可读性和维护性。
现实世界的数据集可能有数百万或数十亿行,占用数千GB存储空间。使用表中的数据进行计算,尤其是直接将它们与其他表连接起来,成本相当高。
此类任务的最终解决方案是使用CTE。
接下来,让我们看看如何使用窗口函数为数据集中的每一行分配一个整数“rank”。
ROW_NUMBER() vs RANK() vs DENSE_RANK()
处理真实数据集时另一个常用的概念是排名。可以在不同的场景中使用它,例如:
- 按销售量排名最畅销品牌
- 按订单数量或收益对产品进行排名
- 获取观看次数最多的每种类型的电影名称
ROW_NUMBER,RANK()和DENSE_RANK()本质上用于将排名分配给结果集所述分区内的每个记录。
当你在某些记录上有关联时,它们之间的差异是显而易见的。
当结果表中存在重复行时,将排名分配给每条记录的行为和方式会发生变化。
让我们看一个Dummy Sales Dataset的示例,按运输成本降序列出所有产品类别和送货地址。
SELECT Product_Category, Shipping_Address, Shipping_Cost, ROW_NUMBER() OVER (PARTITION BY Product_Category, Shipping_Address ORDER BY Shipping_Cost DESC) as RowNumber, RANK() OVER (PARTITION BY Product_Category, Shipping_Address ORDER BY Shipping_Cost DESC) as RankValues, DENSE_RANK() OVER (PARTITION BY Product_Category, Shipping_Address ORDER BY Shipping_Cost DESC) as DenseRankValues FROM Dummy_Sales_Data_v1 WHERE Product_Category IS NOT NULL AND Shipping_Address IN ('Germany','India') AND Status IN ('Delivered')
如你所见,这三种语法都是相同的,但会产生不同的输出,如下所示:

RANK()是根据ORDER BY子句的条件检索已排序的行。如你所见,前两行之间存在联系,即在Shipping_Cost列中具有相同的值(在ORDER BY子句中提到)。
RANK为两行数据分配相同排名。但它将重复行数添加到重复排名,以获得下一行的排名。这就是为什么第三行(红色标记)RANK排名3(2重复行 + 1重复排名)
DENSE_RANK和RANK类似,但即使行之间存在联系,它也不会跳过任何数字。这可以在上图的蓝色框中看到。
与上述两个不同,ROW_NUMBER是将序号分配给分区中从1开始的每个记录。如果它在同一分区中检测到两个相同的值,则为两者分配不同的排名。
对于产品类别的下一个分区- shipping address →Entertainment – India,所有三个排名都从1开始,如下所示。

最终,如果ORDER BY子句使用的列中没有重复值,这些函数将返回相同的输出。
下一个概念将详细介绍如何使用条件语句实现条件语句和数据透视。
CASE WHEN语句
CASE语句将允许你在SQL中实现if-else,因此你可以使用它有条件地运行查询。
CASE语句本质上测试WHEN子句中提到的条件,并返回THEN子句中提到的值。当不满足任何条件时,它将返回ELSE子句中提到的值。
在处理真实数据项目时,CASE语句通常根据其他列中的值对数据进行分类。它也可以与聚合函数一起使用。
例如,让我们再次使用Dummy Sales Data,根据数量将销售订单分为高、中、低三类。
SELECT OrderID, OrderDate, Sales_Manager, Quantity, CASE WHEN Quantity > 51 THEN 'High' WHEN Quantity < 51 THEN 'Low' ELSE 'Medium' END AS OrderVolume FROM Dummy_Sales_Data_v1

简单地说,它创建了一个新列OrderVolume并根据Quantity列中的值添加了“High”、“Low”、“Medium”等值。
你可以包含多个WHEN..THEN子句并跳过 ELSE 子句,因为它是可选的。
如果你没有提到ELSE子句并且没有满足任何条件,则查询将返回NULL。
CASE语句的另一个常用但鲜为人知的用例是——数据透视。
数据透视是重新排列结果集中列和行的过程,以便可以从不同的角度查看数据。
有时你正在处理的数据是长格式(行数>列数),你需要以宽格式(列数>行数)获取它。
CASE语句在这种情况下会很方便。
例如,让我们找出每个销售经理为Singapore、UK、Kenya和India处理了多少订单。
SELECT Sales_Manager, COUNT(CASE WHEN Shipping_Address = 'Singapore' THEN OrderID END) AS Singapore_Orders, COUNT(CASE WHEN Shipping_Address = 'UK' THEN OrderID END) AS UK_Orders, COUNT(CASE WHEN Shipping_Address = 'Kenya' THEN OrderID END) AS Kenya_Orders, COUNT(CASE WHEN Shipping_Address = 'India' THEN OrderID END) AS India_Orders FROM Dummy_Sales_Data_v1 GROUP BY Sales_Manager
使用 CASE..WHEN..THEN为每个运输地址创建单独的列,以获得预期的输出,如下所示。

根据你的用例,你还可以使用不同的聚合函数,例如SUM、AVG、MAX、MIN 和CASE语句。
其次,在处理真实数据时,它通常包含日期时间值。因此,了解如何提取日期时间值的不同部分很重要,例如月、周、年。
Extract Data From Date — Time Columns
在大多数面试中,你将会被要求按月汇总数据或计算特定月份的某些指标。
当数据集中没有单独的月份列时,需要从数据的日期时间变量中提取所需的部分。
不同的SQL环境具有不同的函数来提取日期的一部分。一般来说,在MySQL中你应该知道——
EXTRACT(part_of_date FROM date_time_column_name) YEAR(date_time_column_name) MONTH(date_time_column_name) MONTHNAME(date_time_column_name) DATE_FORMAT(date_time_column_name)
例如,让我们从销售数据集中找出每个月的总订单量。
SELECT strftime('%m', OrderDate) as Month, SUM(Quantity) as Total_Quantity from Dummy_Sales_Data_v1 GROUP BY strftime('%m', OrderDate)
如果你也像我一样使用SQLite DB Browser,则必须使用strftime()函数来提取日期部分,如下所示。你需要在instrftime()中使用“%m”提取月份。

作者提供的图像
下面的图片显示了常用的提取日期部分,以及你应该在EXTRACT函数中使用的关键字。

作者提供的图像
我在这篇文章中解释了几乎所有类型的日期部分提取。
https://python.plainenglish.io/5-must-know-sql-functions-for-data-analysis-1f1a6ca53974
最后值得一提的是,你会在现实世界中经常看到,数据存储在一个大表中,而不是多个小表中。这时,SELF JOIN就可以处理这些数据集,解决一些有趣的问题。
SELF JOIN
这些连接与SQL中的其他连接完全相同,唯一的区别是SELF JOIN将表与自身连接。
请记住,没有SELF JOIN关键字,因此你只需使用JOIN,其中连接中涉及的两个表是同一个表。由于两个表名相同,因此在SELF JOIN的情况下必须使用表别名。
编写一个SQL查询,找出收入超过经理的员工——这是SELF JOIN最常见的面试问题之一。
以此为例,创建一个如下所示的Dummy_Employees数据集。

并尝试使用此查询找出哪些员工处理的订单比他们的经理多。
SELECT t1.EmployeeName, t1.TotalOrders FROM Dummy_Employees AS t1 JOIN Dummy_Employees AS t2 ON t1.ManagerID = t2.EmployeeID WHERE t1.TotalOrders > t2.TotalOrders

正如预期的那样,员工Abdul和Maria比他们的经理Pablo处理的订单多。
在几乎80%的面试中都会遇到这个问题。
我在过去3年里一直在使用SQL,我发现这些经常作为数据分析师、数据科学家职位的面试问题,在实际项目中也非常有用。
感谢阅读!你还可以订阅我们的YouTube频道,观看大量大数据行业相关公开课:https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ;在LinkedIn上关注我们,扩展你的人际网络!https://www.linkedin.com/company/dataapplab/
原文作者:Suraj Gurav
翻译作者:明慧
美工编辑:过儿
校对审稿:Miya
原文链接:https://towardsdatascience.com/5-advanced-sql-concepts-you-should-know-in-2022-b50efe6c99