Window function (SQL): Difference between revisions

Revision as of 03:58, 23 March 2021

Revision as of 05:43, 14 April 2021

Line 3:

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

As an example, here is a query which uses a window function to compare 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="sqlpsql">

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

</syntaxhighlight>Output:<syntaxhighlight>

Output:

depname | empno | salary | avg

- ----------+-------+--------+-----------------------

develop | 11 | 5200 | 5020.0000000000000000

develop | 7 | 4200 | 5020.0000000000000000

Line 18 ⟶ 19:

sales | 1 | 5000 | 4866.6666666666666667

sales | 4 | 4800 | 4866.6666666666666667

(10 rows)

</syntaxhighlight>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.

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

According to the PostgreSQL documentation, a window function has the syntax of one of the following:<ref name=":0" /><syntaxhighlight lang="sqlpsql">

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

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

function_name ( * ) OVER window_name

function_name ( * ) OVER ( window_definition )

</syntaxhighlight>where <code>window_definition</code> has syntax:<syntaxhighlight lang="sqlpsql">

[ existing_window_name ]

[ PARTITION BY expression [, ...] ]

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

[ frame_clause ]

</syntaxhighlight><code>frame_clause</code> has the syntax of one of the following:<syntaxhighlight lang="sqlpsql">

{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]

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