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

Question

我有一个表，其中包含以下字段：

1. ID (PK、整数、非空)
2. 更新时间(日期时间，不为空)

我正在尝试做的是按UpdatedTime对记录进行分组，并计算每个组的数量(实际上我只想从所有组中获取最大计数)。对于每组，UpdatedTime的最大差异小于常数，例如 2 秒或 10 分钟。

如何在此约束下对记录进行分组和计数？

---

例如：

常量 = 2 秒

{ID： 1， 更新时间： 2017-03-23

00：00：00}{ID： 2， 更新时间： 2017-03-23 00：00：02}

{ID： 3， 更新时间： 2017-03-23 00：00：04}

期望的结果就像

组 1： {ID： 1， ID： 2}， 组 2： {ID：2， ID： 3}

因此，每个组的计数为 2。 ID = 2 出现在预期的两个组中。

PS：不是必须明确找到组，我想要的只是(最大)计数

Answer 1

希望这对你有用。

;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
)

Answer 2

试试这个：

---

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