高级T-SQL级别1的Stairway:使用CROSS JOIN引入高级T-SQL分析

高级t-sql级别1的stairway:使用cross join引入高级t-sql。

这是将探索transactsql (tsql)更高级特性的新stairway系列的第一篇文章。这个stairway将包含一系列的文章，这些文章将扩展到您在前两个tsql stairways中学习的tsql基础上:

t-sql dml的stairway

t-sql的stairway:超越基础。

这个“高级transactsql”stairway将涵盖以下tsql主题:

creoss join的使用

apply 的使用

理解公共表表达式(cte)

使用transact-sql游标记录级别处理。

使用pivot将数据转向其一侧。

使用unpivot将列变为行。

使用排序函数排序数据。

使用函数管理日期和时间。

了解以over的变化。

这个stairway的读者应该已经很好地理解了如何从sql server表中查询、更新、插入和删除数据。此外，他们还应该掌握可以用来控制tsql代码流的方法的工作知识，并且能够测试和操作数据。

这个stairway应该帮助读者准备通过微软认证考试70-461:查询微软sql server 2012。

对于这个新的stairway系列的第一部分，我将讨论crossjoin操作符。

cross join操作符介绍。

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

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

select * from a cross join b

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

您需要注意的是，使用crossjoin可以生成一个大型记录集。为了探究这种行为，让我们看看两个不同的示例，看看结果集的大小如何来自于cross join操作。对于第一个示例，假设您是cross join两个表，其中表a有10行，表b有3行。cross join的结果集将是10乘以3或30行。对于第二个示例，假设表a有1,000万行，表b有300万行。在表a和b之间的cross join结果中有多少行那将是一个巨大的30万亿的行。这是很多行，需要很多时间和大量的资源来创建这个结果集，所以在大型记录集上使用cross join操作符时需要非常小心。

让我们通过几个例子来进一步了解使用cross join操作符。

使用crossjoin的基本示例。

在前面的几个例子中，我们将连接两个示例表。清单1中的代码将用于创建这两个示例表。确保在用户数据中运行这些脚本，而不是在master中运行。

create table product (id int,

productname varchar(100),

costmoney);

create table salesitem (id int,

salesdate datetime,

productid int,

qtyint,

totalsalesamt money);

insert into product

values(1,'widget',21.99),

(2,'thingamajig',5.38),

(3,'watchamacallit',1.96);

insert into salesitem

values(1,'2014-10-1',1,1,21.99),

(2,'2014-10-2',3,1,1.96),

(3,'2014-10-3',3,10,19.60),

(4,'2014-10-3',1,2,43.98),

(5,'2014-10-3',1,2,43.98);

清单1:cross join的示例表。

对于第一个crossjion示例，我将运行清单2中的代码。

select * from

product cross join salesitem;

清单2:简单cross join示例。

当我在sql servermanagement studio窗口中运行清单2中的代码时，使用会话设置来输出结果的文本，我得到了报告1中的输出:

id productname cost idsalesdate productidqty totalsalesamt

--- --------------------- -------- ---- -------------------------------- ---- ---------------

1 widget 21.99 12014-10-01 00:00:00.000 11 21.99

1 widget 21.99 22014-10-02 00:00:00.000 31 1.96

1 widget 21.99 32014-10-03 00:00:00.000 310 19.60

1 widget 21.99 42014-10-03 00:00:00.000 12 43.98

1 widget 21.99 52014-10-03 00:00:00.000 12 43.98

2 thingamajig 5.381 2014-10-01 00:00:00.000 1 121.99

2 thingamajig 5.38 22014-10-02 00:00:00.000 31 1.96

2 thingamajig 5.38 32014-10-03 00:00:00.000 310 19.60

2 thingamajig 5.38 42014-10-03 00:00:00.000 12 43.98

2 thingamajig 5.38 52014-10-03 00:00:00.000 12 43.98

3 watchamacallit 1.961 2014-10-01 00:00:00.0001 1 21.99

3 watchamacallit 1.962 2014-10-02 00:00:00.0003 1 1.96

3 watchamacallit 1.963 2014-10-03 00:00:00.0003 10 19.60

3 watchamacallit 1.964 2014-10-03 00:00:00.0001 2 43.98

3 watchamacallit 1.965 2014-10-03 00:00:00.000 1 243.98

