Muster (Pattern) übersetzen

Für eine Auswertung sollte ich die Kontoeröffnungen eines bestimmten Zeitraumes Werbepartnern zuordnen. Die Datenbank war aber nicht wirklich gut aufgebaut, die Partner hatten nicht wirklich IDs, da zusätzlich zu unserem Identifikationsmerkmal noch interne IDs des Partners oder auch eigene Unterscheidungsmerkmale vermerkt waren. Ich hatte z.B. solche Einträge:

Konten
Konto Partner
18 222-gp15864
26 222-587
26 222-fb5874

Wenn der Partner mit „222-“ bedeutet dies, dass es Werbung auf Sozialen Netzwerken war. 222-fb bedeutete Facebook und 222-gp bedeutet Google Plus. Diese Muster habe ich erst einmal in eine Tabelle eingefügt:

Partner
pattern partner

222-% Social Media
222-gp% Google Plus
222-fb% Facebook

Und nun zu dem von mir genutzten Statement:

SELECT k.konto, t.partner
FROM konten k
join translation t on k.partner like t.pattern
left join translation t2 on k.partner like t2.pattern and length(t2.pattern) > length(t.pattern)
where t2.pattern is null

Erst einmal verknüpfe ich die Übersetzungstabelle mittels LIKE. Hier erhalte ich jedoch Dubletten, da ja z.B. 222-% als auch 222-fb% auf den Wert 222-fb5874 matchen. Also hole ich mir die Tabelle TRANSLATION noch einmal per LEFT JOIN dazu, mit der Bedingung, dass es hierbei die Länge des Patterns größer ist als das bisher genutzte Muster. Wenn es dieses Muster nicht gibt, habe ich das längste Muster gefunden, das passt – und dann ist t2.pattern leer (also NULL).

Aktuellsten Umsatz je Konto suchen

Für eine Auswertung benötigte ich je Konto die Angaben zum letzten Umsatz, und zwar Zeitpunkt, Betrag und BLZ des Empfängers. Wäre es nur der Zeitpunkt gewesen, wäre es natürlich ein Kinderspiel. Aber zuerst meine Beispiel-Tabelle:

Umsaetze
Konto Buchung Betrag BLZ
18 2014-01-08 13:48:53.527861 -1000 12345678
18 2013-10-08 13:48:53.527861 500 32657890
26 2014-01-08 14:48:53.527861 -1000 12345678
18 2014-07-08 14:13:52.123578 800 21560215
26 2012-01-08 13:48:53.527861 -1000 12345678
34 2014-08-08 16:43:32.846312 -1000 21752468

Den Zeitpunkt des letzten Umsatzes findet man via max leicht heraus:

select konto, max(buchung) from umsaetze group by konto

Dann habe ich versucht, diese Daten mittels JOIN anzureichern – das hat auch funktioniert, die Selektion hat aber ewig gedauert:

select u.konto, u.buchung, u.betrag, u.blz
from umsaetze as u
join (select konto, max(buchung) as buchung from umsaetze group by konto) as l on l.konto=u.konto and l.buchung=u.buchung

Da diese Daten häufig benötigt werden, habe ich den Select weiter optimiert, es ist jetzt nicht mehr auf Anhieb so verständlich wie das gerade genannte Statement, dafür wesentlich performanter:

select u.konto, u.buchung, u.betrag, u.blz
from umsaetze as u
left join umsaetze as l on l.konto=u.konto and l.buchung > u.buchung
where l.konto is null

Was passiert hier?

Zuerst hole ich alle Umsätze, dann joine ich nochmal alle Umsätze dazu, die aktueller sind. Ich nutze einen LEFT JOIN, so dass es je Konto auch einen Datensatz gibt, für die es keine aktuelleren Umsätze gibt. Und genau diesen identifiziere ich in der WHERE-Klausel.

Man kann das ganze auch noch erweitern, wenn man z.B. den aktuellsten Haben-Umsatz haben will, oder aber den letzten Umsatz vor einem Stichtag – hier im Beispiel jetzt mal den letzten Haben-Umsatz des letzten Jahres:

