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>

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

<codeblock>
  &lt;window-function&gt;     := &lt;function&gt;(&lt;args...&gt;) &#91;&lt;filter&gt;] OVER (&lt;window-definition&gt;)

  &lt;filter&gt;              := FILTER (WHERE &lt;expr&gt;)

  &lt;window-definition&gt;   := &#91;PARTITION BY &lt;expression-list&gt;] &#91;ORDER BY &lt;expression-list&gt;] &#91;&lt;frame-specification&gt;]

  &lt;frame-specification&gt; := ROWS|RANGE BETWEEN &lt;frame-boundary&gt; AND &lt;frame-boundary&gt;

  &lt;frame-boundary&gt;      := UNBOUNDED PRECEDING
  &lt;frame-boundary&gt;      := &lt;expr&gt; PRECEDING
  &lt;frame-boundary&gt;      := CURRENT ROW
  &lt;frame-boundary&gt;      := &lt;expr&gt; FOLLOWING
  &lt;frame-boundary&gt;      := UNBOUNDED FOLLOWING
</codeblock>

<p>For example, given:

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

<p>Then:
<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
  <i>--     1|aaa|1</i>
  <i>--     2|ccc|3</i>
  <i>--     3|bbb|2</i>
  <i>-- </i>
  SELECT x, y, row_number() OVER (ORDER BY y) FROM t0 ORDER BY x;
</codeblock>

<p>The example above uses the special built-in window function row_number().
This function returns a monotonically increasing integer assigned to each
row in order of the the "ORDER BY" clause within the
<i>&lt;window-definition&gt;</i> (in this case "ORDER BY y"). Note that 
this does not affect the order in which results are returned to the user -
that is still governed by the ORDER BY clause attached to the SELECT
statement (in this case "ORDER BY x").

<h1>Aggregate Window Functions</h1>

<p> The examples in this section all assume that the database is populated as
follows:

<codeblock>
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES   (1, 'A', 'one'  ),
                          (2, 'B', 'two'  ),
                          (3, 'C', 'three'),
                          (4, 'D', 'one'  ),
                          (5, 'E', 'two'  ),
                          (6, 'F', 'three'),
                          (7, 'G', 'one'  );
</codeblock>

<p> An aggregate window function is similar to an aggregate function, except
adding it to a query does not change the number of rows returned. Instead,
for each row the result of the aggregate window function is as if the
corresponding aggregate were run over all rows in the "window frame".

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
  <i>--     1|A|A.B</i>
  <i>--     2|B|A.B.C</i>
  <i>--     3|C|B.C.D</i>
  <i>--     4|D|C.D.E</i>
  <i>--     5|E|D.E.F</i>
  <i>--     6|F|E.F.G</i>
  <i>--     7|G|F.G</i>
  <i>-- </i>
  SELECT a, b, group_concat(b, '.') OVER (
    ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1;
</codeblock>

<p> In the example above, the window frame consists of all rows between the
previous row ("1 PRECEDING") and the following row ("1 FOLLOWING"), inclusive,
where rows are sorted according to the ORDER BY clause in the
<i>&lt;window-definition&gt;</i> (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 &lt;frame-specification&gt; is:

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

<p> This means that, after sorting the rows returned by the SELECT according to
the ORDER BY clause in the &lt;window-definition&gt;, 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). For example:

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
  <i>--     1|A|one|A.D.G</i>
  <i>--     2|B|two|A.D.G.C.F.B.E</i>
  <i>--     3|C|three|A.D.G.C.F</i>
  <i>--     4|D|one|A.D.G</i>
  <i>--     5|E|two|A.D.G.C.F.B.E</i>
  <i>--     6|F|three|A.D.G.C.F</i>
  <i>--     7|G|one|A.D.G</i>
  <i>-- </i>
  SELECT a, b, c, group_concat(b, '.') OVER (ORDER BY c) FROM t1 ORDER BY a;
</codeblock>

<h2>Frame Specifications</h2>

<p> A window frame specification consists of three things:

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

<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> This may only be used as a starting frame
        boundary. In which case the start of the frame is always the first
        row in the set.
<tr><td>&lt;expr&gt; PRECEDING <td> &lt;expr&gt;, which may not refer to any
        table columns, must evaluate to a non-negative integer value. The start
        or end of the frame is &lt;expr&gt; rows before the current row. "0
        PRECEDING" is the same as "CURRENT ROW". This frame boundary type may
        only be used with ROWS frames.
<tr><td>CURRENT ROW <td> The current row. For RANGE frame types, all peers of
        the current row are also included in the window frame, regardless of
        whether CURRENT ROW is used as the starting or ending frame boundary.
<tr><td>&lt;expr&gt; FOLLOWING <td> &lt;expr&gt;, which may not refer to any
        table columns, must evaluate to a non-negative integer value. The start
        or end of the frame is &lt;expr&gt; rows after the current row. "0
        FOLLOWING" is the same as "CURRENT ROW". This frame boundary type may
        only be used with ROWS frames.
<tr><td>UNBOUNDED&nbsp;FOLLOWING <td> This may only be used as an ending
        frame boundary. In which case the end of the frame is always the last
        row in the set.
</table>


<h2>The PARTITION BY Clause</h2>

<h2>The FILTER Clause</h2>

<h2>Adding New Aggregate Window Functions</h2>

<p>
Link to C API docs.

<h1>Built-in Window Functions</h1>