Détecter les dates consécutives en SQL et lancer le compteur

Question

Dates

2021-01-01

2021-01-02

2021-01-03

2021-01-10

2021-01-11

2021-01-12

2021-01-15

Sortie attendue :

Dates

Compteur

2021-01-01

1

2021-01-02

2

2021-01-03

3

2021-01-10

1

2021-01-11

2

2021-01-12

3

2021-01-15

1

Savez-vous comment y parvenir ? Je sais comment trouver le minimum, le maximum et le nombre de dates consécutives, mais j'ai besoin d'un compteur qui se réinitialise chaque fois qu'il y a un écart entre les dates.

Answer 1

Une façon d'aborder ce problème de lacunes et d'îlots est de calculer un rang basé sur la différence avec la date précédente.

La méthode pour calculer la différence en jours entre les dates dépend toutefois de votre SGBDR.

Cet exemple utilise DATEDIFF (MS Sql Server)

SELECT 
 Dates, 
 ROW_NUMBER() OVER (PARTITION BY Rnk ORDER BY Dates) AS Counter
FROM
(
    SELECT 
     Dates, 
     SUM(CASE 
         WHEN DATEDIFF(day, prevDate, Dates) = 1 THEN 0 
         ELSE 1 END) OVER (ORDER BY Dates) AS Rnk 
    FROM
    (
      SELECT 
       Dates, 
       LAG(Dates) OVER (ORDER BY Dates) AS prevDate
      FROM your_table
    ) q1
) q2
ORDER BY Dates;

Answer 2

Vous pouvez utiliser une auto join :

with cte as (
   select row_number() over (order by dt) r, dt from test
)
select c.dt, sum(c.dt = date_add(c1.dt, interval c.r-c1.r day)) 
from cte c join cte c1 on c1.r <= c.r group by c.dt order by c.dt