Documentation Source Text

Check-in [6d60e5d021]
Login

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

Overview
Comment:Work on the window function documentation, including importing the new syntax diagrams.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:6d60e5d0211950dddab8ea8f6ae5c79924e14f71808e724763bec028594e2b24
User & Date: drh 2018-07-06 20:43:09
Context
2018-07-07
17:36
Minor changes to the windows function documentation. check-in: ff1e1e885a user: drh tags: trunk
2018-07-06
20:43
Work on the window function documentation, including importing the new syntax diagrams. check-in: 6d60e5d021 user: drh tags: trunk
18:30
Update the syntax diagrams to include the new window function syntax. check-in: f7a6a2f7c2 user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to pages/windowfunctions.in.

    11     11   
    12     12   <h1>Introduction to Window Functions</h1>
    13     13   
    14     14   <p>A window function is a special type of SQL function for which the results
    15     15   depend on the contents of a "window" of one or more contiguous rows returned
    16     16   by the SELECT statement containing the window function. A window function may
    17     17   only be used in the select-list or ORDER BY clause of a SELECT or sub-select
    18         -construction. The syntax for a window function is similar to that for a regular scalar SQL function except that it is followed by an OVER clause. The full
    19         -syntax is:
           18  +construction. The syntax for a window function is similar to that for a regular
           19  +scalar SQL function except that it is followed by an OVER clause. The full
           20  +syntax is as follows:
    20     21   
    21         -<codeblock>
    22         -  &lt;window-function&gt;     := &lt;function&gt;(&lt;args...&gt;) &#91;&lt;filter&gt;] OVER (&lt;window-definition&gt;)
           22  +<tcl>
           23  +RecursiveBubbleDiagram window-function-invocation
           24  +</tcl>
    23     25   
    24         -  &lt;filter&gt;              := FILTER (WHERE &lt;expr&gt;)
           26  +<p>Window function are distingished from ordinary SQL functions by the
           27  +presence of an OVER clause.  If a function invocation has an OVER clause
           28  +then it is a window function, and if lacks a OVER clause it is an ordinary
           29  +function.  Window functions might also have a FILTER
           30  +clause in between the function and the OVER clause.
           31  +Unlike ordinary functions, window functions
           32  +cannot use the DISTINCT keyword.
    25     33   
    26         -  &lt;window-definition&gt;   := &#91;PARTITION BY &lt;expression-list&gt;] &#91;ORDER BY &lt;expression-list&gt;] &#91;&lt;frame-specification&gt;]
    27         -
    28         -  &lt;frame-specification&gt; := ROWS|RANGE BETWEEN &lt;frame-boundary&gt; AND &lt;frame-boundary&gt;
    29         -
    30         -  &lt;frame-boundary&gt;      := UNBOUNDED PRECEDING
    31         -  &lt;frame-boundary&gt;      := &lt;expr&gt; PRECEDING
    32         -  &lt;frame-boundary&gt;      := CURRENT ROW
    33         -  &lt;frame-boundary&gt;      := &lt;expr&gt; FOLLOWING
    34         -  &lt;frame-boundary&gt;      := UNBOUNDED FOLLOWING
    35         -</codeblock>
    36         -
    37         -<p>For example, given:
           34  +<p>The following simple table is used to demonstrate how window
           35  +functions work:
    38     36   
    39     37   <codeblock>
    40     38     CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
    41     39     INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
    42     40   </codeblock>
    43     41   
    44         -<p>Then:
           42  +<p>An example of using window functions:
    45     43   
    46     44   <codeblock>
    47     45     <i>-- The following SELECT statement returns:</i>
    48     46     <i>-- </i>
    49     47     <i>--   x | y | row_number</i>
    50     48     -----------------------
    51     49     <i>--   1 | aaa | 1         </i>
    52     50     <i>--   2 | ccc | 3         </i>
    53     51     <i>--   3 | bbb | 2         </i>
    54     52     <i>-- </i>
    55     53     SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;
    56     54   </codeblock>
    57     55   
    58         -<p>The example above uses the special built-in window function row_number().
    59         -This function returns a monotonically increasing integer assigned to each
           56  +<p>The example uses the built-in window function row_number().
           57  +The row_number() window function
           58  +assigns a monotonically increasing integer to each
    60     59   row in order of the the "ORDER BY" clause within the
    61         -<i>&lt;window-definition&gt;</i> (in this case "ORDER BY y"). Note that 
    62         -this does not affect the order in which results are returned to the user -
    63         -that is still governed by the ORDER BY clause attached to the SELECT
           60  +<yynonterm>window-defn</yynonterm> (in this case "ORDER BY y"). Note that 
           61  +this does not affect the order in which results are returned from
           62  +the overall query.  The order of the final output is
           63  +is still governed by the ORDER BY clause attached to the SELECT
    64     64   statement (in this case "ORDER BY x").
    65     65   
    66         -<p>Named <i>&lt;window-definitions&gt;</i> may also be added to a SELECT 
           66  +<p>Named <yynonterm>window-defn</yynonterm> clauses may also be added to a SELECT 
    67     67   statement using a WINDOW clause and then refered to by name within window
    68     68   function invocations. For example, the following SELECT statement contains
    69         -two named <i>&lt;window-definitions&gt;</i>, "win1" and "win2":
           69  +two named <yynonterm>window-defs</yynonterm> clauses, "win1" and "win2":
    70     70   
    71     71   <codeblock>
    72         -  SELECT x, y, row_number() OVER <b>win1</b>, rank() OVER <b>win2</b> FROM t0 
           72  +  SELECT x, y, row_number() OVER <b>win1</b>, rank() OVER <b>win2</b> 
           73  +  FROM t0 
    73     74     WINDOW <b>win1</b> AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
    74     75            <b>win2</b> AS (PARTITION BY y ORDER BY x)
    75     76     ORDER BY x;
    76     77   </codeblock>
    77     78   
    78     79   <p>The WINDOW clause, when one is present, comes after any HAVING clause and
    79     80   before any ORDER BY.
