SQL Server里PIVOT运算符的”红颜祸水“

在今天的文章里我想讨论下SQL Server里一个特别的T-SQL语言结构——自SQL Server 2005引入的PIVOT运算符。我经常引用这个与语言结构是SQL Server里最危险的一个——很快你就会知道为什么。在我们进入特定问题和陷阱前,首先我想给你下使用SQL Server里的PIVOT能实现什么的一个基本概述。

概述

SQL Server里PIVOT运算符背后的基本思想是在T-SQL查询期间,你可以旋转行为列。运算符本身是SQL Server 2005后引入的,主要用在基于建立在实体属性值模型(Entity Attribute Value model (EAV))原则上的数据库。EAM模型背后的想法是你可以扩展数据库实体,而不需要进行数据库架构的修改。因此EAV模型存储实体的所有属性以键/值对存储在一个表里。我们来看下面一个简单的键/值对模型的表。

如你所见,我们插入2个数据库实体到表里,每个实体包含多个属性。在表里每个属性只是额外的记录。如果你像扩展实体更多的属性,你只插入额外的记录到表里,而没有必要进行数据库架构修改——这就是开放数据库架构的“威力”……

查询这样的EAV表显然很困难,因为你处理的是平键/值对的数据结构。因此你要旋转表内容,行旋转为列。你可以进行用自带的PIVOT运算符进行这个旋转,或者通过传统的CASE表达式进行纯手工来实现。在我们进入PIVOT细节前,我想给你展示下通过手工使用T-SQL和一些CASE表达式来实现。如果你手工进行旋转,你的T-SQL查询需要实现3个阶段:

  1. 分组阶段(Grouping Phase)
  2. 摊开阶段(Spreading Phase)
  3. 聚合阶段(Aggregation Phase)

在分组阶段(Grouping Phase)我们压缩我们的EAV表为不同的数据库实体。在这里我们在RecordID列进行一个GROUP BY。在第2阶段的,摊开阶段(Spreading Phase),我们使用多个CASE表达式来旋转行为列。最后在聚合阶段(Aggregation Phase)我们使用MAX表达式来为每个行和列返回不同值。我们来看下列T-SQL代码。

从代码里可以看到,很容易区分每个阶段,还有它们如何映射到T-SQL查询。下图给你展示了查询结果,最后我们把行转为了列。

 

PIVOT运算符

自SQL Server 2005起(差不多10年前了!),微软在T-SQL里引入PIVOT运算符。使用那个运算符你可以进行同样的转换(行到列),只要一个原生运算符即可。听起来很简单,很有前景,不是么?下列代码显示了使用原生PIVOT运算符进行同样的转换。

当你执行那个查询时,你会收到和刚才图片一样的结果。但当你看PIVOT运算符语法时,和手动方法相比,你会看到一个很大的区别:

你只能指定分摊和聚合元素!不能明确定义分组元素!

分组元素是你在PIVOT运算符里没有引用的剩下列。在我们的例子里,我们没有在PIVOT运算符里没有引用RecordID列,因此这个列在分组阶段(Grouping Phase)被使用。如果我们随后修改数据库架构,这会带来有趣的副作用,例如对基本表增加额外列:

然后我们对其赋值:

现在当你执行用PIVOIT运算符的同个查询时(在那somedata列都有非NULL值),你会拿回完全不同的结果,因为排序阶段现在是在RecordID和SomeData列(我们刚加的)上。

相比如果我们重新执行我们刚开始写的手工T-SQL查询会发生什么。它还是返回同样正确的结果。这是在SQL Server里,PIVOT运算符的其中一个最大的副作用:分组元素不能明确定义。为了克服这个问题,最佳实践是使用只返回需要列的表表达式。使用这个方法,如果你随后修改表架构还是没有问题,因从表表达式默认情况下额外的列还是没有返回。我们来看下列的代码:

从代码里可以看到,我通过一个表表达式输送给PIVOT运算符。而且在表表达式里,你从表里只选择需要的列。这就意味着以后你可以修改表架构也会破坏PIVOT查询的结果。

小结
我希望这篇文章已向你展示了在SQL Server里,为什么PIVOT运算符是非常危险的。这个语法本身带来了非常高效的代码,但作为副作用你不能直接指定分组元素。因次你应该确保使用一个表表达式来定义输送给PIVOT运算符的列来保证给出结果的确定性。

用PIVOT运算符你有什么经历?你是否喜欢它?如果你不喜欢它,你想要什么改变?

2 2 收藏 1 评论

相关文章

可能感兴趣的话题



直接登录
最新评论
跳到底部
返回顶部