23rd Jun 2022 10 Leseminuten SQL Statistische Analyse Teil 1: Berechnung von Häufigkeiten und Histogrammen Dejan Sarka berechnen statistische Abfragen Statistik Datenanalyse Datenanalytiker Datenbank- und Business Intelligence (BI)-Entwickler erstellen täglich eine große Anzahl von Berichten, und Datenanalysen sind ein wesentlicher Bestandteil dieser Berichte. Wenn Sie sich fragen, ob Sie in SQL statistische Analysen durchführen können, lautet die Antwort "ja". Lesen Sie meinen Artikel, um zu erfahren, wie man das macht! Statistiken sind sehr nützlich als erste Stufe einer tiefer gehenden Analyse, d.h. für den Datenüberblick und die Bewertung der Datenqualität. Die Möglichkeiten der statistischen Analyse in SQL sind jedoch etwas eingeschränkt, da es in SQL Server nicht viele statistische Funktionen gibt. . Darüber hinaus ist ein gutes Verständnis von Statistik unter T-SQL-Anwendern nicht sehr verbreitet. In SQL Server 2016 können Sie R verwenden, um alle Arten von statistischen Kennzahlen zu berechnen, aber viele SQL Server-Entwickler und Datenbankadministratoren programmieren nicht in R. Und nicht jeder Standort hat auf SQL Server 2016 aktualisiert. In dieser Serie werden die Grundlagen der statistischen SQL-Analyse erläutert. Der verwendete Code basiert auf meinen Erfahrungen aus der Praxis. Ich beschäftige mich mit BI-Projekten, insbesondere mit Data Mining, und muss in der Anfangsphase eines Projekts oft viele statistische Abfragen erstellen. Bei diesen Projekten ist die einzige Software, auf die ich mich verlassen kann, oft ein RDBMS. Optimierung von SQL-Statistikabfragen Die Optimierung von statistischen Abfragen unterscheidet sich von der Optimierung von Transaktionsabfragen. Um die Statistiken zu berechnen, scannt die Abfrage normalerweise alle Daten. Wenn die Abfrage zu langsam ist, können Sie eine Stichprobe Ihrer Daten vorbereiten und diese scannen. Wenn die Abfragen jedoch blind den Formeln folgen, führen sie oft mehrere Scans der Daten durch. Die Optimierung solcher Abfragen bedeutet, die Anzahl der Scans zu minimieren. Um dies zu erreichen, müssen Sie einen Algorithmus entwickeln, der zusätzliche Mathematik verwendet, um die Formeln in Äquivalente umzuwandeln, die in SQL Server oder einem anderen RDBMS besser optimiert werden können. Außerdem müssen Sie SQL in allen Einzelheiten verstehen. Sie müssen zum Beispiel die Fensterfunktionen und Berechnungen von SQL sehr gut verstehen. Neben der Erläuterung von Statistiken und statistischen Abfragen gibt Ihnen diese Reihe auch einige Ideen zur Optimierung von statistischen und nicht-statistischen Abfragen. Vorbereiten Ihrer Daten für die statistische SQL-Analyse Bevor Sie mit der Analyse beginnen, müssen Sie verstehen, was Sie analysieren wollen. In der Statistik analysieren Sie die Fälle anhand ihrer Variablen. In der RDBMS-Terminologie können Sie sich einen Fall als eine Tabellenzeile und eine Variable als eine Spalte in derselben Tabelle vorstellen. Für die meisten statistischen Analysen bereiten Sie eine einzelne Tabelle oder Ansicht vor. Manchmal ist es nicht so einfach, einen Fall genau zu definieren. Wenn Sie z. B. eine Kreditrisikoanalyse durchführen, definieren Sie vielleicht eine Familie als Fall und nicht einen einzelnen Kunden. Wenn Sie Daten für eine statistische SQL-Analyse vorbereiten, müssen Sie die Quelldaten entsprechend umwandeln. Für jeden Fall müssen Sie alle verfügbaren Informationen in den Spalten der Tabelle kapseln, die Sie analysieren wollen. Kontinuierliche und diskrete Variablen Bevor Sie mit einer seriösen Datenübersicht beginnen, müssen Sie verstehen, wie die Datenwerte in Ihrem Datensatz gemessen werden. Möglicherweise müssen Sie dies mit einem Fachexperten klären und das Geschäftssystem analysieren, das die Quelle für Ihre Daten ist. Es gibt mehrere Möglichkeiten, Datenwerte zu messen, und verschiedene Arten von Spalten: Diskrete Variablen Eine kontinuierliche Variable kann nur einen Wert aus einem begrenzten Bereich von möglichen Werten annehmen. Zu den diskreten Werten gehören kategoriale oder nominale Variablen, die keine natürliche Ordnung haben. Beispiele hierfür sind Zustände, Statuscodes und Farben. Ränge können auch nur einen Wert aus einer diskreten Menge von Werten annehmen. Sie haben eine Ordnung, lassen aber keine Arithmetik zu. Beispiele hierfür sind Meinungsränge und gebinnte (gruppierte, diskretisierte) echte numerische Werte. Es gibt auch einige spezielle Arten von kategorialen Variablen. Einwertige Variablen oder Konstanten sind für die Analyse nicht sehr interessant, da sie keine Informationen liefern. Zweiwertige oder dichotome Variablen haben zwei Werte, die für eine Analyse nur minimal erforderlich sind. Binäre Variablen sind spezifische dichotome Variablen, die nur die Werte 0 und 1 annehmen können. Kontinuierliche Variablen kann eine unbegrenzte Anzahl möglicher Werte annehmen; der Bereich selbst kann jedoch eine untere und/oder obere Grenze haben. Intervalle haben eine oder zwei Grenzen, sind geordnet und erlauben eine gewisse arithmetische Subtraktion (aber nicht immer eine Summierung). Beispiele hierfür sind Daten, Zeiten und Temperaturen. Echte numerische Variablen unterstützen alle arithmetischen Verfahren. Beispiele hierfür sind Beträge und Werte. Monotone Variablen sind eine spezielle Art von kontinuierlichen Variablen, die ohne Begrenzung monoton ansteigen. Wenn es sich um einfache IDs handelt, sind sie möglicherweise uninteressant. Sie können jedoch transformiert (in Kategorien eingeteilt) werden, wenn die ständig wachsende ID Informationen über die zeitliche Reihenfolge enthält (niedrigere IDs sind älter als höhere IDs). Für statistische SQL-Analysen verwendete Daten Für diesen und alle folgenden Artikel verwende ich die AdventureWorksDW2014 Demo-Datenbank. Sie können eine vollständige Sicherungskopie dieser Datenbank von der SQL Server-Beispielseite von Microsoft herunterladen. Ich führe den gesamten Code auf SQL Server 2016 Entwickler-Edition. Ich verwende lieber die AdventureWorks-Beispieldatenbank für SQL Server 2014 als die WideWorldImportersDW-Beispieldatenbank von SQL Server 2016. Die WideWorldImporters-Datenbank ist sehr nützlich, um die neuen Funktionen von SQL Server 2016 zu demonstrieren, aber ihren Daten fehlen die Korrelationen und Assoziationen, die für statistische Analysen benötigt werden. Verwendung der Häufigkeitsverteilung in SQL zum Verstehen diskreter Variablen In SQL wird die Häufigkeitsverteilung (normalerweise in Form einer Tabelle) verwendet, um einen schnellen Überblick über diskrete Variablen zu erhalten. Sie kann sowohl die aktuellen Werte als auch die Werte selbst anzeigen: Absolute Häufigkeit Absoluter Prozentsatz Kumulative Häufigkeit Kumulierter Prozentsatz Außerdem zeigt die SQL-Häufigkeitsverteilung ein Histogramm des absoluten Prozentsatzes der Werte an. Im Folgenden zeige ich Ihnen mehrere Möglichkeiten, die Häufigkeitsverteilung in SQL zu berechnen, beginnend mit einer, die ziemlich ineffizient ist. Häufigkeitsverteilung in SQL ohne Verwendung von Fensterfunktionen Die Berechnung der absoluten Häufigkeit und des absoluten Prozentsatzes von Werten ist eine unkomplizierte Aggregation. Die Berechnung der kumulativen Häufigkeit und des kumulativen Prozentsatzes bedeutet jedoch, dass laufende Summen berechnet werden müssen. Bevor SQL Server 2012 die Unterstützung für Fensteraggregatfunktionen hinzufügte, mussten Sie für diese Aufgabe entweder korrelierte Unterabfragen oder nicht-äqui-selbst-Joins verwenden. Beide Methoden sind nicht sehr effizient. Führen Sie den folgenden Code aus, der korrelierte Unterabfragen verwendet, um in SQL die Häufigkeitsverteilung der Variable NumberCarsOwned aus der Ansicht dbo.vTargetMailin der AdventureWorksDW2014-Demodatenbank zu analysieren. USE AdventureWorksDW2014; GO WITH freqCTE AS ( SELECT v.NumberCarsOwned, COUNT(v.NumberCarsOwned) AS AbsFreq, CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) / (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc FROM dbo.vTargetMail AS v GROUP BY v.NumberCarsOwned ) SELECT c1.NumberCarsOwned AS NCars, c1.AbsFreq, (SELECT SUM(c2.AbsFreq) FROM freqCTE AS c2 WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumFreq, c1.AbsPerc, (SELECT SUM(c2.AbsPerc) FROM freqCTE AS c2 WHERE c2.NumberCarsOwned <= c1.NumberCarsOwned) AS CumPerc, CAST(REPLICATE('*',c1.AbsPerc) AS varchar(100)) AS Histogram FROM freqCTE AS c1 ORDER BY c1.NumberCarsOwned; Dies erzeugt die folgende Ausgabe: NCars AbsFreq CumFreq AbsPerc CumPerc Histogram ----- ------- ------- ------- ------- ----------------------------------- 0 4238 4238 23 23 *********************** 1 4883 9121 26 49 ************************** 2 6457 15578 35 84 *********************************** 3 1645 17223 9 93 ********* 4 1261 18484 7 100 ******* Häufigkeitsverteilung in SQL mit Fensterfunktionen - Lösung 1 Wenn Sie eine statistische SQL-Analyse durchführen, erweisen sich die Fensteraggregatfunktionen als nützlich. Sie bieten eine viel bessere Lösung. Wie bereits erwähnt, sind diese Funktionen in SQL Server Versionen 2012 und höher verfügbar. Wenn Sie sich den ersten Teil der Abfrage ansehen, werden Sie feststellen, dass die Common Table Expression-Abfrage, mit der die absoluten Zahlen berechnet werden, dieselbe ist wie in der vorherigen Abfrage. Die kumulierten Werte - die laufenden Summen - werden jedoch mit Hilfe von Fensteraggregatfunktionen berechnet. WITH freqCTE AS ( SELECT v.NumberCarsOwned, COUNT(v.NumberCarsOwned) AS AbsFreq, CAST(ROUND(100. * (COUNT(v.NumberCarsOwned)) / (SELECT COUNT(*) FROM vTargetMail), 0) AS INT) AS AbsPerc FROM dbo.vTargetMail AS v GROUP BY v.NumberCarsOwned ) SELECT NumberCarsOwned, AbsFreq, SUM(AbsFreq) OVER(ORDER BY NumberCarsOwned ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumFreq, AbsPerc, SUM(AbsPerc) OVER(ORDER BY NumberCarsOwned ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumPerc, CAST(REPLICATE('*',AbsPerc) AS VARCHAR(50)) AS Histogram FROM freqCTE ORDER BY NumberCarsOwned; Das Ergebnis dieser Abfrage ist das gleiche wie das Ergebnis der vorherigen Abfrage. Häufigkeitsverteilung in SQL mit Fensterfunktionen - Lösung 2 Ich habe eine weitere interessante Lösung gefunden, die die analytischen Fensterfunktionen von SQL verwendet. Die Funktion CUME_DIST berechnet die kumulative Verteilung bzw. die relative Position eines Wertes in einer Gruppe von Werten. Für eine Zeile r ist die CUME_DIST von r bei aufsteigender Reihenfolge die Anzahl der Zeilen mit Werten, die kleiner oder gleich dem Wert von r sind, geteilt durch die Anzahl der ausgewerteten Zeilen in der Partition oder Abfrageergebnismenge. Die Funktion PERCENT_RANK berechnet den relativen Rang einer Zeile innerhalb einer Gruppe von Zeilen. Wir können PERCENT_RANK verwenden, um den relativen Rang eines Wertes innerhalb einer Abfrageergebnismenge oder Partition zu bewerten. Die folgende SQL-Abfrage zur statistischen Analyse berechnet die Zeilennummer einmal partitioniert über die Spalte NumberCarsOwned und die Zeilennummer einmal über den gesamten Eingabesatz. Sie berechnet auch den prozentualen Rang und die kumulative Verteilung über die gesamte Eingabemenge. SELECT NumberCarsOwned AS NCars, ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq, ROW_NUMBER() OVER( ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq, PERCENT_RANK() OVER(ORDER BY NumberCarsOwned) AS Pr_AbsPerc, CUME_DIST() OVER(ORDER BY NumberCarsOwned, CustomerKey) AS Cd_CumPerc FROM dbo.vTargetMail; Die Teilausgabe, die nur die Zeilen enthält, die für die Erläuterung des Algorithmus zur Berechnung der Häufigkeiten relevant sind, lautet: NCars Rn_AbsFreq Rn_CumFre Pr_AbsPerc Cd_CumPerc ----- ---------- --------- ----------------- -------------------- 0 1 1 0 5.4100843973166E-05 0 2 2 0 0.000108201687946332 … … … … … 0 4238 4238 0 0.229279376758277 1 1 4239 0.22929178163718 0.229333477602251 … … … … … 1 4883 9121 0.22929178163718 0.493453797879247 2 1 9122 0.493480495590543 0.49350789872322 … … … … … Wie Sie sehen können, stellt die letzte durch NumberCarsOwned partitionierte Zeilennummer in einer Kategorie tatsächlich die absolute Häufigkeit der Werte in dieser Kategorie dar. Die letzte nicht unterteilte Zeilennummer in einer Kategorie stellt die kumulative Häufigkeit bis einschließlich der aktuellen Kategorie dar. Zum Beispiel beträgt die absolute Häufigkeit für NumberCarsOwned = "0" 4.238 und die kumulative Häufigkeit 4.238; für NumberCarsOwned = "1" beträgt die absolute Häufigkeit 4.883 und die kumulative Häufigkeit 9.121. Betrachten wir nun die Funktion CUME_DIST (die Spalte Cd_CumPerc in der Ausgabe). CUME_DIST in der letzten Zeile einer Kategorie gibt den kumulativen Prozentsatz bis einschließlich dieser Kategorie zurück. Wenn Sie den PERCENT_RANK (Spalte Pr_AbsPerc in der Ausgabe) für die letzte Zeile in der Kategorie von der CUME_DIST der letzten Zeile in derselben Kategorie subtrahieren, erhalten Sie den absoluten Prozentsatz für die Kategorie. Zum Beispiel beträgt der absolute Prozentsatz für die Kategorie, in der NumberCarsOwned = "1" ist, mehr als 26 Prozent (0,493453797879247 - 0,22929178163718 = 0,264162016242067). Die folgende Abfrage berechnet die Häufigkeitsverteilung anhand der Beobachtungen aus den Ergebnissen der vorherigen Abfrage. WITH freqCTE AS ( SELECT NumberCarsOwned, ROW_NUMBER() OVER(PARTITION BY NumberCarsOwned ORDER BY NumberCarsOwned, CustomerKey) AS Rn_AbsFreq, ROW_NUMBER() OVER( ORDER BY NumberCarsOwned, CustomerKey) AS Rn_CumFreq, ROUND(100 * PERCENT_RANK() OVER(ORDER BY NumberCarsOwned), 0) AS Pr_AbsPerc, ROUND(100 * CUME_DIST() OVER(ORDER BY NumberCarsOwned, CustomerKey), 0) AS Cd_CumPerc FROM dbo.vTargetMail ) SELECT NumberCarsOwned AS NCars, MAX(Rn_AbsFreq) AS AbsFreq, MAX(Rn_CumFreq) AS CumFreq, MAX(Cd_CumPerc) - MAX(Pr_Absperc) AS AbsPerc, MAX(Cd_CumPerc) AS CumPerc, CAST(REPLICATE('*',MAX(Cd_CumPerc) - MAX(Pr_Absperc)) AS varchar(100)) AS Histogram FROM freqCTE GROUP BY NumberCarsOwned ORDER BY NumberCarsOwned; Obwohl die Idee dieser letzten Abfrage sehr interessant ist, ist diese Abfrage nicht so effizient wie die zweite (unter Verwendung der Fensteraggregatfunktion). Daher ist die zweite Lösung die empfohlene. Schlussfolgerung In diesem Artikel haben Sie gelernt, wie man die SQL-Häufigkeitsverteilung für diskrete Variablen berechnet. Sie haben auch eine Lösung gesehen, die etwas Kreativität erfordert. In den folgenden Artikeln werden Sie andere statistische SQL-Analysemethoden kennen lernen. Der nächste Artikel wird sich mit der Berechnung grundlegender statistischer Maße für kontinuierliche Variablen befassen. Sie werden auch sehen, wie Sie effiziente Abfragen schreiben können, die eher mathematische Kenntnisse als Kreativität erfordern. Tags: berechnen statistische Abfragen Statistik Datenanalyse Datenanalytiker