................................................................................
   117    118       ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
   118    119     ) AS group_concat FROM t1;
   119    120   </codeblock>
   120    121   
   121    122   <p> In the example above, the window frame consists of all rows between the
   122    123   previous row ("1 PRECEDING") and the following row ("1 FOLLOWING"), inclusive,
   123    124   where rows are sorted according to the ORDER BY clause in the
   124         -<i>&lt;window-definition&gt;</i> (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'.
          125  +<yynonterm>window-defn</yynonterm> (in this case "ORDER BY a"). 
          126  +For example, the frame for the row with (a=3) consists of rows (2, 'B', 'two'),
          127  +(3, 'C', 'three') and (4, 'D', 'one'). The result of group_concat(b, '.') 
          128  +for that row is therefore 'B.C.D'.
   125    129   
   126         -<p> The default <i>&lt;frame-specification&gt;</i> is:
          130  +<p> The default <yynonterm>frame-spec</yynonterm> is:
   127    131   
   128    132   <codeblock>
   129    133       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
   130    134   </codeblock>
   131    135   
   132    136   <p> This means that, after sorting the rows returned by the SELECT according to
   133         -the ORDER BY clause in the &lt;window-definition&gt;, the window frame consists
   134         -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:
          137  +the ORDER BY clause in the <yynonterm>window-definition</yynonterm>,
          138  +the window frame consists
          139  +of all rows between the first row and the last row with the same values as
          140  +the current row for all ORDER BY expressions. This implies that rows that
          141  +have the same values for all ORDER BY expressions will also have the same
          142  +value for the result of the window function (as the window frame is the same).
          143  +For example:
   135    144   
   136    145   <codeblock>
   137    146     <i>-- The following SELECT statement returns:</i>
   138    147     <i>-- </i>
   139    148     <i>--   a | b | c | group_concat</i>
   140    149     -----------------------------
   141    150     <i>--   1 | A | one   | A.D.G       </i>
................................................................................
   147    156     <i>--   7 | G | one   | A.D.G       </i>
   148    157     <i>-- </i>
   149    158     SELECT a, b, c, 
   150    159            group_concat(b, '.') OVER (ORDER BY c) AS group_concat 
   151    160     FROM t1 ORDER BY a;
   152    161   </codeblock>
   153    162   
   154         -<p> All of SQLite's [Aggregate Functions|aggregate functions] may be used as aggregate window
   155         -functions. It is also possible to 
          163  +<p> All of SQLite's [Aggregate Functions|aggregate functions] may
          164  +be used as aggregate window functions. It is also possible to 
   156    165   [user-defined window functions|create user-defined aggregate window functions].
   157    166   
   158    167   <h2>Frame Specifications</h2>
   159    168   
   160         -<p> A window frame specification consists of three things:
          169  +<tcl>
          170  +RecursiveBubbleDiagram frame-spec
          171  +</tcl>
          172  +
          173  +<p> The <yynonterm>frame-spec</yynonterm> determines which output rows are
          174  +read by an aggregate window function.  The
          175  +<yynonterm>frame-spec</yynonterm> consists of three parts:
   161    176   
   162    177   <ul>
   163    178     <li> A frame type - either RANGE or ROWS.
   164    179     <li> A starting frame boundary, and
   165    180     <li> An ending frame boundary.
   166    181   </ul>
          182  +
          183  +<p>The default 
          184  +<yynonterm>frame-spec</yynonterm> is:
          185  +
          186  +<codeblock>
          187  +    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          188  +</codeblock>
   167    189   
   168    190   <p> If the frame type is RANGE, then rows with the same values for all ORDER BY
   169    191   expressions are considered "peers". Or, if there are no ORDER BY expressions,
   170    192   all rows are peers. Rows that are peers always have the same window frames.
   171    193   
   172    194   <p> There are five options for frame boundaries:
   173    195   
   174    196   <table striped=1>
   175    197   <tr><th>Frame Boundary <th>Description
   176         -<tr><td>UNBOUNDED&nbsp;PRECEDING <td> This may only be used as a starting frame
   177         -        boundary. In which case the start of the frame is always the first
          198  +<tr><td>UNBOUNDED&nbsp;PRECEDING <td> The start of the frame is the first
   178    199           row in the set.
   179         -<tr><td>&lt;expr&gt; PRECEDING <td> &lt;expr&gt;, which may not refer to any
   180         -        table columns, must evaluate to a non-negative integer value. The start
          200  +<tr><td>&lt;expr&gt; PRECEDING <td> &lt;expr&gt; is a constant expression
          201  +        that evaluates to a non-negative integer value. The start
   181    202           or end of the frame is &lt;expr&gt; rows before the current row. "0
   182    203           PRECEDING" is the same as "CURRENT ROW". This frame boundary type may
   183    204           only be used with ROWS frames.
   184    205   <tr><td>CURRENT ROW <td> The current row. For RANGE frame types, all peers of
   185    206           the current row are also included in the window frame, regardless of
   186    207           whether CURRENT ROW is used as the starting or ending frame boundary.
   187         -<tr><td>&lt;expr&gt; FOLLOWING <td> &lt;expr&gt;, which may not refer to any
   188         -        table columns, must evaluate to a non-negative integer value. The start
          208  +<tr><td>&lt;expr&gt; FOLLOWING <td> &lt;expr&gt; is a constant expression
          209  +        that must evaluate to a non-negative integer value. The start
   189    210           or end of the frame is &lt;expr&gt; rows after the current row. "0
   190    211           FOLLOWING" is the same as "CURRENT ROW". This frame boundary type may
   191    212           only be used with ROWS frames.
   192         -<tr><td>UNBOUNDED&nbsp;FOLLOWING <td> This may only be used as an ending
   193         -        frame boundary. In which case the end of the frame is always the last
          213  +<tr><td>UNBOUNDED&nbsp;FOLLOWING <td> The end of the frame is the last
   194    214           row in the set.
   195    215   </table>
   196    216   
   197    217   <p> The ending frame boundary must not take a form that appears higher in
   198    218   the above list than the starting frame boundary.
   199    219   
   200    220   <p> In the following example, the window frame for each row consists of all
................................................................................
   218    238       ORDER BY c, a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   219    239     ) AS group_concat
   220    240     FROM t1 ORDER BY c, a;
   221    241   </codeblock>
   222    242   
   223    243   <h2>The PARTITION BY Clause</h2>
   224    244   
   225         -<p> A <i>&lt;window-definition&gt;</i> may also include a PARTITION BY clause.
          245  +<p> A <yynonterm>window-defn</yynonterm> may include a PARTITION BY clause.
   226    246   If so, the rows returned by the SELECT statement are divided into groups -
   227    247   partitions - with the same values for each PARTITION BY expression, and then
   228         -window-function processing performed as described above separately for each
   229         -partition. This is similar to the way the rows traversed by an aggregate query
   230         -are divided into groups before any aggregate processing is performed.
          248  +window-function processing is performed separately for each
          249  +partition. This is similar to the way the rows are grouped by the
          250  +GROUP BY clause of an aggregate query.
   231    251   
   232    252   <p> For example:
   233    253   
   234    254   <codeblock>
   235    255     <i>-- The following SELECT statement returns:</i>
   236    256     <i>-- </i>
   237    257     <i>--   c     | a | b | group_concat</i>
