SQL 缺失值填充

需求

缺失值前向填充，可能有连续空值。
输入：

-- 创建示例数据
CREATE TABLE sales_data (id INT,sale_date varchar(20),amount DOUBLE
);-- 插入有连续缺失值的数据
INSERT INTO sales_data VALUES
(1, '2024-01-01', 100.0),
(1, '2024-01-02', NULL),
(1, '2024-01-03', NULL),
(1, '2024-01-04', 150.0),
(1, '2024-01-05', NULL),
(2, '2024-01-01', 200.0),
(2, '2024-01-02', NULL),
(2, '2024-01-03', 250.0);

输出：

解析

使用MAX窗口函数技巧

SELECTid,sale_date,amount,MAX(amount) OVER (PARTITION BY id, flagORDER BY sale_date) AS filled_amount
FROM (SELECTid,sale_date,amount,-- 创建分组标记SUM(CASE WHEN amount IS NOT NULL THEN 1 ELSE 0 END)OVER (PARTITION BY id ORDER BY sale_date) AS flagFROM sales_data
) t;