Documentation Source Text

Check-in [1a7fda4c1f]
Login

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

Overview
Comment:Update the changes long to show the enhancements to window functions.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: 1a7fda4c1f8fbb29133807bf532fa0891bbf9abfc0643864826fabbc6dc9f0dc
User & Date: drh 2019-03-26 14:47:00.351
Context
2019-03-27
19:02
Fix problems in windowfunctions.in. (check-in: dac96e63bd user: dan tags: trunk)
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)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
︙︙
18
19
20
21
22
23
24








25
26
27
28
29
30
31

  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.








>
>
>
>
>
>
>
>








18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

  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.
︙︙
Changes to pages/windowfunctions.in.
︙︙
148
149
150
151
152
153
154

155
156
157
158
159
160
161

  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








>








148
149
150
151
152
153
154
155
156
157
158
159
160
161
162

  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
︙︙
322
323
324
325
326
327
328

329
330
331
332
333
334
335
336

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








>
|








323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338

  <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.
︙︙
372
373
374
375
376
377
378

379
380
381
382
383
384
385
386

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









>
|








374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389

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