如何在SQL中补全客户月度测试记录并延续历史得分？

现有临时表#temp存储客户测试得分，包含字段ClientID（客户ID）、DateCreated（创建日期）、Score（得分）。需求是按月份展示客户得分，对于客户未完成测试的过往或当前月份，需将上一次的测试得分延续至该空白月份。

示例场景：
ClientID 1在2023年11月、2024年1月完成测试，但2023年12月、2024年2月及3月未测试，需将2023年11月的得分56延续至2023年12月，将2024年1月的得分90延续至2024年2月、3月。

示例数据 #

Create Table #temp
(
    ClientID int,
    DateCreated datetime,
    Score decimal(18,2)
)

insert into #temp
(
    ClientID,
    DateCreated,
    Score
)
select
    1,
    '01 Nov 2023',
    56
union all
select
    1,
    '15 Jan 2024',
    90
union all
select
    2,
    '08 Dec 2023',
    76
union all
select
    2,
    '25 Jan 2024',
    98
union all 
select
    2,
    '01 Mar 2024',
    23

期望结果 #

解决方案 #

实现思路 #

1. 生成每个客户对应的完整月份范围（从客户最早测试月份到目标结束月份）
2. 将月份序列与原表关联，通过窗口函数获取最近的有效得分
3. 区分原始记录与自动生成记录，添加对应备注

SQL代码实现 #

-- 定义每个客户的日期范围
WITH ClientDateRanges AS (
    SELECT
        ClientID,
        DATEFROMPARTS(YEAR(MIN(DateCreated)), MONTH(MIN(DateCreated)), 1) AS FirstMonth,
        -- 可替换为指定结束月份，比如DATEFROMPARTS(2024, 3, 1)
        DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1) AS LastMonth
    FROM #temp
    GROUP BY ClientID
),
-- 生成每个客户的所有月份序列
ClientMonths AS (
    SELECT
        c.ClientID,
        DATEADD(MONTH, n.Number, c.FirstMonth) AS MonthStart
    FROM ClientDateRanges c
    JOIN master.dbo.spt_values n
        ON n.Type = 'P'
        AND DATEADD(MONTH, n.Number, c.FirstMonth) <= c.LastMonth
),
-- 关联原表并计算延续得分
CombinedData AS (
    SELECT
        cm.ClientID,
        cm.MonthStart,
        t.DateCreated,
        t.Score,
        -- 获取最近的非空得分
        LAST_VALUE(t.Score) OVER (
            PARTITION BY cm.ClientID
            ORDER BY cm.MonthStart
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS CarriedScore,
        CASE WHEN t.DateCreated IS NULL THEN '(auto-generated)' ELSE '' END AS Comments
    FROM ClientMonths cm
    LEFT JOIN #temp t
        ON cm.ClientID = t.ClientID
        AND MONTH(t.DateCreated) = MONTH(cm.MonthStart)
        AND YEAR(t.DateCreated) = YEAR(cm.MonthStart)
)
-- 整理输出结果
SELECT
    ClientID,
    ISNULL(DateCreated, MonthStart) AS DateCreated,
    CarriedScore AS Score,
    Comments
FROM CombinedData
ORDER BY ClientID, DateCreated;

说明 #

• 若不想依赖系统表master.dbo.spt_values，可改用递归CTE生成月份序列
• 如需固定结束月份，直接修改ClientDateRanges中的LastMonth值即可
• LAST_VALUE窗口函数确保每个空白月份都能继承最近一次的有效得分

内容的提问来源于Stack Exchange，提问作者Philip