连续日期范围 - 差距和岛屿

您可以在 CTE 的帮助下使用递归方法。为此，我添加了一个Row_Numberso ，以确保它是验证的下一条记录。

然后创建递归 CTE...命名cte。在这里，字段很重要rn as rn_init——这是创建记录链的基础。

;
WITH fr AS -- first records
(
SELECT [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]
  ,ROW_NUMBER()OVER( PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY 
   [AssignStart], [AssignEnd]) AS rn
FROM  T1
)
,cte AS
(
 SELECT [Asset_Id], [Trailer_Id]
      , [AssignStart] as [AssignStart_init] , [AssignEnd] as [AssignEnd_Init] 
      , [AssignStart] as [AssignStart],  [AssignEnd] as [AssignEnd]
      , 0 as lvl
      , rn as rn
      , rn as rn_init
 FROM fr
 --WHERE [AssignEnd] is not null

 UNION ALL

 SELECT 
     T1.[Asset_Id], T1.[Trailer_Id]
     ,C.[AssignStart_init],C.[AssignEnd_Init]
     ,T1.[AssignStart], T1.[AssignEnd]
     ,C.lvl + 1 
     ,T1.rn
     ,C.rn_init
 FROM fr AS T1
     INNER JOIN cte as C
        ON T1.[Asset_Id] = C.[Asset_Id]
        AND T1.[Trailer_Id] = C.[Trailer_Id]
        AND CONVERT(DATE,C.[AssignEnd]) = CONVERT(DATE,T1.[AssignStart])
        AND C.[AssignEnd] <= T1.[AssignStart]
        AND C.rn + 1 = T1.rn
)

这里处理NULL的是 AssignEnd 的情况，也很重要，要计算“记录链” count(rn_init) as c_rn_init。基于此，稍后对其进行过滤。

--select * from cte
,myPrecious AS
(
select  --C.* 
   C.[Asset_Id],c.[Trailer_Id]
    ,MIN([AssignStart]) as AssignStart
    --,MAX([AssignEnd]) as AssignEnd
    ,CASE WHEN MAX(isnull([AssignEnd],[AssignStart])) = MAX([AssignStart]) THEN NULL ELSE MAX(isnull([AssignEnd],[AssignStart])) END as [AssignEnd]
    ,count(rn_init) as c_rn_init
 from cte as c
group by C.[Asset_Id],c.[Trailer_Id], C.rn_init

 )

这如果最终查询。添加了 Row_number ，所以要过滤掉记录，ORDER BY c_rn_init DESC

 SELECT 
  Asset_Id,Trailer_Id,AssignStart,AssignEnd
  --,c_rn_init
  --,rn
  FROM(
          SELECT * 
               ,row_number() OVER(PARTITION BY [Asset_Id],[Trailer_Id] , AssignEnd ORDER BY c_rn_init DESC) as rn
          FROM myPrecious
      )A
 WHERE A.rn = 1
 order by [Asset_Id],[Trailer_Id]  ,[AssignStart],[AssignEnd]

输出：

Asset_Id    Trailer_Id  AssignStart         AssignEnd
33000       30000       01/04/2018 15:00:00 15/04/2018 10:30:00
33000       30000       01/05/2018 10:00:00 null
34000       31000       05/04/2018 10:00:00 10/04/2018 09:30:00
34000       32500       10/04/2018 09:31:00 null
37000       32600       19/04/2018 08:55:00 30/04/2018 17:30:00
37000       32600       01/05/2018 18:23:00 null
37124       32607       19/04/2018 08:55:00 null
38000       36000       01/05/2018 10:00:00 10/05/2018 06:30:00
38000       36000       15/05/2018 09:00:00 null

小提琴手

我的想法是从下一条记录中获取 AssignStart 值，并在 [AssignEnd] （当前行）和 AssignStart （下一行）之间进行天差（Step1）

如果是 sql server 2012，我们可以使用 LEAD

LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart

如果是 2008 年，我们不能使用 LEAD ，所以使用 OUTER APPLY 来获取记录：

 OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA

这将给我AssignStart下一行的，基于[Asset_Id], [Trailer_Id]

