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窗口函数技巧

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