Documentation Source Text

                           {line expr FOLLOWING}
                           {line UNBOUNDED FOLLOWING}
                   }
         }
         {or   {line UNBOUNDED PRECEDING}
               {line expr PRECEDING}
               {line CURRENT ROW}
               {line expr FOLLOWING}
         }
      }
    } {
      line {or nil
         {line EXCLUDE NO OTHERS} 
         {line EXCLUDE CURRENT ROW} 
         {line EXCLUDE GROUP} 

                           {line expr FOLLOWING}
                           {line UNBOUNDED FOLLOWING}
                   }
         }
         {or   {line UNBOUNDED PRECEDING}
               {line expr PRECEDING}
               {line CURRENT ROW}

         }
      }
    } {
      line {or nil
         {line EXCLUDE NO OTHERS} 
         {line EXCLUDE CURRENT ROW} 
         {line EXCLUDE GROUP} 

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

<codeblock>
  SELECT group_concat(b, '.') OVER (
    win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
  FROM t1
  WINDOW win AS (PARTITION BY a ORDER BY c)
</codeblock>

<p>
the window used by the group_concat() is equivalent to "PARTITION BY a ORDER 
BY c ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". In order to use window
chaining, all of the following must be true:

<ul>
  <li><p>The new window definition must not include a PARTITION BY clause. The
       PARTITION BY clause, if there is one, must be supplied by the base
       window specification.

  <li><p>If the base window has an ORDER BY clause, it is copied into the new
       window. In this case the new window must not specify an ORDER BY clause.
       If the base window has no ORDER BY clause, one may be specified as part
       of the new window definition.
  
  <li><p>The base window may not specify a frame specification.  The frame
      specification can only be given in the new window specification.
</ul>

<p>The two fragments of SQL below are similar, but not entirely equivalent, as
the latter will fail if the definition of window "win" contains a frame
specification.

<codeblock>
  SELECT group_concat(b, '.') OVER win ...
  SELECT group_concat(b, '.') OVER (win) ...
</codeblock>

<tcl>hd_fragment builtins {built-in window functions} {built-ins}</tcl>
<h1>Built-in Window Functions</h1>

<p> As well as aggregate window functions, SQLite features a set of built-in
window functions based on 
<a href=https://www.postgresql.org/docs/10/static/functions-window.html>
those supported by PostgreSQL</a>.
................................................................................
         first_value(b) OVER win    AS first_value,
         last_value(b) OVER win     AS last_value,
         nth_value(b, 3) OVER win   AS nth_value_3
  FROM t1
  WINDOW win AS (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
</codeblock>


















































<h1 tags="user-defined window functions">User-Defined Aggregate Window Functions</h1>

<p> User-defined aggregate window functions may be created using the
[sqlite3_create_window_function]() API. Implementing an aggregate window
function is very similar to an ordinary aggregate function. Any user-defined
aggregate window function may also be used as an ordinary aggregate. To 
implement a user-defined aggregate window function the application must
supply four callback functions:

  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 builtins {built-in window functions} {built-ins}</tcl>
<h1>Built-in Window Functions</h1>

<p> As well as aggregate window functions, SQLite features a set of built-in
window functions based on 
<a href=https://www.postgresql.org/docs/10/static/functions-window.html>
those supported by PostgreSQL</a>.
................................................................................
         first_value(b) OVER win    AS first_value,
         last_value(b) OVER win     AS last_value,
         nth_value(b, 3) OVER win   AS nth_value_3
  FROM t1
  WINDOW win AS (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
</codeblock>


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

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

<codeblock>
  SELECT group_concat(b, '.') OVER (
    win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  )
  FROM t1
  WINDOW win AS (PARTITION BY a ORDER BY c)
</codeblock>

<p>
the window used by the group_concat() function is equivalent
to "PARTITION BY a ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW". In order to use window
chaining, all of the following must be true:

<ul>
  <li><p>The new window definition must not include a PARTITION BY clause. The
       PARTITION BY clause, if there is one, must be supplied by the base
       window specification.

  <li><p>If the base window has an ORDER BY clause, it is copied into the new
       window. In this case the new window must not specify an ORDER BY clause.
       If the base window has no ORDER BY clause, one may be specified as part
       of the new window definition.
  
  <li><p>The base window may not specify a frame specification.  The frame
      specification can only be given in the new window specification.
</ul>

<p>The two fragments of SQL below are similar, but not entirely equivalent, as
the latter will fail if the definition of window "win" contains a frame
specification.

<codeblock>
  SELECT group_concat(b, '.') OVER win ...
  SELECT group_concat(b, '.') OVER (win) ...
</codeblock>

<tcl>hd_fragment udfwinfunc {user-defined window functions} \
 {application-defined window functions}</tcl>
<h1>User-Defined Aggregate Window Functions</h1>

<p> User-defined aggregate window functions may be created using the
[sqlite3_create_window_function]() API. Implementing an aggregate window
function is very similar to an ordinary aggregate function. Any user-defined
aggregate window function may also be used as an ordinary aggregate. To 
implement a user-defined aggregate window function the application must
supply four callback functions: