Comment regrouper des horodatages consécutifs dans une table SQL ?

Question

J'ai une table SQL dans le serveur SQL avec des horodatages qui ressemble à ceci :

   Timestamps

1  2022-09-23 15:01:00
2  2022-09-23 15:02:00
3  2022-10-03 14:52:00
4  2022-10-03 14:53:00
5  2022-10-03 14:54:00
6  2022-10-03 14:56:00
7  2022-10-03 14:57:00
8  2022-10-03 14:58:00
9  2022-10-03 14:59:00

Je souhaite extraire de la table toutes les plages de dates consécutives pour lesquelles la différence entre chaque horodatage de la plage n'est que d'une minute. Voici le résultat souhaité :

   From                 To

1  2022-09-23 15:01:00  2022-09-23 15:02:00
2  2022-10-03 14:52:00  2022-10-03 14:54:00
3  2022-10-03 14:56:00  2022-10-03 14:59:00

Notez que, par exemple, les lignes 3, 4 et 5 sont regroupées en une seule ligne, car les horodatages 2022-10-03 14:52 , 2022-10-03 14:53 y 2022-10-03 14:54 sont consécutifs. Les autres horodatages de 2022-10-03 14:56 à 2022-10-03 14:59 est regroupée dans sa propre plage, parce qu'il y a un écart entre les plages (où les 2022-10-03 14:55 aurait été).

Quelle est la requête SQL qui permet d'atteindre cet objectif ?

Answer 1

Il s'agit d'un gaps and islands question,

En utilisant le premier cte pour trouver les différences entre les lignes consécutives à l'aide de la fonction de fenêtre LEAD() et le second cte pour trouver l'identifiant du groupe pour chaque ligne consécutive :

with cte as (
    select *, lead(Timestamps) over (order by Timestamps) as lead,
              case when 
              datediff(mi, Timestamps, LEAD(Timestamps) over (order by Timestamps)) = 1 then 0 else 1 end as diff
    from mytable
),
cte2 as (
  select *, sum(diff) over(order by Timestamps) as grp
  from cte
)
select grp+1 as range_id, min(Timestamps) as [From], max(lead) as [To]
from cte2
where lead is not null
group by grp

Résultat :

range_id    From                    To
1           2022-09-23 15:01:00.000 2022-09-23 15:02:00.000
2           2022-09-23 15:02:00.000 2022-10-03 14:54:00.000
3           2022-10-03 14:54:00.000 2022-10-03 14:59:00.000

Démonstration ici

Answer 2

Il semble qu'il s'agisse d'un cas classique de lacunes et d'îlots.

Exemple

Select TS1 = min([timestamps])
      ,TS2 = max([timestamps])
 From ( 
        Select *
              ,Grp = row_number() over( order by [timestamps])
                   - datediff(minute,0,[timestamps])
         From YourTable
      ) A
 Group By Grp

Résultats

TS1                         TS2
2022-10-03 14:56:00.000     2022-10-03 14:59:00.000
2022-10-03 14:52:00.000     2022-10-03 14:54:00.000
2022-09-23 15:01:00.000     2022-09-23 15:02:00.000

Answer 3

Vous pouvez également utiliser sessionnalisation pour cela, je trouve que c'est plus lisible : Créer un nouvel identifiant de session dès que l'écart entre deux horodatages n'est pas de 1 minute. Une requête en deux étapes, avec un compteur fixé à 1 chaque fois que l'écart n'est pas d'une minute, et une requête autour qui obtient la somme courante de ce compteur :

WITH
-- your input ...
indata(id,ts) AS (
          SELECT 1,{ts '2022-09-23 15:01:00'}
UNION ALL SELECT 2,{ts '2022-09-23 15:02:00'}
UNION ALL SELECT 3,{ts '2022-10-03 14:52:00'}
UNION ALL SELECT 4,{ts '2022-10-03 14:53:00'}
UNION ALL SELECT 5,{ts '2022-10-03 14:54:00'}
UNION ALL SELECT 6,{ts '2022-10-03 14:56:00'}
UNION ALL SELECT 7,{ts '2022-10-03 14:57:00'}
UNION ALL SELECT 8,{ts '2022-10-03 14:58:00'}
UNION ALL SELECT 9,{ts '2022-10-03 14:59:00'}
)
-- real query starts here - replace following comma with "WITH" 
,
-- sessionization part 1: counter at 1 if gap > 1 min
sess1 AS (
  SELECT
   *   
  ,CASE
     WHEN DATEDIFF(minute,LAG(ts) OVER(ORDER BY ts), ts) = 1 THEN 0
     ELSE 1
   END AS counter
  FROM indata
)
,
-- get the running sum of the obtained counter above to get a session id
sess2 AS (
  SELECT
    id
  , ts
  , SUM(counter) OVER(ORDER BY ts) AS session_id
  FROM sess1
)
SELECT 
  session_id
, MIN(ts) AS from_ts
, MAX(ts) AS to_ts
FROM sess2
GROUP BY session_id
ORDER BY 1

session_id

from_ts

to_ts

1

2022-09-23 15:01:00.000

2022-09-23 15:02:00.000

2

2022-10-03 14:52:00.000

2022-10-03 14:54:00.000

3

2022-10-03 14:56:00.000

2022-10-03 14:59:00.000

violon