mysql列反转Pivoting

Pivoting是一项可以把行旋转为列的技术。在执行Pivoting的过程中可能会使用到聚合。Pivoting技术应用非常广泛。下面讨论的都是静态的Pivoting查询，即用户需要提前知道旋转的属性和列的值。对于动态Pivoting，需要动态地构造字符串。

开放架构

开发架构是一种用于频繁更改架构的一种设计模式。利用关系型数据库和SQL语句可以非常有效地处理DML，包括INSERT、SELECT、UPDATE和DELETE。

然而，DDL在频繁进行架构更改时显得十分不方便。例如城对表结构进行修改，用户必须添加、修改或删除列，而这种操作正是关系型数据库不擅长的方面。

因此，在频繁更改架构的情况下，可以在一个表中存储所有的数据，每行存储一个属性的值，多用VARCHAR来存储，因为其容纳各种类型的数据。下面的语句生成一张开发架构的表t.

CREATE TABLE t(
id INT,
attribute VARCHAR(10),
value VARCHAR(20),
PRIMARY KEY(id,attribute)
);

INSERT INTO t SELECT 1,'attr1','BMW';
INSERT INTO t SELECT 1,'attr2','100';
INSERT INTO t SELECT 1,'attr3','2010-01-01';
INSERT INTO t SELECT 2,'attr2','200';
INSERT INTO t SELECT 2,'attr3','2010-03-04';
INSERT INTO t SELECT 2,'attr4','M';
INSERT INTO t SELECT 2,'attr5','55.60';
INSERT INTO t SELECT 3,'attr1','SUV';
INSERT INTO t SELECT 3,'attr2','10';
INSERT INTO t SELECT 3,'attr3','2011-11-11';

表的内容如下

从上面的例子中可以看到，在对通过开放架构设计的表进行添加、修改或删除表和列时，只需要通过INSERT、UPDATE、DELETE操作来完成逻辑架构的更改即可。当然使用这种方法可能导致数据库的其他特性无法使用，如完整性约束、SQL优化乖，同时查询数据变得不如之前的SQL语句来得直接和直观。所以，对于利用开放架构设计的表，一般使用Pivoting技术来查询数据。

Privoting技术需要和聚合一起使用，首先要确定结果的行数与表中行数的关系。对于开放架构表t，应该有3行5列，这可以通过分组id来得到。因此可以通过下列Pivoting进行行列互转以得到数据。

SELECT id,
    MAX(CASE WHEN attribute='attr1' THEN value END) AS attr1,
    MAX(CASE WHEN attribute='attr2' THEN value END) AS attr2,
    MAX(CASE WHEN attribute='attr3' THEN value END) AS attr3,
    MAX(CASE WHEN attribute='attr4' THEN value END) AS attr4,
    MAX(CASE WHEN attribute='attr5' THEN value END) AS attr5
FROM t
GROUP BY id;

Pivoting先根据id进行分组，确定行列互转后记录的行数。之后通过已知的5个属性来确定行列互转后有5列数据，并通过CASE得到每列的值。由于使用了分组技术，因此一定要使用分组函数来取得列的值，故这里使用MAX函数，当然也可以使用MIN函数。最后得到的结果如下图

这种旋转方式是非常高效的，因为它只对表进行一次扫描。另外，这是一种静态的Pivoting，用户必须事件知道一共有多少个属性，然而对于一般开放架构表，用户都会定义一个最大的属性个数，这样可以比较容易进行Pivoting

 

关系除法

关系除法(Rational Divistion)和常见的关系运算JOIN、SEMI JOIN一样，都是一个关系代数。

当除数集合中的元素数量较小时，Pivoting可用于解决关系除法问题。先通过创建表t并填充数据。

CREATE TABLE t1 (
orderid VARCHAR(10) NOT NULL,
productid INT NOT NULL,
PRIMARY KEY(orderid,productid)
);

