Dateninseln erkennen und auswerten

Beim Thema Inseln kommt vielleicht spontan Urlaubsstimmung auf, deshalb gibt es heute auch ein paar Fotos dazu, aber hier geht es (leider) um etwas ganz anderes. Der Lösungsansatz zur Behandlung von Dateninseln offenbart sich erst auf den zweiten Blick, aber hier lohnt sich auf jeden Fall die genauere Betrachtung der Möglichkeiten und bestimmt ergeben sich auch Anwendungsfälle bei den eigenen Daten.

Ausgangslage

Man stelle sich vor, dass wir einen Fanshop betreiben und für jede Jahreszeit die passenden Artikel haben. Im Artikelstamm werden die Artikelnummern nach Nummernkreisen (lückenlos) vergeben. Grundsätzlich wäre es besser die Artikelgruppen als separates Feld anzulegen, anstelle der Bildung von Nummernkreisen, diese Nummernkreise finden sich aber leider oft in verschiedensten Anwendungen. Hier wird die Artikelgruppe über die Zehnerstelle gebildet, ist aber eigentlich irrelevant. Viel wichtiger für dieses Beispiel ist eine lückenlose Nummerierung innerhalb einer Gruppe. Theoretisch können auch doppelte Artikelnummern vorkommen oder die Gruppen über die Zehnergrenzen hinweg expandieren, wenn genügend Abstand zur nächsten Gruppe eingeplant wurde.

Aufgabenstellung

Wir suchen für jede Dateninsel, den Artikel mit der kleinsten und der größten Artikelnummer, dazu die Bezeichung des Artikels, den Durchschnitt des Preises der Artikelgruppe und die Anzahl der Artikel in dieser Gruppe.

	Was für das menschliche Auge relativ schnell sichtbar ist, sind die einzelnen Inseln an Informationen über die verschiedenen Artikelgruppen. Die ID hat keine Bedeutung und hilft auch bei der Auswertung nicht weiter. Aus Sicht des SQLs haben wir es aber eigentlich erst mal mit einem Durcheinander zu tun, da klare Abtrennungen zwischen den Gruppen fehlen.
	Unser Ziel ist es jetzt aber erst einmal Ordnung in das Chaos zu bringen. Wir müssen also Gemeinsamkeiten entdecken und darauf aufbauend die Dateninseln definieren, damit wir sie anschliessend über Gruppierungen auswerten können. Der Zugang über die Zehnerstelle der Artikelnummer interessiert hier nicht, da wir eine allgemeinere Lösung suchen.

Wir greifen jetzt wieder die Bedingung von oben auf: Viel wichtiger für dieses Beispiel ist eine lückenlose Nummerierung innerhalb einer Gruppe.
Dieser Ansatz ist auch in anderen Beispielen anwendbar, wenn man den richtigen Weg findet eine lückenlose Nummerierung zu erzeugen. Aber dazu später mehr.

Window Functions

An dieser Stelle greifen wir auf eine Window Function zurück, die uns seit der Version SQL Server 2008 zur Verfügung steht.

Wir bilden jetzt also über ein DENSE_RANK() die Nummerierung der einzelnen Artikel, was uns erst mal diese Liste gibt, aber noch keine Dateninseln.

SELECT Artikelnr, 
DENSE_RANK() OVER(ORDER BY Artikelnr) AS DnsRnk
FROM @myTab
ORDER BY Artikelnr;

Artikelnr	DnsRnk
1	1
10	2
11	3
12	4
21	5
22	6
30	7
31	8
32	9
33	10

Was haben wir gemacht? Wir haben für jede Artikelnummern eine aufsteigende Nummerierung erzeugt, bei doppelten Artikelnummern, würde auch die gleiche Nummer erzeugt.

Wenn die Artikelnummern auf einer Dateninsel lückenlos (also immer um 1 aufsteigend) sind, dann sollte auch die Differenz der Artikelnummer zum Ranking (welches ebenfalls immer um 1 aufsteigt) innerhalb einer Dateninsel identisch sein.

SELECT Artikelnr,  
DENSE_RANK() OVER(ORDER BY Artikelnr) AS DnsRnk, 
Artikelnr -  DENSE_RANK() OVER(ORDER BY Artikelnr) AS Diff
FROM @myTab;

Artikelnr	DnsRnk	Diff
1	1	0
10	2	8
11	3	8
12	4	8
21	5	16
22	6	16
30	7	23
31	8	23
32	9	23
33	10	23

Im nächsten Schritt wollen wir das noch verdeutlichen und die Inseln einmal durchnummerieren. Hier kommt eine CTE zum Einsatz, da wir ansonsten die folgende Fehlermeldung erhalten:

Meldung 4109, Ebene 15, Status 1
Windowed functions cannot be used in the context of another windowed function or aggregate.

Der Grund ist, dass wir jetzt nochmal eine Window Function auf unseren ersten DENSE_RANK anwenden wollen. Die Insel-Nr wird eigentlich nicht benötigt und dient nur zum besseren Verständnis. Gruppieren kann man später auch über die berechnete Differenz DIFF.

WITH Inseln
AS
(
SELECT Artikelnr,  
DENSE_RANK() OVER(ORDER BY Artikelnr) AS DnsRnk, 
Artikelnr -  DENSE_RANK() OVER(ORDER BY Artikelnr) AS Diff
FROM @myTab
)
SELECT Artikelnr, DnsRnk, Diff,
DENSE_RANK() OVER( ORDER BY Diff) AS InselNr
FROM Inseln;

