如何在Google BigQuery中从非唯一记录生成唯一ID及简便查重

嘿，针对你的问题，我来分享几个更高效的方案：

你原来用CONCAT拼接所有列的方式确实能实现需求，但当列数变多的时候不仅写起来繁琐，还可能因为列中包含特殊字符（比如和拼接逻辑冲突的符号）导致误判。BigQuery里有更直接的写法：

方法1：直接GROUP BY所有列 #

不用拼接，直接按所有列分组统计，结果更直观，也更可靠：

SELECT col1, col2, col3, col4, col5, COUNT(*) AS duplicate_count
FROM `your-project.your-dataset.your-table`
GROUP BY col1, col2, col3, col4, col5
HAVING duplicate_count > 1

方法2：用GROUP BY ALL简化多列场景 #

如果列数特别多，不想逐个列名输入，可以用BigQuery的扩展语法GROUP BY ALL，它会自动按SELECT子句中除聚合函数外的所有列分组：

SELECT *, COUNT(*) AS duplicate_count
FROM `your-project.your-dataset.your-table`
GROUP BY ALL
HAVING duplicate_count > 1

方法3：快速判断是否存在重复行 #

如果你只需要确认表中有没有重复行，不需要看具体重复内容，可以用EXISTS子查询快速得到结果：

SELECT EXISTS (
  SELECT 1
  FROM `your-project.your-dataset.your-table`
  GROUP BY ALL
  HAVING COUNT(*) > 1
) AS has_duplicate_rows

根据你的需求，分两种场景来处理：

1. 给每一行生成全局唯一ID #

不管行是否重复，给每一行分配一个全局唯一的标识，用GENERATE_UUID()最方便，它会生成标准的UUID字符串：

SELECT 
  GENERATE_UUID() AS global_unique_id,
  *
FROM `your-project.your-dataset.your-table`

如果偏好整数类型的ID，可以用ROW_NUMBER()窗口函数，排序依据可以选任意列（如果没有特定顺序，用RAND()或者所有列都可以）：

SELECT 
  ROW_NUMBER() OVER (ORDER BY col1, col2, col3, col4, col5) AS global_unique_id,
  *
FROM `your-project.your-dataset.your-table`

2. 给重复组内的记录生成组内唯一ID #

如果是想区分同一组重复记录（即所有列值完全相同的行），给每组内的行分配唯一编号，可以用ROW_NUMBER()配合PARTITION BY：

SELECT
  ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4, col5 ORDER BY NULL) AS group_unique_id,
  *
FROM `your-project.your-dataset.your-table`

同样，多列场景下用PARTITION BY ALL简化：

SELECT
  ROW_NUMBER() OVER (PARTITION BY ALL ORDER BY NULL) AS group_unique_id,
  *
FROM `your-project.your-dataset.your-table`

小技巧：快速获取所有列名 #

当列数特别多的时候，手动写列名太麻烦，你可以通过INFORMATION_SCHEMA查询表的所有列名，复制后直接使用：

SELECT STRING_AGG(column_name, ', ')
FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'your-table'

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