INSERT INTO t1 SELECT 'A',1;
INSERT INTO t1 SELECT 'A',2;
INSERT INTO t1 SELECT 'A',3;
INSERT INTO t1 SELECT 'A',4;
INSERT INTO t1 SELECT 'B',2;
INSERT INTO t1 SELECT 'B',3;
INSERT INTO t1 SELECT 'B',4;
INSERT INTO t1 SELECT 'C',3;
INSERT INTO t1 SELECT 'C',4;
INSERT INTO t1 SELECT 'D',

表的内容如下

表t存储订单中包含的产品，比如A订单中包含的产品的ID为1、2、3、4，B订单中所包含的产品为2、3、4，以此类推。这是一个比较典型的关系除法问题。用Pivoting技术可以反订单中的产品旋转到单独的列中。例如要查询包含productid为2、3、4的订单，可以采用如下方法：

SELECT orderid
FROM (
SELECT 
orderid,
MAX(CASE WHEN productid=2 THEN 1 END) AS p2,
MAX(CASE WHEN productid=3 THEN 1 END) AS P3,
MAX(CASE WHEN productid=4 THEN 1 END) AS p4
FROM t1
GROUP BY orderid
) AS P
WHERE p2=1 AND p3=1 AND p4=1;

上述语句返回“A”和“B”。如果单独运行子查询，将会得到每个订单对应的产品ID，得到的结果如下

对于这个问题，聚合函数可以使用COUNT来替换MAX，这会让派生表的结果显得更加直观。此时若产品存在则返回1，不存在则返回0而不是NULL，故SQL语句可调整为

 

SELECT orderid
FROM (
SELECT 
orderid,
COUNT(CASE WHEN productid=2 THEN 1 END) AS p2,
COUNT(CASE WHEN productid=3 THEN 1 END) AS P3,
COUNT(CASE WHEN productid=4 THEN 1 END) AS p4
FROM t1
GROUP BY orderid
) AS P
WHERE p2=1 AND p3=1 AND p4=1;

 

格式化聚合函数

Privoting技术还可以用来 格式化聚合数据，一般用于报表的展现。为了演示用Pivoting技术来格式化，下面给出一个例子。先通过创建表t2并填充数据

CREATE TABLE t2 (
orderid INT NOT NULL,
orderdate DATE NOT NULL,
empid INT NOT NULL,
custid VARCHAR(10) NOT NULL,
qty INT NOT NULL,
PRIMARY KEY (orderid,orderdate)
);

INSERT INTO t2 SELECT 1,'2010-01-02','3','A',10;
INSERT INTO t2 SELECT 2,'2010-04-02','2','B',20;
INSERT INTO t2 SELECT 3,'2010-05-02','1','A',30;
INSERT INTO t2 SELECT 4,'2010-07-02','3','D',40;
INSERT INTO t2 SELECT 5,'2011-01-02','4','A',20;
INSERT INTO t2 SELECT 6,'2011-01-02','3','B',30;
INSERT INTO t2 SELECT 7,'2011-01-02','1','C',40;
INSERT INTO t2 SELECT 8,'2009-01-02','2','A',10;
INSERT INTO t2 SELECT 9,'2009-01-02','3','B',20;

表的内容如下

可以将表t2看作一张汇总表，比如网上商城的购物明细。这份汇总表显示了订单号、订单日期、员工编号、消费者编号和订单数量。要在此汇总表的基础上进一步统计每个月消费者每年的数量，可能会想到分组来得到结果，例如

SELECT custid,YEAR(orderdate) AS year,SUM(qty) AS sum_qty
FROM t2 GROUP BY custid,YEAR(orderdate)

运行结果如下

上述结果没有任何问题，只是显示的可能不够直观。如果可以通过旋转得到输出结果，那就直观和清晰多了。

这里同样可以使用Pivoting技术。与之前唯一的不同是，此处不再使用聚合函数MAX，而是使用SUM函数。这个解决方案的SQL语句如下：

SELECT custid,
       IFNULL(SUM(CASE WHEN orderyear=2009 THEN qty END),0) AS '2009',
       IFNULL(SUM(CASE WHEN orderyear=2010 THEN qty END),0) AS '2010',
       IFNULL(SUM(CASE WHEN orderyear=2011 THEN qty END),0) AS '2011'       
