MySQL中用前一个非空值填充列内空值的实现问题

你用lag()窗口函数没得到预期结果，是因为lag()只能取紧邻的上一行数据，如果连续多行都是NULL，第二行及以后的NULL用lag()取到的还是NULL，没法延续前面的非空值。要实现连续空值填充前一个非空值，需要先把连续的空值和前面的非空值归为一组，再取组内的非空值。

核心思路 #

1. 生成分组标识：对每个账户，按交易日期排序，每遇到一个非空的Current_Daily_Balance，分组ID加1，空值行继承前一个非空行的分组ID。用COUNT(Current_Daily_Balance) OVER (PARTITION BY DDA_Account ORDER BY Transaction_Date)可以实现这个逻辑（COUNT忽略NULL，只有非空值才会让计数增加）。
2. 按分组填充空值：在每个分组内，取Current_Daily_Balance的非空值（比如MAX/MIN，因为组内只有一个非空值），替换组内的所有NULL。

适配你的查询的解决方案 #

结合你现有的递归日期查询和业务逻辑，调整后的SQL如下：

WITH recursive all_dates(dt) as 
(
SELECT (SELECT MIN(Transaction_Date) FROM test_acct WHERE DDA_Account = 25470) dt
UNION ALL
SELECT dt + interval 1 day from all_dates where dt < (SELECT MAX(Transaction_Date) FROM test_acct WHERE DDA_Account = 25470)
),
cte_acct_data AS (
    SELECT 
        c.DDA_Account, 
        DATE_FORMAT(Transaction_Date, '%Y-%m') as yr_mo, 
        c.Transaction_Date, 
        c.Current_Daily_Balance,
        -- 生成分组ID：每遇到非空值，分组ID递增
        COUNT(c.Current_Daily_Balance) OVER (PARTITION BY c.DDA_Account ORDER BY c.Transaction_Date) AS balance_group
    From
    (
        SELECT a.DDA_Account, a.Transaction_Date, a.Transaction_Amount, a.Sum_D_C as Current_Daily_Balance
        FROM
        (
            SELECT d.DDA_Account, d.Transaction_Date, d.Debit_or_Credit, d.Transaction_Amount,
            SUM(D_C_Amount) OVER (partition by DDA_Account order by Transaction_Date) as Sum_D_C
            FROM
            (
                SELECT DDA_Account, Transaction_Date, Debit_or_Credit, Transaction_Amount,
                CASE WHEN Debit_or_Credit = 'Credit' Then Transaction_Amount Else -1*Transaction_Amount END AS D_C_Amount
                FROM test_acct 
                WHERE DDA_Account = '25470'
            ) d
        ) a 
        GROUP BY Transaction_Date
        ORDER BY Transaction_Date ASC
    ) c
),
cte_filled_balance AS (
    SELECT
        DDA_Account,
        yr_mo,
        Transaction_Date,
        Current_Daily_Balance,
        -- 取分组内的非空值填充NULL
        MAX(Current_Daily_Balance) OVER (PARTITION BY DDA_Account, balance_group) AS Current_Daily_Balance_Expected,
        ROUND(AVG(Current_Daily_Balance) OVER (partition by DDA_Account, yr_mo order by Transaction_Date),2) as moving_avg,
        Row_number() OVER (PARTITION BY DDA_Account, Current_Daily_Balance, yr_mo ORDER BY Transaction_Date ASC) AS Row_Num
    FROM cte_acct_data
)
SELECT 
    IF(ISNULL(m.DDA_Account)=1,'25470',m.DDA_Account) as DDA_Account, 
    m.yr_mo, 
    l.dt as Transaction_Date, 
    m.Current_Daily_Balance, 
    m.moving_avg, 
    m.Row_Num,
    m.Current_Daily_Balance_Expected
FROM all_dates l 
LEFT JOIN cte_filled_balance m ON m.Transaction_Date = l.dt
ORDER BY l.dt ASC;

关键部分解释 #

• balance_group字段：通过COUNT(Current_Daily_Balance) OVER (...)生成，确保连续空值和前面的非空值属于同一个分组。比如2021-12-03的非空值对应分组ID，2021-12-04至06的空值会继承这个ID。
• MAX(Current_Daily_Balance) OVER (PARTITION BY DDA_Account, balance_group)：在每个分组内取非空的余额值，因为组内只有一个非空值，MAX和MIN结果一致，这样所有空值都会被替换成该组的非空值。

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