你的位置:首页 > 数据库

[数据库]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模型存储实体的所有属性以键/值对存储在一个表里。我们来看下面一个简单的键/值对模型的表。 

CREATE TABLE EAVTable(  RecordID INT NOT NULL,  Element CHAR(100) NOT NULL,  Value SQL_VARIANT NOT NULL,  PRIMARY KEY (RecordID, Element))GO-- Insert some recordsINSERT INTO EAVTable (RecordID, Element, Value) VALUES(1, 'FirstName', 'Woody'),(1, 'LastName', 'Tu'),(1, 'City', 'Linhai'),(1, 'Country', 'China'),(2, 'FirstName', 'Bill'),(2, 'LastName', 'Gates'),(2, 'City', 'Seattle'),(2, 'Country', 'USA')GO

如你所见,我们插入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代码。

 1 -- Pivot the data with a handwritten T-SQL statement. 2 -- Make sure you have an index defined on the grouping column. 3 SELECT 4   RecordID, 5   -- Spreading and aggregation phase 6   MAX(CASE WHEN Element = 'FirstName' THEN Value END) AS 'FirstName', 7   MAX(CASE WHEN Element = 'LastName' THEN Value END) AS 'LastName', 8   MAX(CASE WHEN Element = 'City' THEN Value END) AS 'City', 9   MAX(CASE WHEN Element = 'Country' THEN Value END) AS 'Country'10 FROM EAVTable11 GROUP BY RecordID -- Grouping phase12 GO

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

PIVOT运算符

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

 1 -- Perform the same query with the native PIVOT operator. 2 -- The grouping column is not specified explicitly, it's the remaining column 3 -- that is not referenced in the spreading and aggregation elements. 4 SELECT 5   RecordID, 6   FirstName, 7   LastName, 8   City, 9   Country10 FROM EAVTable11 PIVOT(MAX(Value) FOR Element IN (FirstName, LastName, City, Country)) AS t12 GO

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

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

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

1 -- Add a new column to the table2 ALTER TABLE EAVTable ADD SomeData CHAR(1)3 GO

然后我们对其赋值:

1 UPDATE dbo.EAVTable SET SomeData=LEFT(CAST(Value AS VARCHAR(1)),1)

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

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

 1 -- Use a table expression to state explicitly which columns you want to  2 -- return from the base table. Therefore you can always control on which 3 -- columns the PIVOT operator is performing the grouping. 4 SELECT 5   RecordID, 6   FirstName, 7   LastName, 8   City, 9   Country10 FROM11 (12   -- Table Expression13   SELECT RecordID, Element, Value FROM EAVTable14 ) AS t15 PIVOT(MAX(Value) FOR Element IN (FirstName, LastName, City, Country)) AS t116 GO

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

小结

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

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

感谢关注!