mysql列反转Pivoting - 雪山飞猪

相关文章推荐

沉稳的油条 · MySQL数据库精选（从入门使用到底层结构） ...· 1 周前 ·

逼格高的小蝌蚪 · 明道云和简道云哪个好-有什么区别-优缺点-3 ...· 1 年前 ·

满身肌肉的充值卡 · pandas-空值处理 - 贝壳里的星海 ...· 2 年前 ·

光明磊落的高山 · 404 - 知乎· 2 年前 ·

刚毅的硬币 · 流浪猫鲍勃纪念雕像落成 Street ...· 2 年前 ·

任性的枇杷 · “300万美元！2023美网打破纪录，单打冠 ...· 2 年前 ·

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;
上述结果没有任何问题，只是显示的可能不够直观。如果可以通过旋转得到输出结果，那就直观和清晰多了。
这里同样可以使用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'       
(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;
因此可以通过将表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' 
(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'
(SELECT custid, YEAR(orderdate) AS orderyear, qty
FROM t2 ) AS P
GROUP BY custid;
 这里把t2表返回后的内容导入到表p中，如果想得到t2表直接聚合得到的结果，这个问题就变成了Unpivoting问题。解决这个问题需要将列旋转为行。这里使用的技巧是对每行数据产生3个副本，每个副本产生一个需要旋转的列，这个过程可以通过如下的CROSS JOIN来完成。
SELECT * FROM
    (SELECT 2009 AS orderyear
    UNION ALL SELECT 2010
    UNION ALL SELECT 2011) AS o
得到以下结果
接着问题就简单了，只需根据orderyear列来取得对应旋转列的值，例如：