SQL Statistische Analyse Teil 3: Messung der Streuung einer Verteilung

Neben der Kenntnis der Zentren einer Verteilung in Ihren Daten müssen Sie auch wissen, wie unterschiedlich die Beobachtungen sind. In diesem Artikel erklären wir Ihnen, wie Sie die Streuung einer Verteilung in SQL ermitteln können.

Haben Sie es mit einer sehr gleichmäßigen oder einer sehr gestreuten Population zu tun? Um wirklich zu verstehen, was die Zahlen aussagen, müssen Sie die Antwort auf diese Frage kennen.

Im zweiten Teil dieser Serie haben wir folgende Themen behandelt wie man Verteilungszentren berechnet. Genau wie im Fall des Zentrums gibt es mehrere Möglichkeiten, die Streuung der Verteilung in SQL zu messen. Außerdem gibt es viele verschiedene Definitionen für die Streuung der Verteilung. Wir werden die gängigsten besprechen: den Bereich, den Interquartilsbereich, den absoluten Mittelwert, die mittlere quadratische Abweichung, die Varianz, die Standardabweichung und den Variationskoeffizienten. Ich werde auch den Begriff Freiheitsgrade erklären. Schließlich werden wir den Unterschied zwischen Varianz und Standardabweichung für Stichproben und für Populationen betrachten. Nach der Lektüre dieses Eintrags werden Sie in der Lage sein, die Streuung einer Verteilung in SQL selbständig zu ermitteln.

Bereich

Der Bereich ist der einfache Abstand zwischen dem Maximalwert und dem Minimalwert, den die Variable annimmt. (Eine Variable ist ein Attribut einer Beobachtung, das als Spalte in einer Tabelle dargestellt wird.) Er ist das einfachste Maß für die Streuung. Die Formel für die Spanne lautet:

R = vmax - vmin

Die Aggregatfunktionen von T_SQL MAX und MIN berechnen den Bereich einer Variablen, wie unten gezeigt:

USE AdventureWorksDW2014;
SELECT MAX(Age) - MIN(Age) AS Range
FROM dbo.vTargetMail;

Der Code erzeugt die folgende Ausgabe:

Range
-----
   70

Inter-Quartil-Bereich

Kommen wir nun zur Berechnung von Quartilen in SQL. Der Median ist der Wert, der die Verteilung in zwei Hälften teilt. Sie können die Verteilung noch weiter unterteilen, z. B. können Sie jede Hälfte in zwei Hälften aufteilen. So entstehen Quartile: drei Werte, die die Verteilung in Viertel aufteilen.

Schauen wir uns diesen Aufteilungsprozess an, der die Grundlage für die Berechnung von Quartilen in SQL bildet. Sie beginnen mit der Sortierung der Zeilen (Fälle, Beobachtungen) nach einer ausgewählten Spalte (Attribut, Variable). Sie definieren den Rang als die absolute Position einer Zeile in der Folge der sortierten Zeilen. Der Perzentilrang eines Wertes ist ein relatives Maß, das angibt, wie viel Prozent aller (n) Beobachtungen einen niedrigeren Wert als einen ausgewählten Wert haben.

Wenn Sie die Beobachtungen in Quartale unterteilen, erhalten Sie drei Perzentile (bei 25 %, 50 % und 75 % aller Zeilen). Sie können die Werte an den Quartilen ablesen. Das erste Quartil am 25 %-Punkt wird als unteres Quartil bezeichnet. Das zweite Quartil ist der Median (50 %). Das dritte, bei 75%, ist das obere Quartil. Wenn Sie das untere Quartil (Q1) vom oberen Quartil (Q3) abziehen, erhalten Sie die Formel für den Interquartilsbereich (IQR):

IQR = Q3 -_Q1

Die Berechnung von Quartilen in SQL und die Ermittlung des IQR ist so einfach wie die Verwendung der analytischen Funktion PERCENTILE_CONT:

