宽表加工优化案例
1、概述
客户某集市环境提出部分作业运行慢，而且越跑越慢。接到反馈后对其运行的SQL脚本进行分析，并协助进行优化调整，以下是其中一个优化案例。
原始SQL如下：
CREATE TABLE if not exists test.CD_U_MD_SUM_DP_CUST_44_old(
DATA_DATE VARCHAR(8),
PID VARCHAR(16) ,
ORG_ID VARCHAR(12) ,
CCY_COD VARCHAR(3) ,
DP_BAL DECIMAL(18,2) ,
DP_BAL_MAVER DECIMAL(18,2) ,
DP_BAL_SAVER DECIMAL(18,2) ,
DP_BAL_YAVER DECIMAL(18,2) ,
DPSA_BAL DECIMAL(18,2) ,
DPSA_BALMAVER DECIMAL(18,2) ,
DPSA_BAL_SAVER DECIMAL(18,2) ,
DPSA_BAL_YAVER DECIMAL(18,2) ,
AIO_BAL DECIMAL(18,2) ,
DCARD_SA_BAL DECIMAL(18,2) ,
DCCARD_SA_BAL DECIMAL(18,2) ,
TD_BAL DECIMAL(18,2) ,
TD_BAL_MAVER DECIMAL(18,2) ,
TD_BAL_SAVER DECIMAL(18,2) ,
TD_BAL_YAVER DECIMAL(18,2) ,
TD_AIO_BAL DECIMAL(18,2) ,
DCARD_TD_BAL DECIMAL(18,2) ,
DCCARD_TD_BAL DECIMAL(18,2) ,
NTDP_BAL DECIMAL(18,2) ,
NTDP_BAL_MAVER DECIMAL(18,2) ,
NTDP_BAL_SAVER DECIMAL(18,2) ,
NTDP_BAL_YAVER DECIMAL(18,2) ,
NTDP_BAL_1 DECIMAL(18,2) ,
NTDP_BAL_7 DECIMAL(18,2) ,
DCARD_NTDP_BAL DECIMAL(18,2) ,
DCCARD_NTDP_BAL DECIMAL(18,2) ，
CDP_BAL DECIMAL(18,2) ,
PSBK_BAL DECIMAL(18,2) ,
PSRE_BAL DECIMAL(18,2) ,
DP_BAL_MAX DECIMAL(18,2) ,
DP_BAL_MAX_DATE VARCHAR(8) ) DISTRIBUTED BY('PID')
DELETE FROM test.CD_U_MD_SUM_DP_CUST_44_old WHERE DATA_DATE='20160304';
-- 创建临时视图
CREATE OR REPLACE VIEW test.CD_TV_MD_SUM_CUST_BAL_44 AS
SELECT PID,ORG_ID,CCY_COD FROM CD_U_MD_SUM_CUST_BAL_DAY_44 WHERE DATA_DATE='20160304' AND BSNS_COD ='100000'
UNION ALL
SELECT PID,ORG_ID,CCY_COD FROM CD_U_MD_SUM_CUST_BAL_YEAR_44 WHERE DATA_DATE='20160229' AND BSNS_COD ='100000';
INSERT INTO test.CD_U_MD_SUM_DP_CUST_44_old
SELECT
'20160304',
T1.PID,
T1.ORG_ID,
T1.CCY_COD,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
0,
''
FROM test.CD_TV_MD_SUM_CUST_BAL_44 T1
GROUP BY PID,ORG_ID,CCY_COD;
-- 日余额1
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DP_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='100000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额2
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DPSA_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='110000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额3
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.AIO_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='11000A' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额4
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DCARD_SA_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='110004' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额5
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DCCARD_SA_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='110005' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额6
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.TD_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='1A0000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额7
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.TD_AIO_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='1A000A' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额8
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DCARD_TD_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='1A0004' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额9
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DCCARD_TD_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='1A0005' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额10
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.NTDP_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='170000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额11
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.NTDP_BAL_1 = T2.CUR_BAL
WHERE T2.BSNS_COD='171000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额12
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.NTDP_BAL_7 = T2.CUR_BAL
WHERE T2.BSNS_COD='172000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额13
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DCARD_NTDP_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='170004' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额14
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.DCCARD_NTDP_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='170005' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额15
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_DAY_44 T2
SET T1.CDP_BAL = T2.CUR_BAL
WHERE T2.BSNS_COD='17A000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160304' AND T1.DATA_DATE='20160304';
-- 日余额16
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,(
SELECT
PID,
ORG_ID,
CCY_COD,
SUM(CUR_BAL) PSBK_BAL
FROM CD_U_MD_SUM_CUST_BAL_DAY_44
WHERE BSNS_COD IN(
'111001','111003','112001','112003',
'11A001','11A003','11B001','11B003','121003','122003',
'123003','124003','125003','126003','127003','128003',
'12A003','12B003','12C003','12D003','12E003','12F003',
'12G003','131001','131003','132001','132003','133001',
'133003','13A001','13A003','13B001','13B003','13C001',
'13C003','141003','142003','143003','14A003','14B003',
'14C003','171003','172003','173003','174003','17A003',
'17B003','181003','18A003'
) AND DATA_DATE='20160304'
GROUP BY PID,ORG_ID,CCY_COD
) T2
SET T1.PSBK_BAL = T2.PSBK_BAL
WHERE T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T1.DATA_DATE='20160304';
-- 月余额1
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_MTH_44 T2
SET T1.DP_BAL_MAVER = T2.BAL1_MAVER
WHERE T2.BSNS_COD='100000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 月余额2
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_MTH_44 T2
SET T1.DPSA_BALMAVER = T2.BAL1_MAVER
WHERE T2.BSNS_COD='110000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 月余额3
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_MTH_44 T2
SET T1.TD_BAL_MAVER = T2.BAL1_MAVER
WHERE T2.BSNS_COD='1A0000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 月余额4
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_MTH_44 T2
SET T1.NTDP_BAL_MAVER = T2.BAL1_MAVER
WHERE T2.BSNS_COD='170000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 季余额1
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_SEAN_44 T2
SET T1.DP_BAL_SAVER = T2.BAL1_SAVER
WHERE T2.BSNS_COD='100000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 季余额2
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_SEAN_44 T2
SET T1.DPSA_BAL_SAVER = T2.BAL1_SAVER
WHERE T2.BSNS_COD='110000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 季余额3
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_SEAN_44 T2
SET T1.TD_BAL_SAVER = T2.BAL1_SAVER
WHERE T2.BSNS_COD='1A0000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 季余额4
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_SEAN_44 T2
SET T1.NTDP_BAL_SAVER = T2.BAL1_SAVER
WHERE T2.BSNS_COD='170000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 年余额1
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_YEAR_44 T2
SET T1.DP_BAL_YAVER = T2.BAL2_YAVER
WHERE T2.BSNS_COD='100000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 年余额2
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_YEAR_44 T2
SET T1.DPSA_BAL_YAVER = T2.BAL2_YAVER
WHERE T2.BSNS_COD='110000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 年余额3
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_YEAR_44 T2
SET T1.TD_BAL_YAVER = T2.BAL2_YAVER
WHERE T2.BSNS_COD='1A0000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
-- 年余额4
UPDATE test.CD_U_MD_SUM_DP_CUST_44_old T1,CD_U_MD_SUM_CUST_BAL_YEAR_44 T2
SET T1.NTDP_BAL_YAVER = T2.BAL2_YAVER
WHERE T2.BSNS_COD='170000' AND T1.PID = T2.PID
AND T1.ORG_ID = T2.ORG_ID AND T1.CCY_COD = T2.CCY_COD
AND T2.DATA_DATE = '20160229' AND T1.DATA_DATE='20160304';
二、分析过程
常规分析：
目标表：
CD_U_MD_SUM_DP_CUST_44
源表：
CD_U_MD_SUM_CUST_BAL_DAY_44
CD_U_MD_SUM_CUST_BAL_YEAR_44
CD_U_MD_SUM_CUST_BAL_MTH_44
CD_U_MD_SUM_CUST_BAL_SEAN_44
所有源表均为hash分布表，分布字段均为客户号PID，查询各源表在所有节点上分布均衡；分析过程中的每条语句，都比较简单，不需要改写。
加工逻辑分析：
从SQL改写的角度看，该脚本确实无优化空间；我们再从整体作业的加工逻辑进行分析：
1、脚本首先确保目标表存在，并清理当日加工数据（避免作业重跑数据翻倍），目标表为指标宽表；
2、目标表中插入客户、机构、币种维度及指标值，初始化指标值全部为0（类似于制作报表时，先把表画好，再去填写数据）；
3、对28个指标字段逐个进行更新，分别关联4张表之一（DAY、MTH、SEAN、YEAR）进行更新。
通过语句结合GBase执行逻辑，以上加工过程存在的性能问题：
1、目标表插入“表头”部分时，大量指标值均为0，insert执行时select部分常量也是需要物化并网络发送的（注：insert执行2阶段，第一阶段，select into server，即物化、组织DC并发送到同组所有节点的临时表；第二阶段，将临时表数据insert到目标表），如果我们给指标字段均设定默认值为0，select部分仅选择日期、客户、机构、币种四个维度，至少可以节省insert第一阶段31个指标字段的物化等开销；
2、目标表update，分别与DAY、MTH、SEAN、YEAR表进行关联，共28次；且每次关联除BSNS_COD取不同值，其他条件均一致，可直接将相同关联表的数据先拼接为宽表，再与目标表关联更新，这样更新次数就会减少很多（每个源表一次，需4次，但每生成一个宽表需额外进行一次group by），将原来28次join优化为4次join和4次group by；
3、目标表的数据存储周期是1个月，非只有当日数据，而目前现场update采用的是整列update，即被更新字段的历史数据DC也会被重写，因此随着目标表数据量增加，update的耗时会越来越长，多次update更会放大多倍（额...快速update当然更不适合了），因此一般情况尽量避免对目标表的update，目标表一般只进行insert与delete操作；
4、通常来讲我们先生成一张临时表，用来update，再将该临时表数据插入目标表，但是结合上述指标生成逻辑，可以通过多次join直接将目标宽表拼接出来，所有update均可省略，即直接insert目标表，select部分为5表（子查询）关联，而且就update的执行计划说，update直接下发到node上主副本同时join，所以，原脚本总共是要做56次join和一次group by（脚本中原有）的，全部改为insert后，一共是4次join和4次group by。
三、优化策略
通过上述分析，我们做出如下优化：
1、去除视图使用，直接建立临时表替代
不得不承认目前GBase版本视图在条件内推、子查询等方面执行性能还不尽如人意，建议尽量少使用视图，使用加工临时表代替。
drop table if exists test.CD_U_MD_SUM_DP_CUST_44_tmp;
CREATE TABLE test.CD_U_MD_SUM_DP_CUST_44_tmp(
PID VARCHAR(16),
ORG_ID VARCHAR(12),
CCY_COD VARCHAR(3)'
)distributed by('PID') nolock;
insert into test.CD_U_MD_SUM_DP_CUST_44_tmp ELECT PID,ORG_ID,CCY_COD FROM CD_U_MD_SUM_CUST_BAL_DAY_44 WHERE DATA_DATE='20160304' AND BSNS_COD ='100000' group by PID,ORG_ID,CCY_COD;
insert into test.CD_U_MD_SUM_DP_CUST_44_tmp
select a.PID,a.ORG_ID,a.CCY_COD from (SELECT PID,ORG_ID,CCY_COD FROM CD_U_MD_SUM_CUST_BAL_YEAR_44 WHERE DATA_DATE='20160229' AND BSNS_COD ='100000' group by PID,ORG_ID,CCY_COD) a where not exists(select 1 from test.CD_U_MD_SUM_DP_CUST_44_tmp b where a.PID=b.PID and a.ORG_ID=b.ORG_ID and a.CCY_COD=b.CCY_COD);
原视图逻辑为2表union all，后续对视图group by，我们直接将2表的数据做group by后插入到临时表，避免2表group by结果有重复，第二张表结果insert时需判断是否与已有数据重复。
2、将原脚本中insert“表头”、update指标部分，全部合并为一条insert语句：
insert into test.CD_U_MD_SUM_DP_CUST_44_new(
DATA_DATE ,
PID ,
ORG_ID ,
CCY_COD ,
DP_BAL ,
DP_BAL_MAVER ,
DP_BAL_SAVER ,
DP_BAL_YAVER ,
DPSA_BAL ,
DPSA_BALMAVER ,
DPSA_BAL_SAVER ,
DPSA_BAL_YAVER ,
AIO_BAL ,
DCARD_SA_BAL ,
DCCARD_SA_BAL ,
TD_BAL ,
TD_BAL_MAVER ,
TD_BAL_SAVER ,
TD_BAL_YAVER ,
TD_AIO_BAL ,
DCARD_TD_BAL ,
DCCARD_TD_BAL ,
NTDP_BAL ,
NTDP_BAL_MAVER ,
NTDP_BAL_SAVER ,
NTDP_BAL_YAVER ,
NTDP_BAL_1 ,
NTDP_BAL_7 ,
DCARD_NTDP_BAL ,
DCCARD_NTDP_BAL ,
CDP_BAL,
PSBK_BAL,
PSRE_BAL,
DP_BAL_MAX,
DP_BAL_MAX_DATE
)
select
'20160304',
TMP.PID,
TMP.ORG_ID,
TMP.CCY_COD,
IFNULL(DAY.DP_BAL, 0),
IFNULL(MTH.DP_BAL_MAVER , 0),
IFNULL(SEAN.DP_BAL_SAVER , 0),
IFNULL(YEAR.DP_BAL_YAVER , 0),
IFNULL(DAY.DPSA_BAL, 0),
IFNULL(MTH.DPSA_BALMAVER , 0),
IFNULL(SEAN.DPSA_BAL_SAVER , 0),
IFNULL(YEAR.DPSA_BAL_YAVER , 0),
IFNULL(DAY.AIO_BAL, 0),
IFNULL(DAY.DCARD_SA_BAL, 0),
IFNULL(DAY.DCCARD_SA_BAL, 0),
IFNULL(DAY.TD_BAL, 0),
IFNULL(MTH.TD_BAL_MAVER , 0),
IFNULL(SEAN.TD_BAL_SAVER , 0),
IFNULL(YEAR.TD_BAL_YAVER , 0),
IFNULL(DAY.TD_AIO_BAL, 0),
IFNULL(DAY.DCARD_TD_BAL, 0),
IFNULL(DAY.DCCARD_TD_BAL, 0),
IFNULL(DAY.NTDP_BAL, 0),
IFNULL(MTH.NTDP_BAL_MAVER , 0),
IFNULL(SEAN.NTDP_BAL_SAVER , 0),
IFNULL(YEAR.NTDP_BAL_YAVER , 0),
IFNULL(DAY.NTDP_BAL_1, 0),
IFNULL(DAY.NTDP_BAL_7, 0),
IFNULL(DAY.DCARD_NTDP_BAL, 0),
IFNULL(DAY.DCCARD_NTDP_BAL, 0),
IFNULL(DAY.CDP_BAL, 0),
IFNULL(DAY.PSBK_BAL, 0),
0,
0,
''
FROM test.CD_U_MD_SUM_DP_CUST_44_tmp TMP
LEFT JOIN (
SELECT PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DP_BAL,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DPSA_BAL,
SUM(CASE WHEN BSNS_COD='11000A' THEN IFNULL(CUR_BAL,0) ELSE 0 END) AIO_BAL,
SUM(CASE WHEN BSNS_COD='110004' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCARD_SA_BAL,
SUM(CASE WHEN BSNS_COD='110005' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCCARD_SA_BAL,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) TD_BAL,
SUM(CASE WHEN BSNS_COD='1A000A' THEN IFNULL(CUR_BAL,0) ELSE 0 END) TD_AIO_BAL,
SUM(CASE WHEN BSNS_COD='1A0004' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCARD_TD_BAL,
SUM(CASE WHEN BSNS_COD='1A0005' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCCARD_TD_BAL,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) NTDP_BAL,
SUM(CASE WHEN BSNS_COD='171000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) NTDP_BAL_1,
SUM(CASE WHEN BSNS_COD='172000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) NTDP_BAL_7,
SUM(CASE WHEN BSNS_COD='170005' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCCARD_NTDP_BAL,
SUM(CASE WHEN BSNS_COD='170004' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCARD_NTDP_BAL,
SUM(CASE WHEN BSNS_COD='17A000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) CDP_BAL,
SUM(CASE WHEN BSNS_COD IN('111001','111003','112001','112003','11A001','11A003','11B001','11B003','121003','122003','123003','124003','125003','126003','127003','128003','12A003','12B003','12C003','12D003','12E003','12F003','12G003','131001','131003','132001','132003','133001','133003','13A001','13A003','13B001','13B003','13C001','13C003','141003','142003','143003','14A003','14B003','14C003','171003','172003','173003','174003','17A003','17B003','181003','18A003') THEN IFNULL(CUR_BAL,0) ELSE 0 END) PSBK_BAL
FROM CD_U_MD_SUM_CUST_BAL_DAY_44
WHERE DATA_DATE = '20160304' AND BSNS_COD IN('111001','111003','112001','112003','11A001','11A003','11B001','11B003','121003','122003','123003','124003','125003','126003','127003','128003','12A003','12B003','12C003','12D003','12E003','12F003','12G003','131001','131003','132001','132003','133001','133003','13A001','13A003','13B001','13B003','13C001','13C003','141003','142003','143003','14A003','14B003','14C003','171003','172003','173003','174003','17A003','17B003','181003','18A003','100000','110000','11000A','110004','110005','1A0000','1A000A','1A0004','1A0005','170000','171000','172000','170005','170004','17A000' )
GROUP BY PID,ORG_ID,CCY_COD
) DAY ON TMP.PID=DAY.PID AND TMP.ORG_ID=DAY.ORG_ID AND TMP.CCY_COD=DAY.CCY_COD
LEFT JOIN (
SELECT
PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) DP_BAL_MAVER,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) DPSA_BALMAVER,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) TD_BAL_MAVER,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) NTDP_BAL_MAVER
FROM CD_U_MD_SUM_CUST_BAL_MTH_44
WHERE DATA_DATE = '20160229'
AND BSNS_COD IN('100000','110000','1A0000','170000')
GROUP BY PID,ORG_ID,CCY_COD
) MTH ON TMP.PID=MTH.PID AND TMP.ORG_ID=MTH.ORG_ID AND TMP.CCY_COD=MTH.CCY_COD
LEFT JOIN (
SELECT
PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) DP_BAL_SAVER,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) DPSA_BAL_SAVER,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) TD_BAL_SAVER,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) NTDP_BAL_SAVER
FROM CD_U_MD_SUM_CUST_BAL_SEAN_44
WHERE DATA_DATE = '20151231' AND BSNS_COD IN('100000','110000','1A0000','170000')
GROUP BY PID,ORG_ID,CCY_COD
) SEAN ON TMP.PID=SEAN.PID AND TMP.ORG_ID=SEAN.ORG_ID AND TMP.CCY_COD=SEAN.CCY_COD
LEFT JOIN (
SELECT
PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) DP_BAL_YAVER,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) DPSA_BAL_YAVER,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) TD_BAL_YAVER,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) NTDP_BAL_YAVER
FROM
CD_U_MD_SUM_CUST_BAL_YEAR_44
WHERE DATA_DATE = '20151231'
AND BSNS_COD IN(
'100000',
'110000',
'1A0000',
'170000'
)GROUP BY PID,ORG_ID,CCY_COD
) YEAR ON TMP.PID=YEAR.PID AND TMP.ORG_ID=YEAR.ORG_ID AND TMP.CCY_COD=YEAR.CCY_COD;
将原update的4个被关联表group by运算形成4个宽表子查询，left join后生成最终结果集。
需要注意4点：
1、原始update 的join都是inner join，因为即使关联不到，insert时已经写入默认值为0，因此须调整为left join；
2、一行对多行问题，update逻辑中，出现这种情况直接报错，而改为join insert后，不会报错，而是将值求和（和客户确认数据特征，源表中不会出现DATA_DATE、PID、ORG_ID、CYC_COD、BSNS_COD都一样的重复值）；
3、指标值默认为0，但left join未匹配上的指标值是NULL，因此需要使用IFNULL使之为0；
4、Update可以将被关联表的NULL值更新至目标表，但使用IFNULL函数后，这类值都变为0（和客户确认数据特征，被关联表指标值是不允许为NULL的，即便有NULL，变为0也是更好的选择）。
优化后脚本如下：
CREATE TABLE if not exists test.CD_U_MD_SUM_DP_CUST_44_new(
DATA_DATE VARCHAR(8),
PID VARCHAR(16),
ORG_ID VARCHAR(12),
CCY_COD VARCHAR(3),
DP_BAL DECIMAL(18,2),
DP_BAL_MAVER DECIMAL(18,2),
DP_BAL_SAVER DECIMAL(18,2),
DP_BAL_YAVER DECIMAL(18,2),
DPSA_BAL DECIMAL(18,2),
DPSA_BALMAVER DECIMAL(18,2),
DPSA_BAL_SAVER DECIMAL(18,2),
DPSA_BAL_YAVER DECIMAL(18,2),
AIO_BAL DECIMAL(18,2),
DCARD_SA_BAL DECIMAL(18,2),
DCCARD_SA_BAL DECIMAL(18,2),
TD_BAL DECIMAL(18,2),
TD_BAL_MAVER DECIMAL(18,2),
TD_BAL_SAVER DECIMAL(18,2),
TD_BAL_YAVER DECIMAL(18,2),
TD_AIO_BAL DECIMAL(18,2),
DCARD_TD_BAL DECIMAL(18,2),
DCCARD_TD_BAL DECIMAL(18,2),
NTDP_BAL DECIMAL(18,2),
NTDP_BAL_MAVER DECIMAL(18,2),
NTDP_BAL_SAVER DECIMAL(18,2),
NTDP_BAL_YAVER DECIMAL(18,2),
NTDP_BAL_1 DECIMAL(18,2),
NTDP_BAL_7 DECIMAL(18,2),
DCARD_NTDP_BAL DECIMAL(18,2),
DCCARD_NTDP_BAL DECIMAL(18,2),
CDP_BAL DECIMAL(18,2),
PSBK_BAL DECIMAL(18,2),
PSRE_BAL DECIMAL(18,2),
DP_BAL_MAX DECIMAL(18,2),
DP_BAL_MAX_DATE VARCHAR(8))DISTRIBUTED BY('PID');
DELETE FROM test.CD_U_MD_SUM_DP_CUST_44_new WHERE DATA_DATE='20160304';
drop table if exists test.CD_U_MD_SUM_DP_CUST_44_tmp;
CREATE TABLE test.CD_U_MD_SUM_DP_CUST_44_tmp(
PID VARCHAR(16) COMMENT '客户号',
ORG_ID VARCHAR(12) COMMENT '机构号',
CCY_COD VARCHAR(3) COMMENT '货币代码'
)distributed by('PID') nolock;
insert into test.CD_U_MD_SUM_DP_CUST_44_tmp SELECT PID,ORG_ID,CCY_COD FROM CD_U_MD_SUM_CUST_BAL_DAY_44 WHERE DATA_DATE='20160304' AND BSNS_COD ='100000' group by PID,ORG_ID,CCY_COD;
insert into test.CD_U_MD_SUM_DP_CUST_44_tmp
select a.PID,a.ORG_ID,a.CCY_COD from (SELECT PID,ORG_ID,CCY_COD FROM CD_U_MD_SUM_CUST_BAL_YEAR_44 WHERE DATA_DATE='20160229' AND BSNS_COD ='100000' group by PID,ORG_ID,CCY_COD) a where not exists(select 1 from test.CD_U_MD_SUM_DP_CUST_44_tmp b where a.PID=b.PID and a.ORG_ID=b.ORG_ID and a.CCY_COD=b.CCY_COD);
insert into test.CD_U_MD_SUM_DP_CUST_44_new(
DATA_DATE ,
PID ,
ORG_ID ,
CCY_COD ,
DP_BAL ,
DP_BAL_MAVER ,
DP_BAL_SAVER ,
DP_BAL_YAVER ,
DPSA_BAL ,
DPSA_BALMAVER ,
DPSA_BAL_SAVER ,
DPSA_BAL_YAVER ,
AIO_BAL ,
DCARD_SA_BAL ,
DCCARD_SA_BAL ,
TD_BAL ,
TD_BAL_MAVER ,
TD_BAL_SAVER ,
TD_BAL_YAVER ,
TD_AIO_BAL ,
DCARD_TD_BAL ,
DCCARD_TD_BAL ,
NTDP_BAL ,
NTDP_BAL_MAVER ,
NTDP_BAL_SAVER ,
NTDP_BAL_YAVER ,
NTDP_BAL_1 ,
NTDP_BAL_7 ,
DCARD_NTDP_BAL ,
DCCARD_NTDP_BAL ,
CDP_BAL,
PSBK_BAL,
PSRE_BAL,
DP_BAL_MAX,
DP_BAL_MAX_DATE
)
select
'20160304',
TMP.PID,
TMP.ORG_ID,
TMP.CCY_COD,
IFNULL(DAY.DP_BAL,0),
IFNULL(MTH.DP_BAL_MAVER,0),
IFNULL(SEAN.DP_BAL_SAVER,0),
IFNULL(YEAR.DP_BAL_YAVER,0),
IFNULL(DAY.DPSA_BAL,0),
FNULL(MTH.DPSA_BALMAVER,0),
IFNULL(SEAN.DPSA_BAL_SAVER,0),
IFNULL(YEAR.DPSA_BAL_YAVER,0),
IFNULL(DAY.AIO_BAL,0),
IFNULL(DAY.DCARD_SA_BAL,0),
IFNULL(DAY.DCCARD_SA_BAL,0),
IFNULL(DAY.TD_BAL,0),
IFNULL(MTH.TD_BAL_MAVER,0),
IFNULL(SEAN.TD_BAL_SAVER,0),
IFNULL(YEAR.TD_BAL_YAVER,0),
IFNULL(DAY.TD_AIO_BAL,0),
IFNULL(DAY.DCARD_TD_BAL,0),
IFNULL(DAY.DCCARD_TD_BAL,0),
IFNULL(DAY.NTDP_BAL,0),
IFNULL(MTH.NTDP_BAL_MAVER,0),
IFNULL(SEAN.NTDP_BAL_SAVER,0),
IFNULL(YEAR.NTDP_BAL_YAVER,0),
IFNULL(DAY.NTDP_BAL_1,0),
IFNULL(DAY.NTDP_BAL_7,0),
IFNULL(DAY.DCARD_NTDP_BAL,0),
IFNULL(DAY.DCCARD_NTDP_BAL,0),
IFNULL(DAY.CDP_BAL,0),
IFNULL(DAY.PSBK_BAL,0),
0,
0,
''
FROM test.CD_U_MD_SUM_DP_CUST_44_tmp TMP
LEFT JOIN (
SELECT PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DP_BAL,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DPSA_BAL,
SUM(CASE WHEN BSNS_COD='11000A' THEN IFNULL(CUR_BAL,0) ELSE 0 END) AIO_BAL,
SUM(CASE WHEN BSNS_COD='110004' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCARD_SA_BAL,
SUM(CASE WHEN BSNS_COD='110005' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCCARD_SA_BAL,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) TD_BAL,
SUM(CASE WHEN BSNS_COD='1A000A' THEN IFNULL(CUR_BAL,0) ELSE 0 END) TD_AIO_BAL,
SUM(CASE WHEN BSNS_COD='1A0004' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCARD_TD_BAL,
SUM(CASE WHEN BSNS_COD='1A0005' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCCARD_TD_BAL,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) NTDP_BAL,
SUM(CASE WHEN BSNS_COD='171000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) NTDP_BAL_1,
SUM(CASE WHEN BSNS_COD='172000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) NTDP_BAL_7,
SUM(CASE WHEN BSNS_COD='170005' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCCARD_NTDP_BAL,
SUM(CASE WHEN BSNS_COD='170004' THEN IFNULL(CUR_BAL,0) ELSE 0 END) DCARD_NTDP_BAL,
SUM(CASE WHEN BSNS_COD='17A000' THEN IFNULL(CUR_BAL,0) ELSE 0 END) CDP_BAL,
SUM(CASE WHEN BSNS_COD IN('111001','111003','112001','112003','11A001','11A003','11B001','11B003','121003','122003','123003','124003','125003','126003','127003','128003','12A003','12B003','12C003','12D003','12E003','12F003','12G003','131001','131003','132001','132003','133001','133003','13A001','13A003','13B001','13B003','13C001','13C003','141003','142003','143003','14A003','14B003','14C003','171003','172003','173003','174003','17A003','17B003','181003','18A003') THEN IFNULL(CUR_BAL,0) ELSE 0 END) PSBK_BAL
FROM CD_U_MD_SUM_CUST_BAL_DAY_44
WHERE DATA_DATE = '20160304' AND BSNS_COD IN( '111001',
'111003',
'112001',
'112003',
'11A001',
'11A003',
'11B001',
'11B003',
'121003',
'122003',
'123003',
'124003',
'125003',
'126003',
'127003',
'128003',
'12A003',
'12B003',
'12C003',
'12D003',
'12E003',
'12F003',
'12G003',
'131001',
'131003',
'132001',
'132003',
'133001',
'133003',
'13A001',
'13A003',
'13B001',
'13B003',
'13C001',
'13C003',
'141003',
'142003',
'143003',
'14A003',
'14B003',
'14C003',
'171003',
'172003',
'173003',
'174003',
'17A003',
'17B003',
'181003',
'18A003',
'100000',
'110000',
'11000A',
'110004',
'110005',
'1A0000',
'1A000A',
'1A0004',
'1A0005',
'170000',
'171000',
'172000',
'170005',
'170004',
'17A000'
)
GROUP BY PID,ORG_ID,CCY_COD
) DAY ON TMP.PID=DAY.PID AND TMP.ORG_ID=DAY.ORG_ID AND TMP.CCY_COD=DAY.CCY_COD
LEFT JOIN (
SELECT
PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) DP_BAL_MAVER,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) DPSA_BALMAVER,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) TD_BAL_MAVER,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(BAL1_MAVER,0) ELSE 0 END) NTDP_BAL_MAVER
FROM CD_U_MD_SUM_CUST_BAL_MTH_44
WHERE DATA_DATE = '20160229'
AND BSNS_COD IN(
'100000',
'110000',
'1A0000',
'170000')
GROUP BY PID,ORG_ID,CCY_COD
) MTH ON TMP.PID=MTH.PID AND TMP.ORG_ID=MTH.ORG_ID AND TMP.CCY_COD=MTH.CCY_COD
LEFT JOIN (
SELECT
PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) DP_BAL_SAVER,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) DPSA_BAL_SAVER,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) TD_BAL_SAVER,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(BAL1_SAVER,0) ELSE 0 END) NTDP_BAL_SAVER
FROM CD_U_MD_SUM_CUST_BAL_SEAN_44
WHERE DATA_DATE = '20151231' AND BSNS_COD IN(
'100000',
'110000',
'1A0000',
'170000')
GROUP BY PID,ORG_ID,CCY_COD
) SEAN ON TMP.PID=SEAN.PID AND TMP.ORG_ID=SEAN.ORG_ID AND TMP.CCY_COD=SEAN.CCY_COD
LEFT JOIN (
SELECT
PID,
ORG_ID,
CCY_COD,
SUM(CASE WHEN BSNS_COD='100000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) DP_BAL_YAVER,
SUM(CASE WHEN BSNS_COD='110000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) DPSA_BAL_YAVER,
SUM(CASE WHEN BSNS_COD='1A0000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) TD_BAL_YAVER,
SUM(CASE WHEN BSNS_COD='170000' THEN IFNULL(BAL2_YAVER,0) ELSE 0 END) NTDP_BAL_YAVER
FROM
CD_U_MD_SUM_CUST_BAL_YEAR_44
WHERE DATA_DATE = '20151231'
AND BSNS_COD IN(
'100000',
'110000',
'1A0000',
'170000'
)GROUP BY PID,ORG_ID,CCY_COD
) YEAR ON TMP.PID=YEAR.PID AND TMP.ORG_ID=YEAR.ORG_ID AND TMP.CCY_COD=YEAR.CCY_COD;
四、优化效果
无其它负载情况下，执行当天作业，优化前执行耗时约4分05秒，优化后耗时约1分57秒；负载高的情况下，优化效果更明显，耗时降为优化前1/4~1/3（后台25并发时，优化前耗时20分钟，优化后6分钟；后台60并发作业时，优化前耗时36分钟，优化后约11分钟），通过对比，优化前后数据完全一致。
评论
热门帖子
- 12025-12-01浏览数：182762
- 22023-05-09浏览数：25056
- 42023-09-25浏览数：18525
- 52020-05-11浏览数：17528