Dateninseln partitionieren

Wie wir alle wissen, gibt es verschiedene Arten von Inseln, die man nicht unbedingt miteinander vergleichen kann. Sand, Fels, Korallen, ...! Für unsere Dateninseln gilt genau das gleiche.

Erweiterte Ausgangslage

In meinem ersten Artikel zu diesem Thema habe ich am Ende bereits die Thematik der Bestellungen eingeführt. Wer dieses Beispiel noch nicht gelesen hat, sollte dort starten und dann erst hier fortfahren.

Damit wir die Daten auch basierend auf verschiedenen Städten auswerten können, wollen wir diese nun um die Städte aus denen die Bestellungen kommen erweitern. Die Fragestellung wird dann leicht modifiziert:

Wie lange sind die Zeiträume in denen wenigstens in jeder Minute eine Bestellung hereinkam, unterschieden nach den Städten aus denen die Bestellung kam.
Wieviele Bestellungen wurden hier getätigt und welchen Umsatz haben wir dabei gemacht?

Lösungsweg

Aus dem ersten Beispiel kennen wir bereits den Weg zur Bildung von Inseln. Wir machen dies, indem wir bei den Bestellungen die Differenz in Minuten zu einem Zeitpunkt in der Vergangenheit bilden. Aber nun wollen wir ja zusätzlich die Inseln nach Städten unterscheiden. Dazu können wir einfach die Angabe PARTITON bei unserer Window-Function DENSE_RANK() verwenden. Entsprechend müssen wir auch die Stadt in die Ausgabe einbeziehen und natürlich in der Gruppierung berücksichtigen:

WITH Inseln
AS
(SELECT Stadt, Zeitpunkt, Bestellwert,
DATEDIFF(MINUTE, '2012-01-01', Zeitpunkt) - DENSE_RANK() OVER(PARTITION BY Stadt ORDER BY DATEDIFF(MINUTE, '2012-01-01', Zeitpunkt)) AS Diff
FROM @myTab
)
SELECT Stadt, MIN(Zeitpunkt) AS Zeitfenster_Anfang, MAX(Zeitpunkt) AS Zeitfenster_Ende,
COUNT(Zeitpunkt) AS Anzahl_Bestellungen,
SUM(Bestellwert) AS Summe_Bestellungen,
AVG(Bestellwert) AS Durchschnitt_Bestellungen
FROM Inseln
GROUP BY Stadt, Diff
HAVING COUNT(Zeitpunkt) > 1
ORDER BY Stadt, Zeitfenster_Anfang;

Das Ergebnis ist eine Liste unterteilt nach den Städten und zur besseren Lesbarkeit haben wir sie diesmal auch direkt nach den Städten sortiert.

Performance-Anmerkungen

Damit diese Window-Functions auch bei größeren Datenmengen noch performant laufen, sind sogenannte POC-Indizes hilfreich. Diese beinhalten in der unten angegebenen Reihenfolge die Felder aus den folgenden Elementen der Window-Function:

Partition by
Order by
Covering Fields (des gesamten SQLs)

Wer sich nun einmal intensiver mit den Möglichkeiten dieser Window-Functions auseinandersetzen möchte, findet bei Itzik Ben-Gan einen hervorragenden Einstieg und Überblick. Ergänzt wird dies mit zahlreichen Beispielen und Lösungen.

Inselproblem_Teil3.sql