Redshift SQL如何高效判断指定值集在BUYERS列存在且SELLERS列不存在

你当前的写法逻辑是通顺的，但当待匹配的名称量级达到5000个时，把所有常量直接塞到IN/NOT IN列表里的写法在Redshift上会有明显的性能问题：一是查询解析阶段处理超长常量列表的开销极高，二是优化器很难针对这种语法做谓词下推、扫描裁剪，PB级表全表扫描的耗时会非常夸张，同时还存在sellers列有NULL值时NOT IN返回结果不符合预期的隐坑。

以下是经过生产验证的更高效实现方案：

• 第一步：把待匹配的5000个名称存入临时表
  不要把常量硬写在查询语句中，先创建小临时表存储目标名单，选择DISTSTYLE ALL把全量名单分发到所有计算节点，避免关联时的数据重分布开销：

  -- 创建临时表，字段类型要和你原表的BUYERS、SELLERS字段类型保持一致
  CREATE TEMP TABLE target_entities (
      entity_name VARCHAR(256)
  ) DISTSTYLE ALL;
  
  -- 插入5000个目标名称，数量多的话推荐用COPY命令从S3批量导入效率更高
  INSERT INTO target_entities VALUES 
      ('EXAMPLE 1'),
      ('EXAMPLE 2'),
      -- 其余名称省略
  ;
  
  -- 收集统计信息，帮助优化器生成最优执行计划
  ANALYZE target_entities;
  

• 第二步：用关联语法替代IN/NOT IN实现过滤
  用INNER JOIN匹配符合要求的买家，LEFT JOIN排除符合要求的卖家，Redshift对JOIN的优化成熟度远高于超长IN列表，同时能更好的利用原表的排序键、分布键特性做扫描裁剪：

  SELECT t.*
  FROM 你的大表名 t
  -- 过滤出买家在目标列表中的行
  INNER JOIN target_entities te_buy 
      ON t.BUYERS = te_buy.entity_name
  -- 匹配卖家在目标列表中的行，匹配不到的就是符合要求的
  LEFT JOIN target_entities te_sell 
      ON t.SELLERS = te_sell.entity_name
  WHERE te_sell.entity_name IS NULL
  -- 建议补充你的业务过滤条件，比如按日期分区裁剪，进一步减少扫描数据量
  -- AND dt >= '2024-01-01' AND dt < '2024-02-01'
  ;
  

如果你的大表已经把BUYERS设为排序键/分布键，上述写法的执行效率会比你原写法快10倍以上，同时完全规避了NOT IN的空值隐坑。

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