如何在MySQL中用最近已知值填充连续NULL值？

问题描述 #

我有一个MySQL表，存储用户关联的应用、连接日期及余额数据。用户未登录的日期，查询生成的cte3关联表会返回NULL值。我希望构建查询，为每个用户替换这些NULL值，使用user_id、app_id、balance这三个字段的最近已知值。尝试用LAG()函数仅能填充次日的NULL，无法覆盖连续NULL范围。

现有查询语句：

with cte1 as (
select distinct c.date 
from core_table c
where c.date >= '2023-01-01'
),
cte2 as (
select c.date, c.user_id, c.app_id, c.balance 
from core_table c
),
cte3 as (
select cte1.date, cte2.user_id, cte2.app_id, c.balance  
from cte1 
left join cte2 on cte1.date = cte2.date
)
select date, 
case when user_id is NULL then lag(user_id) over (order by date) else user_id end as user_id,
case when app_id is NULL then lag(app_id) over (order by date) else app_id end as app_id,
case when balance is NULL then lag(balance) over (order by date) else balance end as balance
from cte3

现有数据示例 #

期望结果 #

测试单个用户（user_id 1）的错误结果 #

问题分析 #

你忽略了三个核心问题：

1. 窗口函数未分区：原查询的LAG()没有按user_id和app_id分区，导致跨用户/应用获取前一行值，且连续NULL时，第二行之后的记录无法拿到有效历史值。
2. CTE连接逻辑错误：cte1（全局日期）和cte2（原始数据）的LEFT JOIN仅关联date，未关联user_id和app_id，生成的cte3是全局日期与所有用户应用的笛卡尔积，数据逻辑完全混乱。
3. LAG()的局限性：LAG()只能获取前一行的原始数据，当遇到连续多个NULL时，第二行的NULL会被第一行有效值填充，但第三行的NULL会取第二行的原始NULL值，无法实现连续填充。

解决方案 #

正确思路是：先为每个user_id+app_id组合生成完整的日期序列，再用窗口函数填充连续NULL值。以下提供两种适配不同MySQL版本的方案：

方案1：MySQL 8.0.22+ 版本（支持IGNORE NULLS） #

WITH date_range AS (
    -- 获取需要覆盖的所有日期
    SELECT DISTINCT date FROM core_table WHERE date >= '2023-01-01'
),
user_app_groups AS (
    -- 获取所有有效的用户-应用组合
    SELECT DISTINCT user_id, app_id FROM core_table WHERE user_id IS NOT NULL AND app_id IS NOT NULL
),
full_date_series AS (
    -- 为每个用户-应用组合生成完整日期序列
    SELECT dr.date, uag.user_id, uag.app_id
    FROM date_range dr
    CROSS JOIN user_app_groups uag
),
joined_data AS (
    -- 关联原始数据，得到带NULL的完整序列
    SELECT 
        fds.date,
        fds.user_id,
        fds.app_id,
        ct.balance
    FROM full_date_series fds
    LEFT JOIN core_table ct 
        ON fds.date = ct.date 
        AND fds.user_id = ct.user_id 
        AND fds.app_id = ct.app_id
)
SELECT 
    date,
    -- 取分区内到当前行最近的非NULL user_id
    LAST_VALUE(user_id IGNORE NULLS) OVER (
        PARTITION BY user_id, app_id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS user_id,
    LAST_VALUE(app_id IGNORE NULLS) OVER (
        PARTITION BY user_id, app_id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS app_id,
    LAST_VALUE(balance IGNORE NULLS) OVER (
        PARTITION BY user_id, app_id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS balance
FROM joined_data
ORDER BY user_id, app_id, date;

方案2：兼容低版本MySQL（无IGNORE NULLS） #

WITH date_range AS (
    SELECT DISTINCT date FROM core_table WHERE date >= '2023-01-01'
),
user_app_groups AS (
    SELECT DISTINCT user_id, app_id FROM core_table WHERE user_id IS NOT NULL AND app_id IS NOT NULL
),
full_date_series AS (
    SELECT dr.date, uag.user_id, uag.app_id
    FROM date_range dr
    CROSS JOIN user_app_groups uag
),
joined_data AS (
    SELECT 
        fds.date,
        fds.user_id,
        fds.app_id,
        ct.balance,
        -- 标记每个非NULL值的分组，用于后续填充
        SUM(CASE WHEN ct.balance IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY fds.user_id, fds.app_id 
            ORDER BY fds.date
        ) AS group_id
    FROM full_date_series fds
    LEFT JOIN core_table ct 
        ON fds.date = ct.date 
        AND fds.user_id = ct.user_id 
        AND fds.app_id = ct.app_id
)
SELECT 
    date,
    MAX(user_id) OVER (PARTITION BY user_id, app_id, group_id) AS user_id,
    MAX(app_id) OVER (PARTITION BY user_id, app_id, group_id) AS app_id,
    MAX(balance) OVER (PARTITION BY user_id, app_id, group_id) AS balance
FROM joined_data
ORDER BY user_id, app_id, date;

关键说明 #

• 先通过CROSS JOIN生成每个用户-应用组合的完整日期序列，确保每个日期都有对应的用户应用记录。
• 窗口函数按user_id和app_id分区，保证仅在同一用户应用组内填充数据，避免跨组污染。
• LAST_VALUE(IGNORE NULLS)或分组标记法，均可实现连续NULL值的填充，自动继承最近的非NULL历史值。

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