Spark SQL中如何分组聚合获取用户首个有效值？

你的需求是按user_id分组后，获取每个用户的第一个非NULL有效值，之前的尝试没达到预期，问题出在这：

• 用COLLECT_LIST(value)[0]时，会把NULL也收集进去，user_id=1111的第一条记录是NULL，所以取到的就是NULL，不符合要求。
• 单独用COALESCE(value)没有分组逻辑，而且这不是聚合场景下的正确用法，自然得不到想要的结果。

下面给你几个Spark SQL里可行的解决方法：

方法1：用窗口函数FIRST_VALUE（推荐，支持顺序控制） #

如果需要严格按照数据的原始顺序取第一个非NULL值，用窗口函数最稳妥：

SELECT DISTINCT
    user_id,
    FIRST_VALUE(value) OVER (PARTITION BY user_id ORDER BY 1 ASC IGNORE NULLS) AS first_valid_value
FROM your_table;

• PARTITION BY user_id按用户分组
• ORDER BY 1 ASC表示遵循数据的默认存储顺序排序，如果你的表有明确的排序字段（比如创建时间create_time），换成这个字段更严谨
• IGNORE NULLS会让函数自动跳过NULL值，直接取第一个非NULL的value

方法2：过滤NULL后取聚合数组的第一个元素 #

先收集每个用户的所有非NULL值到数组，再取数组的第一个元素，Spark 3.0+可以用ARRAY_FIRST简化：

SELECT
    user_id,
    ARRAY_FIRST(COLLECT_LIST(value) FILTER (WHERE value IS NOT NULL)) AS first_valid_value
FROM your_table
GROUP BY user_id;

如果是Spark 3.0以下版本，用CASE判断数组长度：

SELECT
    user_id,
    CASE WHEN size(filtered_values) > 0 THEN filtered_values[0] ELSE NULL END AS first_valid_value
FROM (
    SELECT
        user_id,
        COLLECT_LIST(value) FILTER (WHERE value IS NOT NULL) AS filtered_values
    FROM your_table
    GROUP BY user_id
) t;

方法3：用聚合函数MAX/MIN（仅限单有效值场景） #

如果每个用户的非NULL值只有一个，也可以直接用聚合函数取非NULL值：

SELECT
    user_id,
    MAX(value) AS first_valid_value
FROM your_table
GROUP BY user_id;

注意：这个方法不保证顺序，只是取非NULL值中的最大值，适合每个用户只有一个有效值的场景。

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