如何在BigQuery中合并表重复行 缺失值用最新更新记录填充

核心逻辑：按邮箱分组后，所有记录按更新时间倒序排列，每个字段优先取最新记录的非空值，为空则依次读取次新记录的对应字段值，直到找到非空值为止。

SQL 实现（数据库直接处理） #

假设表名为leads，通用兼容写法如下：

WITH sorted_leads AS (
    SELECT 
        *,
        -- 按邮箱分组，更新时间从新到旧排序
        ROW_NUMBER() OVER (
            PARTITION BY Email 
            ORDER BY STR_TO_DATE(`Last Updated`, '%m/%d/%Y') DESC
        ) AS row_order
    FROM leads
)
SELECT 
    MAX(CASE WHEN row_order = 1 THEN First END) AS First,
    MAX(CASE WHEN row_order = 1 THEN last END) AS last,
    Email,
    -- 依次取最新的非空手机号
    COALESCE(
        MAX(CASE WHEN row_order = 1 AND Phone != '' THEN Phone END),
        MAX(CASE WHEN row_order = 2 AND Phone != '' THEN Phone END),
        MAX(CASE WHEN row_order = 3 AND Phone != '' THEN Phone END),
        MAX(CASE WHEN row_order = 4 AND Phone != '' THEN Phone END)
    ) AS Phone,
    -- 依次取最新的非空职位
    COALESCE(
        MAX(CASE WHEN row_order = 1 AND `Job Title` != '' THEN `Job Title` END),
        MAX(CASE WHEN row_order = 2 AND `Job Title` != '' THEN `Job Title` END),
        MAX(CASE WHEN row_order = 3 AND `Job Title` != '' THEN `Job Title` END)
    ) AS `Job Title`,
    MAX(CASE WHEN row_order = 1 THEN State END) AS State,
    MAX(CASE WHEN row_order = 1 THEN `Last Updated` END) AS `Last Updated`
FROM sorted_leads
GROUP BY Email;

PostgreSQL、Snowflake等支持FIRST_VALUE忽略空值的数据库可简化为：

SELECT DISTINCT
    Email,
    FIRST_VALUE(First) OVER w AS First,
    FIRST_VALUE(last) OVER w AS last,
    FIRST_VALUE(Phone) OVER (
        PARTITION BY Email 
        ORDER BY CASE WHEN Phone = '' THEN 1 ELSE 0 END, STR_TO_DATE(`Last Updated`, '%m/%d/%Y') DESC
    ) AS Phone,
    FIRST_VALUE(`Job Title`) OVER (
        PARTITION BY Email 
        ORDER BY CASE WHEN `Job Title` = '' THEN 1 ELSE 0 END, STR_TO_DATE(`Last Updated`, '%m/%d/%Y') DESC
    ) AS `Job Title`,
    FIRST_VALUE(State) OVER w AS State,
    FIRST_VALUE(`Last Updated`) OVER w AS `Last Updated`
FROM leads
WINDOW w AS (PARTITION BY Email ORDER BY STR_TO_DATE(`Last Updated`, '%m/%d/%Y') DESC);

Excel/Google Sheets 实现（小批量数据） #

• 第一步：全表按「Email升序，Last Updated降序」排序
• 第二步：新增辅助列组内序号，公式为=IF(A2=A1,B1+1,1)（A列为Email列，B列为辅助列）
• 第三步：对Phone、Job Title等需要补全的字段，用数组公式取当前邮箱下组内序号最小的非空值，示例Phone列公式：=XLOOKUP(1,IF((A:A=当前行Email)*(D:D<>""),B:B,""),D:D,"",0,1)（按Ctrl+Shift+Enter执行数组公式）
• 第四步：筛选组内序号=1的记录，即为合并后的去重结果

Python Pandas 实现（中大型数据集） #

import pandas as pd

# 读取数据，转换日期格式为可排序类型
df = pd.read_csv("leads.csv")
df["Last Updated"] = pd.to_datetime(df["Last Updated"], format="%m/%d/%Y")

# 按邮箱分组合并逻辑
def merge_duplicate_records(group):
    # 组内按更新时间倒序排列
    group = group.sort_values("Last Updated", ascending=False)
    # 向前填充空值，取第一行即为合并后的完整记录
    return group.ffill().iloc[0]

# 执行合并
result = df.groupby("Email", as_index=False).apply(merge_duplicate_records).reset_index(drop=True)
# 输出结果
print(result)

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