................................................................................
   248    268       PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
   249    269     ) AS group_concat
   250    270     FROM t1 ORDER BY c, a;
   251    271   </codeblock>
   252    272   
   253    273   <h2>The FILTER Clause</h2>
   254    274   
          275  +<tcl>
          276  +RecursiveBubbleDiagram filter
          277  +</tcl>
          278  +
   255    279   <p>If a FILTER clause is provided, then only rows for which the <i>expr</i> is
   256    280   true are included in the window frame. The aggregate window still returns a
   257    281   value for every row, but those for which the FILTER expression evaluates to
   258    282   other than true are not included in the window frame for any row. For example:
   259    283   
   260    284   <codeblock>
   261    285     <i>-- The following SELECT statement returns:</i>
................................................................................
   286    310   <p> Built-in window functions honor any PARTITION BY clause in the same way
   287    311   as aggregate window functions - each selected row is assigned to a partition 
   288    312   and each partition is processed separately. The ways in which any ORDER BY
   289    313   clause affects each built-in window function is described below. Some of
   290    314   the window functions (rank(), dense_rank(), percent_rank() and ntile()) use
   291    315   the concept of "peer groups" (rows within the same partition that have the
   292    316   same values for all ORDER BY expressions). In these cases, it does not matter
   293         -whether the <i>&lt;frame-specification&gt;</i> specifies ROWS or RANGE - for
          317  +whether the <yynonterm>frame-spec</yynonterm> specifies ROWS or RANGE - for
   294    318   the purposes of built-in window function processing, rows with the same values
   295    319   for all ORDER BY expressions are considered peers regardless of the frame type.
   296    320   
   297    321   <p> Most built-in window functions ignore the
   298         -<i>&lt;frame-specification&gt;</i>, the exceptions being first_value(),
          322  +<yynonterm>frame-spec</yynonterm>, the exceptions being first_value(),
   299    323   last_value() and nth_value(). It is a syntax error to specify a FILTER 
   300    324   clause as part of a built-in window function invocation.
   301    325   
   302    326   <p> SQLite supports the following 11 built-in window functions:
   303    327   
   304    328   <dl>
   305    329     <dt><p><b>row_number()</b>