Artikelnr	DnsRnk	Diff	InselNr
1	1	0	1
10	2	8	2
11	3	8	2
12	4	8	2
21	5	16	3
22	6	16	3
30	7	23	4
31	8	23	4
32	9	23	4
33	10	23	4

Auswertung der Dateninseln

Nachdem wir nun die Daten soweit strukturiert haben, dass die Dateninseln klar erkennbar sind, ist es kein großes Problem mehr, für jede Insel die geforderten Werte zu ermitteln.

Wir gruppieren jetzt über die Differenz und holen uns in jeder Gruppe mit den Aggregats-Funktionen MIN(), MAX(), AVG() und COUNT() die entsprechenden Werte je Artikelgruppe.

Dazu benötigen wir also ein GROUP BY über die Differenz, was uns leider ohne CTE wieder zu einer Fehlermeldung führen würde:

Meldung 4108, Ebene 15, Status 1
Windowed functions can only appear in the SELECT or ORDER BY clauses.

Daher sieht der Code an dieser Stelle wie folgt aus, wobei die unnötigen Berechnungen bereits entfernt wurden:

WITH Inseln
AS
(SELECT Artikelnr, Preis, 
Artikelnr -  DENSE_RANK() OVER(ORDER BY Artikelnr) AS Diff
FROM @myTab
)
SELECT MIN(Artikelnr) AS Artikel_Min, MAX(Artikelnr) AS Artikel_Max,
AVG(Preis) AS Preis_avg,
COUNT(Artikelnr) AS Artikel_in_Gruppe 
FROM Inseln
GROUP BY Diff;

Artikel_Min	Artikel_Max	Preis_avg	Artikel_in_Gruppe
1	1	10.200000	1
10	12	16.266666	3
21	22	8.600000	2
30	33	34.450000	4

Wenn wir jetzt noch die Bezeichnungen dazu joinen wollen, geht dies am einfachsten über einen Inner Join mit den Teilergebnissen. Hierzu bilden wir eine zweite CTE (Teilergebnis) über die erste CTE (Inseln).

WITH Inseln
AS
(SELECT Artikelnr, Preis, 
Artikelnr -  DENSE_RANK() OVER(ORDER BY Artikelnr) AS Diff
FROM @myTab
),
Teilergebnis
AS
(
SELECT MIN(Artikelnr) AS Artikel_Min, MAX(Artikelnr) AS Artikel_Max,
AVG(Preis) AS Preis_avg,
COUNT(Artikelnr) AS Artikel_in_Gruppe 
FROM Inseln
GROUP BY Diff
)
SELECT T.Artikel_Min, A1.Bezeichnung AS Bezeichnung_Min, T.Artikel_Max, A2.Bezeichnung AS Bezeichnung_Max, T.Preis_avg, T.Artikel_in_Gruppe
FROM Teilergebnis T
INNER JOIN @myTab A1
ON T.Artikel_Min = A1.Artikelnr
INNER JOIN @myTab A2
ON T.Artikel_Max = A2.Artikelnr;

Abendliche Radiowerbung auswerten

Nach diesem offensichtlichen Einstieg über die Artikelnummern, gilt es nun versteckte Inseln in den Daten zu finden. Hierzu dient das nächste Beispiel: Unsere Kunden bestellen die Artikel aus dem Fanshop über ein Internetportal. Nun wollen wir auswerten, ob unsere abendliche Radiowerbung erfolgreich war und ob es Zeiträume gibt, in denen jede Minute mindestens eine Bestellung im Shop eingegangen ist. Wie lange sind diese Zeiträume, wieviele Bestellungen wurden hier getätigt und welchen Umsatz haben wir dabei gemacht?

Hier erst mal ein Blick auf die Daten aus unseren Bestellungen, die für diese Auswertung von Bedeutung sind:

Die drei Zeitfenster mit mehr als einer Bestellung sind farblich markiert. Wir müssen also ausgehend vom Zeitpunkt der Bestellung eine fortlaufende Nummer erzeugen, mit der wir diese Dateninseln separieren können. Danach kommt der gleiche Ablauf wie oben. Im Ergebnis werden die Zeilen unterdrückt, die nur eine Bestellung im Zeitfenster haben.

Am einfachsten geht die Berechnung der laufenden Nummer, indem wir vom Zeitpunkt der Bestellung die Differenz in Minuten zu einem Zeitpunkt in der Vergangenheit bilden.

SELECT Zeitpunkt, DATEDIFF(MINUTE, '2012-01-01', Zeitpunkt) AS Nummerierung
FROM @myTab
ORDER BY Zeitpunkt;

Über diesen Integerwert können wir jetzt wieder unser DENSE_RANK() laufen lassen und anhand der Differenz die Inseln bilden. Anschliessend kommen die Aggregats-Funktionen zum Einsatz und über HAVING werden die Fenster mit zu wenigen Treffern ausgeblendet.

WITH Inseln
AS
(SELECT Zeitpunkt, Bestellwert,
DATEDIFF(MINUTE, '2012-01-01', Zeitpunkt) - DENSE_RANK() OVER(ORDER BY DATEDIFF(MINUTE, '2012-01-01', Zeitpunkt)) AS Diff
FROM @myTab
)
SELECT 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 Diff
HAVING COUNT(Zeitpunkt) > 1;