在多行中查找连续日期的开始和结束日期

小编典典

在多行中查找连续日期的开始和结束日期

sql

我在从表中显示正确的数据时遇到了麻烦。我不太确定要搜索什么。我不确定min（column）或max（column）在这里对我有帮助。让我们看看我是否可以解释我的问题。

我的表包含以下数据：

> Code (nvarchar) | DateFrom (datetime) | DateTo (datetime)
> ========================================================= 
> 3006            | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 
> 3006            | 2014-06-18 09:00:00 | 2014-06-18 22:00:00 
> 1006            | 2014-06-18 07:00:00 | 2014-06-18 09:00:00
> 1006            | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 
> 1006            | 2014-06-18 08:10:00 | 2014-06-18 18:00:00

我将以一种观点来介绍这一点。它将按代码分组。

我想要的是此输出：

> Code | DateFrom            | DateTo
> =========================================================
> 3006 | 2014-06-08 07:00:00 | 2014-06-18 08:00:00
> 3006 | 2014-06-18 09:00:00 | 2014-06-18 22:00:00
> 1006 | 2014-06-18 07:00:00 | 2014-06-18 18:00:00

如您所见，DateTo和DateFrom之间是否存在间隙，我希望将其显示为两行。但是，如果具有相同代码的下一个“
DateFrom”在DateTo结束之前（或同时）开始，则我希望改为显示“ DateTo”。

在这种情况下，我看不到如何使用功能max（）或min（）。由于时隙之间可能存在间隙。

你们有什么线索吗？

我正在使用MS SQL 2012

提前致谢！

编辑：评论。岛屿可能是我的祸根？

阅读 249

2021-03-23

共1个答案

小编典典

旧的答案有一个缺点：每行仅与前一行进行检查，以验证时间段是否重叠；如果前一行的时间段持续的时间更长，则逻辑将不考虑它。例如：

Code | DateStart           | DateFrom            | Overlap
-----+---------------------+---------------------+---------
1006 | 2014-06-18 07:00:00 | 2014-06-18 19:00:00 | 0 
1006 | 2014-06-18 08:10:00 | 2014-06-18 10:00:00 | 1 
1006 | 2014-06-18 16:00:00 | 2014-06-18 20:30:00 | 0 <- don't overlap with 
                                                        previous but overlap
                                                        with the first

为了改善这种PrevStop需要变得LastStop和有以前的最大的价值DateFrom为Code

With N AS (
  SELECT Code, DateFrom, DateTo
       , LastStop = MAX(DateTo) 
                    OVER (PARTITION BY Code ORDER BY DateFrom, DateTo 
                          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
  FROM   Table1
), B AS (
  SELECT Code, DateFrom, DateTo
       , Block = SUM(CASE WHEN LastStop Is Null Then 1
                          WHEN LastStop < DateFrom Then 1
                          ELSE 0
                    END)
                 OVER (PARTITION BY Code ORDER BY DateFrom, LastStop)
  FROM   N
)
SELECT Code
     , MIN(DateFrom) DateFrom
     , MAX(DateTo) DateTo
FROM   B
GROUP BY Code, Block
ORDER BY Code, Block

[SQLFiddle Demo](http://www.sqlfiddle.com/#!6/a5c59/22)

ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING需要从中删除当前行MAX。

旧答案

此查询仅在不完全在先例中的每个期间才有效。

这个想法是检查每一行是否与下一个/上一个链接。
如果行被链接，则它们形成一个块，它们将被分组在一起以得到第一个DateFrom和最后一个DateTo

With N AS (
  SELECT Code, DateFrom, DateTo
       , PrevStop = LAG(DateTo, 1, NULL) 
                    OVER (PARTITION BY Code ORDER BY DateFrom)
  FROM   Table1
), B AS (
  SELECT Code, DateFrom, DateTo
       , Block = SUM(CASE WHEN PrevStop Is Null Then 1
                          WHEN PrevStop < DateFrom Then 1
                          ELSE 0
                     END)
                 OVER (PARTITION BY Code ORDER BY PrevStop)
  FROM   N
)
SELECT Code
     , MIN(DateFrom) DateFrom
     , MAX(DateTo) DateTo
FROM   B
GROUP BY Code, Block
ORDER BY Code, Block

[SQLFiddle demo](http://www.sqlfiddle.com/#!6/e57dc/9) 在同一代码/天上添加了一些数据以检查更多块

该查询搜索块启动器，检查每行是代码的第一行（PrevStop IS NULL）还是在前一行（PrevStop < DateFrom）之外。

窗口SUM仅检索的前一行，ORDER以为链接的数据块创建成本值，例如，使用测试数据，我们将获得

Code | DateStart           | DateFrom            | Starter | Block
-----+---------------------+---------------------+---------+------
1006 | 2014-06-18 07:00:00 | 2014-06-18 09:00:00 |       1 |     1
1006 | 2014-06-18 08:10:00 | 2014-06-18 06:00:00 |       0 |     1
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 |       0 |     1
1006 | 2014-06-18 07:00:00 | 2014-06-18 07:30:00 |       1 |     2
1006 | 2014-06-18 08:00:00 | 2014-06-18 08:30:00 |       1 |     3
1006 | 2014-06-18 08:10:00 | 2014-06-18 09:00:00 |       0 |     3
3006 | 2014-06-18 07:00:00 | 2014-06-18 08:00:00 |       1 |     1
3006 | 2014-06-18 09:00:00 | 2014-06-18 10:00:00 |       1 |     2

分组Code并Block得到结果

2021-03-23