Documentation Source Text

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

<title>SQLite SQL Window Function Support</title>
<table_of_contents>

<h2 style="margin-left:1.0em" notoc id=overview> Overview</h2>

<p>This page describes the support for SQL window functions added to SQLite
[version 3.25.0] ([dateof:3.25.0]). SQLite's window function support is closely
modeled on that of <a href=http://www.postgresql.org>PostgreSQL</a>.

<h1>Introduction to Window Functions</h1>

<p>A window function is a special type of SQL function for which the results
depend on the contents of a "window" of one or more contiguous rows returned
by the SELECT statement containing the window function. A window function may
only be used in the select-list or ORDER BY clause of a SELECT or sub-select
construction. The syntax for a window function is similar to that for a regular
scalar SQL function except that it is followed by an OVER clause. The full
syntax is as follows:




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

<p>Window function 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.

Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.



<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> The default <yynonterm>frame-spec</yynonterm> is:

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

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


<yynonterm>frame-spec</yynonterm> is:

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





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

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