Documentation Source Text

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).
For example:

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
  <i>--   a | b | c | group_concat</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) AS group_concat 
  FROM t1 ORDER BY a;
</codeblock>

<p> All of SQLite's [Aggregate Functions|aggregate functions] may
be used as aggregate window functions. It is also possible to 
[user-defined window functions|create user-defined aggregate window functions].

<tcl>hd_fragment framespec {frame specification} {frames}</tcl>
<h2>Frame Specifications</h2>

  <li> A starting frame boundary,
  <li> An ending frame boundary,
  <li> An EXCLUDE clause.
</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 EXCLUDE NO OTHERS
</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 or GROUPS, 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
        row in the set.

<tr><td>CURRENT ROW <td> The current row. For RANGE and GROUPS 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; is a constant expression
        that must evaluate to a non-negative numeric value. It is handled
        in similarly to "&lt;expr&gt; PRECEDING".
<tr><td>UNBOUNDED&nbsp;FOLLOWING <td> The end of the frame is the last
        row in the set.
</table>

<p> The ending frame boundary must not take a form that appears higher in
the above list than the starting frame boundary.