首页 » 技术分享 » DB2行列转换——pivot/unpovit

DB2行列转换——pivot/unpovit

　
评论关闭　764次浏览
文章目录

背景：最近在开发项目时，涉及到基础指标的调整，相关复合指标及下游依赖指标代码均需要全部重跑的问题。

unpivot 行转列

案例：现在有一个水果表，记录了4个季度的销售数量，现在要将每种水果的每个季度的销售情况用多行数据展示。

创建表和数据

create table Fruit(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int);
insert into Fruit values(1,'Apple',1000,2000,3300,5000);
insert into Fruit values(2,'Orange',3000,3000,3200,1500);
insert into Fruit values(3,'Banana',2500,3500,2200,2500);
insert into Fruit values(4,'Grape',1500,2500,1200,3500);
select * from Fruit

进行行转列处理

select id, name ,'Q1' season, (select q1 from etl.fruit where id=t1.id) sales from etl.Fruit t1
union all
select id, name ,'Q2' season, (select q2 from etl.fruit where id=t1.id) sales from etl.Fruit t1
union all
select id, name ,'Q3' season, (select q3 from etl.fruit where id=t1.id) sales from etl.Fruit t1
union all
select id, name ,'Q4' season, (select q4 from etl.fruit where id=t1.id) sales from etl.Fruit t1

介于这样的语法不够简练，故百度了下oracle 11g中的povit/unpovit函数，并在db2中进行验证。不过db2中不支持这个函数

select id,name,season,sales from etl.unpivot (sales for season in (q1, q2, q3, q4) );

于是，就只能换另外一种方法来处理：参照下图，注意这里values(1,2,3),(4,5,6)为行值,各行对应列中的值 字符类型必须一致，否则会报错，

理解上图中的方法后，我们再回过头去将水果销售表由行转化为列

select t1.id,t1.name,t2.season,t2.sales
from etl.fruit t1,
table(values('Q1',t1.q1),('Q2',t1.q2),('Q3',t1.q3),('Q4',t1.q4)) as t2(season,sales);

我们再回到背景提出的问题，ETL.JOB_SEQ为依赖配置表，JOB_NM为任务名，PRE_JOB为前置任务名，实际开发中有上百个任务，若其中一个任务脚本发生调整则依赖此任务的脚本均需重跑，故现在要把依赖此任务的脚本全部找出来并展示到1列下,插入以下案例的实验数据。

CREATE TABLE ETL.JOB_SEQ(JOB_NM VARCHAR(50),PRE_JOB VARCHAR(50));
INSERT INTO ETL.JOB_SEQ VALUES('A1','A0');
INSERT INTO ETL.JOB_SEQ VALUES('A2','A1');
INSERT INTO ETL.JOB_SEQ VALUES('A3','A2');
INSERT INTO ETL.JOB_SEQ VALUES('A3','A0');
INSERT INTO ETL.JOB_SEQ VALUES('A4','A3');
INSERT INTO ETL.JOB_SEQ VALUES('A4','A0');
INSERT INTO ETL.JOB_SEQ VALUES('B0','A0');
INSERT INTO ETL.JOB_SEQ VALUES('B3','A3');

SELECT * FROM ETL.JOB_SEQ ;

从这里可以看出:A1的下游依赖为A2, A2的下游依赖为A3，A3的下游依赖为A4和B3，我们将查询出的层级依赖全部展示到1列下

SELECT   DISTINCT T.JOB_NM
FROM
(
SELECT    NVL(T0.JOB_NM,'NONE') AS JOB_NM0
          ,NVL(T1.JOB_NM,'NONE')AS JOB_NM1
          ,NVL(T2.JOB_NM,'NONE')AS JOB_NM2
          ,NVL(T3.JOB_NM,'NONE')AS JOB_NM3
FROM      ETL.JOB_SEQ T0         --第0层依赖(基础指标)
LEFT JOIN ETL.JOB_SEQ T1         --第1层依赖(复合指标)
ON        T0.JOB_NM=T1.PRE_JOB   
LEFT JOIN ETL.JOB_SEQ T2         --第2层依赖(复合指标)
ON        T1.JOB_NM=T2.PRE_JOB
LEFT JOIN ETL.JOB_SEQ T3         --第3层依赖  (复合指标)
ON        T2.JOB_NM=T3.PRE_JOB
WHERE     T0.JOB_NM='A1'         
),
TABLE(VALUES(JOB_NM0),(JOB_NM1),(JOB_NM2),(JOB_NM3)) AS T(JOB_NM)
WHERE T.JOB_NM<>'NONE';

这便是我们需要达到的目的，当传入调整的指标代码，即可全部找到下游指标码，并对这些指标进行批量重跑。

UPDATE ETL.JOB_SEQ SET JOB_STS='WAITING' WHERE JOB_NM IN (exec_sql);

pivot 列转行 

这个大家在平常的用法当中比较常见，用decode函数即可处理，这里就不做具体说明了。

参考文档：1.http://www.360doc.com/content/11/0315/10/16915_101249598.shtml

                  2.https://www.cnblogs.com/pureEve/p/6559310.html

转载自原文链接, 如需删除请联系管理员。

原文链接：DB2行列转换——pivot/unpovit，转载请注明来源！

赞 0