【概要】
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是用于将行转换为列的关系运算符，常用报表场景，把相关指标按照列维度展示。
具体改写方式：
- 输出列的别名：pivote中的输出列的列名是由FOR..IN后面字句的别名和语法中的聚集函数别名使用‘_’联合构建而成
- 输出列值计算：聚集函数+CASE THEN语句，WHEN语句是FOR..IN后面的枚举值，THEN语句是对应的聚集函数的入参
- 输出列的顺序：输出的列的别名和输出列值的计算是一 一对应的
- GROUP BY语句： GROUP BY后面的列是star1中除了聚集函数和FOR后面列之外的所有其它列
| 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的反向操作，实现了列转行操作
具体改写方式：
- 输出列的别名：FOR后面的列名在前，FOR前面的列名在后（按照括号里面的顺序输出）
- 输出列值计算：首列为IN后面括号里面AS之后的部分，后面的列是IN后面括号里面AS之前部分（按照括号里面的顺序输出）
- 输出列的顺序：输出的列的别名和输出列值的计算是 一 一 对应的
| 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;
|
原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/tech/bigdata/317265.html