select u.konto, u.buchung, u.betrag, u.blz
from umsaetze as u
left join umsaetze as l on l.konto=u.konto and l.buchung > u.buchung and l.betrag >= 0 and l.buchung < '2014-01-01 00.00:00.000000'
where l.konto is null and u.betrag >= 0 and u.buchung < '2014-01-01 00.00:00.000000'

Man muss nur drauf achten, dass die Bedingungen für den LEFT JOIN und die für die WHERE-Klausel identisch sind.

Ausschluss von Datensätzen mittels (LEFT) JOIN

Ausschluss mittels (INNER) JOIN

Nehmen wir an, wir haben 2 Tabellen und möchten nun nur die Datensätze erhalten, die in beiden Tabellen enthalten sind.
Man kann zwar auch die Funktion
select foo
from bar
where foo in (select foo from baz)

nutzen, bei großen Tabellen leidet die Performance aber extrem. Wesentlich performanter ist die Nutzung von JOINs, also
select bar.foo
from bar
join baz on baz.foo=bar.foo
.
Hierbei gehe ich jetzt davon aus, dass es die Werte von foo in der Tabelle baz immer eindeutig sind, es also keine Dubletten gibt. Sollte dem nicht so sein und man auch keine Dubletten habe möchte, muss man das etwas geschickter formulieren. Hierfür wähle ich jetzt ein anschaulicheres Beispiel. Ich habe eine Tabelle mit aktiven Konten und eine mit Umsätzen. In der Umsatz-Tabelle gibt es auch Konten, die mittlerweile aufgelöst sind. Ich möchte alle Konten erhalten, die auch Umsätze haben:

Konten
Kontonummer Kundennummer
18 1
26 2
42 4
Umsatz
Kontonummer Tag Betrag
18 2014-08-01 1000
18 2014-08-02 -1000
34 2014-08-01 -8000
42 2014-07-01 500

Wenn ich nun wie oben beschrieben
select konten.kontonummer
from konten
join umsatz on konten.kontonummer=umsatz.kontonummer

abfeuere, erhalte ich folgendes Ergebnis:

18
18
42

Es sind also Dubletten enthalten. Da wir diese bermeiden möchten, verknüpfen wir stattdessen nicht die komplette Tabelle Umsatz, sondern einen Sub-Select:
select konten.kontonummer
from konten
join (select distinct kontonummer from umsatz) as umsatz_eindeutig on konten.kontonummer=umsatz_eindeutig.kontonummer

Nun erhalten wir das gewünschte Ergebnis:

18
42

Das DISTINCT könnte man in diesem einfachen Beispiel natürlich auch direkt hinter das erste SELECT stellen, aber es gibt ja auch Fälle, wo es in der ersten Tabelle Dubletten gibt, die man erhalten möchte.

Ausschluss mittels LEFT JOIN

Nun nehmen wir an, wir möchten aus obigem Beispiel die umsatzlosen Konten heraussuchen. Man könnten hier wiederum select kontonummer from konten where kontonummer not in (select kontonummer from umsatz) nutzen, aber hier leidet die Performance geauso wie im obigen Beispiel. Einen INNER JOIN können wir nicht nutzen, wir wollen ja genau die anderen Konten. Zum Glück gibt es aber ja noch den LEFT JOIN:

select konten.kontonummer, umsatz.konten
from konten
left join umsatz on konten.kontonummer=umsatz.kontonummer

liefert folgendes Ergebnis:

18 18
18 18
26 NULL
42 42

Uns interessiert hier der Datensatz für das Konto 26 – dieser hat in der 2. Spalte den Wert NULL, so dass wir diesen über eine WHERE-Klausel identifizieren können:

select konten.kontonummer, umsatz.konten
from konten left join umsatz on konten.kontonummer=umsatz.kontonummer
where umsatz.kontonummer is NULL

liefert das gewünschte Ergebnis:

26 NULL

Bei größeren Abfragen zeigen sich die Performancegewinne deutlich. Wir reden nicht von ein paar Sekunden – ich hatte schon SELECTs, die nach einem Umbau von „NOT IN“ zu „LEFT JOIN“ auf einmal keine 2 Sttunden, sondern 30 Sekunden benötigt haben!

Arten von JOINs – was heißt LEFT, CROSS JOIN etc?

