在SSMS中统计各ID最新值的连续天数（含节假日/周末）

针对你提出的需求，我们可以通过窗口函数标记连续值分组 + 日期区间总天数计算的组合方式来实现，下面是具体的思路、代码和细节解释：

核心思路拆解 #

1. 先给每个ID的记录按日期排序，标记出Value发生变化的节点，把连续相同Value的记录归为同一分组；
2. 定位每个ID对应的最新值分组（也就是最后一段连续相同值的记录块）；
3. 计算该分组的起始日期到该ID最新记录日期之间的总天数（自动包含中间未记录的节假日/周末，因为日期差计算会覆盖区间内所有日期）。

完整SQL代码 #

WITH RankedValues AS (
    -- 第一步：给每个ID的记录按日期排序，标记连续值分组
    SELECT 
        Id,
        Date,
        Value,
        -- 当当前行Value与前一行不同时，分组编号+1
        SUM(CASE WHEN LAG(Value) OVER (PARTITION BY Id ORDER BY Date) = Value THEN 0 ELSE 1 END) 
            OVER (PARTITION BY Id ORDER BY Date) AS GroupId
    FROM YourTableName -- 替换成你的实际表名
),
LatestGroups AS (
    -- 第二步：筛选每个ID的最新值组
    SELECT 
        Id,
        Value,
        MIN(Date) AS StartDate, -- 该值连续块的起始日期
        MAX(Date) AS EndDate   -- 该ID的最新记录日期
    FROM RankedValues
    GROUP BY Id, Value, GroupId
    HAVING MAX(Date) = (SELECT MAX(Date) FROM YourTableName WHERE Id = RankedValues.Id)
)
-- 第三步：计算总天数（日期差+1，因为包含起始和结束当天）
SELECT 
    Id,
    Value,
    DATEDIFF(day, StartDate, EndDate) + 1 AS Days,
    CONCAT('对应 ', CONVERT(VARCHAR, StartDate, 101), ' & ', CONVERT(VARCHAR, EndDate, 101)) AS Note
FROM LatestGroups;

代码细节解释 #

1. RankedValues CTE：

   • 使用LAG(Value) OVER (PARTITION BY Id ORDER BY Date)获取当前记录的上一条同ID记录的Value；
   • 通过SUM(...) OVER (...)生成分组编号，每当Value变化时分组编号递增，确保相同连续Value的记录被分到同一个GroupId下。

2. LatestGroups CTE：

   • 按Id、Value、GroupId分组，提取每个分组的起始和结束日期；
   • 通过HAVING子句筛选出每个ID的最新记录所在的分组，也就是我们需要统计的目标值块。

3. 最终查询：

   • DATEDIFF(day, StartDate, EndDate) + 1用于计算总天数：日期差会自动覆盖区间内所有日期（包括未记录的周末/节假日），加1是因为要包含起始当天。

验证结果 #

针对你给出的示例数据，运行上述代码后会得到：

完全匹配你的期望输出。

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