Lors de l'établissement des limites des valeurs aberrantes statistiques pour les indicateurs de contrôle de qualité, comment calculez-vous la **plage interquartile (IQR)** au sein des ensembles de données groupés en utilisant strictement des fonctions de fenêtres et des agrégats **ANSI SQL**, puis utilisez cette mesure pour filtrer les observations se trouvant au-delà de 1,5×IQR des quartiles ?

Réponse à la question

Historique de la question

Le concept de IQR provient de la méthodologie d'analyse exploratoire des données de John Tukey, développée dans les années 1970, fournissant une statistique robuste pour la détection des valeurs aberrantes qui reste résistante aux valeurs extrêmes. Au fur et à mesure que l'entreposage de données évoluait, les analystes sont passés de packages statistiques procéduraux à des requêtes SQL basées sur les ensembles, nécessitant des implémentations natives de ces calculs dans les bases de données. ANSI SQL:2003 a introduit des fonctions de distribution inverse, affinées plus tard dans SQL:2011, permettant des calculs de percentiles directement au sein du moteur de base de données sans traitement externe.

Le problème

Le défi réside dans le calcul du premier quartile (Q1, 25e percentile) et du troisième quartile (Q3, 75e percentile) pour chaque sous-groupe au sein d'un ensemble de données afin de dériver l'IQR (Q3 moins Q1). Une fois établi, les limites statistiques des valeurs aberrantes sont définies à Q1 − 1,5×IQR et Q3 + 1,5×IQR. La complexité réside dans la réalisation de ces calculs statistiques à travers des partitions en une seule opération basées sur les ensembles, tout en maintenant l'exactitude, puis en filtrant l'ensemble de données d'origine par rapport à ces limites calculées dynamiquement sans recours à des boucles procédurales ou un traitement au niveau de l'application.

La solution

Utilisez PERCENTILE_CONT(0.25) et PERCENTILE_CONT(0.75) comme fonctions de fenêtres ordonnées partitionnées par la colonne de regroupement, qui effectuent une interpolation linéaire pour déterminer des valeurs quartiles exactes. Calculez l'IQR et les conditions limites dans une Expression de Table Commune (CTE), puis rejoignez ce résultat ou filtrez directement en utilisant des clauses WHERE qui comparent les mesures aux limites calculées.

WITH quartiles AS (
  SELECT 
    facility_zone,
    temperature,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q1,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY temperature) 
      OVER (PARTITION BY facility_zone) AS q3
  FROM sensor_readings
),
bounds AS (
  SELECT 
    facility_zone,
    temperature,
    (q3 - q1) AS iqr,
    q1 - 1.5 * (q3 - q1) AS lower_fence,
    q3 + 1.5 * (q3 - q1) AS upper_fence
  FROM quartiles
)
SELECT facility_zone, temperature, iqr
FROM bounds
WHERE temperature < lower_fence 
   OR temperature > upper_fence;

Situation de la vie réelle

Une entreprise pharmaceutique surveille des congélateurs à très basse température stockant des vaccins dans 200 zones d'installation. Chaque zone génère 10 000 relevés de température par jour. La détection des valeurs aberrantes par l'écart type simple a échoué car des fluctuations occasionnelles du réseau électrique ont causé des pics extrêmes qui ont faussé la moyenne, entraînant des faux négatifs pour des dysfonctionnements subtils de l'équipement. L'équipe qualité avait besoin d'une méthode statistique robuste pour signaler uniquement ces relevés s'écartant de manière significative de la plage de fonctionnement typique de la zone, mise en œuvre directement dans leur entrepôt de données PostgreSQL pour alimenter des tableaux de bord Tableau en temps réel.

Solution 1 : Traitement au niveau de l'application utilisant Python et Pandas

Extraire toutes les données historiques via ODBC dans un service Python, calculer les quartiles à l'aide de groupby().quantile(), puis filtrer et écrire les résultats. Avantages : bibliothèques statistiques extrêmement flexibles, débogage facile grâce à une exécution par étapes, et syntaxe familière pour les scientifiques des données. Inconvénients : surcoût réseau massif pour le transfert de millions de lignes, contraintes de mémoire causant des erreurs d'application, et obsolescence des données en raison de fenêtres de traitement de 45 minutes rendant les résultats obsolètes à l'achèvement.

Solution 2 : SQL ANSI natif utilisant des fonctions de fenêtre PERCENTILE_CONT

