Cette question a émergé de l'évolution des normes SQL de SQL-92 à SQL:2003, lorsque les fonctions de fenêtre ont été formellement introduites dans la norme. Avant cette avancée, les développeurs s'appuyaient sur des curseurs procéduraux ou des auto-joints coûteux en calcul pour résoudre des problèmes orientés séquences. Le motif des lacunes et des îlots représente le changement de paradigme des algorithmes procéduraux vers une logique déclarative et basée sur des ensembles qui définit des ensembles de résultats plutôt que des étapes de traitement.
Lors de l'utilisation de tables contenant des valeurs séquentielles telles que des horodatages, des ID ou des dates, vous devez regrouper des valeurs consécutives en blocs contigus (îlots) tout en les distinguant des interruptions (lacunes). Le défi fondamental survient parce que les tables ANSI SQL représentent des ensembles mathématiques non ordonnés, alors que la détection de séquences nécessite un ordre explicite. Les clauses GROUP BY traditionnelles agrègent des valeurs similaires mais détruisent les relations séquentielles nécessaires pour identifier la contiguïté.
Exploitez la différence arithmétique entre ROW_NUMBER() sur l'ensemble de l'ensemble de données et ROW_NUMBER() partitionné par la clé de regroupement pour générer un identifiant d'îlot constant. Cette technique crée des valeurs calculées identiques pour toutes les lignes d'une même séquence contiguë, permettant l'agrégation standard de reconstruire les îlots.
WITH numbered AS ( SELECT event_date, ROW_NUMBER() OVER (ORDER BY event_date) AS rn_global, event_date - ROW_NUMBER() OVER (ORDER BY event_date) AS island_grp FROM events ) SELECT MIN(event_date) AS island_start, MAX(event_date) AS island_end, COUNT(*) AS consecutive_days FROM numbered GROUP BY island_grp;
Une équipe d'analytique de vente au détail avait besoin de reconstruire les sessions de shopping des clients à partir de données de flux de clics stockées dans PostgreSQL. Le système a enregistré des millions d'événements contenant user_id et event_time, mais manquait d'identifiants de session pré-calculés. Les exigences commerciales définissaient une session comme une séquence d'événements où aucune lacune n'excédait 30 minutes d'inactivité.
La première approche a envisagé d'utiliser un auto-joint avec une sous-requête corrélée pour localiser le prédécesseur immédiat de chaque événement. Cette méthode nécessitait des comparaisons de lignes de O(n²), provoquant des temps d'expiration des requêtes lors du traitement de lots quotidiens dépassant cinq millions de lignes, bien qu'elle maintienne la compatibilité avec les systèmes SQL-92 hérités dépourvus de fonctions de fenêtre modernes.
L'équipe a ensuite évalué les curseurs pl/pgSQL pour parcourir les événements ligne par ligne tout en maintenant l'état de session dans des variables procédurales. Bien que cette approche offrait une logique intuitive familière aux développeurs d'applications, elle abandonnait les principes de traitement basé sur des ensembles et nécessitait plus de quatre heures pour terminer les lots quotidiens, créant une latence ETL inacceptable et d'importants problèmes de verrouillage de table.
La solution choisie a utilisé exclusivement des fonctions de fenêtre ANSI SQL. En appliquant LAG() pour capturer le précédent horodatage par utilisateur et en calculant les différences de temps, l'équipe a identifié les limites de session où les lacunes excédaient 30 minutes. Une somme courante conditionnelle a généré des identifiants de session uniques, permettant une agrégation basée sur des ensembles. Cette méthode a traité l'ensemble du jeu de données en huit minutes, a évolué de manière linéaire avec le volume et est restée portable à travers Oracle, SQL Server et PostgreSQL sans modifications de syntaxe spécifiques au fournisseur.
Pourquoi ne puis-je pas simplement tronquer les horodatages à l'heure et grouper par cette valeur pour trouver des sessions ?
Tronquer des horodatages avec DATE_TRUNC ou des fonctions similaires impose des frontières artificielles aux heures de l'horloge plutôt que des différences de temps relatives. Deux événements se produisant à 10h55 et 11h05 seraient séparés en différents groupes malgré une séparation de seulement 10 minutes, tandis que les événements à 10h01 et 10h59 seraient regroupés malgré un écart de 58 minutes. La véritable détection de session nécessite de calculer l'intervalle par rapport au prédécesseur immédiat de chaque événement, et non de s'aligner sur des frontières calendaires.
Comment les valeurs NULL dans la colonne d'ordre affectent-elles la détection des îlots en utilisant LAG ou LEAD ?
LAG et LEAD renvoient NULL pour les première et dernière lignes de chaque partition respectivement. Lorsque vous soustrayez l'horodatage décalé de l'horodatage actuel pour calculer les lacunes, une opération arithmétique avec NULL produit des résultats NULL, ce qui peut faire disparaître entièrement des îlots des agrégations. Vous devez utiliser le paramètre default optionnel dans LAG (par exemple, LAG(event_time, 1, event_time) OVER (...)) ou gérer explicitement NULL avec COALESCE pour éviter la fragmentation des îlots aux frontières de partition.
Qu'est-ce qui change lors de la détection des îlots à travers plusieurs catégories simultanément, comme par utilisateur ou par appareil ?
Les candidats omettent souvent la clause PARTITION BY dans les fonctions de fenêtre, calculant ROW_NUMBER globalement sur l'ensemble de la table plutôt que par catégorie. Sans partitionnement par user_id ou colonnes de regroupement équivalentes, les îlots de différents utilisateurs fusionnent incorrectement lorsque leurs séquences s'alignent temporellement. Chaque fonction de fenêtre impliquée dans le calcul des îlots doit inclure PARTITION BY user_id pour garantir que l'arithmétique se réinitialise pour chaque entité distincte, maintenant la détection des îlots de manière indépendante par partition.