报告1:运行清单2的结果。

如果您查看报告1中的结果，您可以看到有15个不同的记录。这些前5个记录包含产品表的第一行与salesitem表中的5个不同行连接的列值。产品表的2秒和3行也是如此。返回的行数是product表中的行数乘以salesitem表中的行数，即15行。

创建cartesian产品可能有用的一个原因是生成测试数据。假设我想在我的产品和salesitem表中使用日期生成一些不同的产品。我可以使用cross join来实现，如清单3所示:

select row_number() over(order by productname desc) as id,

product.productname

+ cast(salesitem.id as varchar(2)) asproductname,

(product.cost /salesitem.id) * 100 as cost

from product cross join salesitem;

我可以使用交叉连接来做到这种效果，就像我在清单3做到的那样。

清单3：简单的交叉连接示例

当我运行清单3的代码，我会得到报告2所示的输出。

报告2：当运行清单3代码的结果

回顾清单3，如你所见，我创建大量的包含了类似于product表的行。用行号功能，我得以在每一行创建一个唯一的id列。此外我还是用id列从我的salesitem创建了唯一的productname，和cost列的值。生成的行数等于乘积表中的行数乘thesalesitem表中的行数。

本节中的示例仅在两个表之间执行交叉连接。你可以使用交叉连接运算符跨多个表执行交叉连接操作。清单4中的示例在三个表中创建了笛卡尔积。

清单4：在三个表格间用交叉连接创建笛卡尔积

运行清单4的输出有两个不同的交叉连接操作。由该代码创建的笛卡尔积将产生一个结果集，该集合将具有与syts表中的行数相等的总行计数，同时，sysobjts中的行数乘以sys.sysyuser中的行数。

当交叉连接像内部连接一样执行时

在前一节中，我提到当使用交叉连接运算符时，它会产生笛卡尔积。事实不总是这样。当使用where子句限制交叉连接操作中涉及的表的连接时，sqlserver不创建笛卡尔积。相反，它的功能更类似于正常的连接操作。为了说明这种行为，请查看清单5中的代码。

清单5：两个等价的select语句

清单5中的代码包含两个select语句。第一个select语句使用交叉连接运算符，然后使用where子句定义如何加入交叉连接操作中涉及的两个表。第二个select语句使用一个带有on子句的正常内部联接运算符来加入这两个表。sql server的查询优化器足够智能，可以知道清单5中的第一个select语句可以被重新写入内部连接。优化器知道，当交叉连接操作与where子句一起使用时，可以重新编写查询，该where子句在交叉连接中涉及的两个表之间提供连接谓词。因此，sqlserver引擎为清单5中的两个select语句生成相同的执行计划。当不提供一个where约束sql server不知道如何加入涉及交叉连接操作的两个表时，它在与交叉连接操作相关联的两个集合之间创建一个笛卡尔积。

运用交叉连接来寻找没有卖出的产品

前面几节中的例子旨在帮助您理解交叉连接运算符以及如何使用它。一个使用交叉连接运算符的功能是使用它来帮助查找在另一个表中没有匹配记录的一个表中的项。例如，假设我想报告我的产品表中的每一个产品名称的总数量和总销售金额。但是在我的实例中，每一个产品名称都不是每天都有销售，所以我的报告要求意味着我需要显示一个数量0和总销售额0美元的产品，也就是这些产品在某一天没有销售。这就是交叉连接操作符与左外部连接操作如何一起帮助我识别那些在某一天没有销售的项目。来看看报告的代码吧。

清单6：运用交叉连接来寻找没有卖出的产品

让我给你介绍一下这个代码。我创建一个子查询来选择所有不同的销售日期值。这个子查询给了我所有的销售日期。然后我与我的产品表交叉连接。这允许我在每个销售日期和每个产品行之间创建一个笛卡尔积。从交叉连接返回的集合将具有最终结果集中所需的每一个值，除了销售的每个产品的qty和toesalesamt的总和。为了获得这些汇总值，我对saleitit表执行左外部连接，将其与与交叉连接操作创建的笛卡尔积一起加入。我根据studiod和saledead列执行了这个连接。通过使用左外部连接，我的笛卡尔积中的每一行将被返回，并且如果有一个匹配的销售日期记录用于产品和销售日期，qty和toostutsatmt值将与适当的行相关联。这个查询的最后一件事是使用groupby子句总结基于销售日期和产品名称的qty和toalsalestalk。

