Comment détecter et extraire efficacement des plages de dates consécutives dans SQL ?

Détection de plages de dates consécutives en SQL

Introduction

Dans diverses applications, nous rencontrons fréquemment le besoin de regrouper et d'extraire plages de dates consécutives à partir d’une entrée donnée. Cette tâche se pose dans des situations telles que le remplissage du calendrier ou l'analyse des données. En tirant parti des puissantes capacités de SQL, nous pouvons résoudre efficacement le problème sans avoir besoin de jointures complexes ou de CTE récursives.

Définition du problème

Considérez une colonne nommée « InfoDate » qui contient une séquence de dates, comme illustré ci-dessous :

InfoDate

2013-12-04

2013-12-05

2013-12-06

2013-12-09

2013-12-10

2014-01-01

2014-01-02

2014-01-03

2014-01-06

2014-01-07

2014-01-29

2014-01-30

2014-01-31

2014-02-03

2014-02-04

L'objectif est d'identifier chaque plage de dates consécutive et d'en extraire la dates de début et de fin pour chaque intervalle. Comme illustré dans l'exemple ci-dessous, les dates consécutives doivent être regroupées pour former une plage :

StartDate	EndDate
2013-12-04	2013-12-06
2013-12-09	2013-12-10
2014-01-01	2014-01-03
2014-01-06	2014-01-07
2014-01-29	2014-01-31
2014-02-03	2014-02-04

Solution SQL

Pour obtenir ce résultat en utilisant SQL, nous utilisons une technique qui utilise la combinaison de la numérotation des lignes et de la différenciation des dates. En utilisant la fonction ROW_NUMBER(), nous attribuons un numéro séquentiel (« i ») à chaque ligne de la colonne « InfoDate » et créons une colonne nommée « d ». Cette colonne représente la différence entre chaque date et son numéro de ligne correspondant.

WITH t AS (
  SELECT InfoDate d,ROW_NUMBER() OVER(ORDER BY InfoDate) i
  FROM @d
  GROUP BY InfoDate
)

L'étape suivante consiste à regrouper les lignes par la différence entre les colonnes 'i' et 'd' (DATEDIFF(day,i, d)). Ce regroupement nous permet d'identifier des dates consécutives puisqu'elles partageront la même valeur de différence.

SELECT MIN(d),MAX(d)
FROM t
GROUP BY DATEDIFF(day,i,d)

En utilisant les fonctions MIN() et MAX() au sein de chaque groupe, nous pouvons déterminer les dates de début et de fin de chaque plage consécutive.

| StartDate | EndDate |
|---|---|
| 2013-12-04 | 2013-12-06 |
| 2013-12-09 | 2013-12-10 |
| 2014-01-01 | 2014-01-03 |
| 2014-01-06 | 2014-01-07 |
| 2014-01-29 | 2014-01-31 |
| 2014-02-03 | 2014-02-04 |

De cette manière, nous avons extrait efficacement les plages de dates consécutives en utilisant une approche purement basée sur SQL.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!