Spark SQL Join条件内嵌子查询的等价可执行语句转换咨询

由于Spark SQL不支持在JOIN的ON关联条件中直接嵌套子查询，你可以通过提前预计算关联表的目标最大值、先过滤关联表再做关联的方式改写，改写后逻辑和原SQL完全等价，符合Spark SQL语法规范，可正确返回结果。

推荐改写方案（CTE实现，可读性高） #

WITH 
-- 预计算各关联表的最大值
max_b_val AS (SELECT max(FieldX) AS max_b FROM TableB),
max_c_val AS (SELECT max(FieldY) AS max_c FROM TableC),
-- 提前过滤关联表，仅保留符合最大值条件的记录，减少后续关联数据量
filtered_b AS (
    SELECT b.* 
    FROM TableB b
    JOIN max_b_val mb ON b.Field3 = mb.max_b
),
filtered_c AS (
    SELECT c.*
    FROM TableC c
    JOIN max_c_val mc ON c.Field5 = mc.max_c
)
-- 主查询逻辑
SELECT XXXX
FROM TableA A
JOIN filtered_b B 
    ON A.Field1 = B.Field1 
    AND A.Field2 = B.Field2
JOIN filtered_c C
    ON A.Field4 = C.Field4
-- TableD等其余关联表按照相同逻辑提前过滤再关联即可
WHERE A.date = XXXX 
AND A.typeid = 501

简洁版改写方案 #

如果你不需要拆分太多CTE，也可以直接在关联的子查询中完成过滤：

SELECT XXXX
FROM TableA A
JOIN (
    SELECT * FROM TableB 
    WHERE Field3 = (SELECT max(FieldX) FROM TableB)
) B ON A.Field1 = B.Field1 AND A.Field2 = B.Field2
JOIN (
    SELECT * FROM TableC 
    WHERE Field5 = (SELECT max(FieldY) FROM TableC)
) C ON A.Field4 = C.Field4
WHERE A.date = XXXX AND A.typeid = 501

之前你将子查询逻辑移到WHERE子句结果不符合预期，是因为如果先关联全量的关联表再做过滤，中间多表关联过程中会生成大量无效的中间关联结果，最终过滤后和原逻辑先过滤再关联的结果出现偏差，上述两种改写方案都是先过滤关联表再做关联，和原SQL执行逻辑完全一致。

内容的提问来源于stack exchange，提问作者LilMissData