SQL 解释：常见表表达式

原文towardsdatascience.com/sql-explained-common-table-expressions-fc23e4675890在 SQL 中常见的表表达式或称为 CTE即它们所知是临时的、命名的结果集包含从另一个 SQL 查询中派生的中间数据。一旦你在 CTE 中有数据你可以在同一个查询中一次或多次引用这些数据。根据上述描述你可能会认为 CTE 听起来很像 SQL 中的常规临时表在某些方面确实如此。那么为什么你会选择使用 CTE 而不是临时表呢为了回答这个问题我们需要看看临时表的两个主要缺点。其中一个是临时表可以导致代码更加复杂尤其是在它们被用于大型 SQL 脚本的不同部分时。它们需要显式创建、删除并且可能还需要在它们上面建立索引这会增加你的 SQL 和会话管理的开销。第二临时表会消耗物理存储空间这可能是一个考虑因素如果你空间紧张并且有很多临时表。此外当查看使用临时表的查询时可能不清楚临时表中包含哪些数据以及数据来源。CTE 没有上述问题。首先它们是瞬时的这意味着一旦 SQL 会话结束CTE 就超出作用域它使用的任何内存都会被释放。第二你可以确切地看到 CTE 中包含哪些数据。它的创建和填充就在你的 SQL 脚本中。现在除了它们的优点外CTE 确实有一些缺点并且可能不应该在以下情况下使用。你必须在查询中多次或两次以上引用 CTE 中的数据。这是因为每次引用 CTE 时它都必须重新填充。然而如果你的 CTE 数据量很小甚至这个需求也不一定是使用 CTE 的障碍。与上述内容相关CTE 不能被索引因此如果数据量很大其性能可能不如索引过的临时表。因此总结一下在以下场景中使用 CTE公用表表达式。你想要避免使用临时表CTE 的数据量是相对较低的你在查询中只引用一次可能根据数据量是两次CTE 数据CTE 的最后一个优点我还没有提到那就是许多现代 SQL 方言实现了递归 CTE 的能力即引用自己的 CTE。自然地这意味着编写递归和基于层次结构的 SQL 查询要容易得多。我们稍后会看到一些示例。现在我们已经更全面地了解了 CTE 是什么以及它们的作用是时候看看它们的一些使用示例了。注意以下展示的某些包含日期和日期算术操作的 SQL 操作可能不适用于所有数据库 SQL 方言。如果你使用的是 Oracle 以外的数据库请检查你的 SQL 文档。设置测试环境。我使用 Oracle 的 live SQL 网站来运行我的测试。我在之前写的关于在 SQL 中使用分组集、汇总和立方的文章中解释了如何获取并使用这项服务。设置和使用都是完全免费的。相关文章的链接如下。SQL 解释分组集、汇总和立方示例表创建与填充对于非递归的 CTE 示例我们将使用客户交易表。以下是创建表和数据插入语句用于重新创建输入表和数据。CREATE TABLE transactions(TransactionID INT PRIMARY KEY,CustomerID INT,Amount DECIMAL(10,2),TransactionDate DATE);INSERT INTO Transactions(TransactionID,CustomerID,Amount,TransactionDate)VALUES(1,1001,150,TO_DATE(2021-01-01,YYYY-MM-DD));INSERT INTO Transactions(TransactionID,CustomerID,Amount,TransactionDate)VALUES(2,1002,200,TO_DATE(2021-01-04,YYYY-MM-DD));INSERT INTO Transactions(TransactionID,CustomerID,Amount,TransactionDate)VALUES(3,1001,100,TO_DATE(2021-01-04,YYYY-MM-DD));INSERT INTO Transactions(TransactionID,CustomerID,Amount,TransactionDate)VALUES(4,1003,250,TO_DATE(2021-01-05,YYYY-MM-DD));INSERT INTO Transactions(TransactionID,CustomerID,Amount,TransactionDate)VALUES(5,1002,300,TO_DATE(2021-01-05,YYYY-MM-DD));INSERT INTO Transactions(TransactionID,CustomerID,Amount,TransactionDate)VALUES(6,1003,180,TO_DATE(2021-01-08,YYYY-MM-DD));INSERT INTO Transactions(TransactionID,CustomerID,Amount,TransactionDate)VALUES(7,1001,190,TO_DATE(2021-01-08,YYYY-MM-DD));SELECT*FROM Transactions TRANSACTIONID CUSTOMERID AMOUNT TRANSACTIONDATE1100115001-JAN-212100220004-JAN-213100110004-JAN-214100325005-JAN-215100230005-JAN-216100318008-JAN-217100119008-JAN-21标准 CTE 的语法出奇地简单只需这样WITH cte_name[(column_list)]AS(cte_query_definition)statement其中cte_name是给 CTE 取的名字column_list是 CTE 的可选列名列表cte_query_definition是定义 CTE 结果集的查询statement是引用 CTE 的单个SELECT、INSERT、UPDATE、DELETE或MERGE语句测试 1 – 简单的 CTE检索总消费超过$250 的所有客户WITH CustomerTotals AS(SELECT CustomerID,SUM(Amount)AS TotalSpent FROM Transactions GROUP BY CustomerID)SELECT CustomerID,TotalSpent FROM CustomerTotals WHERE TotalSpent250;CUSTOMERID TOTALSPENT100144010025001003430在这种情况下此查询的非 CTE 版本也很简单。只需这样SELECT CustomerID,SUM(Amount)AS TotalSpent FROM Transactions GROUP BY CustomerID HAVING SUM(Amount)250;测试 2 – 更复杂的 CTE其中 CTE 发挥其优势的情况是如果你有一个类似的查询。检索平均消费大于所有客户平均消费的客户使用 CTE这样的查询变得相对简单。我们需要的两个聚合值可以自然地拆分为两个单独的 CTE然后我们只需比较两个的结果来确定要检索的内容。WITH CustomerAverages AS(SELECT CustomerID,AVG(Amount)AS AvgAmount FROM Transactions GROUP BY CustomerID),OverallAverage AS(SELECT AVG(Amount)AS OverallAvg FROM Transactions)SELECT a.CustomerID,a.AvgAmount FROM CustomerAverages a,OverallAverage o WHERE a.AvgAmounto.OverallAvg;CUSTOMERID AVGAMOUNT10022501003215与非 CTE 版本比较SELECT CustomerID,AVG(Amount)AS AvgAmount FROM transactions GROUP BY CustomerID HAVING AVG(Amount)(SELECT AVG(sub.AvgAmount)FROM(SELECT AVG(Amount)AS AvgAmount FROM transactions GROUP BY CustomerID)sub);在我看来使用 CTE 使得发生的事情和 SQL 作者的意图更加明显。测试 3 – 递归 CTE递归 CTE 是查询中一个 CTE 引用自己的查询。敏锐的读者可能已经注意到我们可以使用递归 CTE 来编写上面的 CTE 示例。WITH CustomerAverages AS(SELECT CustomerID,AVG(Amount)AS AvgAmount FROM Transactions GROUP BY CustomerID),OverallAverage AS(SELECT AVG(AvgAmount)AS OverallAvg FROM CustomerAverages)SELECT a.CustomerID,a.AvgAmount FROM CustomerAverages a,OverallAverage o WHERE a.AvgAmounto.OverallAvg;由于我们需要的第二 CTEOverallAverage的所有信息已经在第一个 CTECustomerAverages中包含因此我们可以使用第一个 CTE 中的数据来计算第二个 CTE。注意在大多数 SQL 方言中你通过在with子句和 CTE 名称之间放置保留关键字recursive来指定你正在使用递归 CTE如下所示withrecursive cte(col1,col2 etc...)asmy_cte......Oracle 假设一个 CTE 在其查询中引用自己时是递归的所以我们不需要特殊的recursive关键字。对于我们的最后一个示例我们将使用递归 CTE 来填补原始时间序列数据中的空白。回顾我们的原始表数据你会注意到我们缺少 1 月 2 日和 3 日以及 1 月 6 日和 7 日的客户数据条目。我们的任务是生成一个报告显示每天客户的消费总额。如果没有日期的条目我们返回该日期客户消费总额为零。这是一个递归 CTE 的良好用例。WITH DateRange(dt)AS(SELECT MIN(TRANSACTIONDATE)FROM Transactions UNION ALL SELECT dtINTERVAL1DAY FROM DateRange WHERE dt(SELECT MAX(TRANSACTIONDATE)FROM Transactions)),AggregatedData AS(SELECT TRANSACTIONDATE,SUM(AMOUNT)AS TOTAL_SPEND FROM Transactions GROUP BY TRANSACTIONDATE)SELECT dr.dt AS TRANSACTIONDATE,NVL(ad.TOTAL_AMOUNT,0)AS TOTAL_SPEND FROM DateRange dr LEFT JOIN AggregatedData ad ON dr.dtad.TRANSACTIONDATE ORDER BY dr.dt;TRANSACTIONDATE TOTAL_SPEND01-JAN-2115002-JAN-21003-JAN-21004-JAN-2130005-JAN-2155006-JAN-21007-JAN-21008-JAN-21370日期范围 CTE这部分创建了一个从最早到最新的交易日期的连续日期范围确保没有日期间隔。聚合数据 CTE对每个日期的交易进行求和。最终 SELECT将日期范围与聚合的交易数据连接起来使用NVL函数将NULL值替换为 0确保没有交易的日子显示总金额为 0。摘要总之公共表表达式CTEs是 SQL 中的一种灵活且强大的功能可以增强复杂查询的可读性、可维护性和执行效率。通过这篇文章我探讨了 CTEs 的基本结构和功能探讨了非递归和递归类型以展示它们在各种场景中的实用性。我包含的三个测试案例不仅展示了如何通过将复杂的 SQL 操作分解成更易于管理的部分来简化 CTEs 的管理而且还展示了它们在处理递归数据方面的优势。我还会争辩说使用 CTEs 可以使 SQL 脚本更加清晰和有组织使开发人员和分析师更容易编写、调试和优化他们的数据库查询。如果你仍然对 CTEs 感到不确定想想在没有使用它们的情况下如何解决我的测试 3 示例中的问题。我认为你会发现像这样的问题使用传统的 SQL 方法会更加繁琐且效率较低。_ 好吧就到这里吧。我希望你觉得这篇文章有用。如果你觉得有用请点击这个链接查看我的个人资料页面。从那里你可以看到我其他发表的故事并订阅以获取我发布新内容的通知 _。我知道现在经济困难钱包紧张但如果这篇文章给你带来了真正的价值请考虑*买给我一小杯威士忌。如果你喜欢这篇文章我相信你还会对以下文章感兴趣。SQL 解释分组集、汇总和立方Gradio超越界面