BigQuery多表内连接返回重复行问题求助

多表内连接后结果从43条暴增至800条，核心原因是后续关联的T2/T3/T4/T5中，单条T0-T1匹配记录对应了多条关联表记录，相当于触发了隐性的笛卡尔积，把原本的核心数据行放大了。

第一步：排查重复来源 #

先定位是哪个关联表导致的重复，运行以下查询确认各表的匹配行数：

-- 查看T3中当前F1对应的记录数
SELECT COUNT(*) FROM `TABLE3` T3 WHERE T3.F1 = "010001476713";

-- 查看T4与T0.F3匹配后的记录数
SELECT T0.F3, COUNT(*) 
FROM `TABLE0` T0 
JOIN `TABLE4` T4 ON T4.F3 = T0.F3
WHERE T0.F1 = "010001476713"
GROUP BY T0.F3;

-- 查看T5中当前F1对应的记录数
SELECT COUNT(*) FROM `TABLE5` T5 WHERE T5.F1 = "010001476713";

-- 查看T2与T3关联后的总记录数（基于当前F1）
SELECT COUNT(*) 
FROM `TABLE3` T3
JOIN `TABLE2` T2 ON T2.F24 = T3.F24
WHERE T3.F1 = "010001476713";

第二步：根据业务场景选择解决方案 #

场景1：仅需T0-T1核心数据，关联表字段仅作补充 #

如果不需要关联表的所有记录，只需要每条T0-T1对应一条补充信息，可直接用DISTINCT去重（若关联表字段有不同值，DISTINCT会保留所有组合，此时用聚合函数更稳妥）：

SELECT DISTINCT
  T2.F11,
  T3.F15,
  T2.F12,
  T3.F16,
  T3.F17,
  T1.F1, 
  T2.F13,
  T5.F18,
  T5.F19,
  T5.F20,
  T2.F14,
  T0.F9,
  T1.F10,
  T4.F3,
  T4.F21,
  T4.F22,
  T0.F2, 
  T3.F23,
  T0.F3, 
  T0.F4, 
  T1.F5,
  T1.F6,
  T1.F7,
  T1.F8
FROM `TABLE0` T0
INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1 AND T0.F2 = T1.F2
INNER JOIN `TABLE3`  T3 ON T3.F1=T1.F1
INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
INNER JOIN `TABLE4` T4 ON T4.F3 = T0.F3
INNER JOIN `TABLE5` T5 ON T5.F1=T0.F1
WHERE T0.F1 = "010001476713" 
ORDER BY T0.F4

或者用聚合函数分组，确保每条T0-T1对应唯一行（根据业务选择MAX/MIN等）：

SELECT
  MAX(T2.F11) AS F11,
  MAX(T3.F15) AS F15,
  MAX(T2.F12) AS F12,
  MAX(T3.F16) AS F16,
  MAX(T3.F17) AS F17,
  T1.F1, 
  MAX(T2.F13) AS F13,
  MAX(T5.F18) AS F18,
  MAX(T5.F19) AS F19,
  MAX(T5.F20) AS F20,
  MAX(T2.F14) AS F14,
  T0.F9,
  T1.F10,
  MAX(T4.F3) AS F3,
  MAX(T4.F21) AS F21,
  MAX(T4.F22) AS F22,
  T0.F2, 
  MAX(T3.F23) AS F23,
  T0.F3, 
  T0.F4, 
  T1.F5,
  T1.F6,
  T1.F7,
  T1.F8
FROM `TABLE0` T0
INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1 AND T0.F2 = T1.F2
INNER JOIN `TABLE3`  T3 ON T3.F1=T1.F1
INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
INNER JOIN `TABLE4` T4 ON T4.F3 = T0.F3
INNER JOIN `TABLE5` T5 ON T5.F1=T0.F1
WHERE T0.F1 = "010001476713" 
GROUP BY T1.F1, T0.F9, T1.F10, T0.F2, T0.F3, T0.F4, T1.F5, T1.F6, T1.F7, T1.F8
ORDER BY T0.F4

场景2：需保留关联表信息，但消除T0-T1的重复行 #

用窗口函数给每个T0-T1组合编号，只取每组的第一行（可根据业务调整排序规则，比如按T3的时间字段取最新）：

WITH joined_data AS (
  SELECT
    T2.F11,
    T3.F15,
    T2.F12,
    T3.F16,
    T3.F17,
    T1.F1, 
    T2.F13,
    T5.F18,
    T5.F19,
    T5.F20,
    T2.F14,
    T0.F9,
    T1.F10,
    T4.F3,
    T4.F21,
    T4.F22,
    T0.F2, 
    T3.F23,
    T0.F3, 
    T0.F4, 
    T1.F5,
    T1.F6,
    T1.F7,
    T1.F8,
    -- 按T0-T1核心字段分组，给组内行编号
    ROW_NUMBER() OVER (PARTITION BY T0.F1, T0.F2, T0.F4 ORDER BY T3.F15) AS row_num
  FROM `TABLE0` T0
  INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1 AND T0.F2 = T1.F2
  INNER JOIN `TABLE3`  T3 ON T3.F1=T1.F1
  INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
  INNER JOIN `TABLE4` T4 ON T4.F3 = T0.F3
  INNER JOIN `TABLE5` T5 ON T5.F1=T0.F1
  WHERE T0.F1 = "010001476713" 
)
SELECT * EXCEPT(row_num)
FROM joined_data
WHERE row_num = 1
ORDER BY F4

场景3：修正关联条件，从根源避免重复 #

如果业务上T3/T4/T5与T0-T1应该是一对一关联，说明当前关联条件不足，补充匹配字段即可减少重复：

SELECT
  -- 字段列表不变
FROM `TABLE0` T0
INNER JOIN `TABLE1` T1 ON T1.F1= T0.F1 AND T0.F2 = T1.F2
-- 给T3补充F2匹配条件
INNER JOIN `TABLE3` T3 ON T3.F1=T1.F1 AND T3.F2 = T1.F2
INNER JOIN `TABLE2` T2 ON T2.F24 = T3.F24
-- 给T4补充F1匹配条件
INNER JOIN `TABLE4` T4 ON T4.F3 = T0.F3 AND T4.F1 = T0.F1
-- 给T5补充F2匹配条件
INNER JOIN `TABLE5` T5 ON T5.F1=T0.F1 AND T5.F2 = T0.F2
WHERE T0.F1 = "010001476713" 
ORDER BY T0.F4

内容的提问来源于stack exchange，提问作者Javier Alejandro Rendon Carril