Spark SQL中如何为同一ID的所有行填充对应姓名

问题场景 #

现有人员信息表，ID为人员唯一标识，同一ID会出现在多行数据中；当人员未登录时，First Name和Last Name字段为NULL。需要实现：同一ID的所有行都显示该人员对应的非空姓名，且因数据量庞大，无法手动替换空值。

示例数据表：

解决方案 #

方法一：子查询关联（通用SQL，兼容所有数据库） #

SELECT 
    t.ID,
    COALESCE(t.`First Name`, name_ref.`First Name`) AS `First Name`,
    COALESCE(t.`Last Name`, name_ref.`Last Name`) AS `Last Name`,
    t.Login,
    t.Date
FROM 
    your_table t
JOIN (
    -- 先获取每个ID对应的非空姓名，MAX会自动忽略NULL
    SELECT 
        ID,
        MAX(`First Name`) AS `First Name`,
        MAX(`Last Name`) AS `Last Name`
    FROM 
        your_table
    GROUP BY 
        ID
) name_ref ON t.ID = name_ref.ID;

逻辑说明：

• 子查询通过GROUP BY ID分组，用MAX()提取每个ID对应的非空姓名（同一ID的非空姓名唯一，MAX不会改变值）
• COALESCE()函数优先保留原表已有值，原表为NULL时用子查询的非空值替换

方法二：窗口函数（适合大数据量，支持MySQL 8+、PostgreSQL、SQL Server等） #

SELECT 
    ID,
    -- 按ID分组，优先取登录状态（Yes）的行的姓名
    FIRST_VALUE(`First Name`) OVER (PARTITION BY ID ORDER BY Login DESC) AS `First Name`,
    FIRST_VALUE(`Last Name`) OVER (PARTITION BY ID ORDER BY Login DESC) AS `Last Name`,
    Login,
    Date
FROM 
    your_table;

逻辑说明：

• PARTITION BY ID将数据按ID分组，每组内单独处理
• ORDER BY Login DESC让登录状态为Yes的行排在组内最前面，FIRST_VALUE()直接取该行的非空姓名
• 无需关联子查询，查询效率更高，适合数据量庞大的场景

执行结果 #

两种方法都会得到以下输出：

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