通往T-SQL级别1的阶梯:使用交叉连接引入高级T-SQL

通往T-SQL级别1的阶梯:使用交叉连接引入高级T-SQL

格雷戈里·拉森，2016/02/19(第一次出版:2014/12/17)

该系列

这篇文章是楼梯系列的一部分:高级T-SQL的阶梯。

这个楼梯将包含一系列的文章，这些文章将扩展到您在前面的两个T-SQL楼梯、T-SQL DML和T-SQL的基础上学习的T-SQL基础上。

这个楼梯应该帮助读者准备通过微软认证考试70-461:查询微软SQL Server 2012。

这是将探索Transact SQL (TSQL)更高级特性的新楼梯系列的第一篇文章。

这个楼梯将包含一系列的文章，这些文章将扩展到您在前两个TSQL stairways中学习的TSQL基础上:

·通往T - SQL DML

·T - SQL:除了基础知识，这个“高级Transact SQL”楼梯将涵盖以下TSQL主题:

l ·使用交叉连接操作符

l ·使用应用操作符

l 将使用主数据的支持

l 使用透视将列进行

l 订购您的数据使用排序的功能

l 管理日期和时间函数

l 了解在条款的变化

这个阶梯的读者应该已经很好地理解了如何从SQL Server表中查询、更新、插入和删除数据。

此外，他们还应该掌握可以用来控制TSQL代码流的方法的工作知识，并且能够测试和操作数据。

这个楼梯应该帮助读者准备通过微软认证考试70-461:查询微软SQL Server 2012。

对于这个新的楼梯系列的第一部分，我将讨论交叉连接操作符。

交叉联接操作符介绍。

交叉连接操作符可以用来将一个数据集中的所有记录合并到另一个数据集中的所有记录中。

下面是一个使用交叉连接操作符来连接两个表a和B的简单示例:

从交叉连接B中选择*。

注意，当使用交叉连接操作符时，没有连接子句连接两个表，就像在两个表之间执行内部和外部连接操作时使用的连接子句。

您需要注意的是，使用交叉连接可以生成一个大型记录集。为了探究这种行为，让我们看看两个不同的示例，看看结果集的大小如何来自于交叉连接操作。

对于第一个示例，假设您是交叉连接两个表，其中表A有10行，表B有3行。

交叉连接的结果集将是10乘以3或30行。

对于第二个示例，假设表A有1,000万行，表B有300万行。

在表a和B之间的交叉连接结果中有多少行?

那将是一个巨大的30万亿的行。

这是很多行，需要很多时间和大量的资源来创建这个结果集，所以在大型记录集上使用交叉连接操作符时需要非常小心。

让我们通过几个例子来进一步了解使用交叉连接操作符。

清单 5: 两个等价的 SELECT 语句。

清单5中的代码包含两个 SELECT 语句。第一个 SELECT 语句使用交叉联接运算符, 然后使用 where 子句定义如何联接交叉联接操作中涉及的两个表。第二个 SELECT 语句使用具有 ON 子句的普通内部联接运算符来联接这两个表。SQL server 的查询优化器足够聪明, 能够知道清单5中的第一个 SELECT 语句可以作为内部联接重新编写。优化程序知道当交叉联接操作与 WHERE 子句一起使用时, 它可以重新编写查询, 在交叉联接所涉及的两个表之间提供联接谓词。因此, SQL server 引擎为清单5中的两个 SELECT 语句生成相同的执行计划。当您不提供 WHERE 约束时, SQL server 不知道如何联接涉及交叉联接操作的两个表, 以便在与交叉联接操作关联的两个集合之间创建一个笛卡尔乘积。

使用交叉联接查找未销售的产品

在前几节中找到的示例帮助您了解交叉联接运算符以及如何使用它。使用交叉联接运算符的一种能力是使用它来帮助查找一个表中没有其他表中的匹配记录的项目。例如, 假设我要报告每个日期, 我的 产品表中每一个产品的销售数量和总销售额。由于在我的示例中, 每个产品名称每天都没有销售, 所以我的报告要求意味着我需要显示0的数量和总销售额为0美元, 这些产品在某一天没有售出。这是交叉联接运算符与左外部联接操作结合在一起的地方, 将帮助我识别那些未在给定日期销售的项目。满足这些报告要求的代码可以在清单6中找到:

