Zum Inhalt springen

„Fensterfunktion (SQL)“ – Versionsunterschied

aus Wikipedia, der freien Enzyklopädie
Versionsgeschichte interaktiv durchsuchen
[ungesichtete Version][gesichtete Version]
← Zum vorherigen VersionsunterschiedZum nächsten Versionsunterschied →
Inhalt gelöscht Inhalt hinzugefügt
VisuellWikitext
Grammar
Erstellt durch Übersetzen der Seite „Window function (SQL)“
Zeile 1: Zeile 1:
{{Short description|Function over multiple rows in SQL}}


In [[SQL]], a '''window function''' or '''analytic function'''<ref name=":1">{{Cite web|title=Analytic function concepts in Standard SQL {{!}} BigQuery|url=https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts|access-date=2021-03-23|website=Google Cloud|language=en}}</ref> is a function which uses values from one or multiple [[Row (database)|rows]] to return a value for each row. (This contrasts with an [[aggregate function]], which returns a single value for multiple rows.) Window functions have an OVER clause; any function without an OVER clause is not a window function, but rather an aggregate or single-row (scalar) function.<ref>{{Cite web|title=Window Functions|url=https://sqlite.org/windowfunctions.html|access-date=2021-03-23|website=sqlite.org}}</ref>
Eine '''Fensterfunktion''' (engl.: ''Window Function'') in [[SQL]] ist eine analytische Funktion<ref name=":12">{{Cite web|url=https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts|title=Analytic function concepts in Standard SQL {{!}} BigQuery|website=Google Cloud|language=en|access-date=2021-03-23}}</ref>, 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.<ref>{{Cite web|url=https://sqlite.org/windowfunctions.html|title=Window Functions|website=sqlite.org|access-date=2021-03-23}}</ref>


As an example, here is a query which uses a window function to compare the salary of each employee with the average salary of their department (example from the [[PostgreSQL]] documentation):<ref>{{Cite web|date=2021-02-11|title=3.5. Window Functions|url=https://www.postgresql.org/docs/13/tutorial-window.html|access-date=2021-03-23|website=PostgreSQL Documentation|language=en}}</ref><syntaxhighlight lang="psql">
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):<ref>{{Cite web|url=https://www.postgresql.org/docs/13/tutorial-window.html|title=3.5. Window Functions|date=2021-02-11|website=PostgreSQL Documentation|language=en|access-date=2021-03-23}}</ref><syntaxhighlight lang="psql">
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
</syntaxhighlight>
</syntaxhighlight>Ausgabe:
Output:
depname | empno | salary | avg
depname | empno | salary | avg
----------+-------+--------+----------------------
----------+-------+--------+----------------------
Zeile 20: Zeile 18:
sales | 4 | 4800 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
(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.
The <code>PARTITION BY</code> clause groups rows into partitions, and the function is applied to each partition separately. If the <code>PARTITION BY</code> clause is omitted (such as if we have an empty <code>OVER()</code> clause), then the entire [[result set]] treated as a single partition.<ref name=":0">{{Cite web|date=2021-02-11|title=4.2. Value Expressions|url=https://www.postgresql.org/docs/13/sql-expressions.html|access-date=2021-03-23|website=PostgreSQL Documentation|language=en}}</ref> For this query, the average salary reported would be the average taken over all rows.


Fensterfunktionen werden nach der Aggregation ausgewertet, also nach der GROUP BY-Klausel mit zugehörigen Aggregatfunktionen.<ref name=":1">{{Cite web|url=https://cloud.google.com/bigquery/docs/reference/standard-sql/analytic-function-concepts|title=Analytic function concepts in Standard SQL {{!}} BigQuery|accessdate=2021-03-23|work=Google Cloud|language=en}}</ref>
Window functions are evaluated after aggregation (after the [[Group by (SQL)|<code>GROUP BY</code>]] clause and non-window aggregate functions, for example).<ref name=":1" />


== Syntax ==
== Syntax ==
Laut der PostgreSQL-Dokumentation hat eine Window-Funktion die folgende Syntax:<ref name=":0">{{Cite web|url=https://www.postgresql.org/docs/13/sql-expressions.html|title=4.2. Value Expressions|date=2021-02-11|accessdate=2021-03-23|work=PostgreSQL Documentation|language=en}}</ref><syntaxhighlight lang="psql">
According to the PostgreSQL documentation, a window function has the syntax of one of the following:<ref name=":0" /><syntaxhighlight lang="psql">
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
function_name ( * ) OVER ( window_definition )
</syntaxhighlight>where <code>window_definition</code> has syntax:<syntaxhighlight lang="psql">
</syntaxhighlight>wobei <code>window_definition</code> die Syntax hat:<syntaxhighlight lang="psql">
[ existing_window_name ]
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
[ frame_clause ]
</syntaxhighlight><code>frame_clause</code> has the syntax of one of the following:<syntaxhighlight lang="psql">
</syntaxhighlight><code>frame_clause</code> entspricht einer der folgenden Syntax:<syntaxhighlight lang="psql">
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
</syntaxhighlight><code>frame_start</code> and <code>frame_end</code> can be <code>UNBOUNDED PRECEDING</code>, <code>offset PRECEDING</code>, <code>CURRENT ROW</code>, <code>offset FOLLOWING</code>, or <code>UNBOUNDED FOLLOWING</code>. <code>frame_exclusion</code> can be <code>EXCLUDE CURRENT ROW</code>, <code>EXCLUDE GROUP</code>, <code>EXCLUDE TIES</code>, or <code>EXCLUDE NO OTHERS</code>.
</syntaxhighlight><code>frame_start</code> und <code>frame_end</code> sind entweder <code>UNBOUNDED PRECEDING</code>, <code>offset PRECEDING</code>, <code>CURRENT ROW</code>, <code>offset FOLLOWING</code>, oderr <code>UNBOUNDED FOLLOWING</code>. <code>frame_exclusion</code> können sein: <code>EXCLUDE CURRENT ROW</code>, <code>EXCLUDE GROUP</code>, <code>EXCLUDE TIES</code>, oder <code>EXCLUDE NO OTHERS</code>.


<code>expression</code> refers to any expression that does not contain a call to a window function.
<code>expression</code> bezieht sich auf Ausdrücke die keinen Aufruf einer Fensterfunktion enthalten.


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


⚫
== Beispiel ==
* Brackets [] indicate optional clauses
⚫
Fensterfunktionen ermöglichen den Zugriff auf Daten direkt vor und nach dem aktuellen Tupel.<ref>{{Cite journal|first=Viktor|journal=Proc. VLDB Endow.|doi=10.14778/2794367.2794375|issn=2150-8097}}</ref><ref>{{Cite journal|first=Yu|journal=Proc. VLDB Endow.|arxiv=1208.0086|doi=10.14778/2350229.2350243|issn=2150-8097}}</ref><ref>{{Cite news|title=Probably the Coolest SQL Feature: Window Functions|language=en-US|work=Java, SQL and jOOQ.|date=2013-11-03|accessdate=2017-09-26|url=https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/}}</ref><ref>{{Cite news|title=Window Functions in SQL - Simple Talk|language=en-US|work=Simple Talk|date=2013-10-31|accessdate=2017-09-26|url=https://www.red-gate.com/simple-talk/sql/t-sql-programming/window-functions-in-sql/}}</ref> 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.<ref>{{Cite web|url=https://drill.apache.org/docs/sql-window-functions-introduction/|title=SQL Window Functions Introduction|date=|accessdate=|last=|first=|work=Apache Drill|archiveurl=|archivedate=}}</ref><ref>{{Cite web|url=https://www.postgresql.org/docs/current/tutorial-window.html|title=PostgreSQL: Documentation: Window Functions|accessdate=2020-04-04|work=www.postgresql.org|language=en}}</ref>
* Curly braces {} indicate a set of different possible options, with each option delimited by a vertical bar |

⚫
== Example ==
⚫
Window functions allow access to data in the records right before and after the current record.<ref>{{Cite journal|last=Leis|first=Viktor|last2=Kundhikanjana|first2=Kan|last3=Kemper|first3=Alfons|last4=Neumann|first4=Thomas|date=June 2015|title=Efficient Processing of Window Functions in Analytical SQL Queries|journal=Proc. VLDB Endow.|volume=8|issue=10|pages=1058–1069|doi=10.14778/2794367.2794375|issn=2150-8097}}</ref><ref>{{Cite journal|last=Cao|first=Yu|last2=Chan|first2=Chee-Yong|last3=Li|first3=Jie|last4=Tan|first4=Kian-Lee|date=July 2012|title=Optimization of Analytic Window Functions|journal=Proc. VLDB Endow.|volume=5|issue=11|pages=1244–1255|arxiv=1208.0086|doi=10.14778/2350229.2350243|issn=2150-8097}}</ref><ref>{{Cite news|date=2013-11-03|title=Probably the Coolest SQL Feature: Window Functions|language=en-US|work=Java, SQL and jOOQ.|url=https://blog.jooq.org/2013/11/03/probably-the-coolest-sql-feature-window-functions/|access-date=2017-09-26}}</ref><ref>{{Cite news|date=2013-10-31|title=Window Functions in SQL - Simple Talk|language=en-US|work=Simple Talk|url=https://www.red-gate.com/simple-talk/sql/t-sql-programming/window-functions-in-sql/|access-date=2017-09-26}}</ref> A window function defines a ''frame'' or ''window'' of rows with a given length around the current row, and performs a calculation across the set of data in the window.<ref>{{Cite web|last=|first=|date=|title=SQL Window Functions Introduction|url=https://drill.apache.org/docs/sql-window-functions-introduction/|archive-url=|archive-date=|access-date=|website=Apache Drill}}</ref><ref>{{Cite web|title=PostgreSQL: Documentation: Window Functions|url=https://www.postgresql.org/docs/current/tutorial-window.html|access-date=2020-04-04|website=www.postgresql.org|language=en}}</ref>
NAME |
NAME |
------------
------------
Zeile 61: Zeile 57:
Ophelia|
Ophelia|
Zach| <-- Following (unbounded)
Zach| <-- Following (unbounded)
In the above table, the next query extracts for each row the values of a window with one preceding and one following row:<syntaxhighlight lang="psql">
Die nachfolgende Anfrage extrahiert für jedes Tupel in der obigen Tabelle die Werte einer vorangehenden und einer nachfolgenden Zeile:<syntaxhighlight lang="psql">
SELECT
SELECT
LAG(name, 1)
LAG(name, 1)
Zeile 70: Zeile 66:
FROM people
FROM people
ORDER BY name
ORDER BY name
</syntaxhighlight>The result query contains the following values:
</syntaxhighlight>Das Ergebnis enthält die folgenden Werte:
| PREV | NAME | NEXT |
| PREV | NAME | NEXT |
|----------|----------|----------|
|----------|----------|----------|
Zeile 84: Zeile 80:
| Ophelia| Zach| (null)|
| Ophelia| Zach| (null)|


== History ==
== Geschichte ==
Window functions were introduced in [[SQL:2003]] and had functionality expanded in later specifications.<ref>{{Cite web|title=Window Functions Overview|url=https://mariadb.com/kb/en/window-functions-overview/|access-date=2021-03-23|website=MariaDB KnowledgeBase}}</ref>
Fensterfunktionen wurden in SQL:2003 eingeführt und deren Funktionalität in späteren Spezifikationen erweitert.<ref>{{Cite web|url=https://mariadb.com/kb/en/window-functions-overview/|title=Window Functions Overview|accessdate=2021-03-23|work=MariaDB KnowledgeBase}}</ref>

== See also ==

* [[Select (SQL)#Limiting result rows]]


== References ==
== Einzelnachweise ==
 
{{Reflist}}{{SQL}}
⚫
[[Kategorie:SQL]]
[[Category:Articles with example SQL code]]
⚫
[[Category:SQL]]

Version vom 15. Mai 2022, 18:47 Uhr

Eine Fensterfunktion (engl.: 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]

Syntax

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, oderr 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 |

Beispiel

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)|

Geschichte

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

Einzelnachweise

 

  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. ↑ ? In: Proc. VLDB Endow. ISSN 2150-8097, doi:10.14778/2794367.2794375.
  7. ↑ ? 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.
  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.
Abgerufen von „https://de.wikipedia.org/w/index.php?title=Fensterfunktion_(SQL)&oldid=222899256“