Content deleted Content added
Fix redirect Tag: Redirect target changed |
Enervation (talk | contribs) ←Removed redirect to Select (SQL)#Limiting result rows Tags: Removed redirect Visual edit |
||
Line 1:
In [[SQL]], a '''window function''' or '''analytic function''' is a function which uses information 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>
This is an 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="sql">
SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;
</syntaxhighlight>Output:<syntaxhighlight>
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)
</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.
== Syntax ==
According to the PostgreSQL documentation, a window function has the syntax of one of the following:<ref name=":0" /><syntaxhighlight lang="sql">
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="sql">
[ 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="sql">
{ RANGE | ROWS | GROUPS } frame_start [ 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>.
<code>expression</code> refers to any expression that does not contain a call to a window function.
== See also ==
* [[Select (SQL)#Limiting result rows]]
== References ==
{{Reflist}}
| |||