如何在BigQuery中按用户规则处理重复手机号数据

需求：同一手机号由不同用户录入时全部保留，同一用户录入的重复手机号仅保留一条。

原始数据示例 #

期望结果数据 #

规则说明：手机号789150由不同用户录入，全部保留；手机号507371由同一用户录入，仅保留一条。

原SQL问题 #

原SQL按手机号分组取最大ID，会过滤掉同一手机号不同用户的记录，不符合需求：

select 
distinct ll.id 
,ll.user_id
,ll.phone_number
,ll.created_at
,ll.outlet_id
from `my_table` ll
where ll.id  in (select max(id) from my_table group by phone_number)
and ll.phone_number in ('+62-8999789150','+62-8999507371')
order by 3 desc

正确SQL语句 #

使用窗口函数ROW_NUMBER()按用户ID+手机号分组排序，保留每组第一条记录：

WITH ranked_data AS (
  SELECT
    id,
    user_id,
    phone_number,
    created_at,
    outlet_id,
    -- 按user_id和phone_number分组，按created_at升序取最早记录，也可换id升序
    ROW_NUMBER() OVER (PARTITION BY user_id, phone_number ORDER BY created_at ASC) AS row_num
  FROM `my_table`
  WHERE phone_number IN ('+62-8999789150','+62-8999507371')
)
SELECT
  id,
  user_id,
  phone_number,
  created_at,
  outlet_id
FROM ranked_data
WHERE row_num = 1
ORDER BY phone_number DESC;

逻辑说明 #

• PARTITION BY user_id, phone_number：将数据按用户ID和手机号分组，确保同一用户同一手机号的记录在同一组内
• ORDER BY created_at ASC：每组内按创建时间升序排序，保留最早录入的记录；若想保留最新记录，改为DESC即可
• 筛选row_num = 1：每个分组仅保留第一条记录，实现同一用户同一手机号去重，不同用户同一手机号的记录全部保留

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