使用历史值回填时间序列数据的SQL实现方案问询

核心思路 #

要实现需求，需要两步：

1. 生成目标日期范围内的所有连续日期
2. 将连续日期与原表关联，填充缺失日期对应的最近历史值

通用SQL方案（支持PostgreSQL、SQL Server等） #

假设你的表名为time_series，字段为date（日期类型）和value（数值类型）：

WITH date_range AS (
    -- 递归生成从最小日期到最大日期的所有连续日期
    SELECT MIN(date) AS current_date, MAX(date) AS end_date
    FROM time_series
    UNION ALL
    SELECT DATEADD(day, 1, current_date), end_date
    FROM date_range
    WHERE current_date < end_date
),
filled_series AS (
    -- 关联原表，用最近的非空值填充缺失日期
    SELECT
        dr.current_date AS date,
        LAST_VALUE(ts.value IGNORE NULLS) OVER (ORDER BY dr.current_date) AS value
    FROM date_range dr
    LEFT JOIN time_series ts ON dr.current_date = ts.date
)
-- 输出填充后的完整序列
SELECT * FROM filled_series ORDER BY date;

运行上述SQL后，示例数据会得到以下结果：

MySQL专属方案（MySQL 8.0+） #

MySQL的LAST_VALUE不支持IGNORE NULLS，可以用用户变量实现：

WITH date_range AS (
    SELECT MIN(date) AS current_date, MAX(date) AS end_date
    FROM time_series
    UNION ALL
    SELECT current_date + INTERVAL 1 DAY, end_date
    FROM date_range
    WHERE current_date < end_date
)
SELECT
    dr.current_date AS date,
    @prev_value := COALESCE(ts.value, @prev_value) AS value
FROM date_range dr
LEFT JOIN time_series ts ON dr.current_date = ts.date
CROSS JOIN (SELECT @prev_value := NULL) init
ORDER BY dr.current_date;

将填充结果写入原表 #

如果需要把缺失日期的记录插入回原表，可以执行以下语句（根据数据库类型调整）：

PostgreSQL

INSERT INTO time_series (date, value)
SELECT date, value
FROM filled_series
WHERE date NOT IN (SELECT date FROM time_series);

MySQL

INSERT INTO time_series (date, value)
SELECT date, value
FROM filled_series
WHERE date NOT IN (SELECT date FROM time_series)
ON DUPLICATE KEY UPDATE value = value; -- 避免重复插入报错

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