SQL如何使用下一个非空值填充order_of_occurrence列的NULL值

报错原因 #

你原有写法触发多值返回错误是因为子查询没有添加行匹配限制，会返回整张表所有非空的order_of_occurrence值，单条外层查询行无法匹配多个返回结果。此外你代码中还存在字段拼写错误，order_of_occurrence被误写为order_of_occurence，也会导致执行报错。

正确实现方案 #

方案1：窗口函数实现（适配PostgreSQL 11+、MySQL 8.0.22+、BigQuery、Spark SQL等现代数据库） #

直接使用带IGNORE NULLS参数的FIRST_VALUE窗口函数即可完美匹配需求：

SELECT 
    id,
    `order`,
    certain_event,
    FIRST_VALUE(order_of_occurrence) IGNORE NULLS OVER (
        PARTITION BY id 
        ORDER BY `order` 
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS order_of_occurrence
FROM dataset;

逻辑说明：按id分组后按order升序排序，窗口范围限定为当前行到后续所有行，取范围内第一个非空的order_of_occurrence值，最后一行后续无匹配值则自动返回NULL，和你期望的输出完全一致。

方案2：关联子查询实现（兼容所有支持关联子查询的数据库） #

如果你的数据库版本不支持IGNORE NULLS参数，可以用关联子查询实现：

SELECT 
    a.*,
    (
        SELECT b.order_of_occurrence 
        FROM dataset b 
        WHERE b.id = a.id 
          AND b.`order` >= a.`order` 
          AND b.order_of_occurrence IS NOT NULL
        ORDER BY b.`order` ASC 
        LIMIT 1
    ) AS corrected_order
FROM dataset a;

逻辑说明：对每一行数据，匹配同id下order大于等于当前行、且order_of_occurrence非空的行，取order最小的那条的对应值作为填充结果。

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