计数不在时间范围内的项目

它与第 6 项在另一组中的第 7 项之后有关。因此，它在我的查询中被视为一个新计数，结果为 5。这次 id=6 是否正确？可以在 id=7 之后吗？

查询变得如此复杂以至于我无法解释，或者已经太晚了（?），这不好，但是……。?

下一个似乎产生了正确的结果（有 9 条记录）：

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'),
        (9, 1, '2019-01-01 01:02:31.00'),
        (8, 2, '2019-01-01 01:06:45.00')
    ) X (Id, SharedId, TheDateTime)
),
TMPADates AS (
SELECT 
    Id, 
    SharedId, 
    TheDateTime, 
    --DATEADD(S, 30, TheDateTime ) TheDateTime30, 
    ISNULL((SELECT MIN(TheDateTime) 
            FROM ADates t2 
            WHERE t2.TheDateTime BETWEEN DATEADD(s,-30,ADates.TheDateTime) AND ADates.TheDateTime
              and ADates.SharedId=t2.SharedId),TheDateTime) ingroup
FROM ADates
),
TMPAdates2 AS (
SELECT 
    Id, 
    SharedId, 
    TheDateTime, 
    ingroup,
    -- ISNULL(LAG(ingroup) OVER (ORDER BY SharedId,ingroup DESC),ingroup) as ingroup2
    ISNULL(LAG(ingroup) OVER (PARTITION BY SharedId ORDER BY SharedId,ingroup DESC),ingroup) as ingroup2
FROM TMPADates 
)
SELECT 
    SharedId,  COUNT(DISTINCT ingroup2) As Count
FROM TMPADates2
GROUP BY  SharedId

问题分类

我一直在寻找一种方法来使用跟踪内联操作的分析函数。单个运行分析函数只能执行这么多，但不能扩展到解决这个问题。嵌套分析函数的问题是我们丢失了关于动态模式的信息。

要允许动态内联模式匹配，您可以在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;

关于什么：

    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')
    ) X (Id, SharedId, TheDateTime)
),
TMPADates AS (
SELECT 
    Id, 
    SharedId, 
    TheDateTime, 
    DATEADD(S, 30, TheDateTime ) TheDateTime30, 
    ISNULL((SELECT MIN(TheDateTime) FROM ADates t2 WHERE t2.TheDateTime BETWEEN DATEADD(s,-30,ADates.TheDateTime) AND ADates.TheDateTime),TheDateTime) ingroup
FROM ADates
)
SELECT 
    SharedId,  COUNT(DISTINCT ingroup) AS Count
FROM TMPADates
GROUP BY  SharedId