Dans le contexte de la consolidation des périodes de couverture contractuelles qui se chevauchent en blocs continus effectifs, comment fusionner ces intervalles en plages disjointes et non superposées en utilisant strictement les fonctions de fenêtre SQL ANSI sans boucles procédurales?

Réponse à la question

Historique de la question. Le besoin de consolider des intervalles temporels qui se chevauchent provient de l'algèbre des intervalles d'Allen (1983) et des recherches sur les bases de données temporelles précoce. Les systèmes d'assurance, les plateformes de réservation d'hôtels et les applications de planification de ressources rencontrent souvent ce défi lorsque plusieurs périodes de couverture, réservations ou fenêtres de maintenance se chevauchent et nécessitent une normalisation en blocs disjoints et contigus pour un rapport de disponibilité ou un facturation précise. Contrairement à une simple agrégation, cette opération exige une compréhension de l'ordre et de la continuité, ce qui en fait un test standard de maîtrise des fonctions de fenêtre SQL ANSI avancées.

Le problème. Étant donné une table d'intervalles de dates définis par les colonnes start_date et end_date, l'objectif est de fusionner tous les intervalles qui se chevauchent ou qui sont adjacents en un ensemble minimal de plages non superposées. Une approche procédurale maintiendrait un tampon en cours d'exécution, comparant chaque ligne à la plage fusionnée actuelle, mais cela viole le paradigme basé sur les ensembles de SQL. La difficulté principale réside dans l'identification des "îlots" de continuité sans auto-joints ou CTE récursifs, en particulier lorsque des chevauchements transitoires existent (le domaine A se chevauche avec B, B se chevauche avec C, bien que A et C ne se touchent pas directement).

La solution. Utilisez les fonctions de fenêtre SQL ANSI pour détecter le début de chaque nouvel îlot en comparant la start_date de la ligne actuelle avec la end_date maximale de toutes les lignes précédentes dans la même partition. Lorsque start_date dépasse l'ancienne date de fin maximum, un nouvel îlot commence ; sinon, la ligne actuelle prolonge l'îlot existant. Attribuez un total en cours de ces indicateurs de "rupture" comme island_id, puis regroupez par cet identifiant pour calculer min(start_date) et max(end_date) consolidés. Cette approche atteint une complexité de O(n log n) grâce à un tri et une agrégation en une seule passe.

Situation de la vie réelle

Description du problème. Un fournisseur de soins de santé multinational maintenait une base de données de traitement des réclamations où les patients détenaient plusieurs polices d'assurance qui se chevauchent - couverture principale du 1er janvier au 31 mars, secondaire du 15 février au 15 avril, et tertiaire commençant le 1er mai. Le système existant générait des refus de réclamations en double parce qu'il traitait celles-ci comme des périodes actives séparées plutôt que comme un bloc de couverture continue du 1er janvier au 15 avril suivi par l'extension de mai. L'entreprise nécessitait une vue consolidée pour appliquer les règles de "pas de paiement en double" tout en préservant les pistes d'audit des enregistrements de poli d'origine.

Solution 1 : itération basée sur un curseur procédural. Une proposition initiale utilisait une procédure stockée avec un curseur ordonné par start_date, maintenant les variables @current_start et @current_end. Pour chaque ligne, si start_date ≤ @current_end, le code mettait à jour @current_end à max(@current_end, end_date) ; sinon, il émettait la plage actuelle et réinitialisait les variables. Pour : conceptuellement simple pour les développeurs avec des antécédents impératifs ; facile à déboguer étape par étape. Contre : nécessite des extensions procédurales PL/pgSQL ou T-SQL ; s'exécute ligne par ligne avec une mémoire O(n) mais une mauvaise performance I/O ; viole le besoin d'un SQL ANSI pure déclaratif qui peut fonctionner sur n'importe quel moteur conforme.

Solution 2 : auto-jointure avec détection de fermeture transitive. Une autre approche a effectué une auto-jointure t1 JOIN t2 ON t1.start_date <= t2.end_date AND t1.end_date >= t2.start_date pour trouver des chevauchements immédiats, puis a utilisé un CTE récursif pour parcourir le graphe de chevauchement et identifier les composants connectés. Pour : gère théoriquement correctement des relations transitoires complexes sans fonctions de fenêtre. Contre : génère O(n²) lignes intermédiaires avant la récursion ; computationnellement explosif pour de grands ensembles de données ; repose sur des CTE récursifs qui, bien qu'ils soient standard SQL ANSI, présentent des performances moins bonnes que les fonctions de fenêtre pour ce problème spécifique d'ordre linéaire.

