如何计算各ID的日平均交易余额 已创建日历表不知如何补全每日余额

核心实现逻辑为先为每个ID生成全量日期序列，再向前填充空缺日期的余额，最后按ID聚合求平均值，具体实现步骤及代码如下：

前提说明 #

假设你的原始余额数据表名为#IDBalance，包含字段：ID（用户唯一标识）、firstdate（ID生成日期）、currentdate（余额记录日期）、balance（对应日期的余额），你已提前创建好#Calendar日历表。

实现代码 #

-- 步骤1：获取每个ID的时间边界，以及所有存在的余额记录
WITH IDDateRange AS (
    SELECT 
        ID,
        MIN(firstdate) AS id_start_date, -- 每个ID的生成日期
        MAX(currentdate) AS id_end_date -- 每个ID的最晚余额记录日期
    FROM #IDBalance
    GROUP BY ID
),
-- 步骤2：生成每个ID对应的完整日期序列，关联已有余额数据
IDFullDate AS (
    SELECT 
        dr.ID,
        c.CalendarDate,
        b.balance,
        -- 给非空的余额记录打分组标记，后续用来填充空缺
        SUM(CASE WHEN b.balance IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY dr.ID ORDER BY c.CalendarDate
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS balance_group
    FROM IDDateRange dr
    -- 关联日历表，取ID生成到最晚记录日之间的所有日期
    JOIN #Calendar c 
        ON c.CalendarDate BETWEEN dr.id_start_date AND dr.id_end_date
    -- 左关联原始余额表，匹配已有记录的余额
    LEFT JOIN #IDBalance b 
        ON dr.ID = b.ID 
        AND c.CalendarDate = b.currentdate
),
-- 步骤3：向前填充空缺日期的余额（无变动时沿用最近一次的余额值）
IDFullBalance AS (
    SELECT 
        ID,
        CalendarDate,
        MAX(balance) OVER (
            PARTITION BY ID, balance_group
        ) AS filled_balance
    FROM IDFullDate
)
-- 步骤4：计算每个ID的日平均余额
SELECT 
    ID,
    AVG(filled_balance) AS avg_daily_balance,
    DATEDIFF(day, MIN(CalendarDate), MAX(CalendarDate)) + 1 AS total_calculated_days
FROM IDFullBalance
GROUP BY ID

补充说明 #

• 如果你需要输出每个ID每天的余额明细（即你提到的手动计算结果效果），直接查询IDFullBalance的结果即可，无需最后一步聚合
• 原有日历表的循环生成方式在日期跨度大时性能较差，你可以改用CTE递归的方式生成日历表，性能提升更明显：

-- 优化版日历表生成代码，无需循环
WITH DateCTE AS (
    SELECT CAST('2000-01-01' AS DATE) AS CalendarDate
    UNION ALL
    SELECT DATEADD(day, 1, CalendarDate)
    FROM DateCTE
    WHERE CalendarDate < GETDATE()
)
SELECT * INTO #Calendar FROM DateCTE
OPTION (MAXRECURSION 0) -- 取消递归次数限制，适配大跨度日期

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