Contar itens fora do intervalo de tempo

Tem algo a ver com o item 6 estar depois do item 7 que está em outro grupo. Por isso, é visto como uma nova contagem na minha consulta, resultando em 5. Este tempo com id=6 está correto? pode ser depois de id=7?

A consulta ficou tão complexa que não consigo explicar, ou já é tarde demais (?), o que não é bom, mas…. ?

O próximo parece produzir os resultados corretos (com os 9 registros):

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

Categorização do problema

Eu estava procurando uma maneira de usar uma função analítica que acompanhe as manipulações em linha. Uma função analítica de execução única só é capaz de realizar tanto, mas não a ponto de resolver esse problema. O problema com o aninhamento de funções analíticas é que perdemos informações sobre nosso padrão dinâmico.

Para permitir a correspondência dinâmica de padrões embutidos, no Oracle você pode usar MATCH_RECOGNIZE . Eu não tinha ideia de como fazer isso no Sql Server . Então me deparei com um problema semelhante, que foi resolvido usando um CTE recursivo .

Solução proposta

Brinque com a solução

- 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

CteBasee CteRecursivesão fortemente inspirados pela resposta de Bogdan Sahlean nesta questão relacionada .

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;

A respeito:

    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