今天在工作中,进行批量数据处理时,如果数据量很多的情况下,合理的优化SQL代码是可以缩短大量的工作时间的。
首先讲一下union 和union all 是的使用,在大数据使用的情况下,一般是选择union all,因为union进行去重计算,如果数量大的情况下,会影响运行的速度,另外在不使用排序关键词时,union 会进行排序,因此在对性能有要求时,会选择使用union all;
另外就是使用临时表减少where条件中的子查询,也会使得性能提高;
join关联条件来代替子查询,也会一定程度的提高运行的速度。
下面是两个代码的比对分析:
WITH T_GXDK AS(
SELECT T1.DKYE
, T1.BZ
, T1.KHTYBH
FROM T1
WHERE T1.XDYWZL LIKE '%并购贷款%'
AND T1.DKWJFL = '次级'
AND T1.CJRQ = '20230531'
)
SELECT ROUND(IFNULL(SUM(
CASE WHEN T.BZ IN ('CNY','BWB')
THEN T.DKYE
ELSE T.DKYE * T3.ZBBSL / T3.WBSL
END )/ 10000, 0 ), 2 ) AS GXDK
FROM T_GXDK T
LEFT JOIN T3
ON ( T3.WBBZ = T.BZ
AND T3.HLRQ = '20230531'
)
WHERE T.KHTYBH IN (
SELECT KHTYBH FROM A WHERE CJRQ = '20230531' AND SSHY <> '境外' UNION ALL
SELECT KHTYBH FROM B WHERE CJRQ = '20230531' AND GXLX = '母公司' AND GLRLB NOT IN ('自然人','境外机构'))
WITH T_GXDK AS (
SELECT T1.KHTYBH, T1.DKYE, T1.BZ
FROM T1
WHERE T1.XDYWZL LIKE '%并购贷款%'
AND T1.DKWJFL = '次级'
AND T1.CJRQ = '20230331'
),
DKH_LIST AS (
SELECT KHTYBH
FROM A
WHERE CJRQ = '20230331'
AND SSHY <> '境外'
UNION ALL
SELECT KHTYBH
FROM B
WHERE CJRQ = '20230331'
AND GXLX = '母公司'
AND GLRLB NOT IN ('自然人', '境外机构')
)
SELECT ROUND(IFNULL(SUM(
CASE
WHEN T.BZ IN ('CNY', 'BWB') THEN T.DKYE
ELSE T.DKYE * T3.ZBBSL / T3.WBSL
END
) / 10000, 0), 2) AS GXDK
FROM T_GXDK T
left JOIN T3 ON T3.WBBZ = T.BZ
AND T3.HLRQ = '20230331'
JOIN DKH_LIST D ON T.KHTYBH = D.KHTYBH