CUME_DIST

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…

Referenzen

• Dokumentation von Microsoft