Suche
Make SQL Great Again

10 SQL-Tricks, die du nicht für möglich gehalten hättest

Lukas Eder

@shutterstock/dizain

Artikel in Listenform, wie der Vorliegende, funktionieren – sie wecken nicht nur das Interesse des Lesers, sondern können, sofern der Inhalt wertvoll ist (was er in diesem Fall ist, glauben Sie mir), sehr unterhaltsam sein.

In diesem Artikel lernen Sie 10 SQL-Tricks kennen, von denen viele vermutlich glaubten, sie wären nicht möglich. Der Artikel stellt eine Zusammenfassung meines neuen, rasanten und humorvollen Vortrags dar, den ich auf aktuellen Konferenzen halte (kürzlich unter anderem auf der JAX).

Die gesamten Vortragsfolien sind via Slideshare verfügbar:

Einleitung

Um den Wert dieser 10 SQL-Tricks zu verstehen, ist es wichtig, zuerst den Kontext der SQL-Sprache zu verstehen. Also, warum spreche ich auf einer Java-Konferenz (für gewöhnlich bin ich der Einzige, der das tut!) über SQL? Ich spreche darüber, denn:
SQL is the only ever successful, mainstream, and generalpurpose 4GL

Seit den frühen Tagen des Programmierens hatten Programmiersprachen-Designer stets das Verlangen Sprachen zu designen, in denen dem Computer gesagt wird, was das Ergebnis sein soll, nicht wie es erreicht werden soll. Beispielsweise sagt man dem Computer in SQL, dass man eine Benutzer-Tabelle und eine Adressen-Tabelle „verbinden“ (JOIN) möchte, um alle Benutzer aus der Schweiz zu finden. Es ist völlig egal, wie die Datenbank diese Information heranschafft. (Soll bspw. die User-Tabelle oder die Adress-Tabelle zuerst geladen werden? Sollen die zwei Tabellen durch einen nested loop oder durch eine hashmap verbunden werden? Sollen die gesamten Daten zuerst in den Speicher geladen werden, um dann nach den Schweizer Benutzern zu suchen, oder sollen nur die Schweizer Adressen geladen werden? Und so weiter…)

Wie bei jeder Abstraktion muss man trotzdem die Basics von dem, was hinter dem Vorhang der Datenbank passiert, kennen, um ihr dabei zu helfen, bei Anfragen die richtigen Entscheidungen zu treffen. Beispielsweise macht es Sinn, …

  • … eine formelle foreign key-Verbindung zwischen den Tabellen zu erstellen (dies sagt der Datenbank, dass jede Adresse garantiert einen passenden Benutzer haben muss).
  • … einen Index im Suchfeld anzulegen: z.B. „Das Land“ (dies sagt der Datenbank, dass spezifische Länder in O(log N) statt in O(N) gefunden werden können).

Aber sobald Ihre Datenbank und Ihre Anwendung herangewachsen ist, haben Sie auch alle wichtigen Metadaten an Ort und Stelle gebracht und können sich schließlich komplett auf die Geschäftslogik konzentrieren. Die folgenden 10 Tricks zeigen in wenigen Zeilen deklarativen SQLs erstaunliche Funktionalität und produzieren einfachen und trotzdem vielschichtigen Output.

1. Alles ist eine Tabelle

Trick Nummer 1 ist der trivialste unter den vorgestellten Tricks – und eigentlich gar kein echter Trick. Dennoch ist die Erkenntnisfundamental für ein eingehendes Verständnis von SQL: Alles ist eine Tabelle! Wenn Sie ein SQL-Statement wie das folgende sehen …

SELECT *
FROM person

… werden Sie schnell die Tabelle Person inmitten der FROM-Klausel bemerken. Das ist cool und ja, das ist eine Tabelle. Haben Sie aber bemerkt, dass das ganze Statement ebenfalls eine Tabelle ist? Sie können beispielsweise schreiben:

SELECT *
FROM (
  SELECT *
  FROM person
) t

Und schon haben Sie eine sogenannte „verzweigte“ Tabelle erstellt – d.h. ein verschachteltes SELECT-Statement in einer FROM-Klausel.

Das alles ist trivial und doch, wenn man so darüber nachdenkt, recht elegant. Man kann in manchen Datenbanken ad-hoc, in-Memory-Tabellen mit dem VALUES()-Konstruktor erstellen (z.B. PostgreSQL, SQL Server), …

SELECT *
FROM (
  VALUES(1),(2),(3)
) t(a)

… was einfach Folgendes ausgibt:

a
1
2
3

Sollte diese Klausel nicht unterstützt werden, kann stets zu „verzweigten“ Tabellen zurückgegangen werden, bspw. in Oracle:

SELECT *
FROM (
  SELECT 1 AS a FROM DUAL UNION ALL
  SELECT 2 AS a FROM DUAL UNION ALL
  SELECT 3 AS a FROM DUAL
) t

Nun, da Sie sehen, dass VALUES () und „verzweigte“ Tabellen konzeptionell wirklich das gleiche sind, lassen Sie uns die INSERT-Anweisung überprüfen; sie kommt in zwei Geschmacksrichtungen:

-- SQL Server, PostgreSQL, some others:
INSERT INTO my_table(a)
VALUES(1),(2),(3);

-- Oracle, many others:
INSERT INTO my_table(a)
SELECT 1 AS a FROM DUAL UNION ALL
SELECT 2 AS a FROM DUAL UNION ALL
SELECT 3 AS a FROM DUAL

In SQL ist alles eine Tabelle. Wenn Sie Zeilen in eine Tabelle einfügen, fügen Sie nicht wirklich individuelle Zeilen ein. In Wirklichkeit fügen Sie gesamte Tabellen ein. Die meisten Menschen fügen in den häufigsten Fällen schlicht „Ein-Zeilen-Tabellen“ ein und realisieren dadurch nicht was INSERT wirklich macht.
Alles ist eine Tabelle. In PostgreSQL sind selbst die Funktionen Tabellen:

SELECT *
FROM substring('abcde', 2, 3)

Es ergibt:

substring
bcd

Wenn Sie in Java programmieren, können Sie das Java-8-StreamAPI nutzen, um das alles noch einen Schritt weiter zu treiben. Bedenken Sie die folgenden gleichwertigen Konzepte:

TABLE: Stream<Tuple>
SELECT: map()
DISTINCT: distinct()
JOIN: flatMap()
WHERE / HAVING: filter()
GROUP BY: collect()
ORDER BY: sorted()
UNION ALL: concat()

Mit Java 8 gilt das Prinzip: „Alles ist ein Stream“, zumindest sobald Sie mit Streams arbeiten. Egal, wie Streams transformiert werden, bspw. mit map() oder filter(), der Ergebnistyp wird immer wieder ein Stream sein.

Wir haben einen ganzen Artikel zu dem Thema geschrieben, um es tiefgehender zu erklären und um die StreamAPI mit SQL zu vergleichen: Häufige SQL-Klauseln und ihre Equivalenten in Java 8 Streams.