................................................................................
   446    470            ntile(2) OVER win        AS ntile_2,
   447    471            ntile(4) OVER win        AS ntile_4
   448    472     FROM t2
   449    473     WINDOW win AS (ORDER BY a);
   450    474   </codeblock>
   451    475   
   452    476   <p> The next example demonstrates lag(), lead(), first_value(), last_value()
   453         -and nth_value(). The <i>&lt;frame-specification&gt;</i> is ignored by both lag() and lead(), but respected by first_value(), last_value() and nth_value().
          477  +and nth_value(). The <yynonterm>frame-spec</yynonterm> is ignored by 
          478  +both lag() and lead(), but respected by first_value(), last_value()
          479  +and nth_value().
   454    480   
   455    481   <codeblock>
   456    482     <i>-- The following SELECT statement returns:</i>
   457    483     <i>-- </i>
   458    484     <i>--   b | lead | lag  | first_value | last_value | nth_value_3</i>
   459    485     -------------------------------------------------------------
   460    486     <i>--   A | C    | NULL | A           | A          | NULL       </i>

Changes to rawpages/sqlite.css.

   222    222     background: #fff;
   223    223     border: 1px solid #000;
   224    224     border-radius: 11px;
   225    225     padding-left: 4px;
   226    226     padding-right: 4px;
   227    227     line-height: 125%;
   228    228   }
          229  +.yynonterm {
          230  +  background: #fff;
          231  +  border: 1px solid #000;
          232  +  padding-left: 2px;
          233  +  padding-right: 2px;
          234  +  line-height: 125%;
          235  +}
   229    236   
   230    237   /* Container for an image */
   231    238   .imgcontainer img {
   232    239     max-height: 100%;
   233    240     max-width: 100%;
   234    241   }
   235    242   

Changes to search/hdom.tcl.

    92     92     
    93     93     # All inline tags.
    94     94     variable aInline
    95     95     foreach x {
    96     96       tt i b big small u
    97     97       em strong dfn code samp kbd var cite abbr acronym
    98     98       a img object br script map q sub sup span bdo
    99         -    input select textarea label button tcl yyterm
           99  +    input select textarea label button tcl yyterm yynonterm
   100    100     } { set aInline($x) 1 }
   101    101   
   102    102     # All self-closing tags (set below)
   103    103     variable aSelfClosing
   104    104   
   105    105     variable aContentChecker
   106    106     set aContentChecker(p)        HtmlInlineContent

Changes to wrap.tcl.

    83     83   # appropriate <a href=""> markup.
    84     84   #
    85     85   # Links to keywords within the same main file are resolved using
    86     86   # $::llink() if possible.  All other links and links that could
    87     87   # not be resolved using $::llink() are resolved using $::glink().
    88     88   # 
    89     89   proc hd_resolve_2ndpass {text} {
    90         -  regsub -all {<yyterm>} $text {<span class='yyterm'>} text
    91         -  regsub -all {</yyterm>} $text {</span>} text
           90  +  regsub -all {<(yy(non)?term)>} $text {<span class='\1'>} text
           91  +  regsub -all {</yy(non)?term>} $text {</span>} text
    92     92     regsub -all {\[(.*?)\]} $text \
    93     93         "\175; hd_resolve_one \173\\1\175; hd_puts \173" text
    94     94     eval "hd_puts \173$text\175"
    95     95   }
    96     96   proc hd_resolve_one {x} {
    97     97     if {[string is integer $x] || [string length $x]==1} {
    98     98       hd_puts \[$x\]