FROM 
(SELECT custid,YEAR(orderdate) AS orderyear,qty FROM t2) AS p
GROUP BY custid;

上述SQL语句中的IFNULL函数用来将NULL值返回为0，代表该年消费者没有产生任何订单操作。

使用Pivoting技术来格式化聚合数据会遇到一个问题，即当旋转的元素非常多时，会产生较长的查询字符串。要缩短查询的字符长度，可以预先产生一张矩阵表，包含每个要旋转列的属性，运行如下语句创建并填充矩阵表Matrix

CREATE TABLE Matrix (
orderyear INT PRIMARY KEY,
y2009 INT NULL,
y2010 INT NULL,
y2011 INT NULL
);

INSERT INTO Matrix SELECT 2009,1,0,0;
INSERT INTO Matrix SELECT 2010,0,1,0;
INSERT INTO Matrix SELECT 2011,0,0,1;

矩阵表Matrix的内容如下

因此可以通过将表t和表maxtrix进行连接把原来的：

SUM(CASE WHEN orderyear=n THEN qty END) AS N

替换为SUM（qty*yN) AS N

完整的SQL查询语句为：

SELECT custid,
    SUM(qty*y2009) AS '2009',
    SUM(qty*y2010) AS '2010',
    SUM(qty*y2011) AS '2011' 
FROM 
(SELECT custid,YEAR(orderdate) AS orderyear,qty FROM t2) AS O
INNER JOIN Matrix AS P
    ON O.orderyear=P.orderyear
GROUP BY custid;

运行结果如下

Unpivoting

可以将Unpivoting看做Pivoting的反向操作，即将列旋转为行。要完成这个示例，需要根据下列语句创建并填充表p，

CREATE TABLE p (
custid VARCHAR(10) NOT NULL,
y2009 INT NULL,
y2010 INT NULL,
y2011 INT NULL,
PRIMARY KEY (custid)
);

INSERT INTO p
SELECT
    custid,
    IFNULL(SUM(CASE WHEN orderyear=2009 THEN qty END), 0) AS '2009',
    IFNULL(SUM(CASE WHEN orderyear=2010 THEN qty END), 0) AS '2010',
    IFNULL(SUM(CASE WHEN orderyear=2011 THEN qty END), 0) AS '2011'
FROM
(SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM t2 ) AS P
GROUP BY custid;

 这里把t2表返回后的内容导入到表p中，如果想得到t2表直接聚合得到的结果，这个问题就变成了Unpivoting问题。解决这个问题需要将列旋转为行。这里使用的技巧是对每行数据产生3个副本，每个副本产生一个需要旋转的列，这个过程可以通过如下的CROSS JOIN来完成。

SELECT * FROM
    p,
    (SELECT 2009 AS orderyear
    UNION ALL SELECT 2010
    UNION ALL SELECT 2011) AS o

得到以下结果

接着问题就简单了，只需根据orderyear列来取得对应旋转列的值，例如：

 

CASE orderyear
WHEN 2009 THEN y2009
WHEN 2010 THEN y2010
WHEN 2011 THEN y2011
END AS qty

因此这个Unpivoting问题的解决方案如下

SELECT custid,orderyear, 
		CASE orderyear
		WHEN 2009 THEN y2009
		WHEN 2010 THEN y2010
		WHEN 2011 THEN y2011
		END AS qty
FROM
    p,
    (SELECT 2009 AS orderyear
    UNION ALL SELECT 2010
    UNION ALL SELECT 2011) AS o

若要得到最终结果，则还需要过滤qty等于0的情况，因此最终的解决方案为:

 

SELECT custid,orderyear,qty
FROM (
	SELECT custid,orderyear, 
			CASE orderyear
			WHEN 2009 THEN y2009
			WHEN 2010 THEN y2010
			WHEN 2011 THEN y2011
			END AS qty
	FROM
	    p,
	    (SELECT 2009 AS orderyear
	    UNION ALL SELECT 2010
	    UNION ALL SELECT 2011) AS o
	) AS M
WHERE qty <> 0