MySQL中线性插值补全缺失数据的优化方案问询

从OurWorldInData获取的COVID-19疫苗数据包含total_boosters字段，用于追踪各国加强针接种总量。需要生成每日新增boosters_new和90天滚动总和boosters_90roll列，但total_boosters存在大量缺失值，必须通过线性插值补全，否则滚动求和偏差大、可视化效果差。现有可行SQL查询但过于复杂，寻求更简洁高效的实现方式。

示例输入 #

示例输出 #

当前解决方案 #

-- 将total_boosters及连续空值分组，并统计每组行数和行号
WITH BoostersGrouped AS
(
SELECT
    location,
    date,
    total_boosters,
    boosters_group,
    COUNT(boosters_group) OVER (
        PARTITION BY location, boosters_group
    ) AS group_count,
    ROW_NUMBER() OVER (
        PARTITION BY location, boosters_group
    ) AS group_nrow
FROM
    (
    SELECT
        location,
        date,
        total_boosters,
        COUNT(total_boosters) OVER (
            PARTITION BY location ORDER BY date
        ) AS boosters_group
    FROM Vaccinations
    ) grouped
),
-- 自连接获取当前组和下一组的total_boosters有效值
BoostersFilled AS
(
SELECT
    bg1.location,
    bg1.date,
    bg1.boosters_group,
    bg1.group_count,
    bg1.group_nrow,
    bg2.boosters_current,
    bg3.boosters_next
FROM BoostersGrouped bg1
LEFT JOIN
    (
    SELECT
        location,
        boosters_group,
        max(total_boosters) AS boosters_current
    FROM BoostersGrouped
    GROUP BY location, boosters_group
    ) bg2
    ON bg1.location = bg2.location
    AND bg1.boosters_group = bg2.boosters_group
LEFT JOIN 
    (
    SELECT
        location,
        boosters_group,
        max(total_boosters) AS boosters_next
    FROM BoostersGrouped
    GROUP BY location, boosters_group
    ) bg3
    ON bg1.location = bg3.location
    AND (bg1.boosters_group + 1) = bg3.boosters_group
),
-- 线性插值补全total_boosters
BoostersInterp AS
(
SELECT
    location,
    date,
    CASE
        WHEN boosters_group = 0 THEN 0
        ELSE (boosters_current + ((boosters_next - boosters_current)* group_nrow / group_count))
    END AS boosters_interp
FROM BoostersFilled
),
-- 计算每日新增加强针数
BoostersNew AS
(
SELECT
    location,
    date,
    (boosters_interp - boosters_lag) AS boosters_new
FROM
    (
    SELECT
        location,
        date,
        boosters_interp,
        LAG(boosters_interp) OVER (
            PARTITION BY location ORDER BY date
        ) AS boosters_lag
    FROM BoostersInterp
    ) BoostersLag
)
-- 计算90天滚动总和
SELECT
    location,
    date,
    boosters_new,
    SUM(boosters_new) OVER (
        PARTITION BY location ORDER BY date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
    ) AS boosters_90roll
FROM BoostersNew;

简化后的高效实现 #

利用窗口函数LAST_VALUE、LEAD直接获取前后有效值，减少CTE层级，逻辑更清晰：

WITH interpolated_data AS (
    SELECT
        location,
        date,
        total_boosters,
        -- 获取当前行之前最近的非空加强针总量
        LAST_VALUE(total_boosters IGNORE NULLS) OVER (
            PARTITION BY location ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS prev_valid_total,
        -- 获取当前行之后最近的非空加强针总量
        LEAD(total_boosters IGNORE NULLS) OVER (
            PARTITION BY location ORDER BY date
        ) AS next_valid_total,
        -- 获取前一个有效值的日期
        LAST_VALUE(date IGNORE NULLS) OVER (
            PARTITION BY location ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS prev_valid_date,
        -- 获取后一个有效值的日期
        LEAD(date IGNORE NULLS) OVER (
            PARTITION BY location ORDER BY date
        ) AS next_valid_date
    FROM Vaccinations
),
boosters_interpolated AS (
    SELECT
        location,
        date,
        CASE
            -- 本身有有效值直接使用
            WHEN total_boosters IS NOT NULL THEN total_boosters
            -- 第一个有效值之前的行补0
            WHEN prev_valid_total IS NULL THEN 0
            -- 最后一个有效值之后的行保持最后值
            WHEN next_valid_total IS NULL THEN prev_valid_total
            -- 线性插值计算补全值
            ELSE prev_valid_total + 
                 (next_valid_total - prev_valid_total) * 
                 DATEDIFF(date, prev_valid_date) / 
                 DATEDIFF(next_valid_date, prev_valid_date)
        END AS boosters_interp
    FROM interpolated_data
),
daily_new AS (
    SELECT
        location,
        date,
        -- 计算每日新增，第一行对齐原逻辑的日均计算方式
        CASE
            WHEN LAG(boosters_interp) OVER (PARTITION BY location ORDER BY date) IS NULL 
            THEN (LEAD(boosters_interp) OVER (PARTITION BY location ORDER BY date) - boosters_interp) / 
                 DATEDIFF(LEAD(date) OVER (PARTITION BY location ORDER BY date), date)
            ELSE boosters_interp - LAG(boosters_interp) OVER (PARTITION BY location ORDER BY date)
        END AS boosters_new
    FROM boosters_interpolated
)
-- 计算90天滚动总和
SELECT
    location,
    date,
    boosters_new,
    SUM(boosters_new) OVER (
        PARTITION BY location ORDER BY date ROWS BETWEEN 89 PRECEDING AND CURRENT ROW
    ) AS boosters_90roll
FROM daily_new;

简化说明 #

1. 减少CTE层级：将原方案的4层CTE压缩为3层，减少中间表的生成与关联开销
2. 窗口函数直接取值：用LAST_VALUE、LEAD配合IGNORE NULLS直接获取前后有效值，无需分组自连接
3. 插值逻辑更直观：通过日期差计算插值比例，避免分组计数与行号的复杂关联
4. 性能优化：减少了多次自连接和分组聚合操作，提升查询效率

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