JOINs sind das grundlegende Handwerkszeug eines jeden Datenbank-Benutzers. Ohne sie kann man Daten immer nur aus einer Tabelle abfragen – und das will man ja spätestens nach 5 Minuten nicht mehr.

Also was tun? Fangen wir einmal mit 2 Tabellen an:

Kunden
Kundennummer Name
1 Max Musterman
2 Martin Mustermann
3 Maria Musterfrau
Konten
Kontonummer Kundennummer
18 1
26 2
42 4

Wie man sieht, gibt es in beiden Tabellen Datensätze, die in der anderen Tabelle nicht vorkommen. Und genau dann zeigen sich die Unterschiede der JOINs.

(INNER) JOIN
Bei einem „normalen“ JOIN, auch INNER JOIN genannt, werden nur die Datensätze ausgegeben, die in beiden verknüpften Tabellen vorhanden sind.

select konten.kontonummer, kunden.name from konten join kunden on kunden.kundennummer=konten.kundennummer
liefert also folgendes Ergebnis:

Kontonummer Name
18 Max Musterman
26 Martin Mustermann
LEFT (OUTER) JOIN
Bei einem LEFT JOIN, auch LEFT OUTER JOIN genannt, werden alle Datensätze ausgegeben, die in der zuerst genannten Tabelle vorhanden sind. Bei den Datensätzen, die in der zweiten Tabelle nicht enthalten sind, werden die entsprechenden Spalten mit NULL-Werten ausgegeben. Dies kann man auch nutzen, um diese Datensätze performant auszufiltern.

select konten.kontonummer, kunden.name from konten left join kunden on kunden.kundennummer=konten.kundennummer
liefert also folgendes Ergebnis:

Kontonummer Name
18 Max Musterman
26 Martin Mustermann
42 NULL
RIGHT (OUTER) JOIN
Bei einem RIGHT JOIN, auch RIGHT OUTER JOIN genannt, werden alle Datensätze ausgegeben, die in der zuletzt genannten Tabelle vorhanden sind. Bei den Datensätzen, die in der ersten Tabelle nicht enthalten sind, werden die entsprechenden Spalten mit NULL-Werten ausgegeben.

select konten.kontonummer, kunden.name from konten right join kunden on kunden.kundennummer=konten.kundennummer
liefert also folgendes Ergebnis:

Kontonummer Name
18 Max Musterman
26 Martin Mustermann
NULL Maria Musterfrau
FULL OUTER JOIN
Bei einem FULL OUTER JOIN werden alle Datensätze ausgegeben, die in mindestens einer der verknüpften Tabelle vorhanden sind. Bei den Datensätzen, die in der jeweils anderen Tabelle nicht enthalten sind, werden die entsprechenden Spalten mit NULL-Werten ausgegeben.

select konten.kontonummer, kunden.name from konten full outer join kunden on kunden.kundennummer=konten.kundennummer
liefert also folgendes Ergebnis:

Kontonummer Name
18 Max Musterman
26 Martin Mustermann
NULL Maria Musterfrau
42 NULL
CROSS JOIN
Bei einem CROSS JOIN wird das kartesische Produkt der beiden Tabellen ausgeben. Es wird keine ON-Klausel notiert.

select konten.kontonummer, kunden.name from konten cross join kunden
liefert also folgendes Ergebnis:

Kontonummer Name
18 Max Musterman
18 Martin Mustermann
18 Maria Musterfrau
26 Max Musterman
26 Martin Mustermann
26 Maria Musterfrau
42 Max Musterman
42 Martin Mustermann
42 Maria Musterfrau

Und wie kann man sich das merken?

  • Bei einem JOIN werden alle Datensätze ausgegeben, die in beiden Tabellen vorhanden sind, das muss man sich halt merken.
  • Bei einem LEFT JOIN werden alle Datensätze ausgegeben, die in der ersten Tabelle (≙ linken) vorhanden sind (wir schreiben von links nach rechts, also steht die erste Tabelle im Statement links von der zweiten).
  • Bei einem RIGHT JOIN werden alle Datensätze ausgegeben, die in der zweiten (≙ rechten) Tabelle vorhanden sind.
  • OUTER und CROSS JOINs benötigt man kaum, da muss man dann nur wissen, wo es steht…