計數不在時間範圍內的項目

如果它們不在“組”中第一個項目的 30 秒內，我正在嘗試計算項目的數量。我很難弄清楚這一點。

所以，我有這張桌子：

WITH ADates AS (
   SELECT
         Id
       , SharedId
       , TheDateTime
   FROM (VALUES
       (CAST(1 AS int), CAST(1 AS int), CAST('2019-01-01 01:01:00.00' AS datetime2(7))),
       (2, 1, '2019-01-01 01:01:33.00'),
       (3, 1, '2019-01-01 01:02:00.00'),
       (4, 1, '2019-01-01 01:02:01.00'),
       (5, 1, '2019-01-01 01:02:04.00'),
       (6, 1, '2019-01-01 01:06:15.00'),
       (7, 2, '2019-01-01 01:06:00.00'),
       (8, 2, '2019-01-01 01:06:45.00'),
       (9, 1, '2019-01-01 01:02:31.00'),
       (10, 2, '2019-01-01 01:06:05.00'),
       (11, 2, '2019-01-01 01:06:46.00'),
   ) X (Id, SharedId, TheDateTime)
)

所以，我正在尋找的預期結果是：

+==========+=======+
| SharedId | Count |
+==========+=======+
| 1        | 4     |
+----------+-------+
| 2        | 2     |
+----------+-------+

數字由以下因素決定：

1. 從新組中的第一個開始計數。
2. 不在前一組的 30 秒內，因此它是一個新組併計數。
3. 不要計算，因為它在 2 的 30 秒內。
4. 不要計算，因為它在 2 的 30 秒內。
5. 從前一組的 30 秒內開始計數（第 2 項）。
6. 從前一組的 30 秒內開始計數（第 2 項）。
7. 計算 SharedId 的新組。
8. 計數，因為不在先前的分組內。

我想我應該為此做一個Window Function。只是不知道如何讓它依賴於小組中的第一個。

問題分類

我一直在尋找一種方法來使用跟踪內聯操作的分析函式。單個執行分析函式只能執行這麼多，但不能擴展到解決這個問題。嵌套分析函式的問題是我們失去了關於動態模式的資訊。

要允許動態內聯模式匹配，您可以在Oracle中使用MATCH_RECOGNIZE。不過，我不知道如何在Sql Server中執行此操作。然後我遇到了一個類似的問題，使用遞歸 CTE解決了這個問題。

---

建議的解決方案

擺弄解決方案

- SharedId    GroupStartDateTime
- 1           01/01/2019 01:01:00
- 1           01/01/2019 01:01:33
- 1           01/01/2019 01:02:04
- 1           01/01/2019 01:06:15
- 2           01/01/2019 01:06:00
- 2           01/01/2019 01:06:45 
6 rows

CteBase並且CteRecursive受到Bogdan Sahlean 對這個相關問題的回答的極大啟發。

WITH CteBase
AS
(
       SELECT  v.SharedId,
               v.TheDateTime,
               ROW_NUMBER() OVER(PARTITION BY v.SharedId ORDER BY v.TheDateTime) 
                      AS RowNum
       FROM    ADates v
),  CteRecursive
AS
(
       SELECT  crt.SharedId,
               crt.TheDateTime,
               crt.TheDateTime AS GroupStartDateTime,
               crt.RowNum,
               1 AS SharedIdRowNum
       FROM    CteBase crt
       WHERE   crt.RowNum = 1
       UNION ALL
       SELECT  crt.SharedId,
               crt.TheDateTime,
               CASE 
                   WHEN DATEDIFF(SECOND, prv.GroupStartDateTime, crt.TheDateTime) <= 30 
                   THEN prv.GroupStartDateTime 
                   ELSE crt.TheDateTime 
               END,
               crt.RowNum,
               CASE 
                   WHEN DATEDIFF(SECOND, prv.GroupStartDateTime, crt.TheDateTime) <= 30 
                   THEN prv.SharedIdRowNum + 1
                   ELSE 1
               END             
       FROM    CteBase crt
       INNER JOIN CteRecursive prv ON crt.SharedId = prv.SharedId 
       AND     crt.RowNum = prv.RowNum + 1
)
SELECT SharedId, Count(*) as [COUNT] FROM (
       SELECT  r.SharedId,
               r.GroupStartDateTime
       FROM    CteRecursive r
       WHERE   r.SharedIdRowNum = 1
) X
GROUP BY SharedId;

引用自：https://dba.stackexchange.com/questions/239590