RedShift多NOT IN子查询优化：如何改写为JOIN提升查询效率

• 存在逻辑错误：除前两个NOT IN子查询外，剩余8个子查询的SELECT字段均错误写为device_os，和实际要匹配的字段不对应，会导致查询结果完全不符合预期
• 存在性能问题：10次独立子查询重复扫描同一张表，IO开销极大；RedShift中NOT IN对NULL值处理逻辑特殊、执行效率低，不适合多字段批量匹配场景

你需要的逻辑是从all_values中筛选出10个设备属性字段值，均不存在于对比表对应字段非空集合中的记录。以下方案仅扫描1次对比表，通过LEFT JOIN + 空值判断实现等价逻辑，性能提升幅度非常明显：

WITH device_unique_values AS (
    -- 仅扫描1次对比表，聚合得到所有字段的唯一非空值
    SELECT 
        DISTINCT device_id_source,
        device_os,
        device_model,
        device_design,
        device_serial,
        device_carrier,
        device_os_version,
        device_manufacturer,
        device_current_app_build,
        device_current_app_version
    FROM your_compare_table -- 替换为原代码中实际的对比表名
    WHERE 
        device_id_source IS NOT NULL
        AND device_os IS NOT NULL
        AND device_model IS NOT NULL
        AND device_design IS NOT NULL
        AND device_serial IS NOT NULL
        AND device_carrier IS NOT NULL
        AND device_os_version IS NOT NULL
        AND device_manufacturer IS NOT NULL
        AND device_current_app_build IS NOT NULL
        AND device_current_app_version IS NOT NULL
)
SELECT 
    av.contributor_user_id,
    av.device_id_source,
    av.device_os,
    av.device_model,
    av.device_design,
    av.device_serial,
    av.device_carrier,
    av.device_os_version,
    av.device_manufacturer,
    av.device_current_app_build,
    av.device_current_app_version
FROM all_values av
LEFT JOIN device_unique_values duv
    ON av.device_id_source::VARCHAR = duv.device_id_source
    AND av.device_os::VARCHAR = duv.device_os
    AND av.device_model::VARCHAR = duv.device_model
    AND av.device_design::VARCHAR = duv.device_design
    AND av.device_serial::VARCHAR = duv.device_serial
    AND av.device_carrier::VARCHAR = duv.device_carrier
    AND av.device_os_version::VARCHAR = duv.device_os_version
    AND av.device_manufacturer::VARCHAR = duv.device_manufacturer
    AND av.device_current_app_build::VARCHAR = duv.device_current_app_build
    AND av.device_current_app_version::VARCHAR = duv.device_current_app_version
-- 关联不上的记录即为所有字段均不在对比表集合中的数据，等价于修正后的原NOT IN逻辑
WHERE duv.device_id_source IS NULL

• 类型一致性优化：如果all_values的对应字段本身就是VARCHAR类型，去掉::VARCHAR强制类型转换，避免隐式转换导致索引失效
• 排序键优化：如果该类查询频率很高，可以将all_values和对比表的关联字段设置为排序键，大幅降低JOIN时的扫描开销
• 过滤下推：如果all_values有可前置的过滤条件（如时间范围），可加在主表的WHERE条件中，减少参与JOIN的数据量

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