Step2：做那个差异栏：DATEDIFF(DAY,[AssignEnd],next_AssignStart)

SELECT 
  [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]
  ,next_AssignStart
  ,IsNUll(DATEDIFF(DAY,[AssignEnd],next_AssignStart),0) as diff_days
FROM
(
SELECT
    [Asset_Id], [Trailer_Id],T1.[AssignStart], [AssignEnd]
    --,LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart
    ,OA.[AssignStart] as next_AssignStart
FROM
   T1
   OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA
)A
ORDER BY [Asset_Id], [Trailer_Id], [AssignStart], [AssignEnd]

步骤 3：在此列上进行分组，从步骤 2

SELECT 
  [Asset_Id], [Trailer_Id]
  ,MIN([AssignStart]) as [AssignStart]
  ,CASE WHEN MAX(isnull([AssignEnd],[AssignStart])) = MAX([AssignStart]) THEN NULL ELSE MAX(isnull([AssignEnd],[AssignStart])) END as [AssignEnd]
FROM
(
SELECT
    [Asset_Id], [Trailer_Id], T1.[AssignStart], [AssignEnd]
    --,LEAD([AssignStart],1,[AssignStart]) OVER(PARTITION BY [Asset_Id], [Trailer_Id] ORDER BY [AssignStart]) as next_AssignStart
    ,OA.[AssignStart] as next_AssignStart
FROM
   T1
   OUTER APPLY
   (SELECT TOP(1) T2.[AssignStart]
      FROM T1 as T2
      WHERE T1.[Asset_Id] = T2.[Asset_Id]
          AND T1.[Trailer_Id] = T2.[Trailer_Id] 
          AND T1.[AssignStart]<T2.[AssignStart] 
      ORDER BY T2.[AssignStart] ASC 
   )OA
)A
GROUP BY [Asset_Id], [Trailer_Id], IsNUll(DATEDIFF(DAY,[AssignEnd],next_AssignStart),0)
ORDER BY [Asset_Id], [Trailer_Id],[AssignStart]

输出：

Asset_Id    Trailer_Id  AssignStart         AssignEnd
33000       30000       01/04/2018 15:00:00 15/04/2018 10:30:00
33000       30000       01/05/2018 10:00:00 null
34000       31000       05/04/2018 10:00:00 10/04/2018 09:30:00
34000       32500       10/04/2018 09:31:00 null
37000       32600       19/04/2018 08:55:00 null
37124       32607       19/04/2018 08:55:00 null
38000       36000       01/05/2018 10:00:00 10/05/2018 06:30:00
38000       36000       15/05/2018 09:00:00 null

ps：添加了Order by，所以我可以更容易地验证。

ps2：因为，NULL在MAX中被忽略了，所以我用这个表达式来处理区间的上半部分

小提琴手

我介绍了我的 T-SQL 解决方案。

小提琴 SQL Server 2017

问题：

我们需要按 Asset 和 Trailer 划分的重叠日期范围（基本时间单位“天”）打包记录。

解决方案：

打包重叠的日期范围需要 3 个步骤：

第 1 步：确定 GroupStart

    select Asset_Id, Trailer_Id, AssignStart, AssignEnd
                    , case when max(AssignEnd) 
                        over (partition by Asset_Id, Trailer_Id order by AssignEnd, AssignStart rows between unbounded preceding and 1 preceding)
                            >= AssignStart
                        then 0 else 1 end GroupStart
                from  (
                    select Asset_Id, Trailer_Id, 
                           convert(date, AssignStart) AssignStart, 
                           convert(date, coalesce(AssignEnd, '99991231')) AssignEnd
                    from T1
            ) t0

第 2 步：确定组

select Asset_Id, Trailer_Id, AssignStart, AssignEnd, sum(GroupStart) over (partition by Asset_Id, Trailer_Id order by AssignStart) Grp 
        from (...) t0
) p0

第 3 步：确定打包日期范围

select Asset_Id, Trailer_Id, min(AssignStart) AssignStart, max(AssignEnd) AssignEnd 
    from (...) p1
    group by Asset_Id, Trailer_Id, Grp

设置：

