Zum Inhalt springen

„Fensterfunktion (SQL)“ – Versionsunterschied

aus Wikipedia, der freien Enzyklopädie
Versionsgeschichte interaktiv durchsuchen
[gesichtete Version][ungesichtete Version]
← Zum vorherigen VersionsunterschiedZum nächsten Versionsunterschied →
Inhalt gelöscht Inhalt hinzugefügt
VisuellWikitext
K Titel in Vorlage eingefügt
K 18 Versionen von en:Window_function_(SQL) importiert: WP:IMP * user:Luke081515Bot
Markierung: Zurückgesetzt
Zeile 1: Zeile 1:
{{Short description|Function over multiple rows in SQL}}


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


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">
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">
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>Ausgabe:
</syntaxhighlight>
Output:
depname | empno | salary | avg
depname | empno | salary | avg
----------+-------+--------+----------------------
----------+-------+--------+----------------------
Zeile 18: Zeile 20:
sales | 4 | 4800 | 4866.6666666666666667
sales | 4 | 4800 | 4866.6666666666666667
(10 rows)
(10 rows)
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.
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.


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" />
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>


== Syntax ==
== Syntax ==
According to the PostgreSQL documentation, a window function has the syntax of one of the following:<ref name=":0" /><syntaxhighlight lang="psql">
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">
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>wobei <code>window_definition</code> die Syntax hat:<syntaxhighlight lang="psql">
</syntaxhighlight>where <code>window_definition</code> has syntax:<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> entspricht einer der folgenden Syntax:<syntaxhighlight lang="psql">
</syntaxhighlight><code>frame_clause</code> has the syntax of one of the following:<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> 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>.
</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>.


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


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


* Brackets [] indicate optional clauses
⚫
== Beispiel ==
* Curly braces {} indicate a set of different possible options, with each option delimited by a vertical bar |
⚫
Fensterfunktionen ermöglichen den Zugriff auf Daten direkt vor und nach dem aktuellen Tupel.<ref>{{Cite journal|title=Efficient processing of window functions in analytical SQL queries|first=Viktor|journal=Proc. VLDB Endow.|doi=10.14778/2794367.2794375|issn=2150-8097}}</ref><ref>{{Cite journal|title=Optimization of analytic window functions|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>

⚫
== 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 57: Zeile 61:
Ophelia|
Ophelia|
Zach| <-- Following (unbounded)
Zach| <-- Following (unbounded)
Die nachfolgende Anfrage extrahiert für jedes Tupel in der obigen Tabelle die Werte einer vorangehenden und einer nachfolgenden Zeile:<syntaxhighlight lang="psql">
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">
SELECT
SELECT
LAG(name, 1)
LAG(name, 1)
Zeile 66: Zeile 70:
FROM people
FROM people
ORDER BY name
ORDER BY name
</syntaxhighlight>Das Ergebnis enthält die folgenden Werte:
</syntaxhighlight>The result query contains the following values:
| PREV | NAME | NEXT |
| PREV | NAME | NEXT |
|----------|----------|----------|
|----------|----------|----------|
Zeile 80: Zeile 84:
| Ophelia| Zach| (null)|
| Ophelia| Zach| (null)|


== Geschichte ==
== History ==
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>
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>

== See also ==

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


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

Version vom 16. Mai 2022, 06:20 Uhr

Vorlage:Short description

In SQL, a window function or analytic function[1] is a function which uses values from one or multiple 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.[2]

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):[3]

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

Output: 

 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)

The PARTITION BY clause groups rows into partitions, and the function is applied to each partition separately. If the PARTITION BY clause is omitted (such as if we have an empty OVER() clause), then the entire result set treated as a single partition.[4] For this query, the average salary reported would be the average taken over all rows.

Window functions are evaluated after aggregation (after the GROUP BY clause and non-window aggregate functions, for example).[1]

Syntax

According to the PostgreSQL documentation, a window function has the syntax of one of the following:[4]

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

where window_definition has syntax:

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

frame_clause has the syntax of one of the following:

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

frame_start and frame_end can be UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, or UNBOUNDED FOLLOWING. frame_exclusion can be EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, or EXCLUDE NO OTHERS.

expression refers to any expression that does not contain a call to a window function.

Notation:

  • Brackets [] indicate optional clauses
  • 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.[5][6][7][8] 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.[9][10] 

      NAME |
------------
      Aaron| <-- Preceding (unbounded)
     Andrew|
     Amelia|
      James|
       Jill|
     Johnny| <-- 1st preceding row
    Michael| <-- Current row
       Nick| <-- 1st following row
    Ophelia|
       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:

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

The result query contains the following values: 

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

History

Window functions were introduced in SQL:2003 and had functionality expanded in later specifications.[11]

See also

References

Vorlage:ReflistVorlage:SQL

  1. ↑ a b 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. ↑ a b 4.2. Value Expressions. In: PostgreSQL Documentation. 11. Februar 2021, abgerufen am 23. März 2021 (englisch).
  5. ↑ Viktor Leis, Kan Kundhikanjana, Alfons Kemper, Thomas Neumann: Efficient Processing of Window Functions in Analytical SQL Queries. In: Proc. VLDB Endow. 8. Jahrgang, Nr. 10, Juni 2015, ISSN 2150-8097, S. 1058–1069, doi:10.14778/2794367.2794375.
  6. ↑ Yu Cao, Chee-Yong Chan, Jie Li, Kian-Lee Tan: Optimization of Analytic Window Functions. In: Proc. VLDB Endow. 5. Jahrgang, Nr. 11, Juli 2012, ISSN 2150-8097, S. 1244–1255, doi:10.14778/2350229.2350243, arxiv:1208.0086.
  7. ↑ Probably the Coolest SQL Feature: Window Functions In: Java, SQL and jOOQ., 3. November 2013. Abgerufen am 26. September 2017 (amerikanisches Englisch). 
  8. ↑ Window Functions in SQL - Simple Talk In: Simple Talk, 31. Oktober 2013. Abgerufen am 26. September 2017 (amerikanisches Englisch). 
  9. ↑ SQL Window Functions Introduction. In: Apache Drill.
  10. ↑ PostgreSQL: Documentation: Window Functions. In: www.postgresql.org. Abgerufen am 4. April 2020 (englisch).
  11. ↑ 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=222910121“