Und wenn Sie nach „besseren Streams“ suchen (z.B. Streams mit noch umfangreicherer SQL-Semantik), sehen Sie sich jOOλ, eine Open Source Bibliothek, die SQL-Fensterfunktionen (WINDOW-Funktionen) zu Java bringt, an.

2. Datenerzeugung mit rekursivem SQL

Common Table Expressions (CTE; auch Subquery Factoring genannt, z.B. in Oracle) sind der einzige Weg, Variablen in SQL zu bestimmen (neben obskuren WINDOW-Klauseln, die ohnehin nur von PostgreSQL und Sybase SQL unterstützt werden).

Es handelt sich dabei um ein mächtiges Konzept – ein sehr mächtiges. Nehmen wir das folgende Statement:

-- Table variables
WITH
  t1(v1, v2) AS (SELECT 1, 2),
  t2(w1, w2) AS (
    SELECT v1 * 2, v2 * 2
    FROM t1
  )
SELECT *
FROM t1, t2

Es ergibt:

v1 v2 w1 w2
1 2 2 4

Mittels WITH-Klausel können Listen von Tabellenvariablen spezifiziert werden (wir erinnern uns: Alles ist eine Tabelle), die sogar voneinander abhängig sein können.

Es ist einfach zu verstehen und macht CTE (Common Table Expressions) alleine schon sehr nützlich. Aber was wirklich grandios ist, ist die Tatsache, dass sie rekursiv sein können! Sehen Sie sich das folgende PostgreSQL-Beispiel an:

WITH RECURSIVE t(v) AS (
  SELECT 1     -- Seed Row
  UNION ALL
  SELECT v + 1 -- Recursion
  FROM t
)
SELECT v
FROM t
LIMIT 5

Es ergibt:

v
1
2
3
4
5

Wie es funktioniert, fragen Sie? Das ist recht einfach, sobald Sie die vielen Schlüsselbegriffe kennen. Gehen wir es gemeinsam durch: Sie definieren einen gewöhnlichen Tabellenausdruck mit genau zwei UNION ALLSubqueries.

Das erste UNION ALLSubquery ist etwas, das ich für gewöhnlich seed row nenne. Es seeded (initialisiert) die Rekursion. Es kann eine oder mehrere Zeilen erstellen, die wir nachträglich rekursieren. Denken Sie dran: Alles ist eine Tabelle. Unsere Rekursion wird somit auf einer gesamten Tabelle stattfinden und nicht bloß auf einer individuellen Zeile oder einem individuellen Wert.

Das zweite UNION ALLSubquery ist der Ort, an dem die Rekursion stattfindet. Wenn Sie sich das Beispiel etwas genauer ansehen, werden Sie bemerken, dass dort from t steht. D.h. das zweite Subquery kann SELECTs auf das CTE ausführen, das wir erst gleich anlegen werden: Rekursiv. Es hat dadurch ebenfalls Zugriff auf die Spalte v, die von dem CTE angelegt wurde, die sie bereits benutzt.

In unserem Beispiel seeden wir die Rekursion mit der Zeile (1) und gehen diese mittels v + 1 durch. Die Rekursion wird von der aufrufenden User-Seite mittels LIMIT 5 gestoppt (Achten Sie auf potentiell endlose Rekursionen – wie auch bei Java 8 Streams).

Seitenbemerkung: Turing Vollständigkeit

Rekursive CTE machen SQL:1999 Turing-vollständig. Das heißt, dass jedes Programm in SQL geschrieben werden kann (sofern Sie dafür verrückt genug sind).
Ein beeindruckendes, regelmäßig auf Blogs auftauchendes Beispiel: Das Mandelbrot-Set, z.B. wie hier dargestellt:

WITH RECURSIVE q(r, i, rx, ix, g) AS (
  SELECT r::DOUBLE PRECISION * 0.02, i::DOUBLE PRECISION * 0.02,
        .0::DOUBLE PRECISION      , .0::DOUBLE PRECISION, 0
  FROM generate_series(-60, 20) r, generate_series(-50, 50) i
  UNION ALL
  SELECT r, i, CASE WHEN abs(rx * rx + ix * ix) <= 2 THEN rx * rx - ix * ix END + r,
               CASE WHEN abs(rx * rx + ix * ix) <= 2 THEN 2 * rx * ix END + i, g + 1
  FROM q
  WHERE rx IS NOT NULL AND g < 99
)
SELECT array_to_string(array_agg(s ORDER BY r), '')
FROM (
  SELECT i, r, substring(' .:-=+*#%@', max(g) / 10 + 1, 1) s
  FROM q
  GROUP BY i, r
) q
GROUP BY i
ORDER BY i

Führen Sie diesen Code auf PostgreSQL aus, so erhalten Sie etwas wie:

                             .-.:-.......==..*.=.::-@@@@@:::.:.@..*-.         =.
                             ...=...=...::+%.@:@@@@@@@@@@@@@+*#=.=:+-.      ..-
                             .:.:=::*....@@@@@@@@@@@@@@@@@@@@@@@@=@@.....::...:.
                             ...*@@@@=.@:@@@@@@@@@@@@@@@@@@@@@@@@@@=.=....:...::.
                              .::@@@@@:-@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@..-:@=*:::.
                              .-@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.=@@@@=..:
                              ...@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@@@@@:..
                             ....:-*@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@::
                            .....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-..
                          .....@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-:...
                         .--:+.@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@...
                         .==@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-..
                         ..+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-#.
                         ...=+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..
                         -.=-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..:
                        .*%:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:@-
 .    ..:...           ..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
..............        ....-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@%@=
.--.-.....-=.:..........::@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..
..=:-....=@+..=.........@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:.
.:+@@::@==@-*:%:+.......:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.
::@@@-@@@@@@@@@-:=.....:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:
.:@@@@@@@@@@@@@@@=:.....%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
.:@@@@@@@@@@@@@@@@@-...:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@:-
:@@@@@@@@@@@@@@@@@@@-..%@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.
%@@@@@@@@@@@@@@@@@@@-..-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.
@@@@@@@@@@@@@@@@@@@@@::+@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@+
@@@@@@@@@@@@@@@@@@@@@@:@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@..
@@@@@@@@@@@@@@@@@@@@@@-@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@-
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@.

Beeindruckend, oder?

3. Berechnung der laufenden Summe

Dieser Blog ist voll mit Beispielen zum „Running Total“ (laufende Summe). Darunter sind einige der lehrreichsten Beispiele für fortgeschrittenes SQL, denn es gibt mindestens ein Dutzend Möglichkeiten die laufende Summe zu implementieren.

Konzeptionell ist das „Running Total“ leicht zu verstehen.

running-total

In Microsoft Exel würde man schlicht die Summe (oder Differenz) von zwei Werten berechnen und dann, mithilfe des Fadenkreuz-Cursors, die Formel durch das gesamte Dokument ziehen.

