Bernhard Häussner

SQL Window-Functions

29.05.2014, 12:27
Ein englischsprachiger Text über Window-Functions hinter LEGO-Fenstern

Ein englischsprachiger Text über Window-Functions hinter LEGO-Fenstern

Vor kurzem bin ich über die Window-Functions gestolpert, eine eher selten verwendete Funktionalität der Abfragesprache SQL. Man kann damit die vorherigen und folgenden Zeilen einer Zeile eines Datenbank-Abfrageergebnisses mit einbeziehen und Aggregatfunktionen auf einen Teil des Ergebnisses anwenden. Da ich sie bisher nicht kannte, habe ich sogleich etwas damit herum gespielt. Dabei sind zwei beispielhafte Anwendungen entstanden. Und weil ich denke, dass viele noch nicht von den Window-Functions gehört haben, werde ich sie im folgenden vorstellen.

Was sind die Window-Functions?

Window-Functions haben einige für SQL eher ungewöhnliche Eigenschaften:

  • Sie wenden eine Aggregatfunktion an, ohne dabei Zeilen im Ergebnis zu gruppieren. Hat man z.B. SUM(foo) OVER () in der Feldliste der Abfrage, erhält man in jeder Ergebniszeile die Summe der foos aller Zeilen.
  • Sie können auf weitere Zeilen relativ zur Position der aktuellen Zeile zugreifen. So gibt etwa LEAD(foo, 3) OVER(ORDER BY id) den Wert von foo drei Zeilen nach der aktuellen Zeile bei Sortierung nach id aus.
  • Sie können Aggregatfunktionen auf Bereiche einschränken. Hat man z.B. SUM(foo) OVER (ORDER BY id) in der Feldliste der Abfrage, erhält man in jeder Ergebniszeile die Summe der foos bis zur aktuellen Zeile.
  • Für die Angabe des Bereichs nach OVER können auch Bereiche von Zeilen oder Partitionen angegeben werden.
  • Window-Functions werden von den WHERE-Bedingungen der Abfrage beeinflusst.
  • Zusätzlich zu Aggregatfunktionen gibt es noch eine Liste spezieller Window-Functions, die nur in diesem Kontext Sinn machen.

An zwei Beispielen zu typischen Anwendungsfällen aus dem Bereich Finanzen und aus dem Bereich Biologie kann die genaue Funktionsweise besser nachvollzogen werden. Wer auch experimentieren will findet hier ein Gist mit lauffähigem Beispielcode für PostgreSQL und Beispieldaten oder ein SQL-Fiddle.

Beispiel Finanzwesen: Kumulative Summe

Das erste Beispiel soll die kumulative Summe sein. In alten Sparbüchern findet sich eine Liste von Kontobewegungen und am Ende stehen zwei Spalten: Eine für den Betrag der Transaktion, also die Veränderung des Kontostandes durch diese Kontobewegung, und eine für den neuen Kontostand nach der Transaktion, also die Summer der Beträge der bisherigen Kontobewegungen. Das wiederholte Aufführen der Summe stammt noch aus vor-digitaler Zeit, um das Aufsummieren zu erleichtern. Die Summen stellen allerdings eine Redundante Information dar, da sie leicht aus den Veränderungen berechnet werden können. In SQL würde man die Transaktionstabelle also z.B. so modellieren:

CREATE TABLE transactions
  ( id         SERIAL primary key
  , account_id INT    not null references accounts(id)
  , value_date DATE   not null
  , amount     MONEY
  )
  ;

Hier steht value_date für den Buchungstag und amount für die vorzeichenbehaftete Veränderung. Die Transaktionen aller Konten werden in einer Tabelle gespeichert, daher die account_id.

Sollen beispielsweise alle Kontostände angezeigt werden, hilft eine einfache Abfrage:

   SELECT t.account_id
        , SUM(t.amount) balance
     FROM transactions  t
 GROUP BY t.account_id
        ;
 account_id | balance  
------------+----------
          1 |   €87,39
          3 | -€134,56
          2 |    €7,66

Hier ist noch nichts überraschendes, nur eine ganz normale Aggregation der Daten. Jetzt soll ein Kontoauszug erstellt werden, bei dem in der letzten Spalte die jeweils der Kontostand nach jeder Transaktion angezeigt wird:

 id | account_id | value_date |  amount  | balance  
----+------------+------------+----------+----------
  1 |          1 | 2014-04-26 |  €100,00 |  €100,00
  2 |          1 | 2014-04-28 |   -€3,96 |   €96,04
  3 |          1 | 2014-04-28 |  -€18,65 |   €77,39
  4 |          1 | 2014-04-29 |   €10,00 |   €87,39
  5 |          2 | 2014-04-26 |   €20,00 |   €20,00
  6 |          2 | 2014-04-29 |  -€12,34 |    €7,66
  8 |          3 | 2014-04-27 | -€234,56 | -€234,56
  7 |          3 | 2014-04-28 |  €100,00 | -€134,56

Die Transaktionen sind jeweils nach Konto und Valuta sortiert. Der naive Ansatz, eine solche Tabelle zu erstellen, wäre es, ein Subquery für jede Zeile auszuführen, in dem die Summe bestimmt wird:

   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
        ;

