Trino/Presto SQL：仅替换分组中首个非NULL后的NULL值

需要将NULL值替换为指定字符串，但仅对分组中首个非NULL值之后出现的NULL进行替换；若NULL出现在首个非NULL值之前，则保留为NULL。

示例数据 #

# | user_id | some_date  | animal  |
# |---------|------------|---------|
# | 1       | 2022-01-01 | NULL    | <~~ 保留为NULL
# | 1       | 2022-01-02 | zebra   | <~~ user_id=1的首个非NULL值
# | 1       | 2022-01-03 | lion    |
# | 1       | 2022-01-04 | NULL    | <~~ 替换为'no_animal'
# | 1       | 2022-01-05 | cat     |
# | 2       | 2023-10-05 | NULL    | <~~ 保留为NULL
# | 2       | 2023-10-06 | NULL    | <~~ 保留为NULL
# | 2       | 2023-10-07 | dog     | <~~ user_id=2的首个非NULL值
# | 2       | 2023-10-08 | frog    |
# | 2       | 2023-10-09 | NULL    | <~~ 替换为'no_animal'
# | 3       | 2024-02-03 | hamster | <~~ user_id=3的首个非NULL值
# | 3       | 2024-02-04 | rabbit  |
# | 3       | 2024-02-05 | NULL    | <~~ 替换为'no_animal'
# | 3       | 2024-02-06 | NULL    | <~~ 替换为'no_animal'

期望输出 #

# | user_id | some_date  | animal  | replaced_null |
# |---------|------------|---------|---------------|
# | 1       | 2022-01-01 | NULL    | NULL          |
# | 1       | 2022-01-02 | zebra   | zebra         |
# | 1       | 2022-01-03 | lion    | lion          |
# | 1       | 2022-01-04 | NULL    | no_animal     |
# | 1       | 2022-01-05 | cat     | cat           |
# | 2       | 2023-10-05 | NULL    | NULL          |
# | 2       | 2023-10-06 | NULL    | NULL          |
# | 2       | 2023-10-07 | dog     | dog           |
# | 2       | 2023-10-08 | frog    | frog          |
# | 2       | 2023-10-09 | NULL    | no_animal     |
# | 3       | 2024-02-03 | hamster | hamster       |
# | 3       | 2024-02-04 | rabbit  | rabbit        |
# | 3       | 2024-02-05 | NULL    | no_animal     |
# | 3       | 2024-02-06 | NULL    | no_animal     |

SQL方言 #

使用基于Trino SQL的AWS Athena。

可复现数据 #

WITH my_tbl AS (
    SELECT *
    FROM (VALUES
        (1, DATE '2022-01-01', NULL),
        (1, DATE '2022-01-02', 'zebra'),
        (1, DATE '2022-01-03', 'lion'),
        (1, DATE '2022-01-04', NULL),
        (1, DATE '2022-01-05', 'cat'),
        (2, DATE '2023-10-05', NULL),
        (2, DATE '2023-10-06', NULL),
        (2, DATE '2023-10-07', 'dog'),
        (2, DATE '2023-10-08', 'frog'),
        (2, DATE '2023-10-09', NULL),
        (3, DATE '2024-02-03', 'hamster'),
        (3, DATE '2024-02-04', 'rabbit'),
        (3, DATE '2024-02-05', NULL),
        (3, DATE '2024-02-06', NULL)
    ) AS t(user_id, some_date, animal)
)

核心思路是先定位每个分组（按user_id）中首个非NULL值的出现时间，再根据当前行的时间判断是否需要替换NULL。

完整SQL代码如下：

WITH my_tbl AS (
    SELECT *
    FROM (VALUES
        (1, DATE '2022-01-01', NULL),
        (1, DATE '2022-01-02', 'zebra'),
        (1, DATE '2022-01-03', 'lion'),
        (1, DATE '2022-01-04', NULL),
        (1, DATE '2022-01-05', 'cat'),
        (2, DATE '2023-10-05', NULL),
        (2, DATE '2023-10-06', NULL),
        (2, DATE '2023-10-07', 'dog'),
        (2, DATE '2023-10-08', 'frog'),
        (2, DATE '2023-10-09', NULL),
        (3, DATE '2024-02-03', 'hamster'),
        (3, DATE '2024-02-04', 'rabbit'),
        (3, DATE '2024-02-05', NULL),
        (3, DATE '2024-02-06', NULL)
    ) AS t(user_id, some_date, animal)
),
user_first_non_null AS (
    SELECT 
        *,
        -- 获取每个用户首个非NULL值的日期
        MIN(CASE WHEN animal IS NOT NULL THEN some_date END) OVER (PARTITION BY user_id) AS first_non_null_date
    FROM my_tbl
)
SELECT 
    user_id,
    some_date,
    animal,
    CASE
        -- 非NULL值直接保留
        WHEN animal IS NOT NULL THEN animal
        -- NULL值且在首个非NULL值之后，替换为指定字符串
        WHEN some_date >= first_non_null_date THEN 'no_animal'
        -- 其他情况保留NULL
        ELSE NULL
    END AS replaced_null
FROM user_first_non_null
ORDER BY user_id, some_date;

逻辑说明 #

1. 定位首个非NULL值：通过窗口函数MIN(CASE WHEN animal IS NOT NULL THEN some_date END) OVER (PARTITION BY user_id)，找出每个用户分组中第一个出现非NULL值的日期first_non_null_date。
2. 条件判断替换：
   • 如果animal本身不为NULL，直接返回原值；
   • 如果animal为NULL且当前行日期晚于等于首个非NULL值的日期，替换为'no_animal'；
   • 其余情况（NULL出现在首个非NULL值之前）保留NULL。

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