Documentation Source Text

Check-in [5eff6feebc]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Remove the "expr FOLLOWING" case from frame-specs that only describe the starting boundary.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 5eff6feebcd4cdd7940cdfa571dd17a95fe9e2d7d754394e14ff3949008702d2
User & Date: drh 2019-03-28 20:22:07.130
Context
2019-03-29
16:40
Mention the ON parsing ambiguity of UPSERT on the UPSERT documentation page. (check-in: c3bcaaf1e5 user: drh tags: trunk)
2019-03-28
20:22
Remove the "expr FOLLOWING" case from frame-specs that only describe the starting boundary. (check-in: 5eff6feebc user: drh tags: trunk)
15:23
In the window-defn bubble diagram, change "existing-window-name" to "base-window-name" and make it an indentifier instead of a non-terminal. (check-in: 67d1075ce3 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to art/syntax/bubble-generator-data.tcl.
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
                           {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} 







<







542
543
544
545
546
547
548

549
550
551
552
553
554
555
                           {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} 
Changes to art/syntax/frame-spec.gif.

cannot compute difference between binary files

Changes to pages/windowfunctions.in.
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
  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>.







<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







494
495
496
497
498
499
500













































501
502
503
504
505
506
507
  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>.
742
743
744
745
746
747
748

















































749
750
751
752
753
754
755
756
         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:







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
|







697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
         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: