SQL按ID分组Gap Fill缺失值填充的多组场景实现问题

多分组场景和单分组的核心差异是要按分组维度做隔离，每个分组内独立执行填充逻辑，避免跨分组取值即可，以下是适配你需求的实现方案：

方案1：支持IGNORE NULLS的数据库简洁写法（适用PG、BigQuery、Hive 2.3+、Spark SQL等） #

直接通过窗口函数先填充分组ID，再按分组填充数值即可，代码如下：

SELECT
    `Order`,
    -- 向后查找最近的非空ID确定当前行所属分组
    LAST_VALUE(ID) IGNORE NULLS OVER (ORDER BY `Order` ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS ID,
    -- 按ID分组隔离后，向后查找最近的非空金额填充
    LAST_VALUE(Amount) IGNORE NULLS OVER (
        PARTITION BY LAST_VALUE(ID) IGNORE NULLS OVER (ORDER BY `Order` ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
        ORDER BY `Order` ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
    ) AS Amount
FROM test_order_amount
ORDER BY ID, `Order`;

如果你的需求是向前取最近的非空值，只需把窗口范围ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING替换为ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW即可。

方案2：全兼容通用写法（适用所有支持窗口函数/CTE的数据库，含MySQL 8.0） #

通过生成全量分组+序列框架的方式实现，适配性更强：

WITH 
-- 提取所有非空分组ID
distinct_id AS (
    SELECT DISTINCT ID FROM test_order_amount WHERE ID IS NOT NULL
),
-- 提取所有连续Order序列值
distinct_order AS (
    SELECT DISTINCT `Order` FROM test_order_amount WHERE `Order` IS NOT NULL
),
-- 生成每个ID对应的完整Order序列框架
full_frame AS (
    SELECT di.ID, do.`Order`
    FROM distinct_id di
    CROSS JOIN distinct_order do
),
-- 提取所有有效数值记录
valid_value AS (
    SELECT ID, `Order`, Amount
    FROM test_order_amount
    WHERE ID IS NOT NULL AND Amount IS NOT NULL
)
SELECT 
    f.`Order`,
    f.ID,
    -- 匹配向后最近的有效数值，适配你给出的示例规则
    (SELECT Amount FROM valid_value v 
     WHERE v.ID = f.ID AND v.`Order` >= f.`Order`
     ORDER BY v.`Order` ASC LIMIT 1) AS Amount
FROM full_frame f
ORDER BY f.ID, f.`Order`;

如果需要向前取值，只需把子查询里的v.Order >= f.Order改为v.Order <= f.Order，排序改为ORDER BY v.Order DESC即可。

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