SQL Server中如何为缺失日期填充最近日期的行数据？

要实现这个需求，核心思路是先生成当月完整的日期序列，再和账户列表组合出所有需要的日期-账户对，最后为每个组合匹配最近的可用余额，再计算平均值。以下是具体实现步骤和代码：

1. 假设你的基础表结构 #

假设存储余额的表名为AccountBalances，结构如下：

CREATE TABLE AccountBalances (
    AccountID VARCHAR(50),
    BalanceDate DATE,
    Balance DECIMAL(18,2)
);

2. 生成当月所有日期序列 #

用递归CTE生成当月的每一天，包括周末和无数据的日期：

WITH DateRange AS (
    -- 获取当月第一天
    SELECT CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS DATE) AS CalendarDate
    UNION ALL
    -- 递归生成后续日期，直到当月最后一天
    SELECT DATEADD(day, 1, CalendarDate)
    FROM DateRange
    WHERE CalendarDate < EOMONTH(GETDATE())
)

3. 提取所有账户列表 #

从余额表中获取不重复的账户，确保每个账户都能覆盖当月所有日期：

, AccountList AS (
    SELECT DISTINCT AccountID
    FROM AccountBalances
)

4. 匹配每个日期-账户的最近余额（兼容所有SQL Server版本） #

通过交叉连接生成完整的日期-账户组合，再用OUTER APPLY关联该账户在当前日期及之前最近的余额记录：

, FullBalanceData AS (
    SELECT
        al.AccountID,
        dr.CalendarDate,
        -- 取最近的可用余额，无历史数据则返回NULL（可按需调整默认值）
        ab.Balance
    FROM DateRange dr
    CROSS JOIN AccountList al
    OUTER APPLY (
        SELECT TOP 1 Balance
        FROM AccountBalances ab
        WHERE ab.AccountID = al.AccountID
          AND ab.BalanceDate <= dr.CalendarDate
        ORDER BY ab.BalanceDate DESC
    ) ab
)

5. 计算月均余额 #

对补全后的数据按账户分组计算平均余额：

SELECT
    AccountID,
    AVG(Balance) AS Avg_Balance
FROM FullBalanceData
GROUP BY AccountID
OPTION (MAXRECURSION 31); -- 递归次数限制，当月最多31天足够

简化版（SQL Server 2022及以上版本） #

如果你的SQL Server是2022或更高版本，可以用LAG函数的IGNORE NULLS参数简化逻辑，代码更紧凑：

WITH DateRange AS (
    SELECT CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS DATE) AS CalendarDate
    UNION ALL
    SELECT DATEADD(day, 1, CalendarDate)
    FROM DateRange
    WHERE CalendarDate < EOMONTH(GETDATE())
),
FullCombination AS (
    SELECT
        al.AccountID,
        dr.CalendarDate,
        ab.Balance
    FROM DateRange dr
    CROSS JOIN AccountList al
    LEFT JOIN AccountBalances ab
        ON ab.AccountID = al.AccountID
        AND ab.BalanceDate = dr.CalendarDate
),
FilledBalances AS (
    SELECT
        AccountID,
        CalendarDate,
        -- 向前填充最近的非空余额
        LAG(Balance) IGNORE NULLS OVER (PARTITION BY AccountID ORDER BY CalendarDate) AS FilledBalance
    FROM FullCombination
)
SELECT
    AccountID,
    AVG(FilledBalance) AS Avg_Balance
FROM FilledBalances
GROUP BY AccountID
OPTION (MAXRECURSION 31);

关键说明 #

• 递归CTE生成日期时，MAXRECURSION设为31足够覆盖任何月份的天数。
• OUTER APPLY的方式兼容所有SQL Server版本，而LAG IGNORE NULLS是2022新增特性，代码更简洁。
• 如果需要跨月份填充（比如用上月最后一天的余额填充当月第一天），只需保留BalanceDate <= dr.CalendarDate的条件即可。

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