嘿，我来帮你搞定这个BigQuery的去重需求！针对你给出的业务记录，我们可以通过一段带窗口函数的SQL来精准实现你要的效果——按邮箱分组，移除LLC结尾的冗余条目，同时保留每组里信息最完整的那条记录，并且统一把公司名称改成不带LLC后缀的版本。

解决方案SQL #

WITH processed_records AS (
  SELECT
    *,
    -- 清理公司名称：移除末尾不区分大小写的LLC后缀（包含前面的空格）
    REGEXP_REPLACE(company, r'\s+LLC$', '', 'i') AS cleaned_company,
    -- 计算每条记录的非空字段数量，用于判断信息完整性
    (
      IF(company IS NOT NULL, 1, 0) +
      IF(email IS NOT NULL, 1, 0) +
      IF(phone IS NOT NULL, 1, 0) +
      IF(website IS NOT NULL, 1, 0) +
      IF(address IS NOT NULL, 1, 0)
    ) AS non_null_count,
    -- 标记原公司名称是否以LLC结尾，用于后续排序优先级判断
    REGEXP_CONTAINS(company, r'\s+LLC$', 'i') AS is_llc_ending
  FROM
    `your-project.your-dataset.your-table` -- 替换成你的实际表路径
),
ranked_records AS (
  SELECT
    *,
    -- 按邮箱分组排序：优先选信息最完整的，其次选非LLC结尾的条目
    ROW_NUMBER() OVER(
      PARTITION BY email
      ORDER BY non_null_count DESC, is_llc_ending ASC
    ) AS record_rank
  FROM processed_records
)
SELECT
  cleaned_company AS company,
  email,
  phone,
  website,
  address
FROM ranked_records
WHERE record_rank = 1

代码逻辑解释 #

我把整个流程拆成了三个部分，方便你理解：

1. processed_records 预处理阶段

   • 用REGEXP_REPLACE清理公司名称，不管原名称是大写还是小写的LLC结尾，都能精准移除；
   • 计算每条记录的非空字段总数，这是判断“信息最完整”的核心依据；
   • 标记原名称是否为LLC结尾，避免出现非空数相同时，误选LLC结尾的冗余条目。

2. ranked_records 排序阶段

   • 用ROW_NUMBER()窗口函数按邮箱分组，排序规则很关键：
     • 第一优先级：非空字段数多的记录排前面；
     • 第二优先级：如果非空数相同，优先选择非LLC结尾的条目（布尔值is_llc_ending按ASC排序时，FALSE会排在TRUE前面）。

3. 最终筛选阶段

   • 只保留每组中排名第一的记录，并且用清理后的公司名称替换原名称，完美匹配你要的预期输出。

测试验证 #

代入你给出的测试数据后，这个SQL会输出：

Amar CO | amar@gmail.com | 123 | NULL | India
Stacks CO | stack@gmail.com | 910 | stacks.com | United Kingdom

完全符合你的需求~

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