You „run“ that total through the spreadsheet. A „running total“.

Die beste Lösung dafür in SQL ist die Nutzung von Fensterfunktionen, einem anderen Thema, das dieser blog bereits viele Male thematisiert hat.

Fensterfunktionen sind ein mächtiges Konzept – anfangs nicht einfach zu verstehen, aber eigentlich sehr sehr einfach.

Fensterfunktionen sind Aggregationen oder Ranglisten eines Subsets von Zeilen, die relativ zur aktuellen Zeile stehen und mittels SELECT transformiert werden.

Und das war es auch schon.

Was es im Wesentlichen bedeutet, ist, dass Fensterfunktionen Berechnungen für Zeilen „über“ oder „unter“ der aktuellen Zeile durchführen können. Im Gegensatz zu gewöhnlichen Aggregationen und GROUP BYs transformieren sie die Zeilen allerdings nicht, was sie sehr nützlich macht.

Die Syntax kann wie folgt zusammengefasst werden, wobei einzelne Teile davon optional sind:

function(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS BETWEEN ... AND ...
)

Wir haben also eine beliebige Funktionsart (zu Beispielen für solche Funktionen werden wir später kommen), gefolgt von der OVER()-Klausel, die das Fenster spezifiziert. Die OVER()-Klausel definiert demnach:

  • Die PARTITION: Ausschließlich Zeilen in der gleichen Partition der aktuellen Zeile werden für die Fensterberechnung berücksichtigt.
  • Die ORDER: Das Fenster kann unabhängig von unserer Selektierung geordnet sein.
  • Die ROWS– oder RANGE-Rahmendefinition: Das Fenster kann auf eine feste Anzahl von Zeilen „davor“ und „danach“ festgelegt werden.

Das ist alles, was es zu Fensterfunktionen zu sagen gibt.

Wie hilft uns das bei der Berechnung der laufenden Summe? Sehen Sie sich die folgende Tabelle an:

ID VALUE_DATE AMOUNT BALANCE
9997 2014-03-18 99.17 19985.81
9981 2014-03-16 71.44 19886.64
9979 2014-03-16 -94.60 19815.20
9977 2014-03-16 -6.96 19909.80
9971 2014-03-15 -65.95 19916.76

Lassen Sie uns annehmen, dass wir BALANCE aus AMOUNT berechnen wollen.

Intuitiv wissen wir, dass das Folgende sich bewahrheitet:

running-total-2

Jegliche Balance (dt. Bilanz) kann demnach durch den nachfolgenden pseudo-SQL-Code ausgedrückt werden:

TOP_BALANCE - SUM(AMOUNT) OVER (
"all the rows on top of the current row"
)

In echtem SQL würde es wie folgt geschrieben werden:

SUM(t.amount) OVER (
  PARTITION BY t.account_id
  ORDER BY     t.value_date DESC,
               t.id         DESC
  ROWS BETWEEN UNBOUNDED PRECEDING
       AND     1         PRECEDING
)

Erklärung:

  • PARTITION wird die Summe für jeden einzelnen Bankaccount berechnen; nicht für das gesamte Datenset.
  • ORDER wird dafür sorgen, dass die Transaktionen (in der Partition) vor der Rechnung geordnet sind.
  • ROWS wird ausschließlich die vorangegangenen Zeilen (in der Partition, bereits geordnet) für die Rechnung berücksichtigen.

All das Genannte wird in-Memory, mit dem bereits selektierten Datensatz in den FROM .. WHERE (etc.)-Klauseln ausgeführt und ist dadurch extrem schnell.

Intermezzo

Bevor wir zu all den anderen spektakulären Tricks weiter gehen, bedenken Sie nun Folgendes: Bereits gesehen haben wir

  • (Rekursive) Common Table Expressions (CTE)
  • Fensterfunktionen (Window Functions)

Beide Features sind:

  • Super
  • Sehr mächtig
  • Deklarativ
  • Teil des SQL-Standards
  • In den beliebtesten RDBMS (außer MySQL) verfügbar
  • Sehr wichtige Bausteine

Wenn auch nur irgendetwas aus diesem Artikel mitgenommen werden sollte, ist es der Fakt, dass Sie diese zwei Bausteine des modernen SQL auf jeden Fall kennen sollten. Warum? Weil:

still.using.windows.3.1

4. Das Finden der längsten, lückenlosen Abfolge

Stack Overflow besitzt ein außerordentlich nettes Feature, um Menschen dazu zu motivieren, lange auf ihrer Webseite zu bleiben. Abzeichen:

4.badges

Zum Vergleich sehen Sie hier wieviele Abzeichen ich habe: Tausende.

Wie werden diese Abzeichen berechnet? Lassen Sie uns den Enthusiast und den Fanatic genauer betrachten. Sie werden denjenigen verliehen, die eine gewisse Anzahl an aufeinanderfolgenden Tagen die Webseite besuchen. Egal, ob es sich um den Hochzeitstag oder den Geburtstag der Frau handelt, Sie müssen sich einloggen oder der Zähler startet von vorne.

Da wir uns hier allerdings mit deklarativer Programmierung beschäftigen, müssen uns in-Memory-Zähler und die Wartung von etwaigen Zuständen nicht weiter kümmern. Wir wollen diese Zähler und Zustände in Form von Online Analytic SQL ausdrücken. Nehmen wir bspw. die folgenden Daten:

LOGIN_TIME
2014-03-18 05:37:13
2014-03-16 08:31:47
2014-03-16 06:11:17
2014-03-16 05:59:33
2014-03-15 11:17:28
2014-03-15 10:00:11
2014-03-15 07:45:27
2014-03-15 07:42:19
2014-03-14 09:38:12

Sie helfen uns nicht sonderlich weiter. Lassen Sie uns die Stunden aus dem Timestamp entfernen. Das ist recht einfach:

SELECT DISTINCT
  cast(login_time AS DATE) AS login_date
FROM logins
WHERE user_id = :user_id

Es ergibt:

LOGIN_DATE
2014-03-18
2014-03-16
2014-03-15
2014-03-14

Nun, da wir die Fensterfunktionen bereits kennengelernt haben, lassen Sie uns nun eine schlichte Zeilennummer zu jedem dieser Termine hinzufügen:

SELECT
  login_date,
  row_number() OVER (ORDER BY login_date)
FROM login_dates

Das Ergebnis ist:

LOGIN_DATE RN
2014-03-18 4
2014-3-16 3
2014-3-15 2
2014-3-14 1

Immernoch einfach. Was passiert nun, wenn wir die Werte subtrahieren, anstatt sie einzeln zu selektieren?

SELECT
  login_date -
  row_number() OVER (ORDER BY login_date)
FROM login_dates

Wow. Interessant. 14-1 = 13; 15-2 = 13; 16-3 = 13; aber 18-4 = 14. Nichts kann es besser ausdrücken als Doge:

doge

