Fensterfunktion (SQL)

Eine Fensterfunktion (englisch Window Function) in SQL ist eine analytische Funktion^[1], die Werte aus einem oder mehreren Tupeln verwendet, um einen Wert pro Tupel zurückzugeben. (Dies steht im Gegensatz zu einer Aggregatfunktion, die einen einzigen Wert für mehrere Tupel zurückgibt.) Fensterfunktionen haben eine OVER-Klausel; jede Funktion ohne OVER-Klausel ist keine Fensterfunktion, sondern eine Aggregat- oder einzeilige (skalare) Funktion.^[2]

Als Beispiel ist hier eine Anfrage angegeben, die eine Fensterfunktion verwendet, um das Gehalt jedes Mitarbeiters mit dem Durchschnittsgehalt seiner Abteilung zu vergleichen (Beispiel aus der PostgreSQL-Dokumentation):^[3]

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

Ausgabe:

 depname  | empno | salary |          avg
----------+-------+--------+----------------------
develop   |    11 |   5200 | 5020.0000000000000000
develop   |     7 |   4200 | 5020.0000000000000000
develop   |     9 |   4500 | 5020.0000000000000000
develop   |     8 |   6000 | 5020.0000000000000000
develop   |    10 |   5200 | 5020.0000000000000000
personnel |     5 |   3500 | 3700.0000000000000000
personnel |     2 |   3900 | 3700.0000000000000000
sales     |     3 |   4800 | 4866.6666666666666667
sales     |     1 |   5000 | 4866.6666666666666667
sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

Die PARTITION BY-Klausel gruppiert Tupel in Partitionen, innerhalb derer die Funktion angewendet wird. Fehlt die PARTITION BY-Klausel (z. B. bei einer leeren OVER()-Klausel), wird die gesamte Ergebnismenge als eine einzige Partition behandelt. Bei dieser Abfrage wäre das angegebene Durchschnittsgehalt der Durchschnitt über alle Zeilen.

Fensterfunktionen werden nach der Aggregation ausgewertet, also nach der GROUP BY-Klausel mit zugehörigen Aggregatfunktionen.^[4]

Laut der PostgreSQL-Dokumentation hat eine Window-Funktion die folgende Syntax:^[5]

function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )

wobei window_definition die Syntax hat:

[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]

frame_clause entspricht einer der folgenden Syntax:

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]

frame_start und frame_end sind entweder UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, oder UNBOUNDED FOLLOWING. frame_exclusion können sein: EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, oder EXCLUDE NO OTHERS.

expression bezieht sich auf Ausdrücke die keinen Aufruf einer Fensterfunktion enthalten.

• Eckige Klammern [] geben optionale Bestandteile an
• Geschweifte Klammern {} geben Auswahl möglicher Optionen an, wobei jede Option durch einen vertikalen Balken abgegrenzt ist |

Fensterfunktionen ermöglichen den Zugriff auf Daten direkt vor und nach dem aktuellen Tupel.^[6][7][8][9] Eine Fensterfunktion definiert ein Fenster (engl.: Window) von Tupeln mit einer bestimmten Anzahl vor und nach der aktuellen Zeile und führt eine Berechnung über den Tupeln im jeweils gültigen Fenster durch.^[10][11]

      NAME |
------------
      Aaron| <-- Preceding (unbounded)
     Andrew|
     Amelia|
      James|
       Jill|
     Johnny| <-- 1st preceding row
    Michael| <-- Current row
       Nick| <-- 1st following row
    Ophelia|
       Zach| <-- Following (unbounded)

Die nachfolgende Anfrage extrahiert für jedes Tupel in der obigen Tabelle die Werte einer vorangehenden und einer nachfolgenden Zeile:

 SELECT
  LAG(name, 1)
    OVER(ORDER BY name) "prev",
  name,
  LEAD(name, 1)
    OVER(ORDER BY name) "next"
 FROM people
 ORDER BY name

Das Ergebnis enthält die folgenden Werte:

|     PREV |     NAME |     NEXT |
|----------|----------|----------|
|    (null)|     Aaron|    Andrew|
|     Aaron|    Andrew|    Amelia|
|    Andrew|    Amelia|     James|
|    Amelia|     James|      Jill|
|     James|      Jill|    Johnny|
|      Jill|    Johnny|   Michael|
|    Johnny|   Michael|      Nick|
|   Michael|      Nick|   Ophelia|
|      Nick|   Ophelia|      Zach|
|   Ophelia|      Zach|    (null)|

Fensterfunktionen wurden in SQL:2003 eingeführt und deren Funktionalität in späteren Spezifikationen erweitert.^[12]

1. ↑ Analytic function concepts in Standard SQL | BigQuery. In: Google Cloud. Abgerufen am 23. März 2021 (englisch). 
2. ↑ Window Functions. In: sqlite.org. Abgerufen am 23. März 2021. 
3. ↑ 3.5. Window Functions. In: PostgreSQL Documentation. 11. Februar 2021, abgerufen am 23. März 2021 (englisch). 
4. ↑ Analytic function concepts in Standard SQL | BigQuery. In: Google Cloud. Abgerufen am 23. März 2021 (englisch). 
5. ↑ 4.2. Value Expressions. In: PostgreSQL Documentation. 11. Februar 2021, abgerufen am 23. März 2021 (englisch). 
6. ↑ Efficient processing of window functions in analytical SQL queries. In: Proc. VLDB Endow. ISSN 2150-8097, doi:10.14778/2794367.2794375. 
7. ↑ Optimization of analytic window functions. In: Proc. VLDB Endow. ISSN 2150-8097, doi:10.14778/2350229.2350243, arxiv:1208.0086. 
8. ↑ Probably the Coolest SQL Feature: Window Functions In: Java, SQL and jOOQ., 3. November 2013. Abgerufen am 26. September 2017 (amerikanisches Englisch). 
9. ↑ Window Functions in SQL - Simple Talk In: Simple Talk, 31. Oktober 2013. Abgerufen am 26. September 2017 (amerikanisches Englisch). 
10. ↑ SQL Window Functions Introduction. In: Apache Drill. Abgerufen im 1. Januar 1 
11. ↑ PostgreSQL: Documentation: Window Functions. In: www.postgresql.org. Abgerufen am 4. April 2020 (englisch). 
12. ↑ Window Functions Overview. In: MariaDB KnowledgeBase. Abgerufen am 23. März 2021.