MS SQL中基于LAG函数按日期间隔小于15天分组计算时长方法

这是典型的**间隙与孤岛（Gaps and Islands）**场景，你之前用LAG计算相邻记录间隔的逻辑已经完成了90%的工作，只需要补充分组标识的计算即可实现需求，不需要写循环或者复杂的多层嵌套。

实现逻辑 #

• 第一步：将你原有带窗口函数计算的查询作为基础结果集，解决窗口函数结果无法直接在同层用于分组的问题
• 第二步：为每条记录打分组起点标记：如果当前记录和上一条记录的间隔≥15天（或是该人员的第一条咨询记录），则标记为新分组的起点
• 第三步：通过累计求和窗口函数，为同一连续区间的记录分配相同的组ID
• 第四步：按人员ID、组ID分组，聚合计算每组的总咨询时长等指标

可直接运行的参考代码 #

WITH BaseData AS (
    -- 复用原有基础查询，计算单条记录时长、和上一条记录的间隔
    SELECT 
        Person.Id AS PersonId, 
        Inquiry.Id AS InquiryId, 
        Inquiry.StartDate, 
        Inquiry.EndDate, 
        DATEDIFF(DAY, Inquiry.StartDate, Inquiry.EndDate) AS Duration, 
        LAG(Inquiry.EndDate) OVER (PARTITION BY Person.Id ORDER BY Inquiry.StartDate) AS EndDatePreviousFile, 
        DATEDIFF(DAY, LAG(Inquiry.EndDate) OVER (PARTITION BY Person.Id ORDER BY Inquiry.StartDate), Inquiry.StartDate) AS DateDiffStartPreviousEnd 
    FROM Person 
    INNER JOIN Inquiry ON Person.Id = Inquiry.PersonId
    -- 如需查询全量人员，删除下面的单人员过滤条件即可
    -- WHERE Person.Id = 1
),
GroupAssign AS (
    SELECT
        *,
        -- 标记新分组起点：第一条记录/和上一条间隔≥15天则判定为新组
        CASE WHEN DateDiffStartPreviousEnd IS NULL OR DateDiffStartPreviousEnd >=15 THEN 1 ELSE 0 END AS IsNewGroup,
        -- 累计求和生成组ID：同一段连续区间内的记录GroupId值完全一致
        SUM(
            CASE WHEN DateDiffStartPreviousEnd IS NULL OR DateDiffStartPreviousEnd >=15 THEN 1 ELSE 0 END
        ) OVER (PARTITION BY PersonId ORDER BY StartDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupId
    FROM BaseData
)
-- 最终分组聚合计算
SELECT
    PersonId,
    GroupId,
    MIN(StartDate) AS GroupStartDate,
    MAX(EndDate) AS GroupEndDate,
    SUM(Duration) AS TotalGroupDuration
FROM GroupAssign
GROUP BY PersonId, GroupId
ORDER BY PersonId, GroupStartDate

注意事项 #

• 代码中的间隔阈值>=15可以根据实际业务规则调整，比如如果要求间隔小于等于15天归为同组，就把新组判断条件改为DateDiffStartPreviousEnd >15即可
• 窗口函数的PARTITION BY PersonId已经做了人员维度的数据隔离，去掉单人员过滤条件后可以直接计算全量人员的分组结果，不会出现跨人员混组的问题
• 如果需要关联查询每组下包含的具体咨询记录明细，直接查询GroupAssign CTE的结果即可，PersonId+GroupId就是全局唯一的组标识

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