Für dieses Verhalten gibt es eine einfache Erklärung.

  1. ROW_NUMBER() hat keine Lücken, denn so wurde es definiert.
  2. Unsere Daten allerdings schon.

Wenn wir also eine „lückenlose“ Abfolge aufeinanderfolgender Integer von einer „lückenhaften“ Abfolge nicht-aufeinanderfolgender Daten subtrahieren, werden wir für jedes aufeinanderfolgende Datum die gleiche Zahl erhalten und sobald es eine Lücke in den Datenbank-Daten gibt, ändert sie sich.

Huh.

Das bedeutet, dass wir diesen willkürlichen Zahlenwert nun schlicht gruppieren können (GROUP BY):

SELECT
  min(login_date), max(login_date),
  max(login_date) -
  min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC

Schon sind wir fertig. Die längste Abfolge an aufeinanderfolgenden Datum ohne Lücken wurde gefunden:

MIN MAX LENGTH
2014-03-14 2014-03-16 3
2014-03-18 2014-03-18 1

Das vollständige Query sieht wie folgt aus:

WITH
  login_dates AS (
    SELECT DISTINCT cast(login_time AS DATE) login_date
    FROM logins WHERE user_id = :user_id
  ),
  login_date_groups AS (
    SELECT
      login_date,
      login_date - row_number() OVER (ORDER BY login_date) AS grp
    FROM login_dates
  )
SELECT
  min(login_date), max(login_date),
  max(login_date) - min(login_date) + 1 AS length
FROM login_date_groups
GROUP BY grp
ORDER BY length DESC

too.easy

Am Ende ist es gar nicht so schwer, nicht wahr? Natürlich macht das „Idee haben“ den gesamten Unterschied aus, aber das Query an sich ist sehr, sehr einfach und elegant. Auf keinen Fall könnten Sie einen notwendigen Algorithmus schlanker umsetzen.

5. Die Länge einer Abfolge bestimmen

Zuvor hatten wir uns mit Abfolgen von aufeinanderfolgenden Werten beschäftigt. Sie sind leicht zu bearbeiten, da wir aufeinanderfolgende Integer dafür missbrauchen können. Was ist nun, wenn die „Abfolgen“ der Einträge weniger intuitiv sind und zusätzlich mehrere Einträge die gleichen Werte enthalten? Betrachten Sie die folgenden Daten, wobei LENGTH die Länge jeder Abfolge, die wir berechnen möchten, ist:

ID VALUE_DATE AMOUNT LENGTH
9997 2014-03-18 99.17 2
9981 2014-03-16 71.44 2
9979 2014-03-16 -94.60 3
9977 2014-03-16 -6.96 3
9971 2014-03-15 -65.95 3
9964 2014-03-15 15.13 2
9962 2014-03-15 17.47 2
9960 2014-03-15 -3.55 1
9959 2014-03-14 32.00 1

Sie haben richtig geraten. Eine „Abfolge“ ist dadurch definiert, dass aufeinanderfolgende (nach ID geordnet) Zeilen die glieche Kennzeichnung (SIGN(AMOUNT)) haben. Überprüfen Sie die Daten neu formatiert noch einmal:

ID VALUE_DATE AMOUNT LENGTH
9997 2014-03-18 +99.17 2
9981 2014-03-16 +71.44 2
9979 2014-03-16 -94.60 3
9977 2014-03-16 -6.96 3
9971 2014-03-15 -65.95 3
9964 2014-03-15 +15.13 2
9962 2014-03-15 +17.47 2
9960 2014-03-15 -3.55 1
9959 2014-03-14 +32.00 1

Wie machen wir das? Ganz einfach: Zuerst werden wir alles Störende los und fügen eine zusätzliche Zeilennummer(RN) hinzu:

SELECT
  id, amount,
  sign(amount) AS sign,
  row_number()
    OVER (ORDER BY id DESC) AS rn
FROM trx

Wir erhalten dadurch:

ID AMOUNT SIGN RN
9997 99.17 1 1
9981 71.44 1 2
9979 -94.60 -1 3
9977 -6.96 -1 4
9971 -65.95 -1 5
9964 15.13 1 6
9962 17.47 1 7
9960 -3.55 -1 8
9959 32.00 1 9

Das nächste Ziel ist nun, die folgende Tabelle zu produzieren:

ID AMOUNT SIGN RN LO HI
9997 99.17 1 1 1
9981 71.44 1 2 2
9979 -94.60 -1 3 3
9977 -6.96 -1 4
9971 -65.95 -1 5 5
9964 15.13 1 6 6
9962 17.47 1 7 7
9960 -3.55 -1 8 8 8
9959 32.00 1 9 9 9

In dieser Tabelle wollen wir die ROW_NUMBER des „niedrigen“ Endes jeder Abfolge nach „LO“ kopieren und die des „höchsten“ Endes nach „HI„. Wir werden dafür die magischen Funktionen LEAD() und LAG() nutzen. LEAD() kann auf die n-te nächste Zeile, relativ von der aktuellen Zeile, zugreifen, während LAG() auf die n-te vorherige Zeile zugreifen kann. Ein Beispiel:

SELECT
  lag(v) OVER (ORDER BY v),
  v,
  lead(v) OVER (ORDER BY v)
FROM (
  VALUES (1), (2), (3), (4)
) t(v)

Dieses Query erzeugt:

lead_lag-table

Das ist großartig! Denken Sie daran: Mit Fensterfunktionen können Sie Rankings oder Aggregationen auf einer Teilmenge von Zeilen im Bezug auf die aktuelle Zeile auszuführen. Im Falle von LEAD() und LAG() greifen wir einfach eine einzelne Zeile, relativ zur aktuelle Zeile, durch das Offset auf. Dies ist in vielen Fällen ausgesprochen nützlich.

Das Beispiel mit „LO“ und „HI“ weiterführend, schreiben wir …

SELECT
  trx.*,
  CASE WHEN lag(sign)
       OVER (ORDER BY id DESC) != sign
       THEN rn END AS lo,
  CASE WHEN lead(sign)
       OVER (ORDER BY id DESC) != sign
       THEN rn END AS hi,
FROM trx

… und vergleichen dadurch das „vorherige“ Vorzeichen (LAG(SIGN)) mit dem aktuellen Vorzeichen (SIGN). Unterscheiden sie sich, kopieren wir die Zeilennummer in „LO„, denn dann haben wir das untere Ende der Abfolge gefunden.

Zum Schluss bedarf es noch etwas langweiligem NULL-Handling, um alles in korrekte Bahnen zu lenken und schon sind wir fertig:

SELECT -- With NULL handling...
  trx.*,
  CASE WHEN coalesce(lag(sign)
       OVER (ORDER BY id DESC), 0) != sign
       THEN rn END AS lo,
  CASE WHEN coalesce(lead(sign)
       OVER (ORDER BY id DESC), 0) != sign
       THEN rn END AS hi,
FROM trx

