BigQuery MERGE报错：源行重复排查矛盾及解决方法咨询

在BigQuery执行MERGE语句时触发错误：UPDATE/MERGE must match at most one source row for each target row。排查过程中出现矛盾现象：

• 第二个查询检测MERGE的源数据集，发现75000+条重复行，且均关联自table_2；
• 第三个查询单独检测table_2的去重子集，却未发现任何重复行。

需要解决这一矛盾，确保MERGE操作时每个目标行最多匹配一个源行。

原始MERGE查询 #

MERGE INTO `PROJECT.DATASET.DESTINATION_TABLE` destination_table
USING (
  SELECT DISTINCT
    pat.payments_accounting_transaction_id
    , pat.company_account
    , pat.merchant_account
    , pat.merchant_reference
    , pat.psp_reference
    , pat.booking_date
    , pat.main_currency
    , pat.main_amount
    , pat.record_type
    , ats.user_id
    , ats.value
    , ats.charge
    , ats.result_code
    , ats.event_code
    , ats.contract_type
    , ats.statement_message
    , ats.load_type
  FROM `PROJECT.DATASET.SOURCE_TABLE_1` pat
  FULL OUTER JOIN 
  (
    SELECT DISTINCT
      psp_reference
      , merchant_account_code
      , user_id
      , value
      , charge
      , result_code
      , event_code
      , contract_type
      , statement_message
      , load_type
    FROM `PROJECT.DATASET.SOURCE_TABLE_2`
    WHERE psp_reference IS NOT NULL
     -- providing window for settlement process
    AND created >= DATETIME(TIMESTAMP('2022-07-13T00:00:00+00:00'))
    AND created < DATETIME(TIMESTAMP('2023-01-10T00:00:00+00:00'))
  ) ats ON pat.psp_reference = ats.psp_reference
    AND pat.merchant_account = ats.merchant_account_code
) source_table ON destination_table.psp_reference = source_table.psp_reference
    AND destination_table.merchant_account = source_table.merchant_account
WHEN MATCHED THEN UPDATE SET
    destination_table.payments_accounting_transaction_id = source_table.payments_accounting_transaction_id
    , destination_table.company_account = source_table.company_account
    , destination_table.merchant_reference = source_table.merchant_reference
    , destination_table.booking_date = source_table.booking_date
    , destination_table.main_currency = source_table.main_currency
    , destination_table.main_amount = source_table.main_amount
    , destination_table.record_type = source_table.record_type
    , destination_table.user_id = source_table.user_id
    , destination_table.value = source_table.value
    , destination_table.charge = source_table.charge
    , destination_table.result_code = source_table.result_code
    , destination_table.event_code = source_table.event_code
    , destination_table.contract_type = source_table.contract_type
    , destination_table.statement_message = source_table.statement_message
    , destination_table.load_type = source_table.load_type
WHEN NOT MATCHED THEN INSERT
(
    payments_accounting_transaction_id
    , company_account
    , merchant_account
    , psp_reference
    , merchant_reference
    , booking_date
    , main_currency
    , main_amount
    , record_type
    , user_id
    , value
    , charge
    , result_code
    , event_code
    , contract_type
    , statement_message
    , load_type
)
VALUES
(
    source_table.payments_accounting_transaction_id
    , source_table.company_account
    , source_table.merchant_account
    , source_table.psp_reference
    , source_table.merchant_reference
    , source_table.booking_date
    , source_table.main_currency
    , source_table.main_amount
    , source_table.record_type
    , source_table.user_id
    , source_table.value
    , source_table.charge
    , source_table.result_code
    , source_table.event_code
    , source_table.contract_type
    , source_table.statement_message
    , source_table.load_type
)
;

重复检测查询（第二个查询） #

SELECT 
  payments_accounting_transaction_id
  , company_account
  , merchant_account
  , merchant_reference
  , psp_reference
  , booking_date
  , main_currency
  , main_amount
  , record_type
  , user_id
  , value
  , charge
  , result_code
  , event_code
  , contract_type
  , statement_message
  , load_type
  , COUNT(*) AS count
