BigQuery中Insert Into结合Left outer join导致交易行重复翻倍的解决方法

你尝试通过自左外连接table2，用上一年数据更新5列字段，再更新Text列，但执行代码后出现行数翻倍、重复行无差异的问题，核心原因是错误使用了INSERT INTO语句，以下是问题分析和修正方案：

原代码问题分析 #

原INSERT INTO语句会将左连接后的所有结果（包含原表table2的全部行，因为左连接会保留左表所有记录）插入到原表中，相当于把原表数据完整复制了一遍，直接导致行数翻倍。另外，UPDATE语句中的tgt.R_no=b.G_L_no,'0'存在语法错误，字符串连接需要用BigQuery支持的||或CONCAT()函数。

原代码：

insert into table2
select coalesce(a.F_YEAR,b.F_YEAR+1) F_YEAR, 
coalesce(a.POSTING_DATE,b.POSTING_DATE+366)POSTING_DATE,
coalesce(a.Material,b.Material),
coalesce(a.R_no,b.R_no),
coalesce(a.Text,b.Text),
a.Amt,
a.X_BUDGET,
a.Y_BUDGET,
a.X_FORECAST,
a.Y_FORECAST
from table2 a
Left outer join table2 b 
on a.F_YEAR-1 = b.F_YEAR
and extract(month from a.POSTING_DATE) = extract(month from b.POSTING_DATE)
and a.R_no = b.R_no
and a.MATERIAL = b.MATERIAL
and a.TEXT=b.TEXT;

select *
from table2
where F_Year <= extract(year from current_date('Asia/India'));

update table2 tgt
set tgt.TEXT=b.TEXT
from table3 b
where
tgt.R_no=b.G_L_no,'0' and tgt.TEXT is null;

修正方案 #

根据你的需求（用上一年数据更新现有行，或生成下一年新记录），选择对应的方案：

方案1：更新现有行的5列字段（用上一年数据） #

如果你的目标是更新table2中已有行的Amt、X_BUDGET等5列字段为上一年对应数据，使用MERGE语句替代INSERT，避免重复插入：

MERGE INTO table2 tgt
USING (
  SELECT 
    a.F_YEAR,
    a.POSTING_DATE,
    a.Material,
    a.R_no,
    a.Text,
    b.Amt,
    b.X_BUDGET,
    b.Y_BUDGET,
    b.X_FORECAST,
    b.Y_FORECAST
  FROM table2 a
  LEFT JOIN table2 b 
    ON a.F_YEAR - 1 = b.F_YEAR
    AND EXTRACT(MONTH FROM a.POSTING_DATE) = EXTRACT(MONTH FROM b.POSTING_DATE)
    AND a.R_no = b.R_no
    AND a.Material = b.Material
    AND a.Text = b.Text
) src
ON tgt.F_YEAR = src.F_YEAR
  AND tgt.POSTING_DATE = src.POSTING_DATE
  AND tgt.R_no = src.R_no
  AND tgt.Material = src.Material
  AND tgt.Text = src.Text
WHEN MATCHED AND src.Amt IS NOT NULL THEN -- 仅更新存在上一年数据的行
  UPDATE SET
    Amt = src.Amt,
    X_BUDGET = src.X_BUDGET,
    Y_BUDGET = src.Y_BUDGET,
    X_FORECAST = src.X_FORECAST,
    Y_FORECAST = src.Y_FORECAST;

方案2：生成下一年的新记录（不重复原数据） #

如果你的目标是基于上一年数据生成下一年的新记录，同样用MERGE确保只插入原表不存在的新记录：

MERGE INTO table2 tgt
USING (
  SELECT 
    b.F_YEAR + 1 AS F_YEAR,
    b.POSTING_DATE + 366 AS POSTING_DATE,
    b.Material,
    b.R_no,
    b.Text,
    b.Amt,
    b.X_BUDGET,
    b.Y_BUDGET,
    b.X_FORECAST,
    b.Y_FORECAST
  FROM table2 b
  -- 过滤掉已存在下一年记录的行，避免重复插入
  WHERE NOT EXISTS (
    SELECT 1 FROM table2 t
    WHERE t.F_YEAR = b.F_YEAR + 1
      AND EXTRACT(MONTH FROM t.POSTING_DATE) = EXTRACT(MONTH FROM b.POSTING_DATE)
      AND t.R_no = b.R_no
      AND t.Material = b.Material
      AND t.Text = b.Text
  )
) src
ON tgt.F_YEAR = src.F_YEAR
  AND EXTRACT(MONTH FROM tgt.POSTING_DATE) = EXTRACT(MONTH FROM src.POSTING_DATE)
  AND tgt.R_no = src.R_no
  AND tgt.Material = src.Material
  AND tgt.Text = src.Text
WHEN NOT MATCHED THEN
  INSERT (F_YEAR, POSTING_DATE, Material, R_no, Text, Amt, X_BUDGET, Y_BUDGET, X_FORECAST, Y_FORECAST)
  VALUES (src.F_YEAR, src.POSTING_DATE, src.Material, src.R_no, src.Text, src.Amt, src.X_BUDGET, src.Y_BUDGET, src.X_FORECAST, src.Y_FORECAST);

修正UPDATE语句 #

修正字符串连接的语法错误，确保Text列更新逻辑正确：

UPDATE table2 tgt
SET tgt.Text = b.Text
FROM table3 b
WHERE tgt.R_no = b.G_L_no || '0' -- 或使用CONCAT(b.G_L_no, '0')
  AND tgt.Text IS NULL;

验证结果 #

执行修正后的语句后，再查询数据：

select *
from table2
where F_Year <= extract(year from current_date('Asia/India'));

此时不会出现重复行，数据符合预期。

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