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
