实战项目必备的5个高级SQL技巧！

你是否对SQL感到好奇，但对深入研究犹豫不决？或者，也许你已经熟悉SQL的基础知识，但很难将其应用于现实生活中的项目。

我知道这种感觉。

当我第一次开始学习SQL时，我被大量的信息吓倒了。即使在今天，我仍然不断学习和探索新技术。

在这篇文章中，我想分享我在日常工作中最常用的五种高级SQL技术。通过掌握这些技术，你将能够完成近80%的生产级SQL查询，使你成为任何数据驱动项目的宝贵资产。如果你想了解更多关于SQL的相关内容，可以阅读以下这些文章：
高级SQL技巧与窍门：数据分析师必备！
帮我得到第一份数据分析师工作的9个SQL核心概念
只会Pandas？来学习这25种Pandas变SQL的方法，让你的数据分析更得心应手！
使用SQL总结A/B实验结果

我有意不包括一些其他常用的技术，例如事务。如果你担任的是分析角色而不是软件工程师角色，此方法列表将非常有用。

在整篇文章中，我假设我们在Postgres数据库中。但是现在每个主要的关系数据库都提供类似的功能。

1 窗口功能

窗口函数是一种分析函数，它对与当前行相关的一组行执行计算。窗口函数的结果与结果集中的原始行一起返回，而不更改基础数据。

一个现实生活中的窗口函数的例子可能是计算特定产品在一段时间内的销售收入总额。这对于识别销售趋势非常有用，例如哪些产品在一年中的某些时候最受欢迎。

下面是一个如何使用 SUM 窗口函数计算特定产品在一段时间内的销售收入总额的示例：

在此示例中，SUM 窗口函数用于计算特定product_id随时间推移的revenue总额。PARTITION BY子句按product_id对数据进行分组，ORDER BY子句按日期对数据进行排序。running_total列包含SUM窗口函数的结果。

为什么不使用分组依据？

当我第一次开始使用窗口函数时，这让我感到困惑。

是的，你可以使用GROUP BY在PostgreSQL中聚合数据。然而，使用GROUP BY将提供与窗口函数不同的结果。

在计算一段时间内特定产品销售收入的运行总额的示例中，使用 GROUP BY 将按product_id和日期对销售数据进行分组，然后计算每个组的收入总和。这将为你提供每天的总收入，而不是一段时间内的运行总收入。

下面是使用GROUP BY按product_id和日期聚合销售数据的示例：

了解有关SQL窗口函数的详细信息：https://mode.com/sql-tutorial/sql-window-functions/

2 CTE：公用表表达式

CTE是可以在单个SQL语句中引用的临时命名结果集。它定义了一个子查询，可以在一个巨大的查询中多次引用，从而简化了复杂的查询。

让我们举一个例子来更好地理解这一点。假设你有一个包含所有客户订单的表。你想按产品查找表现最佳的地区的销售额。

如果没有CTE，则必须编写涉及子查询、联接和聚合函数的复杂查询。这可能会使查询难以阅读和理解。但是，通过使用CTE，你可以简化查询并使其更具可读性。

下面是一个了解CTE有用性的经典示例。

在上面的查询中，我们使用两个CTE来计算表现最佳的区域中最畅销的产品。

第一个CTE（regional_sales）通过将订单表的金额列相加并按区域对结果进行分组来计算每个地区的总销售额。

第二个CTE（top_regions）仅选择总销售额大于所有区域总销售额10%的区域。这是使用子查询完成的，该子查询计算所有区域的总销售额并将其除以10。

然后，主查询使用IN子句将订单表与top_regions CTE联接，以筛选结果以仅包含来自表现最佳的区域的订单。

下面是不使用CTE的重写查询： 

CTE 可以简化复杂的查询并使其更具可读性。它使在较大的查询中多次重用同一子查询变得更加容易。

了解有关 CTE 的更多信息：https://learnsql.com/blog/what-is-common-table-expression/

3 递归查询

你是否曾经想过从数据存储在分层或树状结构中的数据库中检索数据？

例如，你可能有一个产品类别树，其中每个类别都有子类别，每个子类别可以有进一步的子类别。在这种情况下，递归查询可能很方便。

递归查询是在定义中引用自身的查询。在遍历数据库中的树或层次结构并检索所有相关数据时，它非常有用。换句话说，它使你能够从依赖于同一表中数据的表中选择数据。

