Die Berechnung basiert auf der Methode der kleinsten Quadrate. Die Steigung (β) wird als die Kovarianz der unabhängigen Variablen X und der abhängigen Variablen Y dividiert durch die Varianz von X definiert. Der Achsenabschnitt (α) wird aus dem Mittelwert von Y minus das Produkt aus der Steigung und dem Mittelwert von X abgeleitet. In ANSI SQL implementieren Sie diese algebraischen Definitionen mit SUM, AVG und COUNT Aggregaten, typischerweise innerhalb einer GROUP BY-Klausel oder als Fensterfunktionen mit einer OVER-Klausel. Die Abfrage muss explizit die Summe der Kreuzprodukte (Σ(X - X̄)(Y - Ȳ)) und die Summe der quadrierten Abweichungen für X (Σ(X - X̄)²) berechnen, um die endgültigen Koeffizienten zu ermitteln.
Ein Einzelhandelsanalyse-Team musste die Preiselastizität der Nachfrage für jede Produktkategorie bestimmen, um dynamische Preisstrategien zu optimieren. Sie besaßen eine Transaktionstabelle mit unit_price und quantity_sold und benötigten eine Trendlinie, die quantifiziert, wie sich die verkaufte Menge mit dem Preis für jede einzelne category_id änderte.
Eine vorgeschlagene Lösung bestand darin, tägliche Aggregate an ein externes Python-Skript zu exportieren, das scikit-learn verwendet, um Regressionsmodelle anzupassen. Dieser Ansatz bot eine einfache Implementierung und Zugang zu umfangreichen statistischen Diagnosen. Allerdings führte er zu erheblichen Datenverzögerungen, verletzte strenge Richtlinien zur Datenverwaltung, indem er externe Kopien sensibler Verkaufsdaten erstellte, und verhinderte die erforderlichen Echtzeit-Dashboard-Aktualisierungen für automatisierte Preisalgorithmen.
Eine weitere in Betracht gezogene Option war die Erstellung einer benutzerdefinierten Aggregatfunktion (UDAF) innerhalb der Datenbank-Engine, die eine Syntax wie REGRESS_SLOPE(preis, menge) ermöglichen würde. Während dies elegant und wiederverwendbar war, opferte es die Portabilität über verschiedene Datenbanksysteme hinweg und erforderte erhöhte administrative Berechtigungen für die Bereitstellung, was es ungeeignet für eine gesperrte, Multi-Tenant-Cloud-Datenlagerumgebung machte.
Die gewählte Lösung implementierte die algebraischen Formeln direkt in ANSI SQL unter Verwendung standardmäßiger Aggregationen. Das Team nutzte SUM und AVG Fensterfunktionen, partitioniert nach category_id, um die erforderlichen Kovarianz- und Varianzwerte in einem einzigen Durchgang über die Daten zu berechnen. Dieser Ansatz hielt die Berechnung am Ort der Daten, beseitigte Extraktions-, Transformations- und Ladelate (ETL), und hielt sich streng an die portablen ANSI SQL-Standards ohne proprietäre Erweiterungen. Das Ergebnis war ein Latenz-Dashboard zur Preiselastizität, das sich in weniger als einer Sekunde automatisch aktualisierte, während neue Transaktionen eingingen, was direkt die automatisierten Preisalgorithmen ermöglichte, die Margen in Echtzeit anzupassen.
Wie gehen Sie mit NULL-Werten in X oder Y um, ohne die Berechnung der gesamten Gruppe ungültig zu machen?
Kandidaten vergessen oft, dass während ANSI SQL-Aggregatfunktionen NULLs ignorieren, arithmetische Operationen mit NULLs NULL zurückgeben. Bei der Berechnung des Kovarianzterms SUM((x - avg_x) * (y - avg_y)), wenn entweder x oder y für eine bestimmte Zeile NULL ist, wird das Produkt NULL und diese Zeile wird von der Summe ausgeschlossen. Dies führt effektiv zu einer paarweisen Löschung, die in der Regel gewünscht ist, aber man muss sicherstellen, dass das für die Freiheitsgrade in den Varianzberechnungen verwendete COUNT die Anzahl der Nicht-NULL-Paare und nicht die Gesamtzeilen widerspiegelt. Die Lösung besteht darin, WHERE x IS NOT NULL AND y IS NOT NULL in einer Unterabfrage zu filtern oder COUNT(x) (das nach der Filterung COUNT(y) entspricht) anstelle von COUNT(*) zu verwenden, um konsistente Nenner über alle Aggregatbegriffe hinweg sicherzustellen.
Was ist der Unterschied zwischen der Berechnung der Regression über eine Population versus eine Stichprobe und wie beeinflusst dies Ihre SQL-Abfrage?
Viele Kandidaten wenden die Formel für die Stichprobenvarianz (Teilung durch n - 1) inkonsistent mit der Kovarianzformel an. In ANSI SQL behandeln eingebaute Funktionen wie VAR_POP und VAR_SAMP diesen Unterschied, aber wenn Sie die Varianz manuell berechnen als SUM(POWER(x - avg_x, 2)) / COUNT(*), müssen Sie bewusst den Nenner wählen. Für die Berechnung der Steigung, falls Sie die Varianz von X im Nenner manuell berechnen, müssen Sie sie mit dem Divisor der Kovarianzberechnung übereinstimmen. Wenn Sie diese mischen (z. B. Stichprobenkovarianz geteilt durch Populationsvarianz), ergibt sich eine verzerrte Steigung. Der korrigierte Ansatz besteht darin, den statistischen Rahmen (Population gegen Stichprobe) zu entscheiden und die gleiche Nennerlogik (entweder n oder n-1) sowohl für den Zähler der Kovarianz als auch für den Nenner der Varianz anzuwenden.
Wie würden Sie den Bestimmtheitsmaß (R²) berechnen, um das Gütemaß der Anpassung innerhalb derselben Abfrage zu messen?
Kandidaten lassen häufig Validierungsmessungen aus. R² wird als 1 - (SS_res / SS_tot) berechnet, wobei SS_res die Summe der quadrierten Residuen (Σ(y - ŷ)²) und SS_tot die Gesamtsumme der Quadrate (Σ(y - ȳ)²) ist. Die Berechnung von ŷ (vorhergesagtes y) erfordert die in früheren Schritten berechnete Steigung und den Achsenabschnitt. In ANSI SQL können Sie dies mithilfe gestapelter Common Table Expressions (CTEs) berechnen: Zuerst die Mittelwerte berechnen, dann die Steigung und den Achsenabschnitt in einer zweiten CTE berechnen und schließlich die quadrierten Abweichungen zwischen tatsächlichen und vorhergesagten Werten in der äußeren Abfrage berechnen. Ein häufiger Fehler besteht darin, zu versuchen, auf die berechnete Steigung in derselben Aggregationsstufe zuzugreifen, in der sie berechnet wird, was die logische Verarbeitungsreihenfolge verletzt. Die Lösung besteht darin, die Logik in sequenzielle CTEs aufzuteilen, um die berechneten Koeffizienten als Konstanten in der endgültigen Aggregation für R² wiederverwenden zu können.