Was tut CUME_DIST?
Angenommen ihr habt eine Tabelle mit bestimmten Werten und möchtet zu den einzelnen Werten wissen, wieviel Prozent der Datensätze unterhalb dem jeweiligen Wert liegen. Diese Art von Fragen kann die CUME_DIST
-Funktion beantworten. CUME_DIST
ist dabei kurz für “Cumulative Distribution”, auf Deutsch auch “Verteilungsfunktion” und ist eine Funktion aus der Statistik, sie berechnet zu einem gegebenen Wert und einer Verteilung, wie hoch die Wahrscheinlichkeit ist, dass ein Datensatz unterhalb des Wertes liegt.
Wie könnt ihr mit CUME_DIST arbeiten?
Nachdem wir nun also geklärt haben, was CUME_DIST
tut, wollen wir uns einmal ansehen, wie ihr die Funktion verwenden könnt. Klar ist, dass wir bei CUME_DIST
mit Verteilungen hantieren, die Funktion wird also eine Gleitkommazahl zwischen 0 und 1 zurückgeben.
Um Erfahrung im Umgang mit CUME_DIST
zu sammeln, schreiben wir zunächst eine Abfrage, die die StockItems
der WideWorldImporters
-Datenbank mit ihren Item-Gruppen zusammenbringt. Da es Items gibt, die mehreren Warengruppen zugeordnet sind, verwenden wir hierfür aber keinen Join sondern ordenen diese Items immer der ersten Gruppe zu:
SELECT UnitPrice ,SG.StockGroupName ,StockItemID OUTER APPLY ( SELECT TOP 1 StockGroupName FROM [Warehouse].[StockItemStockGroups] SISG LEFT JOIN [Warehouse].[StockGroups] SG ON SISG.StockGroupID = SG.StockGroupID WHERE SI.StockItemID = SISG.StockItemID ) SG ORDER BY UnitPrice
Bereits hier bekommen wir ein Gefühl für die Daten, wenn wir durch die Ergebnismenge scrollen, sehen wir beispielsweise intuitiv, dass etwa die hälfte der Produkte weniger als 20 Dollar kostet. Nun fassen wir einige Produkte zusammen, indem wir Preis-Segmente bilden, indem wir jeden Preis auf 10 Dollar runden und die Produkte in jeder Gruppe zählen:
SELECT CAST(ROUND(UnitPrice/10, 0)*10 AS int) as PriceRange ,COUNT(*) AS NumberOfProducts FROM [Warehouse].[StockItems] SI OUTER APPLY ( SELECT TOP 1 StockGroupName FROM [Warehouse].[StockItemStockGroups] SISG LEFT JOIN [Warehouse].[StockGroups] SG ON SISG.StockGroupID = SG.StockGroupID WHERE SI.StockItemID = SISG.StockItemID ) SG GROUP BY CAST(ROUND(UnitPrice/10, 0)*10 AS int)
Wir sehen auch hier, dass etwa 50% der Produkte in Preissegmenten bis 20 Dollar liegt (die Verteilung könnt ihr euch auch grafisch ansehen, wenn ihr in Azure Data Studio neben der Ergebnismenge auf das Chart-Symbol klickt). Nun möchten wir es aber genau wissen: und sehen uns die Information auf Produktebene an, dafür verwenden wir die CUME_DIST-Funktion, um uns die Verteilung auf Produktbasis anzusehen:
SELECT StockItemID ,UnitPrice ,CUME_DIST() OVER ( ORDER BY UnitPrice ) PercentageOfProeuctsBelow FROM [Warehouse].[StockItems] SI ORDER BY UnitPrice
Wichtig ist hierbei, zu beachten, dass Produkte, die denselben UnitPrice haben, hier auch denselben Wert der Verteilungsfunktion zugewiesen bekommen. Da die Verteilungsfunktion nicht zwischen den Produkten anhand der Preise unterscheiden kann, werden sie hier ähnlich wie beim RANK
auch gleich behandelt. Wolltet ihr das ändern, müsstet ihr eine Spalte als Tie-breaker hinzunehmen:
SELECT StockItemID ,UnitPrice ,CUME_DIST() OVER ( ORDER BY UnitPrice, StockItemID ) PercentageOfProeuctsBelow FROM [Warehouse].[StockItems] SI ORDER BY UnitPrice
Doch wir können mit CUME_DIST
auch einen Schritt weiter gehen. Wenn euch nun interessiert, wie der Preis in der jeweiligen Produktgruppe einzuordnen ist, dafür geben wir dem Fenster der CUME_DIST
-Funktion noch ein PARTITION BY
-Argument mit:
SELECT StockItemID ,SG.StockGroupName ,UnitPrice ,CUME_DIST() OVER ( PARTITION BY SG.StockGroupName ORDER BY UnitPrice, StockItemID ) PercentageOfProeuctsBelow FROM [Warehouse].[StockItems] SI OUTER APPLY ( SELECT TOP 1 StockGroupName FROM [Warehouse].[StockItemStockGroups] SISG LEFT JOIN [Warehouse].[StockGroups] SG ON SISG.StockGroupID = SG.StockGroupID WHERE SI.StockItemID = SISG.StockItemID ) SG ORDER BY UnitPrice
So erhalten wir für jedes Produkt die Information, wieviel Prozent der Item in der jeweiligen Gruppe günstiger gleich teuer sind, wie das Produkt, das ihr gerade anseht.
CUME_DIST
ist ähnlich wie die zuletzt vorgestellten FIRST_VALUE
, LAST_VALUE
, LEAD
und LAG
ein Werkzeug, das euch helfen kann, eine Datenmenge mittels Statistik zu erfassen und zu analysieren und so ein Verständnis für die Daten zu erlangen oder euren Anwendern oder Anwendungen entsprechende Mehrwerte zu liefern ohne den SQL Server zu verlassen. Sicher gehört CUME_DIST
nicht zu den am häufigsten verwendeten T-SQL Funktionen, doch als echte Ninjas könnt ihr mit euren Skills protzen, wenn es mal darum geht, die Verteilung von Daten zu ermitteln und ihr CUME_DIST
im Hinterkopf habt…