Lightning Talk

SQL Window-Functions

Von Bernhard F. Häussner bei den Sophisticates am 2014-06-06

SQL-Datenbanken?

  • Persistenz von Anwendungsdaten
  • Einhaltung von ACID
  • Benutzung via ORM
  • Meherere Benutzer, Verteilte Systeme...

SQL-Datenbanken!

  • Persistenz Analyse
  • Anwendung Endbenutzer
  • ORM SQL

→ SQL als deklarative Programmiersprache

Beispiel: Sparbuch

Sparbuch

Modellierung als Relation

SELECT t.* FROM transactions t;
id account_id value_date amount
1 2 2014-06-02 €20,00
2 1 2014-06-02 €100,00
3 3 2014-06-03 -€234,56
4 1 2014-06-04 -€3,96
5 1 2014-06-04 -€18,65
6 3 2014-06-04 €100,00
7 2 2014-06-05 -€12,34
8 1 2014-06-05 €10,00

Einmal Sortieren, bitte!

SELECT t.* FROM transactions t ORDER BY t.account_id, t.value_date, t.id;
id account_id value_date amount
2 1 2014-06-02 €100,00
4 1 2014-06-04 -€3,96
5 1 2014-06-04 -€18,65
8 1 2014-06-05 €10,00
1 2 2014-06-02 €20,00
7 2 2014-06-05 -€12,34
3 3 2014-06-03 -€234,56
6 3 2014-06-04 €100,00

Aktueller Kontostand...?

SELECT t.*, balance??? FROM transactions t ORDER BY t.account_id...
id account_id value_date amount balance
2 1 2014-06-02 €100,00 €100,00
4 1 2014-06-04 -€3,96 €96,04
5 1 2014-06-04 -€18,65 €77,39
8 1 2014-06-05 €10,00 €87,39
1 2 2014-06-02 €20,00 €20,00
7 2 2014-06-05 -€12,34 €7,66
3 3 2014-06-03 -€234,56 -€234,56
6 3 2014-06-04 €100,00 -€134,56

Für eine Spalte

id account_id value_date amount balance
4 1 2014-06-04 -€3,96 €96,04

Was muss berechnet werden?

€96,04 = Summe aller Transaktionen dieses Kontos bis zum 2014-06-04 *.

*) Und, wenn am selben Tag, nicht eine größere ID als 4

Summen sind leicht

SELECT SUM(u.amount) as balance FROM transactions u
 WHERE u.account_id = 1;
balance
€87,39

Aktueller Kontostand von Konto 1.

Wir sind hier:

SELECT t.*, balance??? FROM transactions t ORDER BY t.account_id...
id account_id value_date amount balance
2 1 2014-06-02 €100,00 €100,00
4 1 2014-06-04 -€3,96 €96,04
5 1 2014-06-04 -€18,65 €77,39
8 1 2014-06-05 €10,00 €87,39
1 2 2014-06-02 €20,00 €20,00
7 2 2014-06-05 -€12,34 €7,66
3 3 2014-06-03 -€234,56 -€234,56
6 3 2014-06-04 €100,00 -€134,56

Weitere Bedingungen


SELECT SUM(u.amount) as balance FROM transactions u
 WHERE u.account_id = 1 AND u.value_date <= '2014-06-04';
balance
€77,39

Kontostand am Ende des Tages

Knapp daneben ist auch vorbei:

SELECT t.*, balance??? FROM transactions t ORDER BY t.account_id...
id account_id value_date amount balance
2 1 2014-06-02 €100,00 €100,00
4 1 2014-06-04 -€3,96 €96,04
5 1 2014-06-04 -€18,65 €77,39
8 1 2014-06-05 €10,00 €87,39
1 2 2014-06-02 €20,00 €20,00
7 2 2014-06-05 -€12,34 €7,66
3 3 2014-06-03 -€234,56 -€234,56
6 3 2014-06-04 €100,00 -€134,56

Alle Bedingungen

id account_id value_date amount balance
4 1 2014-06-04 -€3,96 €96,04