SELECT DISTINCT 
 PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY 1.0*Age) OVER () -
 PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY 1.0*Age) OVER () AS IQR
FROM dbo.vTargetMail;

Diese Abfrage liefert das folgende Ergebnis:

IQR
---
 17

Wie der Median ist der IQR resistent gegen Veränderungen. Das bedeutet, dass er nicht auf starke Schwankungen bei einer einzelnen Beobachtung reagiert. Diese Resistenz ist logisch, da Sie nur zwei Schlüsselbeobachtungen verwenden. Wenn Sie einen großen Unterschied zwischen dem Bereich und dem Interquartilsbereich derselben Variablen sehen, bedeutet dies, dass einige Werte in der Verteilung ziemlich weit vom Mittelwert entfernt sind.

Mittlere absolute Abweichung

Für den IQR werden nur zwei Schlüsselbeobachtungen verwendet: das untere und das obere Quartil. Gibt es ein Maß, das alle Beobachtungen berücksichtigt? Ja.

Sie können den Abstand zwischen jedem Wert und dem Mittelwert messen und ihn als Abweichung bezeichnen. Die Summe aller Abstände ergibt ein Maß für die Streuung in der Grundgesamtheit. Sie müssen jedoch bedenken, dass einige der Abstände positiv und andere negativ sind; eigentlich heben sie sich gegenseitig auf, so dass die Summe genau Null ergibt.

Das Gleiche gilt, wenn Sie den Durchschnitt der Abweichungen bilden würden, so dass dies ein unbrauchbares Maß für die Streuung wäre. Sie lösen dieses Problem, indem Sie die positiven/negativen Vorzeichen ignorieren und die absoluten Werte der Abstände zwischen den Werten und dem Mittelwert verwenden.

Berechnet man den Durchschnitt der absoluten Abweichungen, erhält man die Formel für die mittlere absolute Abweichung (MAD):

Aus der Formel für die MAD können Sie ersehen, dass Sie zunächst den Mittelwert berechnen müssen. Zunächst ist es verlockend, dies mit der Aggregatfunktion AVG zu versuchen und das Ergebnis als Eingabe für die Funktion SUM zu verwenden. SQL Server kann jedoch keine Aggregatfunktion für einen Ausdruck ausführen, der ein Aggregat oder eine Unterabfrage enthält; daher müssen wir den Mittelwert (aus AVG) in einer Variablen speichern:

DECLARE @mean AS NUMERIC(10,2);
SET @mean = (SELECT AVG(1.0*YearlyIncome) FROM dbo.vTargetMail);
SELECT SUM(ABS(YearlyIncome - @mean))/COUNT(*) AS MAD
FROM dbo.vTargetMail;

Sie erhalten die folgende Ausgabe:

MAD
------------
25474.966405

Mittlere quadrierte Abweichung

Eine andere Möglichkeit, die Probleme mit den Abweichungszeichen zu vermeiden, besteht darin, jede Abweichung zu quadrieren. Mit einer leichten Modifikation der MAD-Formel - nämlich der Berechnung des Durchschnitts der quadrierten Abweichungen anstelle der absoluten Abweichungen - erhalten Sie die Formel für die mittlere quadrierte Abweichung (MSD):

Sie haben sich vielleicht gefragt, warum ich keine Fensteraggregatfunktionen für die MAD-Berechnung verwendet habe. Das ist natürlich möglich. Ich werde es für die MSD-Berechnung tun. Versuchen wir es mit der folgenden Abfrage!

SELECT 
 SUM( 
  SQUARE(YearlyIncome - 
   (AVG(1.0*YearlyIncome) OVER())
  )
 ) / COUNT(*) AS MSD
FROM dbo.vTargetMail;

Leider ist dieser Ansatz ziemlich naiv. Die Abfrage gibt error 4109 - windowed functions cannot be used in the context of another windowed function or aggregate zurück.

