无唯一键的BigQuery两张大表数据比对方案咨询

原查询的问题 #

EXCEPT DISTINCT会对两张表的查询结果先去重，再计算差集。这意味着：

• 如果table2中某行组合在table1中存在，哪怕table2里有多个重复实例，原查询也不会返回这些重复
• 它只返回table2中去重后完全不在table1去重结果里的行组合，每个组合仅返回一次，无法保留原表的重复数据

解决方案 #

根据需求场景，有两种常用方法：

场景1：找出table2中完全未在table1出现过的所有记录（含重复） #

如果需要保留table2中那些在table1里完全找不到匹配的行（包括重复实例），可以用以下两种方式：

方法1：使用NOT EXISTS

SELECT t2.*
FROM table2 t2
WHERE NOT EXISTS (
  SELECT 1
  FROM table1 t1
  -- 列出所有需要匹配的字段，确保行完全一致
  WHERE t1.col1 = t2.col1
    AND t1.col2 = t2.col2
    -- 继续添加其他需要匹配的列
)

方法2：使用LEFT JOIN

SELECT t2.*
FROM table2 t2
LEFT JOIN table1 t1
  ON t1.col1 = t2.col1
  AND t1.col2 = t2.col2
  -- 继续添加其他需要匹配的列
WHERE t1.col1 IS NULL -- 任意匹配字段为NULL即说明无对应记录

这两种方法都会返回table2中所有在table1里没有对应匹配的记录，包括重复的行。

场景2：找出table2中出现次数超过table1的额外重复记录 #

如果需要考虑重复次数（比如table1中某行出现2次，table2中出现5次，要返回多出来的3次），可以先统计每行的出现次数，再计算差异并展开：

WITH table2_row_counts AS (
  SELECT col1, col2, COUNT(*) AS row_count
  FROM table2
  GROUP BY col1, col2
),
table1_row_counts AS (
  SELECT col1, col2, COUNT(*) AS row_count
  FROM table1
  GROUP BY col1, col2
),
excess_rows AS (
  SELECT
    t2.col1, t2.col2,
    -- 计算table2比table1多的行数，table1无对应行则直接取table2的行数
    t2.row_count - COALESCE(t1.row_count, 0) AS excess_count
  FROM table2_row_counts t2
  LEFT JOIN table1_row_counts t1
    ON t2.col1 = t1.col1 AND t2.col2 = t1.col2
  WHERE t2.row_count - COALESCE(t1.row_count, 0) > 0
)
-- 从原table2中筛选出多出来的重复行
SELECT t2.*
FROM table2 t2
JOIN excess_rows er
  ON t2.col1 = er.col1 AND t2.col2 = er.col2
-- 用窗口函数限制每个行组合只取多出来的数量
QUALIFY ROW_NUMBER() OVER (PARTITION BY t2.col1, t2.col2 ORDER BY (SELECT NULL)) <= er.excess_count

这个方法会精准返回table2中比table1多出来的那些重复记录。

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