Dies ist die einfachste und naheliegendste Lösung. Doch bei etwas größeren Datenmengen zeigen sich schnell Performance-Probleme. Das Subquery muss für jeden Datensatz einzeln ausgeführt werden, wo jedesmal alle Datensätze aufs neue aufsummiert werden, was zu einer O(n²)-Laufzeit führt.

Theoretisch müssten wir aber nur den aktuellen Wert von amount auf die balance der vorherigen Zeile summieren. Um das der Datenbank-Engine beizubringen, kommt eine Window-Function ins Spiel:

   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
        ;

Anstatt eines Subqueries wird nun direkt die Aggregatfunktion SUM als Window-Function verwendet. Dazu wird sie eingeschränkt. Das PARTITION übernimmt die Aufgabe von WHERE und filtert die Datensätze von anderen Konten heraus. Mit RANGE gibt man die gewünschten relativen Zeilenkoordinaten an, hier von unbounded preceding (vom Anfang) bis current row (zur aktuellen Spalte).

Mit dieser Optimierung kann auf das Subquery verzichtet werden und die Lauzeit wird akzeptabel.

Beispiel Biologie: Gleitender Mittelwert

Das zweite Beispiel kommt aus der Bioinformatik. Bei der Analyse von Proteinen betrachtet man Bereiche mit kleinerer und größerer Hydophobizität. Dazu werden die einzelnen Aminosäuren der Kette einem Hydophobizitätswert zugeordnet. Betrachtet man nun die Reihe dieser Werte je in Isolation ergibt sich ein starkes Rauschen. Daher bildet man für jede Position mit den umgebenden Positionen den Mittelwert, ein guter Anwendungsfall für eine Window-Function.

Ich habe hier kurzerhand die Analyse vom Aquaporin-4 Isoform A, die ich schon vorher in R implementiert hatte, neu in SQL implementiert. Natürlich wird es in der Praxis meistens einfacher sein, einfach in R die entsprechenden Daten zu analysieren, gerade weil Plots auch viel leichter erstellt werden können. Für die Eingabedaten habe ich diese zwei Tabellen verwendet:

CREATE TABLE amino_acids
  ( letter         CHAR  primary key
  , name           VARCHAR(40) not null
  , hydrophobicity REAL
  )
  ;
 
CREATE TABLE aqp4
  ( id         SERIAL primary key
  , nucleotide CHAR   not null references amino_acids(letter)
  )
  ;

Die Tabelle amino_acids enthält die Hydophobizitätswerte der einzelnen Aminosäuren nach der Kyte and Doolittle scale, aqp4 die Abfolge der Aminosäuren im Aquaporin. Nun sollen jeweils mittels einer Window-Function die gleitenden Mittelwerte berechnet werden.

   SELECT amino_acids.*
        , AVG(hydrophobicity)
     OVER (ROWS BETWEEN 5 preceding AND 5 following)
     FROM aqp4
LEFT JOIN amino_acids
       ON amino_acids.letter = aqp4.nucleotide

Da nur ein Protein in der Tabelle gespeichert ist, wird dieses Mal keine Partitionierung benötigt. Interessant ist die Einschränkung der Zeilen mit den Zahlenangaben: Mit OVER (ROWS BETWEEN 5 preceding AND 5 following) werden die fünf vorhergehenden und die fünf nachfolgenden Zeilen in die AVG-Funktion einbezogen.

Mit dem SQL-Code erhält man tatsächlich die selben Werte wie mit der Referenzimplementierung in R:

 letter |     name     | hydrophobicity |         avg          
--------+--------------+----------------+----------------------
 M      | Methionin    |            1.9 |    -1.53333334128062
 S      | Serin        |           -0.8 |    -1.05714287076678
 D      | Aspartat     |           -3.5 |    -1.48750001192093
 -- [...]

Durch die Verwendung einer Window-Function konnte die Einschränkung der Mittelwert-Funktion auf die umliegenden Zeilen sehr elegant ausgedrückt werden.

Zusammenfassung

Die Window-Functions erweitern den Einsatzbereich von SQL-Analysen erheblich um Berechnungen, die sonst so einfach nicht programmiert werden könnten. Wer die genaue Dokumentation nachlesen will, wird beim PostgreSQL-Tutorial zu Window-Functions fündig, im letzen Abschnitt dort finden sich auch die Links zu den entsprechenden Kapiteln im Handbuch. Auf die Window-Functions gekommen bin ich durch einen jOOQ-Artikel zum Thema „Calculate Running Totals“, in diesem Blog finden sich auch Texte zu anderen eher exotischen SQL-Funktionalitäten. Außerdem finden sich hier die Folien zum Vortrag über Window-Functions, den ich am 6. Juni 2014 bei den Sophisticates gehalten habe.

Kurze URL http://1-co.de/b/1v. Post to twitter

Kommentare

keine





 
Χρόνογραφ
© 2008-2017 by Bernhard Häussner - Impressum - Login
Kurz-Link zu dieser Seite: http://1-co.de/b/1v