SELECT SUM(u.amount) as balance FROM transactions u
 WHERE u.account_id = 1
 AND (u.value_date < '2014-06-04'
  OR (u.value_date = '2014-06-04' AND u.id <= 4));
balance
€96,04

Der richtige Wert!

Kleine Vereinfachung


SELECT SUM(u.amount) as balance FROM transactions u
 WHERE u.account_id = 1
   AND (u.value_date, u.id)
    <= ('2014-06-04', 4);
balance
€96,04

Row-Value-Expression-Predicates machen uns das Leben leichter.

Für alle Spalten

Füge Abfrage für eine Spalte als Subquery in die ursprüngliche Abfrage ein:


   SELECT t.*
        , (SELECT SUM(u.amount)
             FROM transactions u
            WHERE u.account_id = t.account_id
              AND (u.value_date, u.id)
               <= (t.value_date, t.id)
             ) AS balance
     FROM transactions  t
 ORDER BY t.account_id
        , t.value_date
        , t.id
        ;

Problem: Laufzeit

Schon bei 500k Transaktionen braucht Abfrage „ewig“.

Quadratische Laufzeit statt linear:

500k * 500k = 250G

Lösung: Window-Functions!

Im Prinzip wollen wir schreiben:


   SELECT t.*
        , SUM(t.amount) AS balance
     FROM transactions  t
 ORDER BY t.account_id
        , t.value_date
        , t.id
        ;


FEHLER: Spalte »t.id« muss in der GROUP-BY-Klausel erscheinen oder in einer Aggregatfunktion verwendet werden

Hier kommt OVER!

Funktionsweise mit Partition:


   SELECT t.*
        , SUM(t.amount) OVER
              ( PARTITION BY t.account_id)
                          AS balance
     FROM transactions  t
 ORDER BY t.account_id
        , t.value_date
        , t.id
        ;


id account_id value_date amount balance
2 1 2014-06-02 €100,00 €87,39
4 1 2014-06-04 -€3,96 €87,39
5 1 2014-06-04 -€18,65 €87,39
8 1 2014-06-05 €10,00 €87,39
1 2 2014-06-02 €20,00 €7,66
7 2 2014-06-05 -€12,34 €7,66
3 3 2014-06-03 -€234,56 -€134,56
6 3 2014-06-04 €100,00 -€134,56

Hier kommt OVER!

Funktionsweise mit Reihenfolge:


  SELECT t.*
        , SUM(t.amount) OVER
                  ( ORDER BY t.account_id, t.value_date, t.id)
                          AS balance
     FROM transactions  t
 ORDER BY t.account_id
        , t.value_date
        , t.id
        ;


id account_id value_date amount balance
2 1 2014-06-02 €100,00 €100,00
4 1 2014-06-04 -€3,96 €96,04
5 1 2014-06-04 -€18,65 €77,39
8 1 2014-06-05 €10,00 €87,39
1 2 2014-06-02 €20,00 €107,39
7 2 2014-06-05 -€12,34 €95,05
3 3 2014-06-03 -€234,56 -€139,51
6 3 2014-06-04 €100,00 -€39,51

Hier kommt OVER!

Beides zusammen:


   SELECT t.*
        , SUM(t.amount) OVER
              ( PARTITION BY t.account_id
                    ORDER BY t.value_date
                           , t.id
                           )
                          AS balance
     FROM transactions  t
 ORDER BY t.account_id
        , t.value_date
        , t.id
        ;

Hier kommt OVER!

Bonus: RANGE


   SELECT t.*
        , SUM(t.amount) OVER
              ( PARTITION BY t.account_id
                    ORDER BY t.value_date
                           , t.id
               RANGE BETWEEN unbounded preceding
                         AND current row
                           )
                          AS balance
     FROM transactions  t
 ORDER BY t.account_id
        , t.value_date
        , t.id
        ;

Window-Functions

  • Laufzeit ist akzepzabel.
  • Anfrage wurde vereinfacht.
  • Deklarative Programmierung!



Blogeintrag mit Code und weiteren Beispielen:
http://1-co.de/b/1v