FROM (
  SELECT 
    pat.payments_accounting_transaction_id
    , pat.company_account
    , pat.merchant_account
    , pat.merchant_reference
    , pat.psp_reference
    , pat.booking_date
    , pat.main_currency
    , pat.main_amount
    , pat.record_type
    , ats.user_id
    , ats.value
    , ats.charge
    , ats.result_code
    , ats.event_code
    , ats.contract_type
    , ats.statement_message
    , ats.load_type
  FROM `PROJECT.DATASET.TABLE_1` pat
  FULL OUTER JOIN 
  (
    SELECT DISTINCT
      psp_reference
      , merchant_account_code
      , user_id
      , value
      , charge
      , result_code
      , event_code
      , contract_type
      , statement_message
      , load_type
    FROM `PROJECT.DATASET.TABLE_2`
    WHERE psp_reference IS NOT NULL
     -- providing window for settlement process
    AND created >= DATETIME(TIMESTAMP('2022-07-13T00:00:00+00:00'))
    AND created < DATETIME(TIMESTAMP('2023-01-10T00:00:00+00:00'))
  ) ats ON pat.psp_reference = ats.psp_reference
    AND pat.merchant_account = ats.merchant_account_code
) 
GROUP BY 
  payments_accounting_transaction_id
  , company_account
  , merchant_account
  , merchant_reference
  , psp_reference
  , booking_date
  , main_currency
  , main_amount
  , record_type
  , user_id
  , value
  , charge
  , result_code
  , event_code
  , contract_type
  , statement_message
  , load_type
HAVING COUNT(*) > 1
ORDER BY count DESC

table_2单表去重检测查询（第三个查询） #

SELECT 
    psp_reference
    , merchant_account_code
    , user_id
    , value
    , charge
    , result_code
    , event_code
    , contract_type
    , statement_message
    , load_type
    , COUNT(*) AS count
    FROM 
    (SELECT DISTINCT
      psp_reference
      , merchant_account_code
      , user_id
      , value
      , charge
      , result_code
      , event_code
      , contract_type
      , statement_message
      , load_type
    FROM `PROJECT.DATASET.TABLE_2`
    WHERE psp_reference IS NOT NULL
     -- providing window for settlement process
    AND created >= DATETIME(TIMESTAMP('2022-07-13T00:00:00+00:00'))
    AND created < DATETIME(TIMESTAMP('2023-01-10T00:00:00+00:00')))
GROUP BY
    psp_reference
      , merchant_account_code
      , user_id
      , value
      , charge
      , result_code
      , event_code
      , contract_type
      , statement_message
      , load_type

HAVING COUNT(*) > 1;

这一矛盾的核心是FULL OUTER JOIN引发的笛卡尔积重复：

• 第三个查询仅检测table_2单表的去重结果，确实无重复；但table_1中存在同一个(psp_reference, merchant_account)对应多行的情况，和table_2的匹配行关联后，会生成多条组合行；
• 第二个查询的分组包含所有返回字段，这些组合行因所有字段完全一致被判定为重复（比如table_1的多行除主键外其他字段完全相同，关联后生成重复行）；
• 原始MERGE中的SELECT DISTINCT无法彻底解决问题：如果table_1的重复行和table_2的行关联后存在字段值差异，DISTINCT会保留所有不同组合，仍然导致源数据有多个行匹配同一个目标行。

步骤1：定位重复根源 #

先确认table_1中关联键的重复情况，执行以下查询：

SELECT 
  psp_reference,
  merchant_account,
  COUNT(*) AS row_count
FROM `PROJECT.DATASET.SOURCE_TABLE_1`
GROUP BY psp_reference, merchant_account
HAVING COUNT(*) > 1
ORDER BY row_count DESC;

若返回结果，说明table_1存在关联键重复，这是关联后产生重复的直接原因。

步骤2：修改MERGE的源数据集，确保唯一匹配 #

在MERGE的USING子句中，用窗口函数+过滤的方式，为每个(psp_reference, merchant_account)仅保留一行数据（可根据业务逻辑选择保留最新行、最早行或任意行），替换原有的SELECT DISTINCT：

修改后的MERGE查询示例：

MERGE INTO `PROJECT.DATASET.DESTINATION_TABLE` destination_table
USING (
  SELECT 
    payments_accounting_transaction_id,
    company_account,
    merchant_account,
    merchant_reference,
    psp_reference,
    booking_date,
    main_currency,
    main_amount,
    record_type,
    user_id,
    value,
    charge,
    result_code,
    event_code,
    contract_type,
    statement_message,
    load_type
  FROM (
    SELECT 
      pat.payments_accounting_transaction_id,
      pat.company_account,
      pat.merchant_account,
      pat.merchant_reference,
      pat.psp_reference,
      pat.booking_date,
      pat.main_currency,
      pat.main_amount,
      pat.record_type,
      ats.user_id,
      ats.value,
      ats.charge,
      ats.result_code,
      ats.event_code,
      ats.contract_type,
      ats.statement_message,
      ats.load_type,
      -- 按关联键分组，给每行分配序号，取第一行
      ROW_NUMBER() OVER (
        PARTITION BY pat.psp_reference, pat.merchant_account 
        ORDER BY pat.booking_date DESC -- 业务逻辑：保留最新的booking_date行，可按需修改
      ) AS rn
    FROM `PROJECT.DATASET.SOURCE_TABLE_1` pat
    FULL OUTER JOIN 
    (
      SELECT DISTINCT
        psp_reference,
        merchant_account_code,
        user_id,
        value,
        charge,
        result_code,
        event_code,
        contract_type,
        statement_message,
        load_type
      FROM `PROJECT.DATASET.SOURCE_TABLE_2`
      WHERE psp_reference IS NOT NULL
        AND created >= DATETIME(TIMESTAMP('2022-07-13T00:00:00+00:00'))
        AND created < DATETIME(TIMESTAMP('2023-01-10T00:00:00+00:00'))
    ) ats ON pat.psp_reference = ats.psp_reference
      AND pat.merchant_account = ats.merchant_account_code
  )
  WHERE rn = 1 -- 仅保留每个关联键组的第一行
) source_table ON destination_table.psp_reference = source_table.psp_reference
    AND destination_table.merchant_account = source_table.merchant_account
