Pivoting是一项可以把行旋转为列的技术。在执行Pivoting的过程中可能会使用到聚合。Pivoting技术应用非常广泛。下面讨论的都是静态的Pivoting查询,即用户需要提前知道旋转的属性和列的值。对于动态Pivoting,需要动态地构造字符串。
开放架构
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';
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函数。最后得到的结果如下图
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAYoAAABUCAIAAAATX+esAAAL4klEQVR4nO2du24bORfH+Tx5AGN2HiGvoOqTjAH8CO7cpIjkBaYx3LpfKLF2gSlSunazcqxmgN3NXrzfBxdxEt9iy+BXzI2cC0VZnMND+vxABFqKmjl/zvBvci5n2U/TX6hQoUIFYWE/TX/hBEEQ+LBvT1+/frUbgCmQC0EeHjzedIjHQsiejIFcCPLw4PGmQzwWYt+evnz5YjcAUyAXgjw8eLzpEI+FkD0ZA7kQ5OHB402HeCyE7MkYyIUgDw8ebzrEYyGA9pTGYRinjeqrqyugAHoGuRDk4cHjTYd4LKQ3e2qaUYc9ff78ee0NdmyqIIlU3/aFSSFpHLIcU1KM9nMSFeGxKDESHjw9nHg8icwdMG36OrLgSppCcnt6fFze3T/0W85/DIIfP8qVy+XTv//9n9bexZ+3bSorH/d/YIyxjm/7K4aFzLZHs+zzzyP2w+QcbXiLSWAgPPhi/MS7u384HrFgtK1o4IKQn0ds+9jeEbm9+y6W3J4eHpe1LzYtH/eDYP8s+1DacVYjlOXy6Z+Lf5t7P5uUPwrGH7/f3i3GRUUwmVWfR9tBsD8eMca2j5u7Biy9CLnL2mc/xBnebFivcaMY75CzSRBMFvDnnmkh1g5oJqRW2bc9zYaMDY/FGq3OrcrxNhvN6j+XvS/ffn3XFjrXsBBzWnoILzvdnfQm4x2Se5O542XvyM6GpatlimCF1CoLe3p4vLm9N1nO9oNgf579K9bIzZZPT3/9fdGy9zNpzjWv/bz83LbN9sqeSy9CbmdDFozP0IZnMkLgYrRDzsfVb4QfuifE5pHNhNQqkdpTUHZNaz+6Y08bCDF5fvTSz7f3N7f374dseAza1ag7BPzc6+3Ino8DNPb0/eHx+ubOZJlPtoLJ/OZ4yIK387vrm7v5OGDBZC43Wy6fPv35d3PvW0XL6lfzyRYbvSs3nn3O99K6a6NyVhXDQuaTraLfEIY3H4+K2Krj61bp5cRT1Lsi5P1o+F5uACukVpnb0/33h2/XtybLfLy1Nf71+vbbu3wxuzUc5jVCWS6f/vj0V3PvxY+kX2WVW2/Pqs+Nbf76tprS/uedUUXKYlaIqKJsiSe88pgCdzLa41WV8rR3VMh8vGXpyGZCapW92dNmnetcQS4EeXjUISREaU/3D1+/3cCX5XL5+x9/2tr7yxGCPDzqEBKSCalV5vZ0d//9y9dr+PK4XP72+ydbe385QpCHRx1CQjIhtcrcns4JgiCQUb7UYo2HhweLezcIciHIw4PHmw7xWIj9hCoev2+NCuThwXN1dcUZ96B4JUSG7MkYyIUgDw8er0a17RiMCZEhezIGciHIw4PHq1FtOwZjQmTInoyBXAjy8ODxalTbjsGYEBmyJ2MgF4I8PHhWjeqL2dFg8ObUzNh7fTD4sDt7ZcWenBIiA2pPUcjXSuYb7jFWlGjB+WUc7jE2rdIzJlPGDuP0Mg4Pi8R+l3G4F8aX+ffRHlwyR43xn0RlnDyJcmllhM0aqPCyjt1jbI+FJ6kyGLUEueXqLWzSflPUo/piZ3fw5pS/mu1+GOzuXOT1r2a7R7OL7N+ysaKyNrBrNSD25JgQGSB7SmPOGGfr2pNoOrkNRdFh4T6XcXgYhodxKthQehKGh2G0EH5lWksXantKT0K2x1gRTzJlWZDpSZgZbrMGLrxFlPcYT6K9ML5sD2alBBGdLWzSfnOUo/r0IJsjvJrtHh0cHBXzhU1GNbuYHQ0OXgPbk2tCZCD/Vwg8fLY9LSI2TTK7SU7C7C98MmXRSW5AyTT3rGQaJYsonwKUH0BYPXuq7FKY1uWVzRrw8DjnPI0Pw/iyOxiVBBGdLWzS3gCqUV1OELLB+fogXxxtNKr5xc5uNX+BsSfnhMjgtqdqcRfGl/JIruZTcZpNmk5SztN4GqfFcqD8gwzDM+0p/9ysAQ+Pt3llLRiVBBGdLWzS3gCKUV0Nv2Jwnr4ZHLzedFTz0h2g7Mk9ITK47allcZf7TnU1KmtzGYfZ9GqacJ7Gh1GS/wuH+/aUr+z0zKKlTb4W22PhSaxjN+u2N8tao1oaus8e1a2VsPaEXYiMI/Ykzph4dik3SnhjtBTXUNKTKL7s65JqF44v7kpvag2v4GUs7lpGNeMXO7sHO1gnHd4IkXHEnhZR/Q5dhlCTTMOwvGq+iMLDEPLCE1/PnpBdGpdud7aHpyNBRGcLm7TfnDUu2eT1pwcfBoOj2UV5vbkY7fmtMfzXnvALkYG9c8c4Y7y2btG79lSbKxXUVgrS4wWgF5645p278iEJTA8WiLEVzxa0BKMhQURnC5u03xTtG17C4BRH+4fBICvNmg8Hp7X/ZBzBnTsXhMjQY5nGQC4EeXjwaD0uZHD42X3uyRUhMmRPxkAuBHl48Hj1sLU3QmTInoyBXAjy8ODx6lU12zEYEyJD9mQM5EKQhwePV6PadgzGhMjk9nRFEARhm3Z7ssiVv96PCuThweNNh3gshOyJ7OmF4k2HeCyE7Ins6YXiTYd4LITsiezpheJNh3gsBL89OfzUBiqQhwePNx3isRAQe0p5WLzREsZtMXWPeadz/VVopaP0PltmWcPaXirs3FqeE1VTpzaKDmlPymo6AFMo7Uno8yodRa1GZhHpnQnmsWRPSfWeXcR47Tgr7cntXH8VGuko/c+WWe0lS3q3qn1RH04j4BQOHUlZkbLKnhSpuBosopoN9X1aithf3MXhOvbkeK6/NrrSUVpPqKIOr2DzhCq1b5Xt8yyD4B3SlZQVJwbtqcw629K4/9TYtu2pLaeKwp5cz/XXQmc6Sgzp6ACyZabxYesyoT25Xb6+sGFPrUlZcaK5uGss1lgj45C47iuTUvR7WopYtqfmyo57lrRwBYp0lBjsCSRbprAjZfvMyIRi2iBW2VNbUlaU6M2LxXSDihphrgRyWorYtKdWb+KeJS1UoU5HaX1xB5UtM/u6ngZe2d7O7Kk1KStG1lm2q2tq81zlMr8HbN6561K2xrWnvN6ZXH8VGukovc+WmZ5Egv3pXhqv7dQgq+2p5wBMoXXTo7TaZk35IbsRUS6o96LkJVwaF1NlNp8t0L5z516uv6oHtNJR+p4ts7hj3ZH6sntrZE9KNB8ZEVypWVO7OCg+c+D9gwVKtJ57MugjVp57QgDy8ODxpkM8FoLdnuipcVMgDw8ebzrEYyH47cmZgvwsQR4ePN50iMdCyJ7Inl4o3nSIx0IoWyZBEFhotyeLNGNyFORCkIcHz5VP03bbMRgTIkP2ZAzkQpCHB49Xo9p2DMaEyJA9GQO5EOThwePVqLYdgzEhMmRPxkAuBHl48Hg1qm3HYEyIDNmTMZALQR4ePKtGtVMP3HkjRAbInqLypZao7SzpopbFsfX1VKmyJc8ZGBrjXz/VpHnW6GdVeKKErhrI9s9HPaodS9PqjRAZ6GyZ7enoOpDeoZ8mHW/PC5Zk9d0otT2tlWoSOrxnZctsrRHpu/2GKEe1a2lavREiA724ixivjUWlPcl/ujuSe1R5yxZRI8MWHKtnT7rZSHpBb3Gnny2zu0b9rdn2G6Aa1c6lafVGiAycPcUhZw1v4sphk+UDqyYXXUNlEdVykljhmfbUf5avDC17WiNbZneN+lsX7Mm9NK3eCJGxMHvSX9zlZIk4Ohd3XMivCHQdpxX37WmtbJktNUL2y7SP9mZZa1RjT9PqjRAZaHtKIl4bi3qLDiHxc1knpUYL40UcglzE6cLxxd262TK7a9Tfujl74sjTtHojRAYoHV15fq01ewrryzopy6J0UXkRdSQ5g2Mde0J2afxZ2TI7a9TfumBP7qVp9UaIDNCduzJVZnMZoxg2QjJ84d5ce258oCWSAq07d7qpJkHDe262zJaars320X5DtG94uZCm1RshMvRYpjGQC0EeHjxajwsZHH52n3tyRYgM2ZMxkAtBHh48Xj1s7Y0QGbInYyAXgjw8eLx6Vc12DMaEyJA9GQO5EOThwePVqLYdgzEhMpQtkyAILLTbE0EQBDbIngiCQArZE0EQSCF7IggCKWRPBEEgheyJIAikkD0RBIEUsieCIJBC9kQQBFL+D42mrzc2iozFAAAAAElFTkSuQmCC" alt="" />
关系除法
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,得到的结果如下
aaarticlea/png;base64,iVBORw0KGgoAAAANSUhEUgAAAQ8AAABhCAIAAABHxLP3AAAIiElEQVR4nO2d22/bVBzHz5/Td6TI5AEBYqgIJAQTD0jbw5IqoCEeeOBtmjoQEksyUSRQmVSkCWkCNHpjSsU0NnYRlz1sWZM2bMsuWbe2W1uYe0napimHB9upk9jOzT7H/uX70U+V45z4d7727xs78TkNOzN6FoFAtBLszOhZDgBogd51y9ramuwuuAa0iMkOt1AAWsRk7123rK6uyu6Ca0CLmOxwCwWgRUx2uIUC0CIme5tu+W93+a8vFs8fXs2d6bZf+aFweCgv5lVWqKrqxmZ8AbSIyd6eW/65cWrl2oc769eeXDyk3r3UVb9aqfvGNu655dmzZ25sRgj5oTDTsVTvUy2mbjMWm9LXTsUa15kQo2UqZr0rnbPrbtnZqWxulZ1jefr7pUsHdtTv1JmjK38euX/6ncIfY01fZRuzJxTlxIyYVzVEpbK7+ORpK6p9EZMD0Ult+ecoez4xGxAt5oM1OcC05T0tuYQiR8tElCnRgcZC0rKXNrftQndLeafi0EiLx+c/3v73h62FI1tPTqrTR0pzR+98+5pFy5mkor91DExoD5VkPGp6WH1rUZLZmjVKfKa2/UxSqW9jvKq7qFR25xcWW1EtOmaSijIQMdRGJuoa5OKKEp8JkJbqwZqMaAWwF41rXNVisyezCUVJ5Gr7VpPdYZttuOXJ70Mb9z4vFd7fnPuo9OBg6c7+9KcvNjaLGD3LJhQWndQK3ejrZPVZo7umXaatMbffa1O3pttd6esKY4YfJgZYXT3ZH2OfajF6m00opre5XFxh9dJc12K1J3WrOO5Jh20abinvFEtbdvFs7vr8ucOLvw2s3T5Qyr+xee/d4q39Nz55Lnfhp8bGipLMaMvZpMKi49lkzZq65azppMEYs2xvt4UuorK7++jxgrNqOVEjcDLClHi2+mzdwyBoMV0RNBw1CzluarHYk7Px2mpjSrIxu8M2m7tlJTs+N3awNPdN8e5bpduv3PjqhUxy3+yJV/NXJy3bt+2Wuv0It1i7xdoqwdFiEeMRFpnwTIvT+45131p1y3Z5Z6O4aRmPf3lva+HUWiZUyu1LD79089zp1ZXltX+W7dpHGIuMb24UNzNxhUUmNjKJkJLI6M9ORJhyPGM8qyQyxYlqez3M7fXlxldZp249KpXdh3OPHVRLC7P88aguNpMIGXsgqFqMyMSjhpC9w+qJFss96dg3LbvDNnW3bG2X1zdKlrHw6wfbTz8r3n79+tcvZy/8aNdsLzLxkPHJakx7GIpPV58di2jPhSIRff1ee8ZC8Wlz++py46u6i0plt/DwkYNqaWHeG9oO3ChNH6+5gAgdzwZGS+PBMg4lY+zQmJfHxWpPOvdNy+6wzeZuuXvxy79Pv33z5Ju3ro7LPwAuRcAqLKBa5B6XTvekQwPDLVvltfVi70SlUnlQmPOj6ul4KBRP09Ai97h0uicdGuhu2dzaXl3b6J3YqVTuP3hIQzW0uJvdoYHullkAQDOqI196jnK5LLsLrgEtYrL37oh9jNv1J3TGIFMCFeZP4BY/ggrzJ3CLH0GF+RO4xY+gwvxJ8N2Si7FBxgZZLOdat6QT0AqLhXnjjD9oEZO9JbdMjTLNLWzYnVm+fiBwFZYf4oxxRsItcrV0nL0FtywPhQcZG47FhhkbDA8td9VR/xC4CuOc8zwPk3AL57K1dJS9uVvyV8JskIWv5KsL3XXTL6DCJEPRLfmh6ilFP8kQuRhDhUmGoFuMz/fmoPFZHxUmGXpu0T7fV6++tIsxNmrxb6ACBypMMvTcEhus/WSvXYwNWv3XtKARuArTv8lhnDFedwCgRUx23J2kALSIyQ63UABaxGSHWygALWKywy0UgBYx2XW3qAAAVVVxbrHEeb8EC2gRkx1uoQC0iMkOt1AAWsRkh1soAC1issMtFIAWMdnbHFVJZQAyl31UOgZzJyVmb80to1PcGGFJYwAyD2CFYe6k9Oytu8U00YUEgaswzmWP23UXemOQ66/ESIzV10CFSYasWzSTmJeDDypMMsTdgpnG0oFbpGZvwy34LxbygVukZsc3yIEBcyelZ8fdSQpAi5jscAsFoEVMdriFAtAiJjvcQgFoEZMdcycBqKG5W3oQVVU54zTC+RgHC7la4BZr4BZ/Arf4EbjFn8AtfgRu8Sdwix8R4pZyfqSQOlZ0Z2v9S6nUfL4PbpGWvRW3TMUoDtoX4Jb12HzqWJH3qZdThcuxsr6+T708oq5rf6uNHVbWGaZuTZDdQm7upG4VOpPAqnjvlmJaOxX0qZdHltIjxmmhG7ewcn6kkO4PvFtozp0kNu7YjOduqZ4HtKLvX9IvybpyC1+Pze+dpgLrFs4pjkEmNhffjNdu2Stro+gXjxXS/d26hVddB7cIzw63iHNLjSU6dovVSrhFTHZciQl0C+Prsfl0DOcWzjlFt+BTfudR97lFX19MpwqpEXW9+h2A4SL92zN8bgmyWzi+Qe40zN+JmYre7KJUIaVF45rU0mLdQ8apfSeGuZNBQdz9Fi/OVwF3iwNBvztJE9zL9ydwix/BODF/Arf4EbjFnwTALd3NNgOADs3d0oOoxM4tsvvgpha5VWEP3EIhqGmRWxX2wC0UgpoWuVVhD9xCIahpkVsV9sAtnobA+y2UtMitCnt6+7+Ge1xhQudOUtIiBG9/d1L//ZbBGI2BYt5XmNi5k5S0eIyA353knNYAfs8rTPDcSUpaBOD5b4M1PgwyXleY6LmTlLQIAG5pC/EV5u3cSUpaBCDALbgS66rCPJ07SUmLAPArrW0h+lpfX+/Z3ElKWgSA351sC7HfI3k/d5KSFo/B3Mm2EXePwsVtSr/fIkaL3KqwB27xNHAvvyMtcqvCHriFQlDTIrcq7IFbKAQ1LXKrwh7MnQSghuZuAQA0BW4BoFXgFgBaBW4BoFX+B5ohsC8IpFR5AAAAAElFTkSuQmCC" alt="" />
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;
格式化聚合函数
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;
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;
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;
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;
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
CASE orderyear
WHEN 2009 THEN y2009
WHEN 2010 THEN y2010
WHEN 2011 THEN y2011
END AS qty
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
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