Solution 3 : détection des lacunes par fonction de fenêtre (choisie). L'équipe a mis en œuvre une solution pure par fonction de fenêtre : marquer is_new_island = CASE WHEN start_date > MAX(end_date) OVER (PARTITION BY patient_id ORDER BY start_date ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) THEN 1 ELSE 0 END, puis calculé island_id = SUM(is_new_island) OVER (PARTITION BY patient_id ORDER BY start_date). L'agrégation finale était regroupée par patient_id, island_id. Pour : exécution en une seule passe s'appuyant sur la syntaxe standard SQL ANSI ; complexité O(n log n) limitée par le tri ; gère implicitement les chevauchements transitoires grâce au maximum courant. Contre : nécessite une gestion prudente des dates de fin NULL (couverture indéfinie) et de la sémantique des intervalles adjacents (si des plages touchantes fusionnent).

Résultat. Le déploiement a consolidé 2,3 millions d'enregistrements de police en 890 000 blocs de couverture continue en moins de 12 secondes sur un matériel standard, remplaçant un travail par lot basé sur un curseur de 45 minutes. La requête a été encapsulée sous la forme d'une vue, permettant des vérifications d'éligibilité en temps réel et éliminant 99 % des refus de réclamation en double pendant le trimestre suivant.

WITH coverage_flags AS (
    SELECT patient_id, start_date, end_date,
           CASE 
             WHEN start_date > MAX(end_date) OVER (
                 PARTITION BY patient_id 
                 ORDER BY start_date 
                 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
             ) THEN 1 
             ELSE 0 
           END AS is_new_island
    FROM insurance_periods
),
islands AS (
    SELECT patient_id, start_date, end_date,
           SUM(is_new_island) OVER (
               PARTITION BY patient_id 
               ORDER BY start_date
           ) AS island_id
    FROM coverage_flags
)
SELECT patient_id, 
       MIN(start_date) AS consolidated_start,
       MAX(end_date) AS consolidated_end
FROM islands
GROUP BY patient_id, island_id;

Ce que les candidats oublient souvent

Comment gérez-vous les intervalles adjacents qui se touchent aux extrémités, comme [1er janvier - 10] et [11 janvier - 20], et quelle modification de prédicat est nécessaire ?

Les candidats utilisent souvent une inégalité stricte start_date > previous_end_date, ce qui traite les intervalles adjacents comme des îlots séparés. Pour la couverture santé ou la planification continue, les périodes adjacentes représentent généralement un service ininterrompu et doivent fusionner. Le prédicat doit tenir compte du type d'intervalle : pour les intervalles fermés (début et fin inclus), utilisez start_date > previous_end_date + INTERVAL '1' DAY. Pour des intervalles semi-ouverts [start, end) (où end est exclusif), la condition start_date > previous_end_date fusionne naturellement les adjacents car le 11 janvier équivaut au 11 janvier. SQL ANSI prend en charge directement l'arithmétique d'intervalle, donc la solution nécessite CASE WHEN start_date > MAX(end_date) OVER (...) + INTERVAL '1' DAY THEN 1 ELSE 0 END.

Pourquoi la fonction de fenêtre MAX(end_date) produit-elle des frontières d'île incorrectes lorsque l'entrée contient des valeurs NULL représentant une couverture indéfinie ?

SQL ANSI des fonctions de fenêtre d'agrégation comme MAX() ignore les valeurs NULL dans le cadre. Si une police n'a pas de date de fin (NULL signifiant "courante"), MAX(end_date) sur les lignes précédentes retourne la dernière date non NULL, fusionnant potentiellement des intervalles ultérieurs qui devraient commencer un nouvel îlot après une lacune indéfinie. Les candidats doivent reconnaître que les NULL nécessitent un traitement spécial : soit les filtrer dans un CTE préliminaire, soit utiliser COALESCE(end_date, DATE '9999-12-31') pour traiter la couverture indéfinie comme s'étendant à l'infini. Alternativement, traitez NULL comme une rupture forcée en utilisant CASE WHEN end_date IS NULL THEN 0 ELSE 1 END logique, garantissant que la ligne suivante commence un nouvel îlot.

Comment étendriez-vous cette logique à un emballage multidimensionnel, comme la consolidation d'intervalles séparément pour chaque combinaison de patient_id et insurance_type sans perdre l'atomicité ?

De nombreux candidats tentent des sous-requêtes ou des auto-joints partitionnés manuellement. L'approche correcte exploite la clause PARTITION BY dans les fonctions de fenêtre SQL ANSI. Modifiez la spécification du cadre pour PARTITION BY patient_id, insurance_type dans les deux calculs de MAX(end_date) et SUM(is_new_island). Cela garantit que le maximum en cours et le compteur d'ID d'îlot se réinitialisent pour chaque groupe distinct, maintenant les performances O(n log n) à travers les partitions. Un échec à partitionner correctement provoque un "débordement" où une lacune dans la chronologie d'un patient déclenche incorrectement un nouvel îlot pour un autre patient, corrompant la logique de consolidation.