SELECT S1.SalesDate, ProductName

     , ISNULL(Sum(S2.Qty),0) AS TotalQty

       , ISNULL(SUM(S2.TotalSalesAmt),0) AS TotalSalesFROM Product PCROSS JOIN  (SELECT DISTINCT SalesDate FROM SalesItem

  ) S1LEFT OUTER JOIN 

SalesItem S2ON P.ID = S2.ProductIDAND S1.SalesDate = S2.SalesDate

GROUP BY S1.SalesDate, P.ProductNameORDER BY S1.SalesDate;

清单 6: 查找未使用交叉联接销售的产品

让我给你介绍一下这段代码。创建一个子查询以选择所有不同的SalesDate值。这个子查询给了我销售的所有日期。然后, 我将与产品表交叉联接。这允许我在每个SalesDate和每个产品行之间创建一个笛卡尔乘积。从交叉联接返回的集将在最终结果集中拥有我需要的所有值, 但每个售出的产品的数量和TotalSalesAmt的总和除外。要获取这些汇总值, 我将对SalesItem表执行左外部联接, 并将其与通过交叉联接操作创建的笛卡尔乘积进行连接。我基于ProductID和SalesDate列执行了此连接。通过使用左外部联接, 我的笛卡尔产品中的每一行都将返回, 如果ProductID和 SalesDate 有匹配的SalesDate记录, Qty和TotalSalesAmt值将与相应行关联。此查询所做的最后一件事是使用 GROUP by 子句汇总数量和TotalSalesAmount基于SalesDate和 "名称".

性能考虑

产生笛卡尔产品的交叉联接运算符具有一些性能方面需要考虑。因为 SQL 引擎需要将一组中的每一行与另一个集合中的每一行联接起来, 所以结果集可以相当大。如果我做一个交叉联接一个表, 其中有100万行, 另一个表有10万行, 那么我的结果集将有 100万 X 10万行, 或1000亿行。这是一个很大的结果集, 它将花费大量的时间来创建它。

交叉联接运算符可以是一个很好的解决方案, 用于在两个集合的所有可能组合中标识结果集, 就像每个月的所有客户的所有销售额一样, 即使在某些月中某些客户没有销售额。使用交叉联接运算符时, 如果要优化性能, 则应尽量减少交叉联接的集合的大小。例如, 假设我有一个表, 其中包含过去2月的销售数据。如果我要生成一个报表, 显示一个月内没有销售的客户, 那么标识一个月中的天数的方法可能会大大改变查询的性能。为了证明这一点, 让我首先为1000个客户创建一组销售记录, 为期两个月。我将使用清单7中的代码来执行此项。

CREATE TABLE Cust (Id int, CustName varchar(20));CREATE TABLE Sales (Id int identity

                    ,CustID int

                               ,SaleDate date

                               ,SalesAmt money);SET NOCOUNT ON;DECLARE @I int = 0;DECLARE @Date date;WHILE @I < 1000BEGIN     

      SET @I = @I + 1;

      SET @Date = DATEADD(mm, -2, '2014-11-01');

      INSERT INTO Cust

      VALUES (@I, 

              'Customer #' + right(cast(@I+100000 as varchar(6)),5));

      WHILE @Date < '2014-11-01' 

      BEGIN

            IF @I%7 > 0

                  INSERT INTO Sales (CustID, SaleDate, SalesAmt) 

                  VALUES (@I, @Date, 10.00);

            SET @Date = DATEADD(DD, 1, @Date);

      ENDEND

清单 7: TSQL 创建用于性能测试的示例数据 

清单7中的代码为1000个不同的客户创建了2月的数据。此代码不为每个第七客户添加销售数据。此代码生成1000个客户表记录和52338个 销售表记录。

