Le principe de Pareto est né des observations de Vilfredo Pareto concernant la propriété foncière en Italie, devenant plus tard un pilier du contrôle qualité et de la gestion des inventaires grâce aux travaux de Joseph Juran. Dans les bases de données relationnelles, cela se traduit par la nécessité d'une analyse ABC, où les analystes doivent identifier la minorité critique d'enregistrements générant la majorité de la valeur commerciale sans recourir à des outils statistiques externes.
Le problème nécessite de calculer un pourcentage cumulé d'un métrique ordonné décroissant par rapport au total absolu, puis de tronquer à la limite de 80 %. Étant donné que le SQL ANSI fonctionne sur des ensembles plutôt que sur des curseurs itératifs, les fonctions de fenêtre fournissent le mécanisme déclaratif. La solution utilise une somme cumulative partitionnée sur l'ensemble du jeu de résultats, ordonnée par la valeur en ordre décroissant, puis divisée par le total général dans le même contexte de ligne pour dériver un classement en pourcentage.
De manière critique, la spécification du cadre ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW garantit une accumulation déterministe ligne par ligne. Si un traitement strict des égalités est nécessaire, où tous les enregistrements partageant la valeur limite doivent être inclus ou exclus en tant qu'unité, RANGE remplacerait ROWS. La filtration finale doit avoir lieu dans une requête externe, car les fonctions de fenêtre sont logiquement calculées après la clause WHERE.
WITH ranked_products AS ( SELECT product_id, product_name, annual_revenue, SUM(annual_revenue) OVER ( ORDER BY annual_revenue DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_revenue, SUM(annual_revenue) OVER () AS total_revenue FROM inventory ), pareto_subset AS ( SELECT product_id, product_name, annual_revenue, CAST(cumulative_revenue AS DECIMAL) / total_revenue AS cumulative_pct FROM ranked_products ) SELECT product_id, product_name, annual_revenue, cumulative_pct FROM pareto_subset WHERE cumulative_pct <= 0.80;
Un détaillant national d'électronique faisait face à des coûts en hausse lors des audits d'inventaire trimestriels, nécessitant l'isolement des SKU à forte valeur représentant 80 % du capital total de l'entrepôt (50 millions de dollars à travers 40 000 articles) pour prioriser le comptage cyclique.
Solution 1 : Extraction de tableur impliquait des analystes exportant des fichiers CSV dans Excel, triant par coût unitaire, et sommant manuellement jusqu'à atteindre le seuil. Les avantages nécessitaient aucun temps de développement. Les inconvénients comprenaient des plantages d'application avec de grands ensembles de données, des recalculs horaires nécessaires, et empêchaient l'intégration en temps réel avec le système de gestion d'entrepôt.
Solution 2 : Calcul au niveau de l'application utilisait un script Python streamant les lignes et maintenant un accumulateur roulant. Les avantages offraient une logique flexible et un débogage facile. Les inconvénients introduisaient une latence réseau significative en transférant des millions de lignes, une exécution en un seul fil bloquant le tableau de bord analytique, et des contraintes de mémoire sur la machine cliente.
Solution 3 : Approche basée sur les ensembles SQL ANSI a implémenté directement la requête de fonction de fenêtre dans l'entrepôt PostgreSQL. Les avantages comprenaient une latence au niveau de la milliseconde, l'élimination du mouvement des données, et un rafraîchissement automatique avec des mises à jour nocturnes. Les inconvénients nécessitaient des connaissances avancées en SQL pour la maintenance.
Solution choisie et résultat : La solution 3 a été déployée en tant que vue, révélant que seulement 12 % des SKU représentaient 80 % de la valeur. La portée de l'audit a été réduite de 88 %, économisant 340 heures de travail par trimestre tout en maintenant une couverture complète de la valeur matérielle.
Comment le choix entre les spécifications de cadre ROWS et RANGE affecte-t-il le seuil de 80 % lorsque des valeurs en double existent ?
RANGE traite les lignes similaires partageant des valeurs ORDER BY identiques comme un seul groupe ; si la limite de 80 % tombe dans une égalité, RANGE inclut l'ensemble du groupe, dépassant potentiellement 80 %. ROWS traite les décalages physiques indépendamment des égalités, ce qui peut diviser une unité commerciale logique. Les candidats oublient souvent que le SQL ANSI permet un réglage explicite de ce comportement ; pour les rapports financiers, RANGE garantit que les périodes cohérentes ne sont pas divisées, tandis que ROWS offre une granularité plus fine pour les éléments distincts.
Pourquoi le calcul du pourcentage cumulatif doit-il être effectué dans une table dérivée ou CTE plutôt que directement dans la clause WHERE ?
Les fonctions de fenêtre sont logiquement évaluées lors de la phase SELECT, qui se déroule après que la clause WHERE filtre les lignes. Tenter de filtrer sur cumulative_revenue / total_revenue <= 0.8 directement dans WHERE produit une erreur de syntaxe car le résultat de la fenêtre n'est pas encore matérialisé. Les candidats éprouvent souvent des difficultés avec l'ordre de traitement logique du SQL ANSI : FROM → WHERE → GROUP BY → HAVING → WINDOW → SELECT → ORDER BY. La solution nécessite une imbrication pour calculer la fonction de fenêtre dans une requête interne, puis filtrer la colonne résultante dans une requête externe.
Comment optimiseriez-vous cette requête si la table d'inventaire contient des milliards de lignes et que le sous-ensemble de 80 % est estimé très petit ?
Les candidats omettent souvent le motif d'optimisation Top-N. Au lieu de calculer une fonction de fenêtre sur l'ensemble de la table, un filtre préliminaire utilisant une sous-requête avec DENSE_RANK() ou NTILE() peut limiter le calcul de la fenêtre aux candidats les plus significatifs. Alternativement, utiliser PARTITION BY si l'analyse est segmentée par catégorie empêche des scans de table complets. Comprendre que les fonctions de fenêtre forcent une opération de tri, et que l'indexation sur la colonne de revenus en ordre décroissant peut éliminer le coût du tri, est essentiel pour l'échelle.