SQL关联dash与mv两张表并去除batch_id重复值的方案咨询

实现方案 #

你要的优先保留dash表数据、消除重复的需求，可以通过给mv表的查询增加NOT EXISTS过滤条件实现，比左连接逻辑更直观，性能也更好：

-- dash表数据全量保留
SELECT DISTINCT 
  dash.batch_id,
  dash.atf_id,
  dash.phase,
  dash.model_used,
  CASE WHEN dash.atf_id LIKE 'ATF1' THEN mes.atf1_id ELSE mes.atf2_id END AS controller,
  mes.uf_start,
  mes2.uf_start AS baseline_start
FROM dash
INNER JOIN mes ON dash.batch_id = mes.batchid
INNER JOIN mes2 ON dash.model_used = mes2.batchid

UNION ALL -- 已经提前过滤重复，用UNION ALL比UNION效率高很多

-- mv表只保留dash中不存在匹配(batch_id+phase)的行
SELECT DISTINCT 
  mv.batch_id,
  mv.atf_id,
  mv.phase,
  mv.model_used,
  CASE WHEN mv.atf_id LIKE 'ATF1' THEN mes.atf1_id ELSE mes.atf2_id END AS controller,
  mes.uf_start,
  mes2.uf_start AS baseline_start
FROM mv
INNER JOIN mes ON mv.batch_id = mes.batchid
INNER JOIN mes2 ON mv.model_used = mes2.batchid
WHERE NOT EXISTS (
  SELECT 1 FROM dash 
  WHERE dash.batch_id = mv.batch_id 
  AND dash.phase = mv.phase
  -- 如果还有其他字段需要匹配，在这里补充条件即可
)

逻辑说明 #

• 上半部分原封不动保留dash表的所有符合关联条件的查询结果
• 下半部分查询mv表时，通过NOT EXISTS排除所有和dash表batch_id、phase完全匹配的行，确保重复项只保留dash侧的内容
• 把原来的隐式连接改成了显式INNER JOIN，可读性更强，也避免漏写关联条件产生笛卡尔积
• 因为已经提前过滤了重复行，不需要用UNION做全局去重，换成UNION ALL可以大幅提升查询性能

如果你一定要用左连接实现，也可以先对dash和mv做全外连接筛选出有效行，再关联mes、mes2表，代码如下：

WITH all_source AS (
  -- 先合并dash和mv的原始数据，有冲突时优先取dash的字段值
  SELECT 
    COALESCE(dash.batch_id, mv.batch_id) AS batch_id,
    COALESCE(dash.atf_id, mv.atf_id) AS atf_id,
    COALESCE(dash.phase, mv.phase) AS phase,
    COALESCE(dash.model_used, mv.model_used) AS model_used
  FROM dash
  FULL OUTER JOIN mv 
    ON dash.batch_id = mv.batch_id 
    AND dash.phase = mv.phase
)
SELECT DISTINCT
  s.batch_id,
  s.atf_id,
  s.phase,
  s.model_used,
  CASE WHEN s.atf_id LIKE 'ATF1' THEN mes.atf1_id ELSE mes.atf2_id END AS controller,
  mes.uf_start,
  mes2.uf_start AS baseline_start
FROM all_source s
INNER JOIN mes ON s.batch_id = mes.batchid
INNER JOIN mes2 ON s.model_used = mes2.batchid

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