Historique de la question : L'analyse des intervalles temporels a représenté un défi pour les bases de données relationnelles depuis les années 1970, car SQL a été initialement conçu sans types d'intervalles natifs. Les premières solutions s'appuyaient sur l'itération par curseur ou les produits cartésiens entre les intervalles, entraînant une complexité quadratique. L'introduction des fonctions de fenêtre dans SQL:2003 et la spécification de cadre ROWS BETWEEN ont permis des agrégats en cours d'exécution efficaces, établissant la base pour des calculs modernes de concurrence basés sur des événements.
Le problème : Déterminer la concurrence maximale nécessite de comprendre les moments précis où les changements d'état se produisent, en particulier lorsque qu'une réservation commence ou se termine. L'approche naïve consiste à développer chaque intervalle en unités de temps discrètes (découpe de temps), ce qui est prohibitif sur le plan computationnel pour les séjours de longue durée. Le véritable défi réside dans le calcul du nombre d'intervalles qui se chevauchent à un moment spécifique sans matérialiser chaque instant de la chronologie.
La solution : Appliquer un schéma de simulation d'événements discrets. Transformer la table des intervalles en un flux d'événements en utilisant UNION ALL, en attribuant un poids de +1 à chaque enregistrement (début) et -1 à chaque départ (fin). En triant chronologiquement ces événements et en appliquant une somme cumulative via SUM() OVER (ORDER BY ...) fonctions de fenêtre, vous dérivez le compte actif à chaque point de transition. La valeur maximale de cette somme cumulative représente la pointe de concurrence.
WITH events AS ( SELECT check_in AS event_time, 1 AS delta FROM reservations UNION ALL SELECT check_out AS event_time, -1 AS delta FROM reservations ), concurrency AS ( SELECT event_time, SUM(delta) OVER ( ORDER BY event_time, delta DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS active_count FROM events ) SELECT MAX(active_count) AS peak_concurrency FROM concurrency;
Description du problème : Une chaîne de complexes hôteliers de luxe a connu de mystérieux incidents de surbooking pendant les week-ends de vacances, bien que leur système de disponibilité signalait des places vacantes. La requête héritée calculait le taux d'occupation quotidien en éclatant chaque réservation en lignes individuelles pour chaque nuit à l'aide d'un CTE récursif, générant des millions de lignes pour des séjours d'un mois. Lors de l'analyse du réveillon du Nouvel An, cette approche nécessitait 12 secondes pour s'exécuter et bloquait la table des transactions de réservation, empêchant les réservations en temps réel.
Solution A : Expansion par découpe de temps avec des tables de comptage. L'équipe d'opérations a initialement suggéré de générer à l'avance une table de calendrier et de la joindre aux réservations en utilisant event_date BETWEEN check_in AND check_out. Cette méthode fournit des agrégats quotidiens intuitifs compatibles avec les clauses GROUP BY standard. Avantages : Conceptuellement simple pour les analystes commerciaux, facile à intégrer avec les outils BI existants. Inconvénients : Génère O(N × D) lignes où D est la durée moyenne, provoquant une croissance exponentielle ; échoue de manière catastrophique avec une granularité à la minute ou des baux de longue durée ; consomme un espace excessif dans tempdb.
Solution B : Arbre d'intervalles avec chemins matérialisés. Un architecte senior a proposé de mettre en œuvre une structure d'arbre de segments utilisant des ensembles imbriqués pour indexer les frontières des intervalles, permettant des requêtes de chevauchement en temps logarithmique. Avantages : Complexité théorique optimale pour les mises à jour fréquentes et les requêtes ponctuelles. Inconvénients : Nécessite des déclencheurs complexes pour maintenir l'équilibre de l'arbre ; viole la portabilité ANSI SQL en s'appuyant sur des extensions procédurales ; introduit une amplification d'écriture qui nuit à la charge de travail OLTP pendant les pics de réservation.
Solution C : Flux d'événements chronologiques avec sommes cumulées (choisie). L'équipe de base de données a adopté l'approche basée sur les événements, traitant chaque limite de réservation comme une opération delta. Cela a réduit l'ensemble de données de millions de lignes éclatées à exactement 2N événements (début et fin pour chaque réservation). Avantages : Complexité O(N log N) dominée par l'opération de tri, empreinte mémoire constante, et compatibilité pure avec ANSI SQL à travers PostgreSQL, Oracle, et SQL Server. Inconvénients : Nécessite une gestion soigneuse des événements simultanés et n'identifie pas intrinsèquement quelles réservations spécifiques ont contribué au pic sans jointures supplémentaires.
Résultat : La latence des requêtes est passée de 12 secondes à 45 millisecondes. L'analyse a révélé que le véritable goulet d'étranglement n'était pas l'inventaire des chambres (500 unités) mais la capacité de l'ascenseur, car 320 invités ont tenté des enregistrements simultanés à 18h. Cette révélation a incité à mettre en place des niveaux d'enregistrement échelonnés plutôt que de construire un nouvel aile, économisant 2 millions de dollars en dépenses d'investissement tout en éliminant les blocages.
Pourquoi la solution nécessite-t-elle ORDER BY event_time, delta DESC spécifiquement, et que se passe-t-il si vous omettez le tri secondaire sur delta ?
Les candidats ignorent fréquemment la sémantique des conditions limites aux horodatages partagés. Lorsque qu'un invité quitte à exactement 10h00 et qu'un autre s'enregistre à 10h00, l'ordre de traitement détermine si la chambre apparaît occupée par deux invités simultanément. En triant avec delta DESC, nous veillons à ce que -1 (départ) soit traité avant +1 (arrivée) à des horodatages identiques. Sans ce tri secondaire, la somme cumulative chute temporairement puis remonte, enregistrant potentiellement un pic fantôme alors que l'état précédent était en réalité plus élevé. Cet ordre subtil empêche les erreurs d'unité qui entraînent des vulnérabilités de surbooking dans les systèmes de production.
Comment modifieriez-vous cette requête pour identifier quelles réservations spécifiques étaient actives pendant le moment de la concurrence maximale, et non simplement le compte ?
La plupart des candidats tentent de filtrer au sein du même CTE, ne reconnaissant pas que le pic pourrait s'étendre sur un intervalle continu plutôt que sur un point unique. L'approche robuste nécessite une stratégie en deux passages : d'abord, isoler l'horodatage où active_count est égal au maximum à l'aide d'une sous-requête ou d'un CTE, puis revenir à la table des réservations d'origine en utilisant le prédicat de chevauchement r.check_in <= peak.event_time AND r.check_out > peak.event_time. Les candidats passent souvent à côté du fait que plusieurs horodatages pourraient partager la même valeur maximale, nécessitant une clause DISTINCT ou EXISTS pour éviter les doublons dans les listes de réservations lorsque le plateau de pic persiste à travers plusieurs transitions d'événements.
Quelles modifications sont nécessaires si les règles commerciales changent pour que l'heure de départ soit inclusive (l'invité occupe la chambre jusqu'à 23h59) plutôt qu'exclusive, et comment cela affecte-t-il le poids de l'événement ?
Les candidats négligent souvent la sémantique des limites d'intervalles. Les points d'extrémité inclusifs [début, fin] créent des chevauchements lorsqu'une réservation se termine et qu'une autre commence le même jour. La solution requiert de convertir les limites inclusives en exclusives en ajoutant un epsilon infinitésimal (ou la prochaine unité de temps discrète) aux heures de départ avant de générer l'événement -1. En alternative, ajustez la logique de jointure pour utiliser check_out >= event_time tout en gardant intacte la logique de somme cumulative. Ne pas procéder à cet ajustement transforme le modèle d'événement discret d'intervalles semi-ouverts à des intervalles fermés, entraînant des rapports de conflits où il n'existe aucune et sous-estimant la véritable capacité d'une unité pendant les périodes à fort turnover.