GaussDB(DWS)迁移 - teredata兼容 - 函数 - pivot/unpivot改写

举报
譡里个檔 发表于 2021/03/12 17:10:04 2021/03/12
【摘要】 pivot/unpivot是teredata中用来做行列转换的操作，常用于报表展示场景。GaussDB(DWS)当前并不支持 pivot/unpivot 操作，本文尝试从业务改写的方式，在GaussDB(DWS)中实现类似的功能

【概要】 

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是用于将行转换为列的关系运算符，常用报表场景，把相关指标按照列维度展示。

具体改写方式：

  1. 输出列的别名：pivote中的输出列的列名是由FOR..IN后面字句的别名和语法中的聚集函数别名使用‘_’联合构建而成
  2. 输出列值计算：聚集函数+CASE THEN语句，WHEN语句是FOR..IN后面的枚举值，THEN语句是对应的聚集函数的入参
  3. 输出列的顺序：输出的列的别名和输出列值的计算是一 一对应的
  4. 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的反向操作，实现了列转行操作

具体改写方式：

  1. 输出列的别名：FOR后面的列名在前，FOR前面的列名在后（按照括号里面的顺序输出）
  2. 输出列值计算：首列为IN后面括号里面AS之后的部分，后面的列是IN后面括号里面AS之前部分（按照括号里面的顺序输出）
  3. 输出列的顺序：输出的列的别名和输出列值的计算是 一 一 对应的
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;








20210316-093011(WeLinkPC).png

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
EI企业智能 Gauss AP 云数据仓库 GaussDB(DWS)
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入