使用DuckDB通过线性插值填充空值的实现问题

问题场景 #

先重现你的测试表：

CREATE TABLE df (x NUMERIC, y NUMERIC);
INSERT INTO df VALUES (0, 2), (1, NULL), (3, NULL), (5, 7), (6, 4), (8, 5), (9, NULL);

你尝试用关联子查询嵌套LIMIT的方式获取前后非空值计算插值，但DuckDB会出现内部绑定错误，这是因为嵌套LIMIT的关联子查询在DuckDB的绑定逻辑中存在兼容性问题。改用窗口函数方案可以稳定解决这个需求。

解决方案 #

使用LAST_VALUE和FIRST_VALUE窗口函数（配合IGNORE NULLS）直接获取当前行前后最近的非空值，再计算线性插值：

SELECT
    x,
    COALESCE(
        y,
        -- 线性插值公式：prev_y + (next_y - prev_y) * (当前x - prev_x)/(next_x - prev_x)
        last_y + (first_y - last_y) * (x - last_x) / (first_x - last_x)
    ) AS y_filled
FROM (
    SELECT
        x,
        y,
        -- 获取当前行及之前最后一个非空的y值
        LAST_VALUE(y IGNORE NULLS) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_y,
        -- 获取对应last_y的x值
        LAST_VALUE(x IGNORE NULLS) OVER (ORDER BY x ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_x,
        -- 获取当前行及之后第一个非空的y值
        FIRST_VALUE(y IGNORE NULLS) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS first_y,
        -- 获取对应first_y的x值
        FIRST_VALUE(x IGNORE NULLS) OVER (ORDER BY x ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS first_x
    FROM df
) AS sub;

结果说明 #

执行后会得到填充后的结果：

• 对于x=9的空值，因为后续没有非空y值，插值公式会自动沿用最后一个非空y值（5）；
• 所有空值均基于前后最近的非空(x,y)对完成线性计算，符合你对插值的需求。

内容的提问来源于stack exchange，提问作者Jonas Hörsch