Le mode statistique représente la valeur la plus fréquemment rencontrée dans un ensemble de données. Bien que ANSI SQL définisse des fonctions d'agrégation standard telles que AVG, SUM et COUNT, il omet notablement une fonction d'agrégation MODE intégrée. Cette absence découle de l'accent mis par le modèle relationnel sur les résultats scalaires et de l'ambiguïté inhérente que le mode présente en cas d'égalité. Par conséquent, les praticiens doivent reconstruire cette mesure statistique à l'aide de tables dérivées et de fonctions de fenêtre.
Calculer le mode nécessite d'identifier la valeur avec le maximum de fréquence dans chaque partition. La complexité provient de deux contraintes : premièrement, les fonctions d'agrégation ne peuvent pas être imbriquées directement (par exemple, MAX(COUNT(*))), et deuxièmement, les égalités pour la fréquence la plus élevée doivent être résolues de manière déterministe pour garantir un résultat unique par groupe. Une solution doit fonctionner comme une seule déclaration déclarative sans boucles procédurales ni extensions spécifiques au fournisseur.
L'approche utilise une structure CTE (Common Table Expression) en deux étapes. Tout d'abord, calculez les fréquences en utilisant GROUP BY avec COUNT(*). Deuxièmement, appliquez la fonction de fenêtre RANK() partitionnée par les clés de groupement, ordonnée par fréquence décroissante et par la valeur elle-même croissante pour le départage. Le filtre pour RANK() = 1 donne le mode. Cette méthode est strictement conforme à ANSI SQL:2003 et s'exécute en une seule analyse de table.
WITH frequency_cte AS ( SELECT group_id, target_value, COUNT(*) AS val_freq FROM observations GROUP BY group_id, target_value ), ranked_cte AS ( SELECT group_id, target_value, RANK() OVER ( PARTITION BY group_id ORDER BY val_freq DESC, target_value ASC ) AS freq_rank FROM frequency_cte ) SELECT group_id, target_value AS mode_value FROM ranked_cte WHERE freq_rank = 1;
Une équipe d'analyse du commerce électronique avait besoin de faire rapport sur la taille de produit la plus populaire (mode) pour chaque catégorie de vêtements sur une base mensuelle afin d'optimiser les niveaux de stock dans l'entrepôt. La table sales contenait des millions de lignes avec les colonnes category_id, sale_month, et size_label. Une règle commerciale critique exigeait que si deux tailles étaient à égalité pour le volume de ventes le plus élevé, le système devait systématiquement sélectionner la plus petite taille alphanumérique (par exemple, "M" avant "L") pour maintenir des projections d'inventaire déterministes.
Solution 1 : Sous-requête corrélée avec comparaison scalaire.
Une approche consistait à utiliser une sous-requête corrélée pour trouver le maximum de compte pour chaque groupe, puis à joindre à nouveau pour trouver la taille correspondante. Cette méthode s'appuyait sur des fonctionnalités SQL-92 standard disponibles dans les systèmes hérités. La sous-requête calculait la fréquence maximale par paire catégorie-mois de vente, et la requête externe filtrait pour les tailles correspondant à cette fréquence. Bien que universellement compatible, cette approche souffrait d'une complexité temporelle quadratique O(n²) en raison de la corrélation. Elle nécessitait plusieurs passes sur les données et avait du mal à gérer élégamment le départage, nécessitant souvent des sous-requêtes supplémentaires pour résoudre les doublons. Le plan de la requête impliquait des jointures imbriquées qui se dégradaient considérablement à mesure que le volume des ventes augmentait.
Solution 2 : Fonction de fenêtre avec classement déterministe.
La solution choisie utilisait des fonctions de fenêtre ANSI SQL:2003 comme expliqué dans la solution générale ci-dessus. En matérialisant les fréquences dans un CTE et en appliquant RANK(), l'optimiseur de base de données pouvait utiliser des opérations basées sur le tri et des agrégations par hachage. Cette approche s'exécutait en temps linéaire O(n log n), se mettait à l'échelle horizontalement avec un bon indexage sur category_id et sale_month, et gérait le départage naturellement grâce à la clé de tri secondaire. La résolution déterministe des égalités garantissait que l'algorithme d'inventaire recevait des entrées cohérentes, empêchant des recommandations fluctuantes entre les exécutions de rapport.
Résultat.
L'implémentation a réduit le temps de génération de rapport de 12 minutes à 8 secondes sur un ensemble de données de 50 millions d'enregistrements. Le départage déterministe a éliminé les divergences dans les systèmes de réapprovisionnement automatisés, réduisant les ruptures de stock pour les tailles secondaires populaires de 15%.
Pourquoi l'imbrication d'agrégats comme MAX(COUNT(*)) produit-elle une erreur de syntaxe, et comment l'ordre de traitement logique de SQL nécessite-t-il l'approche basée sur CTE ?
De nombreux candidats essaient d'écrire SELECT group_id, MAX(COUNT(*)) FROM ... sans savoir que ANSI SQL interdit l'imbrication des fonctions d'agrégation. L'ordre de traitement logique dicte que WHERE, GROUP BY, et HAVING s'exécutent avant SELECT, ce qui signifie que les résultats agrégés ne sont pas disponibles lors de la phase de groupement. L'approche CTE ou sous-requête crée un pipeline où la première étape matérialise les comptages en tant que table dérivée, les rendant disponibles comme valeurs scalaires pour le classement des fonctions de fenêtre dans la deuxième étape. Comprendre cette séparation des phases d'agrégation et de fenêtrage est crucial pour construire des requêtes SQL valides.
Comment le choix entre RANK(), DENSE_RANK(), et ROW_NUMBER() affecte-t-il l'exactitude du calcul du mode lorsque des égalités existent, et pourquoi est-il essentiel d'avoir un départage déterministe ?
Les candidats ont souvent tendance à utiliser ROW_NUMBER() car cela garantit exactement une ligne par partition. Cependant, ROW_NUMBER() attribue de manière arbitraire des entiers distincts aux lignes à égalité en fonction de l'ordre de tri physique, ce qui peut potentiellement sélectionner une valeur de mode différente à chaque exécution si la clé de tri secondaire est omise. RANK() identifie correctement toutes les valeurs à égalité comme rang 1, nécessitant une logique explicite de départage (par exemple, MIN(target_value)) pour satisfaire la condition d'un "exactement un résultat" de manière déterministe. DENSE_RANK() retournerait également des lignes à égalité mais avec une numérotation consécutive, ce qui le rend inadapté pour un simple filtrage sans logique supplémentaire. Un comportement déterministe garantit que les applications analytiques et les pipelines ETL en aval reçoivent des résultats cohérents et reproductibles.
Quelles sont les implications en terme de cardinalité et de mémoire de l'utilisation d'une auto-jointure par rapport aux fonctions de fenêtre pour l'analyse de fréquence, et comment cela impacte-t-il la planification des requêtes ?
Une idée reçue courante est que les fonctions de fenêtre surpassent toujours les jointures. Dans le calcul du mode, une approche d'auto-jointure joindrait la table de fréquence agrégée à elle-même sur group_id et val_freq = max_freq, produisant potentiellement un produit cartésien au sein des groupes en cas de nombreuses égalités. Cela crée des ensembles de résultats intermédiaires avec une cardinalité égale à la somme des égalités, ce qui pourrait exploser l'utilisation de la mémoire. En revanche, les fonctions de fenêtre comme RANK() effectuent un calcul basé sur le tri, nécessitant une mémoire proportionnelle à la taille de la partition pour maintenir le tampon de tri. Les candidats manquent que bien que les fonctions de fenêtre soient généralement plus rapides, elles peuvent déborder sur disque si les tailles de partition dépassent work_mem (en termes de PostgreSQL) ou les limites de tampon équivalentes, tandis que les auto-jointures basées sur le hachage pourraient mieux fonctionner pour des clés de groupement à très haute cardinalité avec peu d'égalités. Comprendre ces compromis permet aux développeurs d'analyser les plans EXPLAIN et d'optimiser les paramètres de tampon en conséquence.