效率考量

产生笛卡尔积的交叉连接算子有一些性能方面的问题需要考虑。因为sql引擎需要在一个集合中的每一行中加入另一个集合中的每一行，所以结果集可以相当庞大。如果我做一个交叉连接一个表，它有1000000行，另一个表有100000行，那么我的结果集将有1000000×100000行，或者100000000000行。这是一个大的结果集，它将花费sql server大量的时间来创建表格。

交叉连接操作符可以是一个很好的解决方案，用于识别两个集合中所有可能组合的结果集，比如每个月的所有客户的所有销售，甚至在几个月内一些客户没有销售。当使用交叉连接运算符时，如果要优化性能，则应该尽量减少交叉连接的集合的大小。例如，假设我有一个表，包含过去2个月的销售数据。如果我想制作一个报告，显示一个月没有销售的客户，那么一个月内的天数的识别方法会极大地改善我查询的性能。

清单7：tsql为性能测试创建示例数据

清单7中的代码为1,000个不同的客户创建了2个月的数据。此代码不会为每位第7位的客户添加销售数据。此代码可生成1,000个cust表记录和52,338个销售表记录。

为了演示如何根据cross join输入集中使用的集合的大小使用cross join运算符执行不同的操作，让我运行清单8和清单9中的代码。对于每个测试，我将记录返回结果。

清单8：针对所有销售记录的cross join

清单9：针对不同的销售日期列表的cross join

在清单8中，cross join操作员将1,000个cust记录与52,338个销售记录结合在一起，生成一个52,338,000行的记录集，然后用它来确定一个月内销售额为零的客户。在清单9中，我从我的sales表中将我的选择标准更改为仅返回一组不同的salesdate值。这个不同的集合仅产生61个不同的salesdate值，因此清单9中的cross join操作的结果只产生了61,000条记录。通过减少cross join操作的结果集，我的清单9中的查询运行时间不到1秒，而清单8中的代码在我的机器上运行了19秒。这种性能差异的主要原因是sql server需要为每个查询执行的大量的不同操作处理记录。如果你看看两个列表的执行计划，你会发现计划略有不同。但是，如果您查看图形计划右侧的嵌套循环（inner join）操作生成的估计记录数，您将看到清单8估计的记录数为52,338,000条，而清单9中的相同操作仅估计了61,000条记录。清单8的查询计划中的cross join嵌套循环操作生成的这个大记录集之后被传递了几个附加操作。因为清单8中的所有这些操作都需要对5200万条记录进行操作。清单8比清单9慢很多。

正如您所看到的，cross join操作中使用的记录数可能会显著影响查询运行的时间长度。因此，如果您可以编写最大限度地减少cross join操作中涉及的记录数的查询，那么您的查询将会更加高效。

结论

cross join运算符在两个记录集之间生成笛卡尔乘积。 此操作符在帮助识别在另一个表中没有匹配记录的一个表中的项目时非常有用。应注意尽量减少与cross join操作员一起使用的记录集的大小。通过确保尽可能小的cross join的结果集，您将确保代码尽可能快地运行。

问题和答案

在本节中，您可以通过回答以下问题来查看您对使用cross join操作符的理解程度。

问题1：

cross join运算符根据on子句中指定的列匹配两个记录集来创建一个结果集。 （对或错）？

对

错

问题2：

当表a和b包含重复行时，可以使用哪个公式来标识从a和b两个表之间的无约束cross join返回的行数？

表a中的行数a乘以表b中的行数

表a中的行数a乘以表b中的唯一行数

表a中的唯一行数乘以表b中的行数

表a中的唯一行数乘以表b中的唯一行数

问题3：

哪种方法可以最大限度地减少由cross join操作产生的笛卡尔积的大小？

确保被连接的两组尽可能的多

确保被连接的两组尽可能的少

确保cross join操作左侧的设置尽可能的少

确保cross join操作右侧的集合尽可能的少

答案：

问题1：

正确答案是b。 cross join运算符不使用on子句执行cross join操作。它将一个表中的每一行连接到另一个表中的每一行。 cross join在加入两组时创建了一个cartesian产品。

问题2：

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

问题3：

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

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

问题3：

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