一個(gè)復(fù)雜的標(biāo)量子查詢改寫 left join 的例子斜脂，在 case when 部分多張表與外表關(guān)聯(lián)，并且有聚合触机，標(biāo)量子查詢SQL如下帚戳，要跑600秒：
SELECT
AAZ661 ZFLSH,
'1' ZFXH,
AAE002 TIMESTAMP,
AAE924 JSXM,
SUM(
CASE
WHEN (
SELECT COUNT(DISTINCT B.BAA526)
FROM t1 A, t2 B
WHERE A.AAA345 = B.AAA345
AND A.AAZ661 = Q.AAZ661
AND (B.BAA531 = Q.LTXBZ OR B.BAA531 = '9' OR Q.LTXBZ = '9')
AND B.BAA526 IN ('3', '4')
) = 2
THEN
CASE
WHEN BAA526 = '4' THEN AAE774
WHEN AAE924 NOT IN ('7004', '7008', '7009', '7010', '7011', '7029') AND BAA526 = '5' THEN AAE019 - ZZZHBF
ELSE AAE019
END
ELSE AAE019
END
) JE
FROM Q
GROUP BY AAZ661, AAE002, AAE924;
第一次改寫：
SELECT
Q.AAZ661 ZFLSH,
'1' ZFXH,
Q.AAE002 TIMESTAMP,
Q.AAE924 JSXM,
SUM(
CASE
WHEN V.COUNT_BAA526 = 2
THEN
CASE
WHEN B.BAA526 = '4' THEN Q.AAE774
WHEN Q.AAE924 NOT IN ('7004', '7008', '7009', '7010', '7011', '7029') AND B.BAA526 = '5' THEN Q.AAE019 - Q.ZZZHBF
ELSE Q.AAE019
END
ELSE Q.AAE019
END
) JE
FROM Q
LEFT JOIN (
SELECT
A.AAZ661,B.BAA531
COUNT(DISTINCT B.BAA526) AS COUNT_BAA526
FROM t1 A
JOIN t2 B ON A.AAA345 = B.AAA345
WHERE B.BAA526 IN ('3', '4')
GROUP BY A.AAZ661,B.BAA531
) V ON Q.AAZ661 = V.AAZ661 and(Q.LTXBZ = V.BAA531 OR V.BAA531 = '9' OR Q.LTXBZ = '9')
GROUP BY Q.AAZ661, Q.AAE002, Q.AAE924;
改寫后只要50秒，但是結(jié)果不對(duì)儡首，需要修正（chatgpt幫忙做的修正片任，值得一提的是提問(wèn)方法，一開始問(wèn)他原始 SQL 應(yīng)該怎么改蔬胯，總是給出明顯有錯(cuò)誤的 SQL蚂踊；但后來(lái)我先給出一個(gè)改寫后的 SQL，問(wèn)他改寫的是否正確時(shí)笔宿，他就能給出下面這個(gè)答案了）：
- 子查詢中的 GROUP BY 只需要按 A.AAZ661 進(jìn)行分組犁钟，因?yàn)槲覀冇?jì)算的是 COUNT(DISTINCT B.BAA526)，并且獲取 BAA531 的最大值泼橘。
- 在子查詢中使用 MAX(B.BAA531) 獲取 BAA531 的最大值涝动，這樣在 LEFT JOIN 時(shí)可以正確地進(jìn)行條件判斷。
其實(shí)沒(méi)太理解為什么要取 MAX(B.BAA531) 炬灭，盡管它的結(jié)果是對(duì)的醋粟。按道理來(lái)說(shuō)，原始 SQL 是取了 Q 表每行數(shù)據(jù)中的 Q.AAZ661重归、Q.LTXBZ 代入子查詢米愿，對(duì)滿足條件的記錄計(jì)算 COUNT(DISTINCT B.BAA526) ，那改成 left join 后鼻吮，應(yīng)該對(duì) AAZ661育苟、BAA531 進(jìn)行分組，第一次改寫的 SQL 就是這個(gè)邏輯椎木，但是結(jié)果卻不對(duì)违柏。希望有老師能指點(diǎn)下博烂，不勝感激。
SELECT
Q.AAZ661 AS ZFLSH,
'1' AS ZFXH,
Q.AAE002 AS TIMESTAMP,
Q.AAE924 AS JSXM,
SUM(
CASE
WHEN V.COUNT_BAA526 = 2
THEN
CASE
WHEN Q.BAA526 = '4' THEN Q.AAE774
WHEN Q.AAE924 NOT IN ('7004', '7008', '7009', '7010', '7011', '7029') AND Q.BAA526 = '5' THEN Q.AAE019 - Q.ZZZHBF
ELSE Q.AAE019
END
ELSE Q.AAE019
END
) AS JE
FROM Q
LEFT JOIN (
SELECT
A.AAZ661,
COUNT(DISTINCT B.BAA526) AS COUNT_BAA526,
MAX(B.BAA531) AS BAA531
FROM t1 A
JOIN t2 B ON A.AAA345 = B.AAA345
WHERE B.BAA526 IN ('3', '4')
GROUP BY A.AAZ661
) V ON Q.AAZ661 = V.AAZ661
AND (V.BAA531 = Q.LTXBZ OR V.BAA531 = '9' OR Q.LTXBZ = '9')
GROUP BY Q.AAZ661, Q.AAE002, Q.AAE924;