Auf zum nächsten Schritt. Wir wollen, dass „LO“ und „HI“ in allen Zeilen, nicht nur in den „niedrigsten“ und „höchsten“ Randwerten, zu sehen sind. Beispielsweise wie in der folgenden Tabelle:

ID AMOUNT SIGN RN LO HI
9997 99.17 1 1 1 2
9981 71.44 1 2 1 2
9979 -94.60 -1 3 3 5
9977 -6.96 -1 4 3 5
9971 -65.95 -1 5 3 5
9964 15.13 1 6 6 7
9962 17.47 1 7 6 7
9960 -3.55 -1 8 8 8
9959 32.00 1 9 9 9

Wir benutzen dafür ein Feature, das zumindest in Redshift, Sybase SQL Anywhere, DB2 und Oracle verfügbar ist. Wir benutzen die IGNORE NULLS-Klausel, die in einigen Fensterfunktionen genutzt werden kann.

SELECT
  trx.*,
  last_value (lo) IGNORE NULLS OVER (
    ORDER BY id DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW) AS lo,
  first_value(hi) IGNORE NULLS OVER (
    ORDER BY id DESC
    ROWS BETWEEN CURRENT ROW
    AND UNBOUNDED FOLLOWING) AS hi
FROM trx

Eine Menge Schlüsselbegriffe! Aber das Wesentliche ist immer das Gleiche. Von einer bestimmten „aktuellen“ Zeile schauen wir uns alle „vorangehenden Werte“ (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) an, aber ignorieren dabei die NULL-Werte. Aus den vorangegangenen Werten nehmen wir den niedrigsten Wert, und das ist unser neuer „LO“ Wert. Mit anderen Worten nehmen wir den „sich am nächsten befindlichen, vorangehenden“ „LO“ Wert.

Das selbe geschieht mit „HI“. Von einer bestimmten „aktuellen“ Zeile schauen wir uns alle „nachfolgenden Werte“ (ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) an und ignorieren erneut die NULL-Werte. Aus den vorangegangenen Werten nehmen wir den höchsten Wert, welcher unser neuer „HI„-Wert ist. Mit anderen Worten nehmen wir den „sich am nächsten befindlichen, nachfolgenden“ „HI„-Wert.

Durch Powerpoint erklärt:

length.of.a.series

Für eine hundertprozentige Korrektheit müssen wir allerdings noch etwas mit NULL herumhantieren:

SELECT -- With NULL handling...
  trx.*,
  coalesce(last_value (lo) IGNORE NULLS OVER (
    ORDER BY id DESC
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW), rn) AS lo,
  coalesce(first_value(hi) IGNORE NULLS OVER (
    ORDER BY id DESC
    ROWS BETWEEN CURRENT ROW
    AND UNBOUNDED FOLLOWING), rn) AS hi
FROM trx

Schließlich unternehmen wir nur noch einen trivialen letzten Schritt und behalten im Hinterkopf:
off-by-1 Errors:

SELECT
  trx.*,
  1 + hi - lo AS length
FROM trx

Und wir sind fertig. Hier ist unser Ergebnis:

ID AMOUNT SIGN RN LO HI LENGTH
9997 99.17 1 1 1 2 2
9981 71.44 1 2 1 2 2
9979 -94.60 -1 3 3 5 3
9977 -6.96 -1 4 3 5 3
9971 -65.95 -1 5 3 5 3
9964 15.13 1 6 6 7 2
9962 17.47 1 7 6 7 2
9960 -3.55 -1 8 8 8 1
9959 32.00 1 9 9 9 1

Hier ist das vollständige Query:

WITH
  trx1(id, amount, sign, rn) AS (
    SELECT id, amount, sign(amount), row_number() OVER (ORDER BY id DESC)
    FROM trx
  ),
  trx2(id, amount, sign, rn, lo, hi) AS (
    SELECT trx1.*,
    CASE WHEN coalesce(lag(sign) OVER (ORDER BY id DESC), 0) != sign
         THEN rn END,
    CASE WHEN coalesce(lead(sign) OVER (ORDER BY id DESC), 0) != sign
         THEN rn END
    FROM trx1
  )
