Ce défi provient des domaines de planification de capacité et d'allocation de ressources, en particulier dans des systèmes comme les plateformes de réservation d'hôtels, l'infrastructure cloud à mise à l'échelle automatique et la planification des installations médicales. Les premières solutions reposaient sur des itérations basées sur des curseurs ou une logique d'application externe pour parcourir les chronologies, souffrant de pénalités de performance sévères sur de grands ensembles de données. L'avènement des fonctions de fenêtre ANSI SQL:2003 a permis d'adopter des approches purement relationnelles pour l'analyse temporelle, permettant aux bases de données de gérer efficacement l'arithmétique d'intervalles complexes au sein du moteur.
Étant donné une table de réservations de ressources avec des horodatages start_time et end_time, l'objectif est de déterminer le nombre maximum de réservations concurrentes actives à un moment donné, et d'identifier les fenêtres de temps spécifiques où ce pic s'est produit. La complexité réside dans le fait que l'agrégation standard collapse les données temporelles, tandis que de simples jointures créent une explosion cartésienne lorsque les intervalles se chevauchent. Une solution robuste doit traiter le début et la fin de l'intervalle comme des événements discrets, calculant un total courant de ressources actives à chaque point de transition.
L'approche canonique transforme les intervalles en événements discrets en utilisant UNION ALL pour séparer les débuts (poids +1) et les fins (poids -1), puis applique une somme cumulative via SUM() OVER (ORDER BY timestamp) pour suivre la concurrence. Pour traiter les débuts/fins simultanés de manière déterministe, les événements de fin doivent être traités avant les événements de début au même horodatage (en utilisant une clé de tri secondaire). Enfin, encapsulez cela dans un CTE pour filtrer la valeur maximale de concurrence.
WITH events AS ( SELECT start_time AS ts, 1 AS delta, 0 AS is_end FROM reservations UNION ALL SELECT end_time AS ts, -1 AS delta, 1 AS is_end FROM reservations ), concurrency AS ( SELECT ts, SUM(delta) OVER (ORDER BY ts, is_end, delta ROWS UNBOUNDED PRECEDING) AS concurrent_count FROM events ) SELECT MAX(concurrent_count) AS peak_concurrency FROM concurrency;
Pour trouver les fenêtres de temps spécifiques de l'utilisation de pointe, faites une jointure pour identifier les périodes entre les horodatages consécutifs où le compte est égal au maximum.
Une plateforme SaaS a suivi des millions de travaux de transcodage vidéo dans une table jobs avec des horodatages started_at et completed_at. L'équipe des opérations avait besoin d'identifier les périodes exactes où l'utilisation du GPU atteignait 100 % pour optimiser la planification des files d'attente.
Une approche envisagée était d'utiliser un curseur pour itérer chronologiquement, en incrémentant un compteur sur les débuts et en le décrémentant sur les fins. Bien que simple pour les développeurs familiers avec les langages impératifs, cette méthode traitait les lignes séquentiellement, prenant plus de 45 minutes sur des données de production et verrouillant les tables. Elle nécessitait également une gestion complexe des transactions pour assurer la cohérence des lectures.
Une autre alternative consistait à générer une table de séries temporelles avec une ligne par minute et à la joindre contre des intervalles en utilisant des prédicats BETWEEN. Cela produisait des résultats précis mais nécessitait des milliards de lignes pour une précision au niveau de la minute sur une année, consommant des téraoctets de stockage temporaire et échouant à capturer des pics de pointe inférieurs à la minute.
L'équipe a choisi l'approche basée sur les événements UNION ALL avec les fonctions de fenêtre ANSI SQL. En traitant les débuts et les fins comme des événements +1/-1, la requête s'est exécutée en 12 secondes en utilisant des index B-arbre standard sur les colonnes d'horodatage. Cette méthode a correctement géré les cas limites où les travaux se terminaient exactement au moment où d'autres commençaient.
L'analyse a révélé que la concurrence maximale s'est produite lors du traitement par lots nocturne entre 02:00 et 02:07 UTC, atteignant 847 travaux simultanés. En mettant en œuvre un throttling dynamique des files d'attente spécifiquement pour cette fenêtre, ils ont empêché des pannes en cascade et réduit la surprovisionnement d'infrastructure de 30 %.
Comment gérez-vous les intervalles de durée nulle (start_time = end_time) sans gonfler incorrectement le compte de concurrence ?
Les intervalles de durée nulle représentent des événements instantanés qui ne devraient pas contribuer à la charge concurrente. S'ils sont traités comme des intervalles standard, ils pourraient être comptés comme actifs pendant leur propre événement de fin. La solution nécessite d'attribuer une clé d'ordre stricte : traiter les événements de fin (-1) avant les événements de début (+1) lorsque les horodatages s'entrechoquent, et exclure les intervalles de durée nulle du flux d'événements entièrement ou leur attribuer une delta de 0, en fonction de la logique commerciale. En ANSI SQL, cela est mis en œuvre en ajoutant une colonne de discriminant : ORDER BY ts, is_end ASC, delta ASC, garantissant que les terminaisons décrémentent le compte avant que les nouvelles allocations ne l'incrémentent à l'horodatage identique.
Pourquoi l'approche basée sur les événements pourrait-elle retourner des résultats incorrects si vous utilisez UNION au lieu de UNION ALL lors de la combinaison des événements de début et de fin ?
UNION effectue implicitement une opération DISTINCT, réduisant ainsi les horodatages en double. Si deux réservations commencent exactement à 2023-10-01 10:00:00, UNION réduit cela à une seule ligne, ce qui conduit à ce que la somme cumulative manque une incrémentation +1. Cela entraîne un sous-comptage de la concurrence. UNION ALL préserve chaque limite d'intervalle individuelle comme un événement séparé, ce qui est mathématiquement requis car chaque réservation contribue indépendamment à la charge totale. Les candidats omettent souvent cette distinction, supposant l'unicité des horodatages où la multiplicité est essentielle pour une agrégation correcte.
Lorsque vous calculez les fenêtres de temps spécifiques de la concurrence maximale (et non seulement la valeur maximale), comment évitez-vous les lacunes dans la sortie si plusieurs périodes consécutives partagent la même valeur maximale ?
Après avoir identifié la valeur de concurrence maximale, rejoindre en arrière pour trouver tous les horodatages où cela se produit donne des points discrets. Pour reconstruire des blocs de durée continue, vous devez appliquer la technique Gaps and Islands : utilisez LAG() pour vérifier si la ligne précédente était également au maximum, et LEAD() pour vérifier si la ligne suivante est au maximum. Ne produisez que des lignes où la valeur précédente diffère (début de l'île) ou la valeur suivante diffère (fin de l'île). Ensuite, associez-les en utilisant ROW_NUMBER() pour créer des paires début-fin. Les candidats produisent souvent des listes brutes d'horodatages ou utilisent GROUP BY sur la valeur de compte, ce qui fait perdre l'information d'adjacence temporelle nécessaire pour distinguer des incidents de pic séparés d'une période de pic continue.