sql - 使行簇及时靠近

Question

我有一张基本上像这样的 table

   ID   |  ItemID  |       Start        |         End        |
---------------------------------------------------------------
    1       234      10/20/09 8:34:22      10/20/09 8:35:10
    2       274      10/20/09 8:35:30      10/20/09 8:36:27
    3       272      10/21/09 12:15:00     10/21/09 12:17:00
    4       112      10/21/09 12:20:14     10/21/09 12:21:21
    5       15       10/21/09 12:22:39     10/21/09 12:24:15

这里有两个条目“集群”，1-2 和 3-5 间隔时间间隔，特别是 > 30 分钟是我感兴趣的。

我想要的是条目集群的第一行和最后一行。通过检索所有行并按开始时间的顺序循环遍历它们，这很容易实现，但如果可能的话，我希望在 SQL 中使用它。

我正在使用 SQL Server 2008，谢谢。

编辑:

每行将包含

  first.* , last.*

其中 first 是集群中的第一行，last 是最后一行。

该表的结果将是

    1       234      10/20/09 8:34:22      10/20/09 8:35:10           2       274      10/20/09 8:35:30      10/20/09 8:36:27
    3       272      10/21/09 12:15:00     10/21/09 12:17:00          5       15       10/21/09 12:22:39     10/21/09 12:24:15

Answer 1

试试这个:

DECLARE @YourTable table (ID int, ItemID int, StartD datetime, EndD datetime)
INSERT @YourTable VALUES (1,234,'10/20/09 8:34:22' ,'10/20/09 8:35:10' )
INSERT @YourTable VALUES (2,274,'10/20/09 8:35:30' ,'10/20/09 8:36:27' )
INSERT @YourTable VALUES (3,272,'10/21/09 12:15:00','10/21/09 12:17:00')
INSERT @YourTable VALUES (4,112,'10/21/09 12:20:14','10/21/09 12:21:21')
INSERT @YourTable VALUES (5,15 ,'10/21/09 12:22:39','10/21/09 12:24:15')

;WITH AggValues AS
(SELECT
     MAX(ID) AS MaxID, COUNT(ID) AS CountOf, MIN(ID) AS MinID
     FROM @YourTable
)
, NumberRows AS
(SELECT --generate a first row to help get a range
     0 AS ID, a.StartD-1 AS StartD, a.EndD-1 AS EndD, 0 AS RowNumber
     FROM @YourTable a
         INNER JOIN AggValues dt ON a.ID=dt.MinID
 UNION
 SELECT --get all actual rows
     ID, StartD, EndD, ROW_NUMBER() OVER(ORDER BY ID) AS RowNumber
     FROM @YourTable
 UNION
 SELECT --generate a last row to help get a range
     dt2.MaxID+1 AS ID, a.StartD+1 AS StartD, a.EndD+1 AS EndD, dt2.CountOf+1 AS RowNumber
     FROM @YourTable a
         INNER JOIN AggValues dt2 ON a.ID=dt2.MaxID
)
, FindGaps AS
(SELECT
     a.ID,DATEDIFF(minute,b.StartD,a.EndD) AS Diff, a.RowNumber, c.RowNumber AS PreviousRowNumber
     FROM NumberRows                 a
         LEFT OUTER JOIN NumberRows  b ON a.RowNumber=b.RowNumber+1
         LEFT OUTER JOIN NumberRows  c ON a.RowNumber-1=c.RowNumber
)
, Gaps AS
(SELECT
     f.ID,f.RowNumber, f.PreviousRowNumber, ROW_NUMBER() OVER(ORDER BY ID) AS GapRowNumber
     FROM FindGaps f
     WHERE f.Diff>30
)
, Results AS
(SELECT
     g.ID,n.ID AS IDEnd
     FROM Gaps                       g
         LEFT OUTER JOIN Gaps        x ON g.GapRowNumber+1=x.GapRowNumber
         LEFT OUTER JOIN NumberRows  n ON x.PreviousRowNumber=n.RowNumber
     WHERE n.ID IS NOT NULL
)
SELECT
    a.*,b.*
    from Results                      r
        LEFT OUTER JOIN @YourTable    a ON r.ID=a.ID
        LEFT OUTER JOIN @YourTable    b ON r.IDEnd=b.ID

输出:

ID          ItemID      StartD                  EndD                    ID          ItemID      StartD                  EndD
----------- ----------- ----------------------- ----------------------- ----------- ----------- ----------------------- -----------------------
1           234         2009-10-20 08:34:22.000 2009-10-20 08:35:10.000 2           274         2009-10-20 08:35:30.000 2009-10-20 08:36:27.000
3           272         2009-10-21 12:15:00.000 2009-10-21 12:17:00.000 5           15          2009-10-21 12:22:39.000 2009-10-21 12:24:15.000

(2 row(s) affected)