CREATE TABLE T1
    (Asset_Id int, Trailer_Id int, AssignStart datetime, AssignEnd DATETIME)
;
INSERT INTO T1
    (Asset_Id, Trailer_Id, AssignStart, AssignEnd)
VALUES
    (37124, 32607, '2018-04-19 08:55:00', '2018-05-05 10:00:00.000'),
    (37124, 32607, '2018-05-05 11:23:00', NULL),
    (33000, 30000, '2018-04-01 15:00:00', '2018-04-15 10:30:00.000'),
    (34000, 31000, '2018-04-05 10:00:00', '2018-04-10 09:30:00.000'),
    (34000, 32500, '2018-04-10 09:31:00', NULL),
    (37000, 32600, '2018-04-19 08:55:00', '2018-04-25 08:30:00.000'),
    (37000, 32600, '2018-04-25 09:23:00', '2018-04-25 10:00:00.000'),
    (37000, 32600, '2018-04-25 11:23:00', '2018-04-30 15:00:00.000'),
    (37000, 32600, '2018-04-30 16:15:00', '2018-04-30 17:30:00.000'),
    (37000, 32600, '2018-05-01 18:23:00', NULL),
    (38000, 36000, '2018-05-01 10:00:00', '2018-05-10 06:30:00.000'),
    (38000, 36000, '2018-05-15 09:00:00', '2018-05-20 11:00:00.000'),
    (38000, 36000, '2018-05-20 12:00:00', NULL),
    (33000, 30000, '2018-05-01 10:00:00', NULL)

完整查询：

 with packed as (
    select Asset_Id, Trailer_Id, min(AssignStart) AssignStart, max(AssignEnd) AssignEnd from (
        select Asset_Id, Trailer_Id, AssignStart, AssignEnd, sum(GroupStart) over (partition by Asset_Id, Trailer_Id order by AssignStart) Grp from (
            select Asset_Id, Trailer_Id, AssignStart, AssignEnd
                , case when max(AssignEnd) 
                    over (partition by Asset_Id, Trailer_Id order by AssignEnd, AssignStart rows between unbounded preceding and 1 preceding)
                        >= AssignStart
                    then 0 else 1 end GroupStart
            from (
                    select Asset_Id, Trailer_Id, 
                           convert(date, AssignStart) AssignStart, 
                           convert(date, coalesce(AssignEnd, '99991231')) AssignEnd
                    from T1
            ) t0
        ) p0
    ) p1
    group by Asset_Id, Trailer_Id, Grp
)
select Asset_Id, Trailer_Id, AssignStart, nullif(AssignEnd, '99991231')
from packed
order by Asset_Id, Trailer_Id, AssignStart;

您可以尝试自加入作为替代方案。

在我的环境中（使用示例数据），它生成的读取次数少于 OUTER APPLY。并制定成本更低的执行计划。但是当我添加不同的索引时，外部应用有时会排在首位。

--SET STATISTICS IO ON
--SET STATISTICS TIME ON

;WITH cteSequence AS
(
SELECT 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,CUR.AssignStart
    ,CUR.AssignEnd
    ,ROW_NUMBER() OVER(PARTITION BY Asset_Id,Trailer_Id ORDER BY AssignStart) AS SEQ
FROM 
    T1 CUR
)
SELECT 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,MIN(CUR.AssignStart) AS AssignStart
    ,CASE 
        WHEN MAX(COALESCE(NXT.[AssignEnd],CUR.[AssignStart])) = MAX(NXT.[AssignStart]) THEN NULL 
        ELSE MAX(COALESCE(CUR.[AssignEnd],NXT.[AssignStart])) END as [AssignEnd]
FROM 
    cteSequence CUR
        LEFT JOIN cteSequence NXT
            ON
            CUR.Asset_Id = NXT.Asset_Id
            AND
            CUR.Trailer_Id = NXT.Trailer_Id
            AND
            CUR.SEQ = NXT.SEQ-1
GROUP BY 
    CUR.Asset_Id 
    ,CUR.Trailer_Id
    ,COALESCE(DATEDIFF(DD,CUR.AssignEnd,NXT.AssignStart),0)