Microsoft SQL Server 2008技术内幕：T-SQL语言基础（透视、逆透视及分组集）

七、透视、逆透视及分组集

7.1 透视转换

7.1.1 概念及作用

• 透视数据
  • 把数据从行的状态旋转为列的状态
• 例如，生成每个雇员中每个客户的总订货量

select empid,custid,sum(qty) as sumqty
from dbo.orders
group by empid,custid;

• 透视转换
  • 将custid列中所有可能的取值在列上拓展，即：

• 透视转换涉及的逻辑处理
  • 分组
    • 例如，最终结果按empid分组
  • 拓展
    • 例如，按custid拓展
  • 聚合
    • 例如，按qty聚合

7.1.2 使用标准SQL进行透视转换

select empid,
	sum(case when custid='A' then qty end) as A,
	sum(case when custid='B' then qty end) as B,
	sum(case when custid='C' then qty end) as C,
	sum(case when custid='D' then qty end) as D
from dbo.orders
group by empid;

• 分组
  • group by empid
• 拓展
  • case when custid=‘A’ then qty end
  • 只展示相应拓展列的信息
• 聚合
  • sum(case when custid=‘A’ then qty end) as A

7.1.3 使用T-SQL PIVOT运算符进行透视转换

• pivot
  • 表运算符
  • 不需要显式指定分组元素
    • 不在拓展元素、聚合元素中的就是分组元素
    • pivot运算符的源表除了分组、拓展和聚合元素以外，不能包含其他属性（列）
  • 拓展元素
    • 既代表目标列名
    • 又代表源表列值

select empid,A,B,C,D
from
	-- source table or table expression 
	(select empid,custid,qty
	from dbo.orders) as D
	
	pivot(sum(qty) for 
		custid in (A,B,C,D)/*list of target columns*/
		) as P;

7.2 逆透视转换

7.2.1 概念及作用

• 逆透视
  • 把数据从列的状态旋转为行的状态
• 例如，转换前：

• 转换后：

• 逆透视转换涉及的逻辑处理
  • 生成副本
    • 例如，将empid组合转换为(empid,custid)的组合
    • 方式：empid和custid进行笛卡尔积运算
  • 提取元素
    • 例如，为(empid,custid)的组合生成对应的列值
  • 删除不相关的交叉
    • 例如，笛卡尔积运算可能生成多余的组合

7.2.2 使用标准SQL进行逆透视转换

• 表值构造函数

(values(),(),()) as custs(custid)

• 等价于（非标准形式）

(select ...
 union all select ...
 union all select ...) as custs(custid)

• 逆透视转换
  
  

select * from 
(select empid,custid,
case custid
	when 'A' then A/*当custid='A'，取empcustorders中列A的值*/
	when 'B' then B
	when 'C' then C
	when 'D' then D
end as qty
from dbo.empcustorders
cross join
(values('A'),('B'),('C'),('D')) as custs(custid)) as d
where qty is not null

7.2.3 使用T-SQL的unpivot运算符进行逆透视转换

select empid,custid,qty
from dbo.empcustorders
unpivot(qty /*用于保存源表列值的列名*/
for custid/*用于保存源表列名的列名*/ 
in(A,B,C,D)/*源表的列名列表*/) as u

7.3 分组集

• 在group by子句中设置属性，实现按不同组合进行分组
• 不使用分组集的情况

select empid,custid,sum(qty) as sumqty
from dbo.orders
group by empid,custid

union all

select empid,null,sum(qty) as sumqty
from dbo.orders
group by empid

union all

select null,custid,sum(qty) as sumqty
from dbo.orders
group by custid

union all

select null,null,sum(qty) as sumqty
from dbo.orders

• 缺点
  • 代码长度
  • 性能
    • 多次遍历表

7.3.1 grouping sets从属子句

select empid,custid,sum(qty) as sumqty
from dbo.orders
group by 
	grouping sets
	((empid,custid),
	 (empid),
	 (custid),
	 ());

• 优点
  • 代码量减少
  • SQL Server对遍历表进行了优化

7.3.2 cube从属子句

select empid,custid,sum(qty) as sumqty
from dbo.orders
group by cube(empid,custid);

• 基于输入成员而定义所有可能的分组集
  • empid,custid
  • empid
  • custid
  • ()

7.3.3 rollup从属子句

select 
	year(orderdate) as orderyear,
	month(orderdate) as ordermonth,
	day(orderdate) as orderday,
	sum(qty) as sumqty
from dbo.orders
group by 
	rollup(year(orderdate),month(orderdate),day(orderdate));

• 基于输入成员而定义具有一定层次关系的分组集
  • orderyear,ordermonth,orderday
  • orderyear,ordermonth
  • orderyear
  • ()

7.3.4 grouping和grouping_id函数

• 作用
  • 将结果行和分组集关联
    • 标识出结果行属于哪个分组集
    • 结果行中的null代码占位符，即该列不属于分组元素
• grouping(colName)
  • 判断该列是否为当前列构成的分组集的成员
  • 是则返回0，否则返回1
  • 两个列的grouping都为0，则两个列都属于分组集的成员

select 
	grouping(empid) as grpemp,
	grouping(custid) as grpcust,
	empid,custid,sum(qty) as sumqty
from dbo.orders
group by cube(empid,custid);

• grouping_id()
  • 接收所有元素作为输入
  • (a,b,c,d)分别对应 0b0000=0
  • (a,c)分别对应 0101 =5
  • 相当于将grouping()需要参照多个数进行判断简化为只需参照一个数就可以进行判断

select 
	grouping_id(empid,custid) as groupingset,
	empid,custid,sum(qty) as sumqty
from dbo.orders
group by cube(empid,custid);