mysql行转列动态横向扩展 mysql 行列倒置

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

开放架构

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

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

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技术来查询数据。

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一样，都是一个关系代数。

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',

表的内容如下

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)

运行结果如下

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

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，代表该年消费者没有产生任何订单操作。

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

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

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