Documentation Source Text

Check-in [bdc2a67078]
Login

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

Overview
Comment:Update window functions documentation.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: bdc2a67078be3e0d4bc18fdd1dafb07d60583ccf893913037317d40348fa2359
User & Date: dan 2019-03-26 13:11:05
Context
2019-03-26
14:47
Update the changes long to show the enhancements to window functions. check-in: 1a7fda4c1f user: drh tags: trunk
13:11
Update window functions documentation. check-in: bdc2a67078 user: dan tags: trunk
00:14
Update to the ".parameter" command documentation to conform to the latest implementation. check-in: 9dab48bb9d user: drh tags: trunk
2019-03-25
20:49
Update documentation to match the proposed window function changes. Closed-Leaf check-in: 783032878d user: dan tags: window-functions
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to art/syntax/all-bnf.html.

cannot compute difference between binary files

Changes to art/syntax/all-text.html.

cannot compute difference between binary files

Changes to art/syntax/bubble-generator-data.tcl.

   521    521         {line /* {loop nil /anything-except-*/}
   522    522              {or */ /end-of-input}}
   523    523     }
   524    524     filter {
   525    525       line FILTER ( WHERE expr )
   526    526     }
   527    527     window-defn {
   528         -    stack {line ( {opt PARTITION BY {loop expr ,}}}
          528  +    stack {line ( {opt existing-window-name} {opt PARTITION BY {loop expr ,}}}
   529    529             {opt ORDER BY {loop ordering-term ,}}
   530    530             {line {optx frame-spec} )}
   531    531     }
   532    532     frame-spec {
   533         -    line {or RANGE ROWS} {or
   534         -       {line BETWEEN {or {line UNBOUNDED PRECEDING}
   535         -                         {line expr PRECEDING}
   536         -                         {line CURRENT ROW}
   537         -                         {line expr FOLLOWING}
   538         -                     }
   539         -             AND {or     {line expr PRECEDING}
   540         -                         {line CURRENT ROW}
   541         -                         {line expr FOLLOWING}
   542         -                         {line UNBOUNDED FOLLOWING}
   543         -                 }
   544         -       }
   545         -       {or   {line UNBOUNDED PRECEDING}
   546         -             {line expr PRECEDING}
   547         -             {line CURRENT ROW}
   548         -             {line expr FOLLOWING}
   549         -       }
          533  +    stack {
          534  +      line {or RANGE ROWS GROUPS} {or
          535  +         {line BETWEEN {or {line UNBOUNDED PRECEDING}
          536  +                           {line expr PRECEDING}
          537  +                           {line CURRENT ROW}
          538  +                           {line expr FOLLOWING}
          539  +                       }
          540  +               AND {or     {line expr PRECEDING}
          541  +                           {line CURRENT ROW}
          542  +                           {line expr FOLLOWING}
          543  +                           {line UNBOUNDED FOLLOWING}
          544  +                   }
          545  +         }
          546  +         {or   {line UNBOUNDED PRECEDING}
          547  +               {line expr PRECEDING}
          548  +               {line CURRENT ROW}
          549  +               {line expr FOLLOWING}
          550  +         }
          551  +      }
          552  +    } {
          553  +      line {opt {or 
          554  +         {line EXCLUDE NO OTHERS} 
          555  +         {line EXCLUDE CURRENT ROW} 
          556  +         {line EXCLUDE GROUP} 
          557  +         {line EXCLUDE TIES} 
          558  +      } }
   550    559       }
   551    560     }
   552    561     function-invocation {
   553    562        line /function-name ( {or {line {optx DISTINCT} {toploop expr ,}} {} *} )
   554    563     }
   555    564     window-function-invocation {
   556    565       line /window-func ( {or {line {toploop expr ,}} {} *} ) 
   557    566            {opt filter} OVER {or window-defn /window-name}
   558    567     }
   559    568   }

Changes to art/syntax/frame-spec.gif.

cannot compute difference between binary files

Changes to art/syntax/window-defn.gif.

cannot compute difference between binary files

Changes to pages/windowfunctions.in.

    11     11   
    12     12   
    13     13   <tcl>
    14     14   RecursiveBubbleDiagram window-function-invocation window-defn frame-spec filter
    15     15   </tcl>
    16     16   
    17     17   <p>Window functions are distinguished from ordinary SQL functions by the
    18         -presence of an OVER clause.  If a function invocation has an OVER clause
    19         -then it is a window function, and if lacks an OVER clause it is an ordinary
    20         -function.  Window functions might also have a FILTER
           18  +presence of an OVER clause. If a function invocation has an OVER clause,
           19  +then it is a window function. If it lacks an OVER clause, then it is an
           20  +ordinary aggregate or scalar function. Window functions may have a FILTER
    21     21   clause in between the function and the OVER clause.
    22     22   
    23     23   <p>Unlike ordinary functions, window functions
    24     24   cannot use the DISTINCT keyword.
    25     25   Also, Window functions may only appear in the result set and in the
    26     26   ORDER BY clause of a SELECT statement.
    27     27   
................................................................................
   159    159   </tcl>
   160    160   
   161    161   <p> The <yynonterm>frame-spec</yynonterm> determines which output rows are
   162    162   read by an aggregate window function.  The
   163    163   <yynonterm>frame-spec</yynonterm> consists of three parts:
   164    164   
   165    165   <ul>
   166         -  <li> A frame type - either RANGE or ROWS.
   167         -  <li> A starting frame boundary, and
   168         -  <li> An ending frame boundary.
          166  +  <li> A frame type - either ROWS, RANGE or GROUPS,
          167  +  <li> A starting frame boundary,
          168  +  <li> An ending frame boundary,
          169  +  <li> An EXCLUDE clause.
   169    170   </ul>
   170    171   
   171    172   <p>The ending frame boundary can be omitted, in which case it defaults
   172    173   to CURRENT ROW.
   173    174   
   174    175   <p>The default <yynonterm>frame-spec</yynonterm> is:
   175    176   
   176    177   <codeblock>
   177         -    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          178  +    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
   178    179   </codeblock>
   179    180   
   180    181   <p>The default means that aggregate window functions read all
   181    182   rows from the beginning of the partition up to and including the
   182    183   current row and its peers.
   183    184   
   184         -<p> If the frame type is RANGE, then rows with the same values for all ORDER BY
   185         -expressions are considered "peers". Or, if there are no ORDER BY terms,
   186         -all rows are peers. Rows that are peers always have the same window frames.
          185  +<p> If the frame type is RANGE or GROUPS, then rows with the same values for
          186  +all ORDER BY expressions are considered "peers". Or, if there are no ORDER BY
          187  +terms, all rows are peers. Rows that are peers always have the same window
          188  +frames.
   187    189   
   188    190   <p> There are five options for frame boundaries:
   189    191   
   190    192   <table striped=1>
   191    193   <tr><th>Frame Boundary <th>Description
   192    194   <tr><td>UNBOUNDED&nbsp;PRECEDING <td> The start of the frame is the first
   193    195           row in the set.
   194    196   <tr><td>&lt;expr&gt; PRECEDING <td> &lt;expr&gt; is a constant expression
   195         -        that evaluates to a non-negative integer value. The start
   196         -        or end of the frame is &lt;expr&gt; rows before the current row. "0
   197         -        PRECEDING" is the same as "CURRENT ROW". This frame boundary type may
   198         -        only be used with ROWS frames.
   199         -<tr><td>CURRENT ROW <td> The current row. For RANGE frame types, all peers of
   200         -        the current row are also included in the window frame, regardless of
   201         -        whether CURRENT ROW is used as the starting or ending frame boundary.
          197  +        that evaluates to a non-negative numeric value. If the frame type
          198  +        is ROWS or GROUPS, the expression must evaluate to an integer
          199  +        value.<br><br>
          200  +        For a ROWS frame, the start or end of the frame is &lt;expr&gt; rows
          201  +        before the current row.<br><br>
          202  +        For a GROUPS frame, the frame begins or ends with the peers
          203  +        &lt;expr&gt; groups of peers before or after the current row's group.
          204  +        For both ROWS and GROUPS frames, "0 PRECEDING" is the same as "CURRENT
          205  +        ROW".
          206  +        <br><br> A RANGE frame that uses "&lt;expr&gt; PRECEDING" must be
          207  +        paired with an ORDER BY clause that contains a single expression. If
          208  +        the ORDER BY expression for the current row is not a numeric value, 
          209  +        then "&lt;expr&gt; PRECEDING" is equivalent to "CURRENT ROW".
          210  +        Otherwise, if the ORDER BY value is numeric for the current row, then
          211  +        the value of "&lt;expr&gt;" is used as a logical range for determining
          212  +        which groups are part of the current frame.
          213  +        <br><br>
          214  +        For example, if the ORDER BY clause is ASC (not DESC) and the
          215  +        "&lt;expr&gt; PRECEDING" is used as the start of the frame, then
          216  +        the first group in the frame is that with the smallest value for
          217  +        the ORDER BY expression that is greater than (current-row -
          218  +        &lt;expr&gt;). If the ORDER BY clause is DESC, then the first group
          219  +        is that with an ORDER BY expresion greater than (current-row +
          220  +        &lt;expr&gt;).
          221  +        <br><br>
          222  +        Or, if it is used as the end of the frame and the ORDER BY is ASC, then
          223  +        the last group in the grame is that with the largest value for the
          224  +        ORDER BY expression that is less than (current-row - &lt;expr&gt;).
          225  +        For DESC the last group is that with the smallest ORDER BY value
          226  +        that is greater than (current-row + &lt;expr&gt;).
          227  +
          228  +<tr><td>CURRENT ROW <td> The current row. For RANGE and GROUPS frame types, all
          229  +        peers of the current row are also included in the window frame,
          230  +        regardless of whether CURRENT ROW is used as the starting or ending
          231  +        frame boundary.
   202    232   <tr><td>&lt;expr&gt; FOLLOWING <td> &lt;expr&gt; is a constant expression
   203         -        that must evaluate to a non-negative integer value. The start
   204         -        or end of the frame is &lt;expr&gt; rows after the current row. "0
   205         -        FOLLOWING" is the same as "CURRENT ROW". This frame boundary type may
   206         -        only be used with ROWS frames.
          233  +        that must evaluate to a non-negative numeric value. It is handled
          234  +        in similarly to "&lt;expr&gt; PRECEDING".
   207    235   <tr><td>UNBOUNDED&nbsp;FOLLOWING <td> The end of the frame is the last
   208    236           row in the set.
   209    237   </table>
   210    238   
   211    239   <p> The ending frame boundary must not take a form that appears higher in
   212    240   the above list than the starting frame boundary.
   213    241   
................................................................................
   229    257     <i>--   two   | 5 | E | E           </i>
   230    258     <i>-- </i>
   231    259     SELECT c, a, b, group_concat(b, '.') OVER (
   232    260       ORDER BY c, a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   233    261     ) AS group_concat
   234    262     FROM t1 ORDER BY c, a;
   235    263   </codeblock>
          264  +
          265  +<p> Even though they are technically part of the frame-specification, 
          266  +[exclude clause|the EXCLUDE clause] and [window chaining] are described
          267  +separately below.
   236    268   
   237    269   <h2>The PARTITION BY Clause</h2>
   238    270   
   239    271   <p> A <yynonterm>window-defn</yynonterm> may include a PARTITION BY clause.
   240    272   If so, the rows returned by the SELECT statement are divided into groups -
   241    273   partitions - with the same values for each PARTITION BY expression, and then
   242    274   window-function processing is performed separately for each
................................................................................
   289    321     <i>--   one   | 7 | G | A.C.D.F.G   </i>
   290    322     <i>-- </i>
   291    323     SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
   292    324       ORDER BY a
   293    325     ) AS group_concat
   294    326     FROM t1 ORDER BY a;
   295    327   </codeblock>
          328  +
          329  +<h2 tags="exclude clause">The EXCLUDE Clause</h2>
          330  +
          331  +<p> The optional EXCLUDE clause may take any of the following four forms:
          332  +
          333  +<ul>
          334  +  <li> <p><b> EXCLUDE NO OTHERS</b>: This is the default. In this case no
          335  +  rows are excluded from the window frame as defined by its starting and ending
          336  +  frame boundaries.
          337  +
          338  +  <li> <p><b> EXCLUDE CURRENT ROW</b>: In this case the current row is 
          339  +  excluded from the window frame.
          340  +
          341  +  <li> <p><b> EXCLUDE GROUP</b>: In this case the current row and all other
          342  +  rows that are peers of the current row are excluded from the group. When
          343  +  processing an EXCLUDE clause, all rows with the same ORDER BY values, or all
          344  +  rows in the partition if there is no ORDER BY clause, are considered peers,
          345  +  even if the frame type is ROWS.
          346  +
          347  +  <li> <p><b> EXCLUDE TIES</b>: In this case the current row is part of the
          348  +  frame, but all other peers are excluded.
          349  +</ul>
          350  +
          351  +<codeblock>
          352  +  <i>-- The following SELECT statement returns:</i>
          353  +  <i>-- </i>
          354  +  <i>--   c    | a | b | no_others     | current_row | grp       | ties</i>
          355  +  <i>--  one   | 1 | A | A.D.G         | D.G         |           | A</i>
          356  +  <i>--  one   | 4 | D | A.D.G         | A.G         |           | D</i>
          357  +  <i>--  one   | 7 | G | A.D.G         | A.D         |           | G</i>
          358  +  <i>--  three | 3 | C | A.D.G.C.F     | A.D.G.F     | A.D.G     | A.D.G.C</i>
          359  +  <i>--  three | 6 | F | A.D.G.C.F     | A.D.G.C     | A.D.G     | A.D.G.F</i>
          360  +  <i>--  two   | 2 | B | A.D.G.C.F.B.E | A.D.G.C.F.E | A.D.G.C.F | A.D.G.C.F.B</i>
          361  +  <i>--  two   | 5 | E | A.D.G.C.F.B.E | A.D.G.C.F.B | A.D.G.C.F | A.D.G.C.F.E</i>
          362  +  <i>-- </i>
          363  +  SELECT c, a, b,
          364  +    group_concat(b, '.') OVER (
          365  +      ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
          366  +    ) AS no_others,
          367  +    group_concat(b, '.') OVER (
          368  +      ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW
          369  +    ) AS current_row,
          370  +    group_concat(b, '.') OVER (
          371  +      ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP
          372  +    ) AS grp,
          373  +    group_concat(b, '.') OVER (
          374  +      ORDER BY c GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES
          375  +    ) AS ties
          376  +  FROM t1 ORDER BY c, a;
          377  +</codeblock>
          378  +
          379  +<h2 tags="window chaining">Window Chaining</h2>
          380  +
          381  +<p>
          382  +Window chaining is a shorthand that allows one window to be defined in terms 
          383  +of another. Specifically, the shorthand allows the new window to implicitly
          384  +copy the PARTITION BY and optionally ORDER BY clauses of the base window. For
          385  +example, in the following:
          386  +
          387  +<codeblock>
          388  +  SELECT group_concat(b, '.') OVER (
          389  +    win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          390  +  )
          391  +  FROM t1
          392  +  WINDOW win AS (PARTITION BY a ORDER BY c)
          393  +</codeblock>
          394  +
          395  +<p>
          396  +the window used by the group_concat() is equivalent to "PARTITION BY a ORDER 
          397  +BY c ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". In order to use window
          398  +chaining, all of the following must be true:
          399  +
          400  +<ul>
          401  +  <li><p>The new window definition must not include a PARTITION BY clause. The
          402  +       PARTITION BY clause, or lack thereof, is always copied from the base
          403  +       window.
          404  +
          405  +  <li><p>If the base window has an ORDER BY clause, it is copied into the new
          406  +       window. In this case the new window must not specify an ORDER BY clause.
          407  +       If the base window has no ORDER BY clause, one may be specified as part
          408  +       of the new window definition.
          409  +  
          410  +  <li><p>The base window may not specify a frame specification.
          411  +</ul>
          412  +
          413  +<p>The two fragments of SQL below are similar, but not entirely equivalent, as
          414  +the latter will fail if the definition of window "win" contains a frame
          415  +specification.
          416  +
          417  +<codeblock>
          418  +  SELECT group_concat(b, '.') OVER win ...
          419  +  SELECT group_concat(b, '.') OVER (win) ...
          420  +</codeblock>
   296    421   
   297    422   <h1>Built-in Window Functions</h1>
   298    423   
   299    424   <p> As well as aggregate window functions, SQLite features a set of built-in
   300    425   window functions based on 
   301    426   <a href=https://www.postgresql.org/docs/10/static/functions-window.html>
   302    427   those supported by PostgreSQL</a>.