SELECT
  trx2.*, 1
  - last_value (lo) IGNORE NULLS OVER (ORDER BY id DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  + first_value(hi) IGNORE NULLS OVER (ORDER BY id DESC
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
FROM trx2

still.okay

Huh. Diese SQL-Sache wird langsam interessant!

Bereit für mehr?

6. Das Teilsummenproblem mit SQL

Das ist mein Liebling!

Was ist das Teilsummenproblem? Hier ist eine witzige Erklärung:
https://xkcd.com/287

Und hier eine langweilige:
https://en.wikipedia.org/wiki/Subset_sum_problem

Kurz gesagt, wollen wir für jede dieser Totalen …

ID TOTAL
1 25150
2 19800
3 27511

… die „beste“ (d.h. nächste) mögliche Summe, bestehend aus sämtlichen Kombinationen der folgenden Einträge, finden.

ID ITEM
1 7120
2 8150
3 8255
4 9051
5 1220
6 12515
7 13555
8 5221
9 812
10 6562

Da Sie alle über eine schnelle mentale mathematische Verarbeitung verfügen, haben Sie die besten Summen sofort selbst berechnet.

TOTAL BEST CALCULATION
25150 25133 7120 + 8150 + 9051 + 812
19800 19768 1220 + 12515 + 5221 + 812
27511 27488 8150 + 8255 + 9051 + 1220 + 812

Wie macht man das mit SQL? Ganz einfach: Erstellen Sie einfach eine CTE, die alle 2^n „möglichen“ Summen beinhaltet und finden sie die nächste Summe für jedes TOTAL:

-- All the possible 2N sums
WITH sums(sum, max_id, calc) AS (...)

-- Find the best sum per “TOTAL”
SELECT
  totals.total,
  something_something(total - sum) AS best,
  something_something(total - sum) AS calc
FROM draw_the_rest_of_the_*bleep*_owl

Während Sie diesen Artikel lesen, könnten Sie meinem Freund hier womöglich ähneln:
maybe.if.i.just.hide

Aber keine Sorge, die Lösung ist – wieder – nicht so schwer (obwohl sie aufgrund der Natur des Algorithmus nicht sonderlich performant ist):

WITH sums(sum, id, calc) AS (
  SELECT item, id, to_char(item) FROM items
  UNION ALL
  SELECT item + sum, items.id, calc || ' + ' || item
  FROM sums JOIN items ON sums.id < items.id
)
SELECT
  totals.id,
  totals.total,
  min (sum) KEEP (
    DENSE_RANK FIRST ORDER BY abs(total - sum)
  ) AS best,
  min (calc) KEEP (
    DENSE_RANK FIRST ORDER BY abs(total - sum)
  ) AS calc,
FROM totals
CROSS JOIN sums
GROUP BY totals.id, totals.total

In diesem Artikel werde ich die Details dieser Lösung nicht erklären, da das dazugehörige Beispiel aus einem vorangegangen Artikel genommen wurde. Sie finden ihn hier:
Wie findet man die nächste Teilsumme in SQL?

Genießen Sie die Details, aber kommen Sie auf jeden Fall für die restlichen vier Tricks zurück:

7. Das Abschneiden der laufenden Summe („running total“)

Bisher haben wir gesehen, wie man das „normale“ Running Total mittels Fensterfunktionen in SQL berechnet. Das war recht einfach. Wie wäre es nun, wenn wir das Running Total abschneiden, sodass es niemals unter 0 geht? Im Prinzip wollen wir die folgende Tabelle berechnen:

DATE AMOUNT TOTAL
2012-01-01 800 800
2012-02-01 1900 2700
2012-03-01 1750 4450
2012-04-01 -20000 0
2012-05-01 900 900
2012-06-01 3900 4800
2012-07-01 -2600 2200
2012-08-01 -2600 0
2012-09-01 2100 2100
2012-10-01 -2400 0
2012-11-01 1100 1100
2012-12-01 1300 2400

Als der große, negative Betrag (AMOUNT) -20000 abgezogen wurde, zeigen wir statt dem wirklichen TOTAL von -15550, schlicht 0 an. In anderen Worten (bzw. Daten):

DATE AMOUNT TOTAL
2012-01-01 800 800 GREATEST(0, 800)
2012-02-01 1900 2700 GREATEST(0, 2700)
2012-03-01 1750 4450 GREATEST(0, 4450)
2012-04-01 -20000 0 GREATEST(0, -15550)
2012-05-01 900 900 GREATEST(0, 900)
2012-06-01 3900 4800 GREATEST(0, 4800)
2012-07-01 -2600 2200 GREATEST(0, 2200)
2012-08-01 -2600 0 GREATEST(0, -400)
2012-09-01 2100 2100 GREATEST(0, 2100)
2012-10-01 -2400 0 GREATEST(0, -300)
2012-11-01 1100 1100 GREATEST(0, 1100)
2012-12-01 1300 2400 GREATEST(0, 2400)

Wie machen wir das?

capping.the.running.total

Ganz genau. Mit düsterem, Anbieterspezifischem SQL. In diesem Fall nutzen wir Oracle SQL.

burning.kid.meme

Wie funktioniert das? Überraschend einfach!

Fügen Sie schlicht MODEL nach jeder Tabelle an und sie öffnen damit eine Büchse spektakulärer SQL-Würmer.

SELECT ... FROM some_table

-- Put this after any table
MODEL ... 

Sobald wir MODEL hinzufügen, können wir Tabellenkalkulationslogik direkt in die SQL-Anweisungen, wie mit Microsoft Excel, implementieren.

Die folgenden drei Klauseln sind die mächtigsten und verbreitetsten (d.h. sie werden 1-2 mal von jemandem auf diesem Planeten genutzt):

MODEL
  -- The spreadsheet dimensions
  DIMENSION BY ...

  -- The spreadsheet cell type
  MEASURES ...

  -- The spreadsheet formulas
  RULES ... 

Die Bedeutung jeder dieser drei zusätzlichen Klauseln ist wieder einmal durch Präsentationsfolien am besten erklärt.

Die DIMENSION BY-Klausel spezifiziert die Dimensionen des Tabellendokuments. Ungleich MS Excel lässt Oracle unendlich viele Dimensionen zu.

dimension.by

Die MEASURES-Klausel spezifiziert den Wertebereich jeder Zelle des Dokuments. Ungleich MS Exel lässt Oracle ganze Tupel in jeder Zelle zu und nicht bloß einzelne Werte.

measures

Die RULES-Klausel spezifiziert die auf jede Zelle des Dokuments angewendeten Formeln. Ungleich MS Excel sind diese Regeln bzw. Formeln an einem einzelnen Ort zentralisiert und nicht in jeder Zelle selbst abgelegt.

rules

Das Design macht MODEL etwas schwerer nutzbar als in MS Excel, aber, sobald Sie sich trauen, sehr viel mächtiger.

SELECT *
FROM (
  SELECT date, amount, 0 AS total
  FROM amounts
)
MODEL
  DIMENSION BY (row_number() OVER (ORDER BY date) AS rn)
  MEASURES (date, amount, total)
  RULES (
    total[any] = greatest(0,
    coalesce(total[cv(rn) - 1], 0) + amount[cv(rn)])
  )

Das Ding ist so mächtig, dass es dazu eine eigene Dokumentation von Oracle gibt. Statt also in diesem Artikel weiter zu erklären, lesen Sie bitte die ausgezeichnete Dokumentation:

http://www.oracle.com/technetwork/middleware/bi-foundation/10gr1-twp-bi-dw-sqlmodel-131067.pdf

8. Erkennung des Zeitreihenanalyse-Patterns

Wenn Sie in der fraud detection oder jedem anderen Bereich, in dem Echtzeit-Analysen auf großen Datenmengen ausgeführt werden, arbeiten, ist das Zeitreihenanalyse-Pattern sicherlich kein neuer Begriff für Sie.

Wenn wir die Tabelle unter „Länge einer Abfolge“ (5. Die Länge einer Abfolge bestimmen) erneut zu Rate ziehen, könnten wir auf Dauer Trigger für komplexe Ereignisse erzeugen wollen:

ID VALUE_DATE AMOUNT LEN TRIGGER
9997 2014-03-18 +99.17 1
9981 2014-03-16 -71.44 4
9979 2014-03-16 -94.60 4 X
9977 2014-03-16 -6.96 4
9971 2014-03-15 -65.95 4
9964 2014-03-15 +15.13 3
9962 2014-03-15 +17.47 3
9960 2014-03-15 +3.55 3
9959 2014-03-14 -32.00 1

Die Regel dieses Triggers ist:

Löse bei der 3. Wiederholung eines Events aus, wenn das Event mehr als drei Mal auftritt.

Wie bereits bei der vorangegangenen MODEL-Klausel können wir dies mit einer Oracle-spezifischen Klausel erreichen, die mit Oracle 12c hinzugefügt wurde:

SELECT ... FROM some_table

-- Put this after any table to pattern-match
-- the table’s contents
MATCH_RECOGNIZE (...) 

Die einfachste und gleichzeitig mögliche Anwendung von MATCH_RECOGNIZE arbeitet mit der folgenden Unterklausel:

SELECT *
FROM series
MATCH_RECOGNIZE (
  -- Pattern matching is done in this order
  ORDER BY ...

  -- These are the columns produced by matches
  MEASURES ...

  -- A short specification of what rows are
  -- returned from each match
  ALL ROWS PER MATCH

  -- «Regular expressions» of events to match
  PATTERN (...)

  -- The definitions of «what is an event»
  DEFINE ...
) 

Das klingt verrückt. Sehen wir uns einige Beispielimplementierungen der Klausel an.

SELECT *
FROM series
MATCH_RECOGNIZE (
  ORDER BY id
  MEASURES classifier() AS trg
  ALL ROWS PER MATCH
  PATTERN (S (R X R+)?)
  DEFINE
    R AS sign(R.amount) = prev(sign(R.amount)),
    X AS sign(X.amount) = prev(sign(X.amount))
) 

Was geschieht hier?

  • Wir sortieren die Tabelle nach der ID, was wiederum die Anordnung ist, in der wir die Events bearbeiten wollen.
  • Danach spezifizieren wir die Werte, die wir als Ergebnis erhalten wollen. Wir wollen den „MEASURE trg„, der als Classifier festgelegt wurde, also zum Beispiel das Literal, welches wir im nachfolgenden PATTERNnutzen wollen. Außerdem sämtliche Zeilen eines Treffers.
  • Danach legen wir ein reguläres Expression-ähnliches Pattern fest. Das Pattern ist ein Event: „S“ für Start, optional gefolgt von „R“ für Repeat (Wiederholen), „X“ für das spezielle Event X, gefolgt von einem weiteren „R“ für Repeat. Wenn das gesamte Pattern passt, dann erhalten wir Kombinationen wie SRXR, SRXRR oder SRXRRR, usw. X wird an der dritten Position einer Abfolge der Länge >=4 sein.
  • Abschließend definieren wir R und X als dasselbe: Dies ist der Fall, wenn das SIGN(AMOUNT) der aktuellen Zeile gleich dem SIGN(AMOUNT) der vorherigen Zeile ist. S müssen wir nicht definieren. S ist nur eine andere Zeile.

Das Query wird auf magische Weise den folgenden Output liefern:

ID VALUE_DATE AMOUNT TRG
9997 2014-03-18 +99.17 S
9981 2014-03-16 -71.44 R
9979 2014-03-16 -94.60 X
9977 2014-03-16 -6.96 R
9971 2014-03-15 -65.95 S
9964 2014-03-15 +15.13 S
9962 2014-03-15 +17.47 S
9960 2014-03-15 +3.55 S
9959 2014-03-14 -32.00 S

Es ist ein einziges „X“ in unserem Event-Datenstrom. Genau an der Stelle, an der wir es erwartet haben: In der dritten Wiederholung des Events (gleiches Vorzeichen), in einer Abfolge der Länge > 3.

Boom!

Da uns die „S“- und „R“-Werte eigentlich egal sind, sollten wir sie in diesem Sinne entfernen:

SELECT
  id, value_date, amount,
  CASE trg WHEN 'X' THEN 'X' END trg
FROM series
MATCH_RECOGNIZE (
  ORDER BY id
  MEASURES classifier() AS trg
  ALL ROWS PER MATCH
  PATTERN (S (R X R+)?)
  DEFINE
    R AS sign(R.amount) = prev(sign(R.amount)),
    X AS sign(X.amount) = prev(sign(X.amount))
) 

Damit erhalten wir:

ID VALUE_DATE AMOUNT TRG
9997 2014-03-18 +99.17
9981 2014-03-16 -71.44
9979 2014-03-16 -94.60 X
9977 2014-03-16 -6.96
9971 2014-03-15 -65.95
9964 2014-03-15 +15.13
9962 2014-03-15 +17.47
9960 2014-03-15 +3.55
9959 2014-03-14 -32.00

Danke, Oracle!

shut.up.and.take.my.money

Erwarten Sie bitte auch an dieser Stelle nicht, dass ich es besser erkläre, als die exzellente Oracle Dokumentation es bereits getan hat. Ich empfehle das Lesen der Dokumentation sehr, sofern Sie Oracle 12c denn nutzen:
http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/1965433.pdf

9. Pivoting und Unpivoting

Wenn Sie bereits bis hier gelesen haben, wird das folgende beinahe beschämend einfach:

Hier sind unsere Daten, d.h. Schauspieler (NAME), Filmtitel (TITLES) und Bewertungen (RATINGS):

NAME TITLE RATING
A. GRANT ANNIE IDENTITY G
A. GRANT DISCIPLE MOTHER PG
A. GRANT GLORY TRACY PG-13
A. HUDSON LEGEND JEDI PG
A. CRONYN IRON MOON PG
A. CRONYN LADY STAGE PG
B. WALKEN SIEGE MADRE R

Das folgende bedeutet „Pivoting“:

NAME NC-17 PG G PG-13 R
A. GRANT 3 6 5 3 1
A. HUDSON 12 4 7 9 2
A. CRONYN 6 9 2 6 4
B. WALKEN 8 8 4 7 3
B. WILLIS 5 5 14 3 6
C. DENCH 6 4 5 4 5
C. NEESON 3 8 4 7 3

Sehen Sie wie wir die Schauspieler quasi gruppiert (GROUP BY) haben und danach die Anzahl ihrer Filme anhand der Bewertung durchgegangen sind? Anstatt alles auf „relationale“ Art (d.h. jede Gruppierung hätte ihre eigene Zeile) darzustellen, sind wir die gesamten Daten durchgegangen („pivoting“) um es in einer Spalte pro Gruppierung anzuzeigen. Wir können dies tun, da wir alle möglichen Gruppierungen im Voraus kennen.

„Unpivoting“ ist das genaue Gegenteil; wenn wir vom gerade genannten Beispiel zurück zu „einer Zeile pro Gruppierung“-Anzeige wollen:

NAME RATING COUNT
A. GRANT NC-17 3
A. GRANT PG 6
A. GRANT G 5
A. GRANT PG-13 3
A. GRANT R 6
A. HUDSON NC-17 12
A. HUDSON PG 4

Tatsächlich ist es wirklich „Easy“. So wird es in PostgreSQL gemacht:

SELECT
  first_name, last_name,
  count(*) FILTER (WHERE rating = 'NC-17') AS "NC-17",
  count(*) FILTER (WHERE rating = 'PG'   ) AS "PG",
  count(*) FILTER (WHERE rating = 'G'    ) AS "G",
  count(*) FILTER (WHERE rating = 'PG-13') AS "PG-13",
  count(*) FILTER (WHERE rating = 'R'    ) AS "R"
FROM actor AS a
JOIN film_actor AS fa USING (actor_id)
JOIN film AS f USING (film_id)
GROUP BY actor_id

Wir können eine einfache FILTER-Klausel zu einer aggregierten Funktion hinzufügen, um nur einen Teil der Daten zu zählen.

In allen anderen Datenbanken sieht das Query folgendermaßen aus:

SELECT
  first_name, last_name,
  count(CASE rating WHEN 'NC-17' THEN 1 END) AS "NC-17",
  count(CASE rating WHEN 'PG'    THEN 1 END) AS "PG",
  count(CASE rating WHEN 'G'     THEN 1 END) AS "G",
  count(CASE rating WHEN 'PG-13' THEN 1 END) AS "PG-13",
  count(CASE rating WHEN 'R'     THEN 1 END) AS "R"
FROM actor AS a
JOIN film_actor AS fa USING (actor_id)
JOIN film AS f USING (film_id)
GROUP BY actor_id

Das schöne ist, dass aggregierte Funktionen für gewöhnlich nur Nicht-NULL-Werte berücksichtigen. Wenn wir demnach alle uninteressanten Werte per Aggregation auf NULL setzen erhalten wir das gleiche Ergebnis.

Sollten Sie entweder den SQL-Server oder Oracle nutzen, können sie ebenso die eingebaute PIVOT- oder UNPIVOT-Klausel benutzen. Wie bei MODEL oder MATCH_RECOGNIZE muss dafür das neue Keyword schlicht nach der Tabellenerstellung gesetzt werden, um das gleiche Ergebnis zu erhalten:

-- PIVOTING
SELECT something, something
FROM some_table
PIVOT (
  count(*) FOR rating IN (
    'NC-17' AS "NC-17",
    'PG'    AS "PG",
    'G'     AS "G",
    'PG-13' AS "PG-13",
    'R'     AS "R"
  )
)

-- UNPIVOTING
SELECT something, something
FROM some_table
UNPIVOT (
  count    FOR rating IN (
    "NC-17" AS 'NC-17',
    "PG"    AS 'PG',
    "G"     AS 'G',
    "PG-13" AS 'PG-13',
    "R"     AS 'R'
  )
)

„Easy“. Weiter geht’s.

10. XML und JSON abusen

Als Erstes:

abusing.xml.and.json.1

JSON ist nur XML mit weniger Features und weniger Syntax

Jeder weiß, dass XML super ist. Die Konsequenz ist jedoch:

JSON ist weniger super.

Benutzen sie kein JSON.

Da dies nun geklärt ist, können wir sicher den aktuellen JSON-Datenbank-Hype (den die meisten in fünf Jahren ohnehin bereuen werden) ignorieren und zum letzten Beispiel übergehen: Wie man XML in der Datenbank nutzt.

abusing.xml

Vom orginalen XML-Dokument ausgehend, lesen wir zuerst ein, um danach die Komma-sortierte Liste von Filmen pro Schauspieler aufspalten und eine denormalisierte Repräsentation von Schauspielern / Filmen in einer einzelnen Beziehung erstellen.

Achtung, fertig, los! Das ist die Idee. Wir haben drei CTEs:

WITH RECURSIVE
  x(v) AS (SELECT '...'::xml),
  actors(
    actor_id, first_name, last_name, films
  ) AS (...),
  films(
    actor_id, first_name, last_name,
    film_id, film
  ) AS (...)
SELECT *
FROM films

Im ersten Schritt lesen wir das XML schlicht ein. Hier mit PostgreSQL:

WITH RECURSIVE
  x(v) AS (SELECT '
<actors>
  <actor>
    <first-name>Bud</first-name>
    <last-name>Spencer</last-name>
    <films>God Forgives... I Don’t, Double Trouble, They Call Him Bulldozer</films>
  </actor>
  <actor>
    <first-name>Terence</first-name>
    <last-name>Hill</last-name>
    <films>God Forgives... I Don’t, Double Trouble, Lucky Luke</films>
  </actor>
</actors>'::xml),
  actors(actor_id, first_name, last_name, films) AS (...),
  films(actor_id, first_name, last_name, film_id, film) AS (...)
SELECT *
FROM films

„Easy“.

Als nächstes zaubern wir etwas mit XPath und extrahieren die einzelnen Werte der XML-Struktur und kopieren sie in Spalten:

WITH RECURSIVE
  x(v) AS (SELECT '...'::xml),
  actors(actor_id, first_name, last_name, films) AS (
    SELECT
      row_number() OVER (),
      (xpath('//first-name/text()', t.v))[1]::TEXT,
      (xpath('//last-name/text()' , t.v))[1]::TEXT,
      (xpath('//films/text()'     , t.v))[1]::TEXT
    FROM unnest(xpath('//actor', (SELECT v FROM x))) t(v)
  ),
  films(actor_id, first_name, last_name, film_id, film) AS (...)
SELECT *
FROM films

Immernoch „Easy“.

Zum Schluss nur noch etwas rekursive RegEx-Pattern-Sortierungsmagie und wir sind fertig!

WITH RECURSIVE
  x(v) AS (SELECT '...'::xml),
  actors(actor_id, first_name, last_name, films) AS (...),
  films(actor_id, first_name, last_name, film_id, film) AS (
    SELECT actor_id, first_name, last_name, 1,
      regexp_replace(films, ',.+', '')
    FROM actors
    UNION ALL
    SELECT actor_id, a.first_name, a.last_name, f.film_id + 1,
      regexp_replace(a.films, '.*' || f.film || ', ?(.*?)(,.+)?', '\1')
    FROM films AS f
    JOIN actors AS a USING (actor_id)
    WHERE a.films NOT LIKE '%' || f.film
  )
SELECT *
FROM films

Kommen wir zum Fazit.

drunk.genius

Schlussfolgerung

Alles, was dieser Artikel gezeigt hat, war deklarativ. Und ziemlich einfach. Natürlich wurde für den Spaß, den ich mit diesem Vortrag ebenfalls haben möchte, etwas überspitztes SQL gewählt und ich habe ausdrücklich alles „Easy“ genannt. Ohne die nötige Übung in SQL ist es allerdings alles andere als „Easy“. Wie viele andere Sprachen auch, muss geübt werden, aber es mit SQL ist es ein bisschen schwerer, denn:

  • Die Syntax ist von Zeit zu Zeit etwas ungelenk.
  • Deklaratives Denken ist nicht einfach. Zumindest ist es sehr „anders“.

Sobald man allerdings den Dreh raus hat, lohnt sich deklarative Programmierung mit SQL sehr, da man komplexe Beziehungen zwischen den Daten in sehr wenig Code und mit einer schlichten Beschreibung des gewünschten Datenbankergebnisses ausdrücken kann.

Ist das nicht super?

Und wenn das ein bisschen übertrieben war, dann möchte ich an dieser Stelle anmerken, dass ich gerne Ihre JUG / Konferenz besuchen werde, um diesen Vortrag zu halten. (Kontaktieren sie uns). Zudem, falls Sie diesen Themen wirklich tiefgehend nachgehen wollen, bieten wir ebenfalls öffentliche Vorträge und in-house Workshops an. Setzen sie sich mit uns in Verbindung – wir warten darauf.

Hier noch einmal die gesammelten Folien auf Slideshare.

 

Aufmacherbild: SQL – Structured Query Language von Shutterstock / Urheberrecht: dizain

Geschrieben von
Lukas Eder
Lukas Eder
Lukas Eder ist leidenschaftlicher Java- und SQL-Entwickler. Er ist Gründer und Leiter der Forschungs- und Entwicklungsabteilung der Data Geekery GmbH, dem Unternehmen hinter jOOQ - die einfachste Art, um SQL in Java zu schreiben.
Kommentare
  1. Steven2016-06-13 15:01:59

    Schöner Artikel. Danke sehr. :)

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.