如何在BigQuery中为重复记录的指定列更新值为1

先解决你遇到的两个核心问题：

1. 分区字段不能用FLOAT64类型：可以通过将浮点字段转换为字符串（或精度可控的数值）规避，也可以用TO_JSON_STRING对目标字段/整行序列化作为分区键。
2. 之前的更新语句错误导致全表更新：WHERE EXCEPT(...)是错误用法，实际等价于WHERE TRUE，正确逻辑是通过匹配重复行的唯一标识来限定更新范围。

场景1：更新所有列完全重复的记录 #

如果重复定义是整行所有字段一致，推荐用QUALIFY结合ROW_NUMBER()筛选重复行，再通过哈希值或主键关联更新：

方法1：利用整行哈希值匹配 #

WITH duplicate_rows AS (
  SELECT 
    *,
    -- 生成整行哈希值作为匹配依据
    FARM_FINGERPRINT(TO_JSON_STRING(t)) AS row_hash,
    ROW_NUMBER() OVER (PARTITION BY TO_JSON_STRING(t)) AS rn
  FROM `your_project.your_dataset.your_table` t
)
UPDATE `your_project.your_dataset.your_table` target
SET column_to_update = 1
WHERE EXISTS (
  SELECT 1 FROM duplicate_rows dr
  WHERE 
    FARM_FINGERPRINT(TO_JSON_STRING(target)) = dr.row_hash
    AND dr.rn > 1 -- 仅匹配重复行（排除每组第一行）
)

方法2：表有主键/唯一ID时更高效 #

如果表存在唯一主键（比如id），直接定位重复行的ID即可：

WITH duplicate_ids AS (
  SELECT id
  FROM `your_project.your_dataset.your_table`
  QUALIFY ROW_NUMBER() OVER (PARTITION BY TO_JSON_STRING(t)) > 1
)
UPDATE `your_project.your_dataset.your_table`
SET column_to_update = 1
WHERE id IN (SELECT id FROM duplicate_ids)

场景2：更新指定列重复的记录（解决FLOAT64分区问题） #

如果重复是基于包含FLOAT64类型的指定列，不能直接用浮点字段分区，可通过以下两种方式处理：

示例：指定列含FLOAT64字段 #

WITH duplicate_rows AS (
  SELECT 
    id, -- 假设表有唯一ID
    ROW_NUMBER() OVER (
      PARTITION BY 
        column1, 
        column2, 
        -- 方案1：将FLOAT字段转为字符串
        CAST(column3 AS STRING)
        -- 方案2：保留指定精度后分区，比如保留6位小数
        -- ROUND(column3, 6)
    ) AS rn
  FROM `your_project.your_dataset.your_table`
)
UPDATE `your_project.your_dataset.your_table`
SET column_to_update = 1
WHERE id IN (SELECT id FROM duplicate_rows WHERE rn > 1)

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