Sie müssen die Fensteraggregatfunktion innerhalb eines allgemeinen Tabellenausdrucks verwenden und dann die endgültige Aggregation in einer äußeren Abfrage durchführen. Das folgende Codeschnipsel zeigt, wie es gemacht wird:

WITH MSDCTE AS
(
SELECT 
  YearlyIncome, 
  AVG(1.0*YearlyIncome) OVER() AS Deviation
FROM dbo.vTargetMail
)
SELECT SUM(SQUARE(YearlyIncome - Deviation)) / COUNT(*) AS MSD
FROM MSDCTE;

Dies liefert das gewünschte Ergebnis:

MSD
----------------
1042319181.07085

Freiheitsgrade und Varianz

Nehmen wir an, dass Sie nur eine Beobachtung haben (n=1). Diese Beobachtung ist auch Ihr Stichprobenmittelwert, aber es gibt überhaupt keine Streuung. Sie können die Streuung nur berechnen, wenn n größer als 1 ist. Nur die (n-1)-Informationen helfen Ihnen bei der Berechnung der Streuung, wenn man davon ausgeht, dass die erste Beobachtung Ihr Mittelwert ist. Diese Informationen werden als Freiheitsgrade bezeichnet.

Unter Freiheitsgraden versteht man die Anzahl der Informationen, die variieren können. Stellen Sie sich zum Beispiel eine Variable vor, die fünf verschiedene diskrete Zustände annehmen kann. Sie brauchen nur die Häufigkeiten von vier Zuständen zu berechnen, um die Verteilung der Variablen zu kennen; die Häufigkeit des letzten Zustands wird durch die berechneten Häufigkeiten der ersten vier Zustände bestimmt. Sie können nicht variieren, da der kumulierte Prozentsatz aller Zustände gleich 100 sein muss.

Die Summe aller Abweichungen, ohne Berücksichtigung der positiven/negativen Vorzeichen, ist immer Null. Aus diesem Grund werden in der Formel für die Varianz quadrierte Abweichungen verwendet. Es gibt nur (n-1) freie Abweichungen; die letzte ist streng durch die anderen bestimmt. Die Definition der Varianz (Var) ist ähnlich wie die Definition des MSD; man ersetzt einfach die Anzahl der Fälle n durch die Freiheitsgrade (n-1):

Dies ist die Formel für die Varianz einer Stichprobe, die als Schätzer für die Varianz der Grundgesamtheit verwendet werden kann. Stellen Sie sich nun vor, dass Ihre Daten die gesamte Grundgesamtheit repräsentieren. In diesem Fall tragen alle Beobachtungen gleichermaßen zur Berechnung der Varianz bei, und die Freiheitsgrade machen keinen Sinn. Die Varianz einer Grundgesamtheit (VarP) wird dann mit der gleichen Formel wie der MSD definiert:

Wenn Sie eine große Stichprobe haben, ist der Unterschied zwischen Var und VarP natürlich minimal.

Transact-SQL enthält eine Aggregatfunktion (die VAR-Funktion), mit der die Varianz einer Stichprobe als Schätzer berechnet werden kann. Die VARP-Funktion berechnet die Varianz der Grundgesamtheit. Die Verwendung beider Funktionen in einer Abfrage ist sehr einfach. Im folgenden Beispiel werden beide Varianzen für die Spalte "YearlyIncome" berechnet. Es vergleicht sie auch auf zwei Arten: durch Division und durch Division der Anzahl der Fälle minus eins durch die Anzahl der Fälle. Letzteres zeigt, dass der Unterschied nur ein Ergebnis der Freiheitsgrade ist, die bei der Berechnung der Varianz der Stichprobe als Schätzer für die Varianz der Grundgesamtheit verwendet werden:

SELECT VAR(1.0*YearlyIncome) AS SampleVariance,
  VARP(1.0*YearlyIncome) AS PopulationVariance,
  VARP(1.0*YearlyIncome) / VAR(1.0*YearlyIncome) AS SampleVsPopulation1,
  (1.0 * COUNT(*) - 1) / COUNT(*) AS SampleVsPopulation2
FROM dbo.vTargetMail;

Die Abfrage liefert das folgende Ergebnis:

SampleVariance   PopulationVariance SampleVsPopulation1 SampleVsPopulation2
---------------- ------------------ ------------------- -------------------
1042375574.46912 1042319181.07081   0.999945899156027   0.999945899156

Standardabweichung und Variationskoeffizient

Um zu kompensieren, dass die Abweichungen in der Formel für die Varianz quadriert werden, können Sie die Quadratwurzel der Varianz nehmen. Dies ist die Definition der Standardabweichung (σ):

Sie können diese Formel zur Berechnung der Standardabweichung in SQL verwenden, sowohl für die Grundgesamtheit als auch für eine Stichprobe - setzen Sie einfach die entsprechende Varianz in die Formel ein.

Angenommen, wir haben die absoluten Maße einer Streuung abgeleitet. Die Interpretation ist für eine einzelne Variable recht offensichtlich: Je größer die Werte der Maße sind, desto stärker ist die Variable in den Beobachtungen gestreut. Aber absolute Maße können nicht verwendet werden, um die Streuung zwischen zwei oder mehr Variablen zu vergleichen. Daher müssen wir relative Maße ableiten.

Wir können die relativen Maße der Streuung für jedes der genannten absoluten Maße ableiten. Wir beschränken uns jedoch auf das am weitesten verbreitete Maß: die Standardabweichung. Die Definition der relativen Standardabweichung (auch bekannt als Variationskoeffizient oder CV) ist eine einfache Division der Standardabweichung durch den Mittelwert:

T-SQL enthält eine Aggregatfunktion zur Berechnung der Standardabweichung in SQL für die Grundgesamtheit (STDEVP) und eine Funktion zur Berechnung der Standardabweichung für eine Stichprobe als Schätzer (STDEV). Die Berechnung der Standardabweichung in SQL sowie des Variationskoeffizienten ist daher einfach. Die folgende Abfrage berechnet die Standardabweichungen für die Variablen "Alter" und "Jahreseinkommen" und den Variationskoeffizienten für diese Spalten:

SELECT STDEV(1.0*Age) AS StDevAge,
 STDEV(1.0*YearlyIncome) AS StDevIncome,
 STDEV(1.0*Age) / AVG(1.0*Age) AS CVAge,
 STDEV(1.0*YearlyIncome) / AVG(1.0*YearlyIncome) AS CVIncome
FROM dbo.vTargetMail;

Hier ist das Ergebnis:

StDevAge          StDevIncome       CVAge              CVIncome
----------------  ----------------  -----------------  -----------------
11.5178146121881  32285.8417029682  0.241654328044298	  0.563395923529214

Nach der Berechnung der Standardabweichung in SQL können Sie sehen, dass die Standardabweichung für "Jahreseinkommen" viel höher ist als für "Alter"Die relative Streuung, der Variationskoeffizient, ist jedoch nicht so unterschiedlich.

Schlussfolgerung

Verteilungsschwerpunkte, insbesondere der Mittelwert, sind wahrscheinlich die am meisten missbrauchten Maße in der Statistik. Der Mittelwert bedeutet nicht viel, wenn die Streuung nicht erwähnt wird. Es gibt verschiedene Maße für die Streuung: Standardabweichung, Varianz und Variationskoeffizient sind die wichtigsten davon. Da Sie nun mehr über die Messung der Streuung einer Verteilung in SQL wissen und Ihnen Themen wie die Berechnung von Quartilen in SQL oder das Schreiben von Abfragen zur Ermittlung der Standardabweichung in SQL nicht fremd sind, haben Sie Ihre statistische Analyse auf die nächste Stufe gebracht!