如何在BigQuery中移除包含数组的重复行？

针对含数组字段的BigQuery表，直接用DISTINCT或普通GROUP BY会因数组类型无法处理，以下是两种可靠的去重方案，均能保留原有表结构：

方案1：保留每组重复行的任意一行（数组内容完全一致的重复行） #

适用于重复行的所有字段（包括数组）内容完全一致的场景，通过将数组转为JSON字符串作为分组依据，配合窗口函数标记重复行：

CREATE OR REPLACE TABLE `your_project.your_dataset.your_table`
AS
SELECT * EXCEPT(row_num)
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY total, average, percent, TO_JSON_STRING(array_field)
           ORDER BY (SELECT NULL) -- 无需指定排序规则，取任意一行即可
         ) AS row_num
  FROM `your_project.your_dataset.your_table`
)
WHERE row_num = 1;

如果不想覆盖原表，可创建新表：

CREATE TABLE `your_project.your_dataset.deduplicated_table`
AS
SELECT * EXCEPT(row_num)
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY total, average, percent, TO_JSON_STRING(array_field)
           ORDER BY (SELECT NULL)
         ) AS row_num
  FROM `your_project.your_dataset.your_table`
)
WHERE row_num = 1;

方案2：同时去重数组内部元素与重复行 #

如果重复行的数组内部存在重复元素，且需要先清理数组再去重整行，可在去重行的同时对数组做内部去重：

CREATE OR REPLACE TABLE `your_project.your_dataset.your_table`
AS
SELECT 
  total,
  average,
  percent,
  ARRAY(SELECT DISTINCT * FROM UNNEST(array_field)) AS array_field -- 去重数组内部元素
FROM (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY total, average, percent, TO_JSON_STRING(array_field)
           ORDER BY (SELECT NULL)
         ) AS row_num
  FROM `your_project.your_dataset.your_table`
)
WHERE row_num = 1;

注意事项 #

• 若数组元素顺序不同但内容相同需判定为重复，需先对数组排序再转JSON字符串，例如：TO_JSON_STRING(ARRAY(SELECT * FROM UNNEST(array_field) ORDER BY value))（value为数组元素的排序字段，基本类型可直接写*）
• 若存在多个数组字段，需将每个数组都用TO_JSON_STRING转换后加入PARTITION BY子句
• 使用CREATE OR REPLACE TABLE前建议备份原表，避免数据丢失

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