要演示如何使用交叉联接运算符执行不同的操作, 具体取决于在交叉联接输入集中使用的集的大小, 让我运行清单8和清单9中的代码。对于每个测试, 我将记录返回结果所需的时间。

SELECT CONVERT(CHAR(6),S1.SaleDate,112) AS SalesMonth, C.CustName, 

       ISNULL(SUM(S2.SalesAmt),0) AS TotalSalesFROM Cust CCROSS JOIN  (SELECT SaleDate FROM Sales ) AS S1LEFT OUTER JOIN 

Sales  S2ON C.ID = S2.CustIDAND S1.SaleDate = S2.SaleDate

GROUP BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustNameHAVING ISNULL(SUM(S2.SalesAmt),0) = 0ORDER BY CONVERT(CHAR(6),S1.SaleDate,112),C.CustName

 

清单9：针对销售日期的不同列表交叉连接

在清单8中，交叉连接操作符将1000条 Cust 记录与52338条销售记录连接起来，生成52333000行的记录集，然后用于确定一个月内零销售额的客户。在清单9中，我将选择条件从Sales表更改为只返回一组不同的SalesDate值。这个不同的集合只产生61个不同的SalesDate值，因此清单9中的交叉连接操作的结果只生成61，000条记录。通过减少交叉连接操作的结果集，清单9中的查询在1秒内运行，而清单8中的代码在我的机器上运行时间为19秒。造成这种性能差异的主要原因是SQL Server需要为每个查询执行的不同操作处理大量记录。如果查看从嵌套循环(内连接)操作生成的估计记录数量，在图形计划的右侧，您将看到清单8估计了52，338，000条记录，而清单9中的相同操作仅估计了61，000条记录。清单8的查询计划从交叉连接嵌套循环操作生成的大型记录集然后传递到几个附加操作。因为清单8中的所有这些操作都必须针对5200万条记录工作。清单8比清单9慢得多。

如您所见，在交叉连接操作中使用的记录数量可能会极大地影响查询运行的时间长度。因此，如果您可以编写查询以最小化交叉连接操作中涉及的记录数量，则查询的执行效率将大大提高。

结语

交叉连接运算符在两个记录集之间产生一个笛卡尔积。此操作符有助于识别一个表中没有匹配记录的项。应注意尽量减少与交叉连接操作符一起使用的记录集的大小。通过确保交叉连接的结果集尽可能小，您将确保代码运行得越快。

问题与答案

在本节中，您可以通过回答以下问题来回顾如何使用交叉联接运算符来理解。

问题1：

交叉联接运算符根据ON子句中指定的列，通过匹配两个记录集来创建结果集。(对还是错)？

真

假

问题2：

当表A和表B包含重复行时，哪一个公式可以用来标识从两个表A和B之间的无约束交叉连接返回的行数？

表A中的行数乘以表B中的行数

表A中的行数乘以表B中的唯一行数

表A中的唯一行数乘以表B中的行数

表A中唯一行数乘以表B中唯一行数

问题3：

哪种方法提供了减少交叉连接操作产生的笛卡尔产品的最佳机会？

确保连接的两个集合尽可能多行。

确保连接的两个集合尽可能少行。

确保交叉连接操作左边的设置尽可能少行。

确保交叉连接操作右侧的设置尽可能少行。

回答：

问题1：

正确的答案是b。交叉连接运算符不使用ON子句来执行交叉连接操作。它将一个表中的每一行连接到另一个表中的每一行。当交叉连接去连接两个集合时，它创建了一个笛卡尔积。

问题2：

正确的答案是a、b、c和d，因为如果表A或B中有重复行，则在为交叉联接操作创建笛卡尔积时，每个重复行都是联接。

问题3：

正确的答案是b。通过减少交叉连接操作中涉及的两个集合的大小，使由交叉连接操作创建的最终集的大小最小化。c和d还有助于减小交叉连接操作创建的最终集的大小，但并不像确保涉及到交叉连接操作的两个集合的行数最少一样优化。