Le calcul repose sur la méthode des moindres carrés. La pente (β) est définie comme la covariance de la variable indépendante X et de la variable dépendante Y divisée par la variance de X. L'intercept (α) est dérivé de la moyenne de Y moins le produit de la pente et de la moyenne de X. En ANSI SQL, vous implémentez ces définitions algébriques à l'aide des agrégats SUM, AVG, et COUNT, généralement dans une clause GROUP BY ou comme fonctions de fenêtre avec une clause OVER. La requête doit explicitement calculer la somme des produits croisés (Σ(X - X̄)(Y - Ȳ)) et la somme des écarts quadratiques pour X (Σ(X - X̄)²) pour résoudre les coefficients finaux.
Une équipe d'analytique de vente au détail devait déterminer l'élasticité-prix de la demande pour chaque catégorie de produit afin d'optimiser les stratégies de tarification dynamique. Ils possédaient une table de transactions contenant unit_price et quantity_sold, et nécessitaient une ligne de tendance quantifiant comment la quantité vendue changeait avec le prix pour chaque category_id distinct.
Une solution proposée consistait à exporter des agrégats quotidiens vers un script Python externe utilisant scikit-learn pour ajuster des modèles de régression. Cette approche offrait une simplicité d'implémentation et un accès à de riches diagnostics statistiques. Cependant, elle introduisait une latence de données significative, violait des politiques strictes de gouvernance des données en créant des copies externes de données de vente sensibles, et empêchait les mises à jour en temps réel du tableau de bord requises pour les algorithmes de tarification automatisés.
Une autre option considérée était de créer une fonction d'agrégation définie par l'utilisateur (UDAF) au sein du moteur de base de données, ce qui permettrait une syntaxe telle que REGRESS_SLOPE(price, quantity). Bien que cela soit élégant et réutilisable, cela sacrifierait la portabilité entre différents systèmes de base de données et nécessiterait des privilèges administratifs élevés pour être déployé, ce qui le rendait inadapté pour un environnement d'entrepôt de données cloud multi-tenant verrouillé.
La solution choisie a implémenté les formules algébriques directement en ANSI SQL en utilisant des agrégats standards. L'équipe a tiré parti des fonctions de fenêtre SUM et AVG partitionnées par category_id pour calculer les termes de covariance et de variance nécessaires en un seul passage sur les données. Cette approche a permis de maintenir le calcul collocalisé avec les données, d'éliminer les retards d'extraction-transformation- chargement (ETL), et de respecter strictement les normes ANSI SQL portables sans extensions propriétaires. Le résultat a été un tableau de bord d'élasticité des prix avec une latence de sous-seconde qui se mettait à jour automatiquement à mesure que de nouvelles transactions arrivaient, permettant directement aux algorithmes de tarification automatisés d'ajuster les marges en temps réel.
Comment gérez-vous les valeurs NULL dans X ou Y sans invalider le calcul de l'ensemble du groupe ?
Les candidats oublient souvent que, tandis que les fonctions d'agrégation ANSI SQL ignorent les NULLs, les opérations arithmétiques impliquant des NULLs renvoient NULL. Lors du calcul du terme de covariance SUM((x - avg_x) * (y - avg_y)), si x ou y est NULL pour une ligne spécifique, le produit devient NULL et cette ligne est exclue de la somme. Cela effectue effectivement une suppression pairwise, ce qui est généralement souhaité, mais il faut s'assurer que le COUNT utilisé pour les degrés de liberté dans les calculs de variance reflète le nombre de paires non-NULL, pas le total des lignes. La solution consiste à filtrer WHERE x IS NOT NULL AND y IS NOT NULL dans une sous-requête ou à utiliser COUNT(x) (ce qui est égal à COUNT(y) après filtrage) plutôt que COUNT(*), garantissant des dénominateurs cohérents à travers tous les termes d'agrégation.
Quelle est la distinction entre le calcul de la régression sur une population par rapport à un échantillon, et comment cela affecte-t-il votre requête SQL ?
De nombreux candidats appliquent la formule de variance de l'échantillon (en divisant par n - 1) de manière inconsistante avec la formule de covariance. En ANSI SQL, des fonctions intégrées comme VAR_POP et VAR_SAMP gèrent cette distinction, mais lors du calcul manuel de la variance comme SUM(POWER(x - avg_x, 2)) / COUNT(*), vous devez consciemment choisir le dénominateur. Pour le calcul de la pente, si vous calculez manuellement la variance de X dans le dénominateur, vous devez l'associer au diviseur du calcul de covariance. Les mélanger (par exemple, la covariance d'échantillon divisée par la variance de population) entraîne une pente biaisée. L'approche corrigée consiste à décider du cadre statistique (population contre échantillon) et à appliquer la même logique de diviseur (soit n ou n-1) à la fois au numérateur de la covariance et au dénominateur de la variance.
Comment calculeriez-vous le coefficient de détermination (R²) pour mesurer le bon ajustement dans la même requête ?
Les candidats omettent fréquemment des métriques de validation. R² est calculé comme 1 - (SS_res / SS_tot), où SS_res est la somme des résidus carrés (Σ(y - ŷ)²) et SS_tot est la somme totale des carrés (Σ(y - ȳ)²). Le calcul de ŷ (y prédit) nécessite la pente et l'intercept calculés dans les étapes précédentes. En ANSI SQL, vous pouvez le calculer en utilisant des expressions de table communes imbriquées (CTEs) : d'abord, calculez les moyennes, puis calculez la pente et l'intercept dans un second CTE, et enfin calculez les différences carrées entre les valeurs réelles et prédites dans la requête extérieure. Une erreur courante consiste à tenter de référencer la pente calculée dans le même niveau d'agrégation où elle est calculée, ce qui viole l'ordre de traitement logique. La solution consiste à séparer la logique en CTEs séquentiels pour permettre aux coefficients calculés d'être réutilisés comme des constantes dans l'agrégation finale pour R².