Historique de la question. La nécessité de compter des valeurs distinctes a émergé des charges analytiques suivant des métriques telles que l'acquisition cumulative de clients uniques ou l'introduction distincte de SKU au fil du temps. Avant les extensions de fonctions de fenêtre ANSI SQL:2003, les analystes s'appuyaient sur des auto-jointures ou des sous-requêtes corrélées, ce qui entraînait une complexité temporelle quadratique inacceptable pour les volumes de données modernes. La standardisation des fonctions de fenêtre a fourni un mécanisme basé sur les ensembles en temps linéaire pour maintenir la cardinalité courante sans boucles procédurales.
Le problème. Le SQL ANSI interdit explicitement le mot-clé DISTINCT dans les fonctions d'agrégation de fenêtres (par exemple, COUNT(DISTINCT col) OVER (...)). Cette restriction empêche le calcul direct des valeurs distinctes dans une fenêtre cumulative ou glissante. Le défi principal réside dans l'identification de la première apparition de chaque entité dans l'ordre de tri de la partition et la somme progressive de ces indicateurs binaires (première apparition = 1, sinon = 0).
La solution. L'approche canonique combine ROW_NUMBER() pour signaler les premières occurrences avec une fonction de fenêtre SUM() conditionnelle. En partitionnant ROW_NUMBER() par l'identifiant de l'entité, la première occurrence chronologique reçoit la valeur 1 ; les apparitions suivantes reçoivent des entiers croissants. Une requête externe somme ensuite une expression de cas émettant 1 uniquement lorsque le numéro de ligne est égal à 1, évaluée sur un cadre précédent non borné.
SELECT event_date, region_id, user_id, SUM(CASE WHEN rn = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY region_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_unique_users FROM ( SELECT event_date, region_id, user_id, ROW_NUMBER() OVER ( PARTITION BY region_id, user_id ORDER BY event_date, event_id -- event_id comme critère de départage ) AS rn FROM user_activity ) flagged;
Description du problème. Une startup fintech avait besoin de surveiller la conformité réglementaire en suivant le nombre cumulative de marchands intégrés par région de vente tout au long de l'année fiscale. Leur table merchant_signups contenait 120 millions de lignes avec region_code, merchant_id, et signup_timestamp. Les travaux batch Python existants prenaient 35 minutes pour calculer ces métriques chaque nuit, provoquant des retards de reporting et des données de tableau de bord obsolètes. L'exigence était de produire des décomptes cumulés en temps réel dans un SQL ANSI strict pour la portabilité à travers les entrepôts de données cloud.
Solution A : L'approche d'auto-jointure. Cette méthode joint la table à elle-même sur la correspondance de la région et des horodatages antérieurs, comptant des marchands distincts par ligne extérieure. Avantages : elle ne nécessite pas de support de fonction de fenêtre et fonctionne sur des moteurs SQL-92 anciens. Inconvénients : L'algorithme présente une complexité O(n²) ; pour des millions de lignes, cela génère des produits cartésiens intermédiaires consommant des téraoctets de stockage temporaire et ne parvient pas à se terminer en quelques heures, ce qui la rend opérationnellement peu faisable.
Solution B : La sous-requête scalaires corrélée. Ici, la clause SELECT intègre une sous-requête : (SELECT COUNT(DISTINCT merchant_id) FROM merchant_signups m2 WHERE m2.region_code = m1.region_code AND m2.signup_timestamp <= m1.signup_timestamp). Avantages : elle est déclarative et logiquement transparente à lire. Inconvénients : La sous-requête s'exécute une fois par ligne (120 millions de fois), empêchant la propagation des prédicats et causant des entrées/sorties aléatoires massives ; les optimisateurs de base de données ne peuvent pas découpler les agrégats distincts à travers des plages temporelles variées, résultant en des temps d'exécution estimés dépassant 90 minutes.
Solution C : La technique de fonction de fenêtre ANSI SQL. Utilisation de ROW_NUMBER() pour identifier les premières apparitions suivie par un SUM() courant comme montré dans l'exemple de code ci-dessus. Avantages : cela effectue une seule analyse de table avec tri, utilisant les capacités de spooling de fenêtre de l'optimiseur pour une complexité O(n log n) et une utilisation de mémoire bornée. Inconvénients : Cela nécessite une gestion soigneuse des égalités temporelles ; si deux inscriptions partagent des horodatages identiques, un ordonnancement non déterministe pourrait double-compter à moins qu'un critère de départage unique (comme event_id) soit ajouté à la clause ORDER BY.
Solution choisie et résultat. La solution C a été mise en œuvre. En incluant event_id dans le ORDER BY pour assurer une détection déterministe de la première apparition, la requête a été exécutée en 4 minutes sur le cluster existant — une amélioration de 9x. Le résultat a permis des tableaux de bord de conformité en temps réel, permettant aux responsables des risques de surveiller la diversité de l'intégration sans délais ETL, et la requête était entièrement portable à PostgreSQL, Snowflake, et BigQuery sans modification.
Pourquoi COUNT(DISTINCT column) OVER (ORDER BY ...) soulève-t-il une erreur de syntaxe dans un SQL ANSI strict ?
La norme SQL interdit explicitement le mot-clé DISTINCT dans l'argument d'une fonction d'agrégation de fenêtre tel que COUNT, SUM, ou AVG. Bien que certains fournisseurs (par exemple, PostgreSQL 16+, Oracle) offrent cela comme extension propriétaire, le SQL ANSI:2011 et les versions antérieures limitent les agrégats de fenêtre à fonctionner sur toutes les lignes dans le cadre défini. Cette limitation existe parce que le maintien d'une table de hachage d'ensemble distinct pour chaque cadre de fenêtre possible lors de l'évaluation de flux n'est pas requis par la grammaire standard. Les candidats doivent reconnaître que DISTINCT n'est autorisé que dans des fonctions d'agrégation standard n'ayant pas de clauses OVER, ou au sein de fonctions de distribution inverse comme PERCENTILE_CONT, mais jamais comme un décompte distinct de fenêtre.
Comment gérez-vous les horodatages en double lors de la détermination de la "première" occurrence d'une entité ?
ROW_NUMBER() attribue des valeurs arbitraires parmi les égalités à moins que la clause ORDER BY ne spécifie un classement total. Si un marchand a deux entrées avec des horodatages identiques, les deux lignes pourraient potentiellement recevoir rn = 1 si le classement est non déterministe, entraînant une augmentation erronée du décompte cumulatif. La solution consiste à ajouter une clé primaire unique ou un identifiant auto-incrémenté à la clause ORDER BY : ORDER BY signup_timestamp, merchant_signup_id. Cela assure un séquençage déterministe où l'ID précédemment attribué est considéré comme la première apparition, préservant l'intégrité mathématique du décompte distinct en cours.
Cette technique peut-elle être adaptée pour un décompte distinct mobile sur une fenêtre de nombre de lignes fixe (par exemple, les 100 dernières transactions) plutôt que sur un précédent non borné ?
Non, pas efficacement avec du pur SQL ANSI. La méthode non bornée précédente réussit parce que la distinctivité est monote. Une fois une entité apparue, elle reste "comptée" pour toujours. Dans une fenêtre glissante (par exemple, ROWS BETWEEN 100 PRECEDING AND CURRENT ROW), une entité sortant de la fenêtre doit diminuer le décompte, nécessitant de savoir si la ligne sortante représente la seule instance de cette entité dans le cadre actuel. Le SQL ANSI manque d'opérateurs d'agrégation de tableau ou de différence d'ensemble dans les cadres de fenêtre pour suivre efficacement cette sortie. La mise en œuvre nécessite soit des CTE récursives (qui se dégradent à O(n²) pour ce scénario) soit des extensions propriétaires comme ARRAY_AGG combinées à des opérations sur des ensembles, qui violent toutes les deux la conformité stricte au SQL ANSI.