PySpark SQL多表关联按最大vers_dt过滤报错解决方案

我有一段多表关联的PySpark SQL语句，需要按its.WCD_VERS表中vers_dt列（字符串格式日期）的最大值（最新日期）过滤数据，且无需将vers_dt列包含在最终结果表中。尝试添加WHERE子句时触发“no viable alternative at input”错误，以下是我使用的SQL代码片段：

FROM its.WKLD_PDN AS B 
INNER JOIN its.PT_STRUC AS C 
ON B.wkld_pdn_id = C.wkld_pdn_id 
AND B.location_cd = C.location_cd 
INNER JOIN its.WCD_VERS AS D 
ON C.dsasemb_wcd_id = SUBSTR(D.wcd_vers_id, 1,6) 
AND C.location_cd = D.location_cd 
INNER JOIN its.WCD_SUB_OP AS E 
ON D.wcd_vers_id = E.wcd_vers_id 
AND D.location_cd = E.location_cd

LEFT OUTER JOIN its.SUB_OP_DET AS F 
ON REPLACE(E.wcd_sub_op_id,'.00','') = REPLACE(F.wcd_sub_op_id,'.00','')
AND E.location_cd = F.location_cd 
--  WHERE D.vers_dt IS NULL             -- 2023.08.15 removed this
--  AND E.trig_wcd_id IS NOT NULL       -- which caused a rewording of this 
WHERE E.trig_wcd_id IS NOT NULL   -- to this
AND SUBSTR(C.dsasemb_wcd_id, 6, 1) = 'W'

要按its.WCD_VERS表的vers_dt最大值过滤，不能直接在WHERE子句中使用聚合条件，需先通过子查询或CTE计算出分组后的最新日期，再关联回主查询过滤。以下是两种可行的实现方式：

方法1：使用CTE（可读性更高） #

WITH latest_wcd_vers AS (
    SELECT 
        SUBSTR(wcd_vers_id, 1,6) AS dsasemb_wcd_id,
        location_cd,
        MAX(vers_dt) AS max_vers_dt
    FROM its.WCD_VERS
    GROUP BY SUBSTR(wcd_vers_id, 1,6), location_cd
)
SELECT 
    -- 此处填写你需要的最终输出字段，不要包含vers_dt
    B.*, C.*, E.*, F.*
FROM its.WKLD_PDN AS B 
INNER JOIN its.PT_STRUC AS C 
ON B.wkld_pdn_id = C.wkld_pdn_id 
AND B.location_cd = C.location_cd 
INNER JOIN latest_wcd_vers AS LW
ON C.dsasemb_wcd_id = LW.dsasemb_wcd_id
AND C.location_cd = LW.location_cd
INNER JOIN its.WCD_VERS AS D 
ON C.dsasemb_wcd_id = SUBSTR(D.wcd_vers_id, 1,6) 
AND C.location_cd = D.location_cd
AND D.vers_dt = LW.max_vers_dt  -- 关联到对应分组的最新日期记录
INNER JOIN its.WCD_SUB_OP AS E 
ON D.wcd_vers_id = E.wcd_vers_id 
AND D.location_cd = E.location_cd
LEFT OUTER JOIN its.SUB_OP_DET AS F 
ON REPLACE(E.wcd_sub_op_id,'.00','') = REPLACE(F.wcd_sub_op_id,'.00','')
AND E.location_cd = F.location_cd 
WHERE E.trig_wcd_id IS NOT NULL
AND SUBSTR(C.dsasemb_wcd_id, 6, 1) = 'W'

方法2：使用子查询直接关联 #

SELECT 
    -- 此处填写你需要的最终输出字段，不要包含vers_dt
    B.*, C.*, E.*, F.*
FROM its.WKLD_PDN AS B 
INNER JOIN its.PT_STRUC AS C 
ON B.wkld_pdn_id = C.wkld_pdn_id 
AND B.location_cd = C.location_cd 
INNER JOIN its.WCD_VERS AS D 
ON C.dsasemb_wcd_id = SUBSTR(D.wcd_vers_id, 1,6) 
AND C.location_cd = D.location_cd
INNER JOIN (
    SELECT 
        SUBSTR(wcd_vers_id, 1,6) AS dsasemb_wcd_id,
        location_cd,
        MAX(vers_dt) AS max_vers_dt
    FROM its.WCD_VERS
    GROUP BY SUBSTR(wcd_vers_id, 1,6), location_cd
) AS LW ON D.location_cd = LW.location_cd 
AND SUBSTR(D.wcd_vers_id,1,6) = LW.dsasemb_wcd_id
AND D.vers_dt = LW.max_vers_dt
INNER JOIN its.WCD_SUB_OP AS E 
ON D.wcd_vers_id = E.wcd_vers_id 
AND D.location_cd = E.location_cd
LEFT OUTER JOIN its.SUB_OP_DET AS F 
ON REPLACE(E.wcd_sub_op_id,'.00','') = REPLACE(F.wcd_sub_op_id,'.00','')
AND E.location_cd = F.location_cd 
WHERE E.trig_wcd_id IS NOT NULL
AND SUBSTR(C.dsasemb_wcd_id, 6, 1) = 'W'

关键注意点 #

1. 若vers_dt的字符串格式不可直接排序（比如dd/MM/yyyy），需先转换为日期类型再取最大值，示例：MAX(to_date(vers_dt, 'dd/MM/yyyy'))。
2. 聚合函数不能直接用在WHERE子句中，必须先通过子查询/CTE计算出最大值，再通过JOIN关联过滤。
3. 最终SELECT语句中不包含vers_dt字段，即可满足需求。

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