下面是可用于遍历类别树的递归查询示例：

在此示例中，我们使用带有WITH子句的CTE来定义递归查询。递归关键字告诉Postgres这是一个递归查询。

category_tree CTE由两个SELECT语句定义。第一个SELECT语句选择类别树的根节点（没有父节点的节点），第二个SELECT语句以递归方式选择子节点。UNION ALL运算符组合了两个SELECT语句的结果。

depth列用于跟踪树中每个类别节点的深度。path列是存储从根到当前节点的路径的数组。

通过此查询，我们可以检索树中的所有类别及其各自的深度和路径。

了解有关递归查询的更多信息：https://learnsql.com/blog/sql-recursive-cte/

4 动态 SQL

如果你曾经使用过SQL查询，你可能会遇到一些非常复杂且需要在运行时生成的查询。编写此类查询可能令人生畏，执行它们可能更具挑战性。

过去，我曾经依靠Python来生成复杂的SQL查询，并使用诸如psycopg2之类的数据库连接器执行它们。这种方法很有效，但不是很优雅。

但是，我最近在Postgres中发现了动态SQL，使生成和执行复杂的查询更易于管理。使用动态SQL，你可以根据运行时条件动态创建查询，这在处理复杂的数据结构或业务逻辑时非常有用。

假设你要检索在特定日期下的所有订单。在静态查询中，你可以编写如下内容：

SELECT * FROM orders WHERE order_date = '2022-03-01';

但是，如果你允许用户选择日期范围，该怎么办？使用动态SQL，你可以根据用户输入生成查询，如下所示：

在此示例中，我们创建了一个函数，该函数采用两个参数（start_date和 end_date），并返回该日期范围内的订单表。EXECUTE语句允许我们基于输入参数动态生成查询，USING子句指定查询的值。

这个例子是基本的。但在大型项目中，你需要动态生成大量 SQL。

了解有关动态 SQL 的更多信息：https://www.postgresql.org/docs/current/ecpg-dynamic.html

5 游标

我们的查询可能在受限环境中运行。一次在大型表上运行密集型操作可能并不总是可取的。或者，我们可能需要对操作进行更多的控制，而不是将其应用于整个表。

这就是游标派上用场的地方。游标允许你一次检索和操作结果集中的一行数据。你可以使用游标循环访问数据集并对每一行执行复杂的操作。

假设你有一个名为“产品”的表，其中包含有关所有产品的信息，包括产品ID、产品名称和当前库存。你可以使用游标循环访问包含特定产品的所有订单并更新其库存。

在此示例中，我们首先声明一个名为“cur_orders”的游标，该游标选择包含特定产品ID的所有订单详细信息。然后，我们定义一个名为“product_inventory”的变量来存储产品的当前库存。

在循环中，我们从光标中获取每个订单ID、产品ID和数量，从当前库存中减去一定数值，然后使用新的库存值更新产品表。

最后，我们关闭游标并在更新清单后执行一些操作，例如记录更改。

结论

总之，SQL是一种强大的语言，它提供了许多处理复杂数据的技术。但是，一开始学习它们可能会吓到你。

这篇博文探讨了五种最常用的高级SQL技术，包括CTE、窗口函数、递归查询、动态查询和游标。虽然连接和子查询等基本SQL概念是处理数据的基础，但这些技术将帮助你处理几乎任何SQL项目。

虽然这篇文章概述了高级SQL技术，但它并不打算对每种技术进行详尽的讨论。但是，已经为那些希望进一步探讨这些概念的人提供了相关链接。将来，我计划单独深入研究这些技术中的每一种，以更全面地了解它们的功能和潜在应用。

感谢你的阅读，朋友！你还可以订阅我们的YouTube频道，观看大量大数据行业相关公开课：https://www.youtube.com/channel/UCa8NLpvi70mHVsW4J_x9OeQ；在LinkedIn上关注我们，扩展你的人际网络！https://www.linkedin.com/company/dataapplab/

原文作者：Thuwarakesh Murallie
翻译作者：马薏菲
美工编辑：过儿
校对审稿：Chuang
原文链接：https://towardsdatascience.com/5-advanced-sql-techniques-for-real-life-projects-f2db9b6680e2