Spark SQL中CASE WHEN用NOT IN报错的替代方案咨询

Spark SQL不允许在CASE WHEN子句中使用IN/EXISTS类型的子查询，我们可以通过将子查询转为关联查询的方式改写，具体代码如下：

WITH excluded_ids AS (
    -- 提取需要排除的ID集合，复用原查询中的子查询逻辑
    SELECT DISTINCT id
    FROM all
    WHERE dates = current_date - INTERVAL '2' day
)
SELECT
    t1.id,
    t1.year,
    t1.day,
    t1.month,
    CASE WHEN t2.id IS NULL THEN FALSE ELSE TRUE END AS id_present,
    CASE
        -- 用LEFT JOIN的结果判断ID是否不在排除集合中，替代原NOT IN逻辑
        WHEN ei.id IS NULL AND (t1.click_date = current_date OR (t1.count > 0 AND t1.click_date < current_date - INTERVAL '12' MONTH))
            THEN 'Action1'
        WHEN ei.id IS NULL AND t1.count > 0 AND t1.click_date < current_date - INTERVAL '12' MONTH
            THEN 'Action2'
        ELSE 'Action3'
    END AS actions
FROM dataset1 t1
LEFT JOIN current t2 ON t1.id = t2.id
-- 通过LEFT JOIN关联排除ID集合，ei.id为空则表示当前ID不在排除列表中
LEFT JOIN excluded_ids ei ON t1.id = ei.id

改写说明 #

1. 提取公共子查询为CTE：将原查询中重复的NOT IN子查询转为公共表表达式excluded_ids，既避免重复计算，也符合Spark的查询规范。
2. 用关联查询替代IN子查询：通过LEFT JOIN将dataset1与excluded_ids关联，利用ei.id IS NULL直接判断ID是否不在排除集合中，绕过Spark的语法限制。
3. 保留原业务逻辑：完全保留原查询中id_present和actions字段的判断逻辑，仅调整子查询的实现方式。

注：原查询中第一个WHEN条件已覆盖第二个WHEN条件，实际执行中第二个分支永远不会触发。如果这是业务逻辑笔误，可根据实际需求调整第一个分支的条件（比如仅保留t1.click_date = current_date）。

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