PIVOT 行列相转

时间:2023-03-08 15:50:29
PIVOT 行列相转
先介绍一下英文释义:
pivot
英 ['pɪvət]  美 ['pɪvət]
n. 枢轴;中心点;旋转运动
vt. 以…为中心旋转;把…置于枢轴上
vi. 在枢轴上转动;随…转移
adj. 枢轴的;关键的
从上面就不难看出这个函数是做什么的,旋转,转换,用于列和行之间对数据进行旋转或透视转换,同时执行聚合预算。
假设现在想查proudct数据表中返回每个员工每年处理过的订单的总价格。在输出中,每个员工占一行,每个订单年份占一列,每个员工行和订单年份列交叉位置上则是总价。以下我们用PIVOT查询可以实现这一需求。
-----需求表如下--------
员工     年份    金额
-------------------------
白洁      2005   150
孙倩      2007    360
--------------------------
SQL语句:
SELECT  *
FROM    ( SELECT   NAME ,
                    YEAR(shijian) AS T ,
                    jiage
          FROM      product
        ) AS ov PIVOT ( SUM(jiage) FOR t IN ( [2005], [2006], [2007] ) ) AS p
----------结果如下----------
 PIVOT 行列相转
PIVOT 行列相转
下面我们在分析一下PIVOT操作涉及以下三个逻辑阶段:
1.P1:分姐
2.P2:扩展
3:聚合
  第一个阶段(p1)有点意思,从查询语句中可以看到,PIVOT运算符引用了ov中的三个列作为输入参数(shijian(T),jiage),第一阶段会隐式对OV中的行进行分组,分组是根据那些未作为PIVOT输入的所有列,就像是一个GROUT BY子句。这里只有name列没有出现在PIVOT的输入参数中,所有每个员工都一个组。
*PIVOT的隐式分组阶段不会影响查询中其他行何的显式的GROUP BY 子名,因为它最终会生成一个虚拟表,作为下一个逻辑阶段(也可能是另一个表运算,也许是WHERE阶段)的输入。
第二个阶段(P2)将把(spreading_col)列表中的值扩展到它们相应的目标列上,下面用代码解释一下,更加直观:
case when shijian=2005 then jiage end,
case when shijian=2006 then jiage end,
case when shijian=2007 then jiange end
*不带ELSE的子句的CASE表达式相录于包含一个隐式的ELSE NULL
每三阶段(3)第三个阶段对每个CASE表达式应用指定的聚合函数,生成结果列,来段代码,更加直观:
sum(case when shijian=2006 then jiage end)as 2005,
sum(case when shijian=2006 then jiage end)as 2006,
sum(case when shijian=2007 then jiage end)as 2007
总之PIVOT查询在逻辑上相当于下面的语句:
SELECT  name ,
        SUM(CASE WHEN t = 2006 THEN jiage
            END) AS [2005] ,
        SUM(CASE WHEN t = 2006 THEN jiage
            END) AS [2006] ,
        SUM(CASE WHEN t = 2007 THEN jiage
            END) AS [2007]
FROM    ( SELECT    name ,
                    YEAR(shijian) AS t ,
                    jiage
          FROM      product
        ) AS ov
GROUP BY name
------结果如下----------------
 PIVOT 行列相转
PIVOT 行列相转
----2017-08-03 1:13 深圳福田梅林三村----