【概要】
pivot/unpivot是teredata中用来做行列转换的操作，常用于报表展示场景。GaussDB(DWS)当前并不支持 pivot/unpivot 操作，本文尝试从业务改写的方式，在GaussDB(DWS)中实现类似的功能
【预置条件】
DROP TABLE star1;
CREATE TABLE star1
(
country VARCHAR(20),
state VARCHAR(10),
yr INTEGER,
qtr VARCHAR(3),
sales INTEGER,
cogs INTEGER
);
INSERT INTO star1 VALUES('USA','CA',2001,'Q1',30,15);
INSERT INTO star1 VALUES('Canada','ON',2001,'Q2', 10, 0);
INSERT INTO star1 VALUES('Canada','BC',2001,'Q3', 10 ,0);
INSERT INTO star1 VALUES('USA','NY',2001,'Q1',45, 25);
INSERT INTO star1 VALUES('USA','CA',2001,'Q2', 50 ,20);
SELECT * FROM star1;
DROP TABLE star1p;
CREATE TABLE star1p
(
country VARCHAR(20),
state VARCHAR(20),
Q101Sales INTEGER,
Q201Sales INTEGER,
Q301Sales INTEGER,
Q101Cogs INTEGER,
Q201Cogs INTEGER,
Q301Cogs INTEGER
);
INSERT INTO star1p VALUES('Canada','ON',NULL, 10 ,NULL, NULL, 0, NULL);
INSERT INTO star1p VALUES('Canada','BC', NULL, NULL ,10, NULL, NULL, 0);
INSERT INTO star1p VALUES('USA','NY', 45, NULL, NULL, 25 ,NULL, NULL);
INSERT INTO star1p VALUES('USA','CA', 30 ,50, NULL, 15, 20, NULL);
SELECT * FROM star1p;
【pivot改写】
pivot是用于将行转换为列的关系运算符，常用报表场景，把相关指标按照列维度展示
| teredata的pivot语法 |
GausDB(DWS)等价改写语法 |
SELECT *
FROM star1 PIVOT
(
SUM(sales) AS ss1,
SUM(cogs) AS sc
FOR qtr IN
(
'Q1' AS Quarter1,
'Q2' AS Quarter2,
'Q3' AS Quarter3,
'Q4' AS Quarter4
)
)tmp;
|
SELECT *
FROM
(
SELECT
country,
state,
yr,
sum(CASE WHEN qtr = 'Q1' THEN sales END) as "Quarter1_ss1",
sum(CASE WHEN qtr = 'Q1' THEN cogs END) as "Quarter1_sc",
sum(CASE WHEN qtr = 'Q2' THEN sales END) as "Quarter2_ss1",
sum(CASE WHEN qtr = 'Q2' THEN cogs END) as "Quarter2_sc",
sum(CASE WHEN qtr = 'Q3' THEN sales END) as "Quarter3_ss1",
sum(CASE WHEN qtr = 'Q3' THEN cogs END) as "Quarter3_sc",
sum(CASE WHEN qtr = 'Q4' THEN sales END) as "Quarter4_ss1",
sum(CASE WHEN qtr = 'Q4' THEN cogs END) as "Quarter4_sc"
FROM star1
WHERE qtr IN ('Q1', 'Q2', 'Q3', 'Q4')
GROUP BY country, state, yr
) tmp;
|
SELECT *
FROM star1 PIVOT
(
SUM(sales) AS ss1,
SUM(cogs) AS sc
FOR (yr, qtr)
IN (
(2001, 'Q1'),
(2001, 'Q2'),
(2001, 'Q3'),
(2001, 'Q4')
)
)tmp;
|
SELECT *
FROM
(
SELECT
country,
state,
sum(CASE WHEN yr = 2001 AND qtr = 'Q1' THEN sales END) as "2001_Q1_ss1",
sum(CASE WHEN yr = 2001 AND qtr = 'Q1' THEN cogs END) as "2001_Q1_sc",
sum(CASE WHEN yr = 2001 AND qtr = 'Q2' THEN sales END) as "2001_Q2_ss1",
sum(CASE WHEN yr = 2001 AND qtr = 'Q2' THEN cogs END) as "2001_Q2_sc",
sum(CASE WHEN yr = 2001 AND qtr = 'Q3' THEN sales END) as "2001_Q3_ss1",
sum(CASE WHEN yr = 2001 AND qtr = 'Q3' THEN cogs END) as "2001_Q3_sc",
sum(CASE WHEN yr = 2001 AND qtr = 'Q4' THEN sales END) as "2001_Q4_ss1",
sum(CASE WHEN yr = 2001 AND qtr = 'Q4' THEN cogs END) as "2001_Q4_sc"
FROM star1
WHERE (yr, qtr) IN ((2001, 'Q1'),(2001, 'Q2'),(2001, 'Q3'),(2001, 'Q4'))
GROUP BY country, state
) tmp;
|
【unpovit改写】
UNPIVOT是pivot的反向操作，实现了列转行操作
| teredata的pivot语法 |
GausDB(DWS)等价改写语法 |
SELECT *
FROM star1p UNPIVOT EXCLUDE NULLS
(
(sales,cogs) FOR yr_qtr IN
(
(Q101Sales, Q101Cogs) AS 'Q101',
(Q201Sales, Q201Cogs) AS 'Q201',
(Q301Sales, Q301Cogs) AS 'Q301'
)
) tmp;
|
SELECT *
FROM
(
SELECT
*
FROM
(
SELECT
country,
state,
unnest(ARRAY['Q101', 'Q201', 'Q301']) AS yr_qtr,
unnest(ARRAY[Q101Sales, Q201Sales, Q301Sales]) AS sales,
unnest(ARRAY[Q101Cogs, Q201Cogs, Q301Cogs]) AS Cogs
FROM star1p
)
WHERE sales IS NOT NULL AND Cogs IS NOT NULL
)tmp;
|
SELECT *
FROM star1p UNPIVOT INCLUDE NULLS
(
(sales,cogs) FOR yr_qtr IN
(
(Q101Sales, Q101Cogs) AS 'Q101',
(Q201Sales, Q201Cogs) AS 'Q201',
(Q301Sales, Q301Cogs) AS 'Q301'
)
) tmp;
|
SELECT *
FROM
(
SELECT
*
FROM
(
SELECT
country,
state,
unnest(ARRAY['Q101', 'Q201', 'Q301']) AS yr_qtr,
unnest(ARRAY[Q101Sales, Q201Sales, Q301Sales]) AS sales,
unnest(ARRAY[Q101Cogs, Q201Cogs, Q301Cogs]) AS Cogs
FROM star1p
)
)tmp;
|
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
（完）