Documentation Source Text

  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-04-00 (3.28.0)} {








<li> Added the new [sqlite3_stmt_isexplain(S)] interface for determining
     whether or not a [prepared statement] is an [EXPLAIN].
<li> Enhanced [VACUUM INTO] so that it works for read-only databases.
<li> New query optimizations:
<ol type="a">
<li> Enable the [LIKE optimization] for cases when the ESCAPE keyword
     is present and [PRAGMA case_sensitive_like] is on.

  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-04-00 (3.28.0)} {
<li> Enhanced [window functions]:
<ol type="a">
<li> Add support the [EXCLUDE clause].
<li> Add support for [window chaining].
<li> Add support for GROUPS [frames].
<li> Add support for "&lt;expr&gt; PRECEDING" and
     "&lt;expr&gt; FOLLOWING" in RANGE [frames].
</ol>
<li> Added the new [sqlite3_stmt_isexplain(S)] interface for determining
     whether or not a [prepared statement] is an [EXPLAIN].
<li> Enhanced [VACUUM INTO] so that it works for read-only databases.
<li> New query optimizations:
<ol type="a">
<li> Enable the [LIKE optimization] for cases when the ESCAPE keyword
     is present and [PRAGMA case_sensitive_like] is on.

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


<h2>Frame Specifications</h2>

<tcl>
RecursiveBubbleDiagram frame-spec
</tcl>

<p> The <yynonterm>frame-spec</yynonterm> determines which output rows are
................................................................................
  <i>-- </i>
  SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
    ORDER BY a
  ) AS group_concat
  FROM t1 ORDER BY a;
</codeblock>


<h2 tags="exclude clause">The EXCLUDE Clause</h2>

<p> The optional EXCLUDE clause may take any of the following four forms:

<ul>
  <li> <p><b> EXCLUDE NO OTHERS</b>: This is the default. In this case no
  rows are excluded from the window frame as defined by its starting and ending
  frame boundaries.
................................................................................
    ) AS grp,
    group_concat(b, '.') OVER (
      ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
    ) AS ties
  FROM t1 ORDER BY c, a;
</codeblock>


<h2 tags="window chaining">Window Chaining</h2>

<p>
Window chaining is a shorthand that allows one window to be defined in terms 
of another. Specifically, the shorthand allows the new window to implicitly
copy the PARTITION BY and optionally ORDER BY clauses of the base window. For
example, in the following:

  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>

<tcl>
RecursiveBubbleDiagram frame-spec
</tcl>

<p> The <yynonterm>frame-spec</yynonterm> determines which output rows are
................................................................................
  <i>-- </i>
  SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
    ORDER BY a
  ) AS group_concat
  FROM t1 ORDER BY a;
</codeblock>

<tcl>hd_fragment wexcls {exclude clause} {EXCLUDE clause}</tcl> 
<h2>The EXCLUDE Clause</h2>

<p> The optional EXCLUDE clause may take any of the following four forms:

<ul>
  <li> <p><b> EXCLUDE NO OTHERS</b>: This is the default. In this case no
  rows are excluded from the window frame as defined by its starting and ending
  frame boundaries.
................................................................................
    ) AS grp,
    group_concat(b, '.') OVER (
      ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
    ) AS ties
  FROM t1 ORDER BY c, a;
</codeblock>

<tcl>hd_fragment wchaining {window chaining}</tcl>
<h2>Window Chaining</h2>

<p>
Window chaining is a shorthand that allows one window to be defined in terms 
of another. Specifically, the shorthand allows the new window to implicitly
copy the PARTITION BY and optionally ORDER BY clauses of the base window. For
example, in the following: