问如何对每个组的时间范围在一个常数内的数据进行分组和计数？

EN

     DECLARE @TblUpdateTime AS TABLE
   (
      ID int,
      UpdatedTime datetime
   )

   INSERT INTO @TblUpdateTime VALUES (1, '2017-03-23 00:00:00')
   INSERT INTO @TblUpdateTime VALUES (2, '2017-03-23 00:00:02')
   INSERT INTO @TblUpdateTime VALUES (3, '2017-03-23 00:00:04')
   DECLARE @Constant int = 2 -- seconds



   ;WITH temp AS
      (
      SELECT tut.UpdatedTime, count(tut2.ID) AS grQuantity
      FROM @TblUpdateTime tut
      INNER JOIN @TblUpdateTime tut2 ON datediff(second, tut.UpdatedTime, tut2.UpdatedTime) BETWEEN 0 AND @Constant
      GROUP BY tut.UpdatedTime
      )
   SELECT max(t.grQuantity) AS MaxQuantity FROM temp t
   --SELECT max(t.grQuantity), t.UpdatedTime  AS MaxQuantity FROM temp t GROUP BY t.UpdatedTime -- If you want get max count by each UpdatedTime

;WITH cte_inputData(Id, UpdatedTime) AS
(
SELECT 1, CAST('2017-03-23 00:00:00' AS DATETIME) UNION ALL
SELECT 2, CAST('2017-03-23 00:00:02' AS DATETIME) UNION ALL
SELECT 3, CAST('2017-03-23 00:00:04' AS DATETIME) UNION ALL
SELECT 4, CAST('2017-03-23 00:00:06' AS DATETIME) UNION ALL
SELECT 5, CAST('2017-03-23 00:00:16' AS DATETIME) UNION ALL
SELECT 6, CAST('2017-03-23 00:00:26' AS DATETIME) UNION ALL
SELECT 7, CAST('2017-03-23 00:00:36' AS DATETIME) UNION ALL
SELECT 8, CAST('2017-03-23 00:00:38' AS DATETIME) UNION ALL
SELECT 9, CAST('2017-03-23 00:00:40' AS DATETIME) UNION ALL
SELECT 10, CAST('2017-03-23 00:00:50' AS DATETIME) UNION ALL
SELECT 11, CAST('2017-03-23 00:01:00' AS DATETIME)
),
--Compensate for any Missing sequences in the ID
cte_Sequence(Id, OriginalID, UpdatedTime) AS (
    SELECT ROW_NUMBER() OVER (
            ORDER BY ID
            ) AS Id,
        Id AS OriginalID,
        UpdatedTime
    FROM cte_inputData
    ),
cte_BuildResult AS (
    SELECT b.Id,
        CAST(b.Id AS VARCHAR(10)) + ',' + CAST(a.OriginalID AS VARCHAR(10)) AS GroupNumber,
        DATEDIFF(SECOND, b.UpdatedTime, a.UpdatedTime) AS Differance
    FROM cte_Sequence a
    INNER JOIN cte_inputData b
        ON a.Id = b.ID + 1
    ),
cte_resultBuilderFinal(ID, Differance) AS (
    SELECT TOP 1 MAX(Id),
        MAX(Differance)
    FROM cte_BuildResult
    GROUP BY Differance
    ORDER BY MAX(Differance) DESC
    )

SELECT GroupNumber,
Differance
FROM cte_BuildResult b
WHERE EXISTS (
    SELECT 1
    FROM cte_resultBuilderFinal a
    WHERE a.ID = b.Id
    )