Implémentez la requête en utilisant PERCENTILE_CONT comme agrégats des ensembles ordonnés avec des clauses OVER partitionnées par facility_zone. Avantages : zéro transfert de données, exploite les index existants en B-arbre sur les identifiants de zone, réduit le temps de traitement à moins de 15 secondes, et fournit des résultats en temps réel directement utilisables par les outils BI. Inconvénients : nécessite une base de données conforme à SQL:2003/2011 (non disponible dans les anciennes versions de MySQL), crée des opérations de tri temporaires qui augmentent le CPU pendant l'exécution, et implique une syntaxe complexe peu familière pour de nombreux développeurs d'application.

Solution 3 : Approximation utilisant NTILE(4)

Divisez les relevés de chaque zone en quatre seaux égaux à l'aide de la fonction de fenêtre NTILE, puis utilisez MIN() et MAX() sur les seaux 1 et 4 pour approximer les limites Q1 et Q3. Avantages : compatible avec les anciennes versions de bases de données manquant de fonctions de distribution inverse, s'exécute plus rapidement grâce à des calculs approximatifs. Inconvénients : produit uniquement des limites approximatives non conformes aux réglementations, échoue catastrophiquement avec de petites tailles d'échantillons ou de fortes valeurs égales, et introduit un comportement non déterministe lorsque les limites se situent entre des relevés de capteur discrets.

Solution choisie et résultat

L'équipe a sélectionné Solution 2 (approche PERCENTILE_CONT) car les réglementations pharmaceutiques imposent des calculs statistiques exacts plutôt que des approximations. L'admin de base de données a créé des index composites sur (facility_zone, temperature), éliminant les opérations de tri. La requête résultante a identifié 0,03 % des relevés comme de véritables valeurs aberrantes statistiques, déclenchant des inspections automatisées des congélateurs qui ont empêché une perte d'inventaire d'une valeur d'environ 2 millions de dollars par an tout en réduisant les coûts d'infrastructure en supprimant la couche ETL Python.

Ce que les candidats oublient souvent

Pourquoi PERCENTILE_CONT produit-il des résultats différents de PERCENTILE_DISC lors du calcul des quartiles, et lequel doit être utilisé pour l'IQR ?

PERCENTILE_CONT (continu) effectue une interpolation linéaire entre les deux valeurs les plus proches entourant la position du percentile demandé, retournant une valeur calculée qui pourrait ne pas exister dans l'ensemble de données d'origine. PERCENTILE_DISC (discret) retourne la plus petite valeur de distribution cumulative supérieure ou égale au percentile, sélectionnant effectivement une mesure observée réelle. Pour les calculs d'IQR dans la détection des valeurs aberrantes, PERCENTILE_CONT est généralement préféré car il fournit une échelle continue moins sensible aux artefacts d'échantillonnage discrets, bien que PERCENTILE_DISC devienne nécessaire lorsque les limites des valeurs aberrantes doivent correspondre à des valeurs physiquement observées plutôt qu'à des interpolations mathématiques.

Comment gérez-vous les groupes contenant moins de quatre valeurs distinctes où l'IQR s'effondre mathématiquement à zéro ou devient indéfini ?

Lorsqu'une partition contient des valeurs identiques ou moins de quatre points de données, PERCENTILE_CONT retourne des valeurs identiques pour Q1 et Q3, entraînant un IQR de zéro. Cela fait s'effondrer les clôtures des valeurs aberrantes sur la valeur médiane, pouvant potentiellement signaler chaque observation distincte comme une valeur aberrante. Les candidats doivent implémenter des vérifications NULLIF ou des expressions CASE pour détecter des scénarios d'IQR à zéro, soit en retournant NULL pour le statut des valeurs aberrantes, en revenant à des méthodes d'écart type pour de petits groupes, ou en excluant explicitement les groupes avec COUNT(DISTINCT value) < 4 de l'analyse des valeurs aberrantes selon les règles commerciales.

Quelle stratégie d'indexation optimise les performances des fonctions de distribution inverse lors du traitement de milliards de lignes partitionnées par des catégories de haute cardinalité ?

Étant donné que PERCENTILE_CONT nécessite un tri de chaque partition pour déterminer les positions des percentiles, les candidats négligent souvent la nécessité d'index composites sur (category, measurement). De tels index permettent au moteur de base de données de parcourir les pages de feuilles d'index préalablement ordonnées, éliminant des opérations de tri externes coûteuses sur disque. Sans ces index, la base de données effectue des tris séparés pour chaque partition, provoquant un fort thrashing I/O et une exhaustion temporaire de l'espace disque. De plus, les candidats oublient que le fait de pousser des clauses WHERE sélectives dans des CTEs précoces réduit l'ensemble de travail avant les calculs percentiles coûteux, car les fonctions de distribution inverse ne peuvent pas tirer parti des index une fois la phase d'agrégation commencée.