BigQuery无唯一列场景下如何消除多表左连接产生的重复行

方案1：源头消除笛卡尔积（推荐，性能最优） #

笛卡尔积的核心诱因是关联链路中存在一对多的关联键不匹配场景，即右表同一个关联键对应多条记录，和左表关联时直接放大行数。操作逻辑为：在每一层左连接前，先对每个右表按关联键做单值化处理，确保每个关联键仅保留1条符合业务规则的记录，再执行关联。

• 实现代码参考：

WITH 
-- 逐表预处理右表，每个关联键仅保留1条需要的记录
dedup_t2 AS (
  SELECT *
  FROM (
    SELECT
      *,
      -- 按业务规则指定保留行的排序逻辑，例如取最近更新的记录
      ROW_NUMBER() OVER(PARTITION BY CUSTOMER_CODE ORDER BY UPDATE_TS DESC) AS rn
    FROM `TABLE`
  )
  WHERE rn = 1
),
dedup_t3 AS (
  SELECT *
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY MIAL_CODE ORDER BY CREATE_TS DESC) AS rn
    FROM `TABLE3`
  )
  WHERE rn = 1
),
dedup_t5 AS (
  SELECT *
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY WORK_CODE ORDER BY UPDATE_TS DESC) AS rn
    FROM `TABLE5`
  )
  WHERE rn = 1
),
dedup_t7 AS (
  SELECT *
  FROM (
    SELECT
      *,
      ROW_NUMBER() OVER(PARTITION BY date ORDER BY STAT_TIME DESC) AS rn
    FROM `TABLE7`
  )
  WHERE rn = 1
)
-- 使用预处理后的单值右表执行关联，从根源避免行数放大
SELECT
  T1.Col1, T1.Col2,
  dedup_t2.Col1, dedup_t2.Col2,
  dedup_t3.Col1, dedup_t3.Col3,
  dedup_t5.Col1, dedup_t5.Col2,
  dedup_t7.Col1
FROM `TABLE1` AS T1
LEFT JOIN dedup_t2 ON T1.CUSTOMER_CODE = dedup_t2.CUSTOMER_CODE
LEFT JOIN dedup_t3 ON T1.MIAL_CODE = dedup_t3.MIAL_CODE
LEFT JOIN dedup_t5 ON T1.WORK_CODE = dedup_t5.WORK_CODE
LEFT JOIN dedup_t7 ON T1.CA_DATE = dedup_t7.date
ORDER BY T1.CA_DATE

方案2：对已生成的重复结果集直接去重 #

如果不想修改原有关联逻辑，已经跑出了全量带重复的结果集，可根据重复行特征选择对应去重方式，全程不需要依赖结果集的唯一标识列：

• 若重复行是整行完全一致的无效重复，直接对全结果集做去重即可：

WITH raw_result AS (
  -- 此处替换为你原本写的完整查询逻辑
  SELECT T1.Col1,T1.Col2,
    T2.Col1,T2.Col2,
    T3.Col1,T3.Col3,
    T5.Col1,T5.Col2,
    T7.Col1
  FROM `TABLE1` as T1
  LEFT JOIN `TABLE`as T2 ON T1.CUSTOMER_CODE = T2.CUSTOMER_CODE
  LEFT JOIN `TABLE3` as T3 ON (T1.MIAL_CODE) = T3.MIAL_CODE
  LEFT JOIN `TABLE5` as T5 ON T1.WORK_CODE = T5.WORK_CODE
  LEFT JOIN `TABLE7` as T7 ON T1.CA_DATE = T7.date
)
SELECT DISTINCT * FROM raw_result
ORDER BY CA_DATE

• 若重复行是核心业务字段一致、关联带出的冗余字段存在差异的衍生重复，可按核心业务标识分组，每组仅保留1条记录：

WITH raw_result AS (
  -- 同上，替换为原有查询逻辑
)
SELECT *
FROM (
  SELECT
    *,
    -- PARTITION BY 后替换为能唯一代表单条主记录的核心字段，例如T1的主键、核心维度字段
    ROW_NUMBER() OVER(
      PARTITION BY T1.CUSTOMER_CODE, T1.MIAL_CODE, T1.WORK_CODE, T1.CA_DATE
      -- 按业务规则指定优先保留行的排序逻辑，例如优先取关联表最新更新的记录
      ORDER BY T2.UPDATE_TS DESC
    ) AS row_rn
  FROM raw_result
)
WHERE row_rn = 1
ORDER BY CA_DATE

注意事项 #

• 优先使用源头预处理右表的方案，相比对千万级以上的大结果集做全局去重，预处理的扫描成本低、执行效率更高。
• 预处理右表和结果集去重时，不要随机选取保留行，需要严格匹配业务规则定义排序逻辑，避免丢失有效数据。

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