如何用同id非空值填充数据集空行？SQL数据清洗求助

已查阅但未解决的相关问题 #

• SQL: 如何为某列有重复值的每组行选取一行？
• 在Redshift中用后续第一个非空值填充缺失值

原始稀疏数据集 #

预期结果 #

备注 #

• 部分id的某个字段可能所有行都为空
• 多数id的各字段非空值一致，少数存在不同值的情况，填充任意非空值即可（该情况极少可忽略）
• 部分字段大多仅出现在特定operation行中，例如mode仅出现在operation='Start'行

尝试的代码及问题 #

我尝试按id分组，对title、channel_type、mode字段执行listagg聚合，再用coalesce处理，代码如下：

WITH my_data AS (
  SELECT
    id,
    operation,
    title,
    channel_type,
    mode
  FROM
    my_db.my_table
),

list_aggregated_data AS (
  SELECT
    id,
    listagg(title) AS titles,
    listagg(channel_type) AS channel_types,
    listagg(mode) AS modes
  FROM
    my_data
  GROUP BY
    id
),

coalesced_data AS (
  SELECT DISTINCT
    id,
    coalesce(titles) AS title,
    coalesce(channel_types) AS channel_type,
    coalesce(modes) AS mode
  FROM
    list_aggregated_data
),

joined_data AS (
  SELECT
    md.id,
    md.operation,
    cd.title,
    cd.channel_type,
    cd.mode
  FROM
    my_data AS md
  LEFT JOIN
    coalesced_data AS cd ON cd.id = md.id
)

SELECT
  *
FROM
  joined_data
ORDER BY
  id,
  operation

但结果出现了值拼接的情况，例如title字段变成recoveryrecovery，不符合预期。

正确解决方法 #

问题出在listagg函数会将同一ID下的所有非空值拼接在一起，而我们只需要任意一个非空值。可以改用MAX()或MIN()聚合函数，这两个函数会自动忽略NULL值，返回该ID下对应的非空值（如果存在多个非空值，取最大/最小的那个，符合“填充任意非空值”的需求）。

修改后的SQL代码如下：

WITH my_data AS (
  SELECT
    id,
    operation,
    title,
    channel_type,
    mode
  FROM
    my_db.my_table
),

id_level_values AS (
  SELECT
    id,
    MAX(title) AS filled_title,
    MAX(channel_type) AS filled_channel_type,
    MAX(mode) AS filled_mode
  FROM
    my_data
  GROUP BY
    id
)

SELECT
  md.id,
  md.operation,
  ilv.filled_title AS title,
  ilv.filled_channel_type AS channel_type,
  ilv.filled_mode AS mode
FROM
  my_data md
LEFT JOIN
  id_level_values ilv ON md.id = ilv.id
ORDER BY
  md.id,
  md.operation;

说明 #

1. MAX()聚合函数会忽略NULL，提取每个ID下对应字段的非空值；如果该ID下该字段全为空，返回NULL，符合需求。
2. 如果需要保留原始行已有的非空值（仅填充空值），可以把字段替换为COALESCE(md.title, ilv.filled_title)，效果更灵活。

内容的提问来源于stack exchange，提问作者y2k-shubham