Documentation Source Text

<tcl>hd_keywords {window functions}</tcl>

<title>Window Functions</title>
<table_of_contents>







<h1>Introduction to Window Functions</h1>

<p>A window function is a special SQL function where the input
values are taken from
a "window" of one or more rows in the results set of a SELECT statement.






<tcl>
RecursiveBubbleDiagram window-function-invocation
</tcl>

<p>Window functions are distingished from ordinary SQL functions by the
presence of an OVER clause.  If a function invocation has an OVER clause
then it is a window function, and if lacks a OVER clause it is an ordinary
function.  Window functions might also have a FILTER
clause in between the function and the OVER clause.

<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
Also, Window functions may only appears in the result set and in the
ORDER BY clause of a SELECT statement.

<p>The following simple table is used to demonstrate how window
functions work:

<codeblock>
  CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
  INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');

previous row ("1 PRECEDING") and the following row ("1 FOLLOWING"), inclusive,
where rows are sorted according to the ORDER BY clause in the
<yynonterm>window-defn</yynonterm> (in this case "ORDER BY a"). 
For example, the frame for the row with (a=3) consists of rows (2, 'B', 'two'),
(3, 'C', 'three') and (4, 'D', 'one'). The result of group_concat(b, '.') 
for that row is therefore 'B.C.D'.







<p> This means that, after sorting the rows returned by the SELECT according to
the ORDER BY clause in the <yynonterm>window-definition</yynonterm>,
the window frame consists
of all rows between the first row and the last row with the same values as
the current row for all ORDER BY expressions. This implies that rows that
have the same values for all ORDER BY expressions will also have the same
value for the result of the window function (as the window frame is the same).

<ul>
  <li> A frame type - either RANGE or ROWS.
  <li> A starting frame boundary, and
  <li> An ending frame boundary.
</ul>

<p>The ending frame boundary can be omitted, in which case it defaults
to CURRENT ROW.

<p>The default <yynonterm>frame-spec</yynonterm> is:

<codeblock>
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
</codeblock>

<p>The default means that aggregate window functions read all
rows from the beginning of the partition up to and including the
current row and its peers.

<p> If the frame type is RANGE, then rows with the same values for all ORDER BY
expressions are considered "peers". Or, if there are no ORDER BY terms,
all rows are peers. Rows that are peers always have the same window frames.

<p> There are five options for frame boundaries:

<table striped=1>
<tr><th>Frame Boundary <th>Description
<tr><td>UNBOUNDED&nbsp;PRECEDING <td> The start of the frame is the first

  <li> <b>xInverse(5)</b> - remove value "5" from the window.
  <li> <b>xStep(1)</b> - add value "1" to the window.
  <li> <b>xValue()</b> - invoked to obtain the value for row (x='d').
  <li> <b>xInverse(3)</b> - remove value "3" from the window. The window now
       contains values 8 and 1 only.
  <li> <b>xValue()</b> - invoked to obtain the value for row (x='d'). 9.
</ol>

<h1>History</h1>

<p>Window function support was added to SQLite with release
[version 3.25.0] ([dateof:3.25.0]). The SQLite developers used
the <a href=http://www.postgresql.org>PostgreSQL</a> window function
documentation as their primary reference for how window functions
ought to behave.  Many test cases have been run against PostgreSQL
to ensure that window functions operate the same way in both
SQLite and PostgreSQL.