如何无循环实现SQL数据按条件变更拆分及时间区间统计

数据表定义 #

if exists(select top 1 1 from sys.tables where name='ObjInfo')
drop table ObjInfo

create table ObjInfo(
    id int identity,
    ObjNumber int,
    ObjDate datetime,
    ObjConditionId int
)

insert into ObjInfo(ObjNumber,ObjDate,ObjConditionId)
values(1,'2014-01-03',1)
,(1,'2014-01-05',1)
,(1,'2014-01-06',1)
,(1,'2014-01-08',2)
,(1,'2014-01-13',1)
,(1,'2014-01-15',1)
,(1,'2014-01-25',4)
,(2,'2014-01-01',1)
,(2,'2014-01-05',1)
,(2,'2014-01-07',2)
,(2,'2014-01-08',2)
,(2,'2014-01-12',2)
,(2,'2014-01-14',3)
,(2,'2014-01-15',4)

需求描述 #

按ObjNumber分组，展示每个对象每次ObjConditionId变更的时间区间，最后一次条件的结束时间用GETDATE()填充。

预期输出 #

ObjNumber ObjConditionId ConditionBeg ConditionEnd
1           1           2014-01-03      2014-01-08
1           2           2014-01-08      2014-01-13
1           1           2014-01-13      2014-01-25
1           4           2014-01-25      getdate()
2           1           2014-01-01      2014-01-07
2           2           2014-01-07      2014-01-14
2           3           2014-01-14      2014-01-15
2           4           2014-01-15      getdate()

原代码问题分析 #

你尝试的代码直接对每条记录使用LEAD函数，没有合并连续相同ObjConditionId的记录，导致输出结果包含重复的条件行，不符合需求。

正确实现代码 #

WITH GroupedCTE AS (
    -- 标记连续相同条件的分组起点
    SELECT 
        ObjNumber,
        ObjConditionId,
        ObjDate,
        CASE WHEN LAG(ObjConditionId) OVER (PARTITION BY ObjNumber ORDER BY ObjDate) = ObjConditionId THEN 0 ELSE 1 END AS GroupFlag
    FROM ObjInfo
),
ConditionGroups AS (
    -- 累计分组标识，生成每个连续条件组的唯一ID
    SELECT 
        ObjNumber,
        ObjConditionId,
        ObjDate,
        SUM(GroupFlag) OVER (PARTITION BY ObjNumber ORDER BY ObjDate) AS GroupId
    FROM GroupedCTE
),
GroupSummary AS (
    -- 聚合每个分组的最早日期作为条件开始时间
    SELECT 
        ObjNumber,
        ObjConditionId,
        MIN(ObjDate) AS ConditionBeg
    FROM ConditionGroups
    GROUP BY ObjNumber, ObjConditionId, GroupId
    ORDER BY ObjNumber, ConditionBeg
)
-- 获取每个分组的结束时间，最后一组用GETDATE()填充
SELECT 
    ObjNumber,
    ObjConditionId,
    ConditionBeg,
    ISNULL(LEAD(ConditionBeg) OVER (PARTITION BY ObjNumber ORDER BY ConditionBeg), GETDATE()) AS ConditionEnd
FROM GroupSummary;

代码说明 #

1. GroupedCTE：用LAG函数对比当前行与上一行的条件值，标记是否为新的条件组起点。
2. ConditionGroups：通过累加分组标识，将连续相同的条件归为同一个分组ID。
3. GroupSummary：按对象编号和分组ID聚合，取每组的最早日期作为条件开始时间。
4. 最终查询：用LEAD函数获取下一个条件组的开始时间作为当前组的结束时间，最后一组无后续分组时用GETDATE()填充。

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