BigQuery使用CTE删除含嵌套transaction字段的重复记录问题求解

• 语法报错Expected end of input but got ","的核心原因是：BigQuery 的 DELETE DML 不支持在 FROM 子句中直接通过逗号隐式连接 UNNEST 结果，你原写法的 JOIN 逻辑不符合 DELETE 语法规范。
• 逻辑层面的问题是：DELETE 语句不能直接用嵌套数组内的字段做全局 STRUCT 匹配，UNNEST 后的数据和父表行的对应关系会错位，要么出现删多的情况，要么完全匹配不到待删除行。

你可以通过 EXISTS 子查询来关联父表和待删除的重复记录标识，既能避免语法错误，也能精准控制只删除每组重复项中除第一条外的记录，代码如下：

DELETE FROM analytics.SANDBOX.bookings p
WHERE EXISTS (
  WITH cte AS (
    SELECT 
      date,
      uniqueId,
      hashedEmail,
      transactionId,
      -- 按重复维度分组，每组仅保留排序后的第一条
      ROW_NUMBER() OVER(
        PARTITION BY date, uniqueId, hashedEmail 
        ORDER BY transactionId
      ) AS rn
    FROM analytics.SANDBOX.bookings o,
    UNNEST(transaction) AS t
    WHERE 
      -- 匹配基础过滤规则
      SUBSTRING(t.transactionId, 1, 8) = FORMAT_DATE('%Y%m%d', o.date)
      AND o.visit.device.platform = 'Web'
      AND t.typeGroup = 'New'
  )
  SELECT 1 FROM cte
  WHERE 
    -- 匹配父行的重复判定字段
    cte.date = p.date
    AND cte.uniqueId = p.uniqueId
    AND cte.hashedEmail = p.hashedEmail
    -- 匹配父行内对应的嵌套 transaction 记录
    AND EXISTS (
      SELECT 1 FROM UNNEST(p.transaction) pt
      WHERE pt.transactionId = cte.transactionId
    )
    -- 仅筛选分组内需要删除的重复项
    AND cte.rn > 1
)

• 执行删除前建议先把代码开头的 DELETE FROM 替换为 SELECT * FROM，运行确认返回的待删除行符合预期后再执行删除操作，避免误删数据。
• 如果你不需要删除整行，仅需要移除嵌套 transaction 数组内的重复元素，可以把 DELETE 语句改为 UPDATE 语句重写 transaction 字段即可。

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