WHEN MATCHED THEN UPDATE SET
    destination_table.payments_accounting_transaction_id = source_table.payments_accounting_transaction_id,
    destination_table.company_account = source_table.company_account,
    destination_table.merchant_reference = source_table.merchant_reference,
    destination_table.booking_date = source_table.booking_date,
    destination_table.main_currency = source_table.main_currency,
    destination_table.main_amount = source_table.main_amount,
    destination_table.record_type = source_table.record_type,
    destination_table.user_id = source_table.user_id,
    destination_table.value = source_table.value,
    destination_table.charge = source_table.charge,
    destination_table.result_code = source_table.result_code,
    destination_table.event_code = source_table.event_code,
    destination_table.contract_type = source_table.contract_type,
    destination_table.statement_message = source_table.statement_message,
    destination_table.load_type = source_table.load_type
WHEN NOT MATCHED THEN INSERT
(
    payments_accounting_transaction_id,
    company_account,
    merchant_account,
    psp_reference,
    merchant_reference,
    booking_date,
    main_currency,
    main_amount,
    record_type,
    user_id,
    value,
    charge,
    result_code,
    event_code,
    contract_type,
    statement_message,
    load_type
)
VALUES
(
    source_table.payments_accounting_transaction_id,
    source_table.company_account,
    source_table.merchant_account,
    source_table.psp_reference,
    source_table.merchant_reference,
    source_table.booking_date,
    source_table.main_currency,
    source_table.main_amount,
    source_table.record_type,
    source_table.user_id,
    source_table.value,
    source_table.charge,
    source_table.result_code,
    source_table.event_code,
    source_table.contract_type,
    source_table.statement_message,
    source_table.load_type
);

步骤3：验证修改后的源数据集 #

执行以下查询，确认修改后的源数据无重复的(psp_reference, merchant_account)行：

SELECT 
  psp_reference,
  merchant_account,
  COUNT(*) AS row_count
FROM (
  -- 复制修改后的USING子句内的查询
  SELECT 
    payments_accounting_transaction_id,
    company_account,
    merchant_account,
    merchant_reference,
    psp_reference,
    booking_date,
    main_currency,
    main_amount,
    record_type,
    user_id,
    value,
    charge,
    result_code,
    event_code,
    contract_type,
    statement_message,
    load_type
  FROM (
    SELECT 
      pat.payments_accounting_transaction_id,
      pat.company_account,
      pat.merchant_account,
      pat.merchant_reference,
      pat.psp_reference,
      pat.booking_date,
      pat.main_currency,
      pat.main_amount,
      pat.record_type,
      ats.user_id,
      ats.value,
      ats.charge,
      ats.result_code,
      ats.event_code,
      ats.contract_type,
      ats.statement_message,
      ats.load_type,
      ROW_NUMBER() OVER (
        PARTITION BY pat.psp_reference, pat.merchant_account 
        ORDER BY pat.booking_date DESC
      ) AS rn
    FROM `PROJECT.DATASET.SOURCE_TABLE_1` pat
    FULL OUTER JOIN 
    (
      SELECT DISTINCT
        psp_reference,
        merchant_account_code,
        user_id,
        value,
        charge,
        result_code,
        event_code,
        contract_type,
        statement_message,
        load_type
      FROM `PROJECT.DATASET.SOURCE_TABLE_2`
      WHERE psp_reference IS NOT NULL
        AND created >= DATETIME(TIMESTAMP('2022-07-13T00:00:00+00:00'))
        AND created < DATETIME(TIMESTAMP('2023-01-10T00:00:00+00:00'))
    ) ats ON pat.psp_reference = ats.psp_reference
      AND pat.merchant_account = ats.merchant_account_code
  )
  WHERE rn = 1
)
GROUP BY psp_reference, merchant_account
HAVING COUNT(*) > 1;

若返回空结果，说明源数据已满足MERGE的唯一性要求。

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