Documentation Source Text

Check-in [c1e5cf0cb1]
Login

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

Overview
Comment:Add beginning of documentation for window functions in new file windowfunctions.in.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:c1e5cf0cb1617822e0bd6bad410df943da38a22ec88f35b5689f4b5d574bf7ba
User & Date: dan 2018-06-20 21:13:04
Context
2018-06-21
21:00
Improve windowfunctions.in. check-in: 7094fcac78 user: dan tags: trunk
2018-06-20
21:13
Add beginning of documentation for window functions in new file windowfunctions.in. check-in: c1e5cf0cb1 user: dan tags: trunk
2018-06-08
13:41
Fix the -auth redirect logic to avoid duplicating part of the URL. check-in: 76396bf1df user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Added pages/windowfunctions.in.

































































































































































































































































































































































>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
<tcl>hd_keywords {window functions}</tcl>

<title>SQLite SQL Window Function Support</title>
<table_of_contents>

<h2 style="margin-left:1.0em" notoc id=overview> Overview</h2>

<h1>Introduction to Window Functions</h1>

<p>A window function is a special type of SQL function for which the results
depend on the contents of a "window" of one or more contiguous rows returned
by the SELECT statement containing the window function. A window function may
only be used in the select-list or ORDER BY clause of a SELECT or sub-select
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
syntax is:

<codeblock>
  &lt;window-function&gt;     := &lt;function&gt;(&lt;args...&gt;) &#91;&lt;filter&gt;] OVER (&lt;window-definition&gt;)

  &lt;filter&gt;              := FILTER (WHERE &lt;expr&gt;)

  &lt;window-definition&gt;   := &#91;PARTITION BY &lt;expression-list&gt;] &#91;ORDER BY &lt;expression-list&gt;] &#91;&lt;frame-specification&gt;]

  &lt;frame-specification&gt; := ROWS|RANGE BETWEEN &lt;frame-boundary&gt; AND &lt;frame-boundary&gt;

  &lt;frame-boundary&gt;      := UNBOUNDED PRECEDING
  &lt;frame-boundary&gt;      := &lt;expr&gt; PRECEDING
  &lt;frame-boundary&gt;      := CURRENT ROW
  &lt;frame-boundary&gt;      := &lt;expr&gt; FOLLOWING
  &lt;frame-boundary&gt;      := UNBOUNDED FOLLOWING
</codeblock>

<p>For example, given:

<codeblock>
  CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
  INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');
</codeblock>

<p>Then:
<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
  <i>--     1|aaa|1</i>
  <i>--     2|ccc|3</i>
  <i>--     3|bbb|2</i>
  <i>-- </i>
  SELECT x, y, row_number() OVER (ORDER BY y) FROM t0 ORDER BY x;
</codeblock>

<p>The example above uses the special built-in window function row_number().
This function returns a monotonically increasing integer assigned to each
row in order of the the "ORDER BY" clause within the
<i>&lt;window-definition&gt;</i> (in this case "ORDER BY y"). Note that 
this does not affect the order in which results are returned to the user -
that is still governed by the ORDER BY clause attached to the SELECT
statement (in this case "ORDER BY x").

<h1>Aggregate Window Functions</h1>

<p> The examples in this section all assume that the database is populated as
follows:

<codeblock>
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
  INSERT INTO t1 VALUES   (1, 'A', 'one'  ),
                          (2, 'B', 'two'  ),
                          (3, 'C', 'three'),
                          (4, 'D', 'one'  ),
                          (5, 'E', 'two'  ),
                          (6, 'F', 'three'),
                          (7, 'G', 'one'  );
</codeblock>

<p> An aggregate window function is similar to an aggregate function, except
adding it to a query does not change the number of rows returned. Instead,
for each row the result of the aggregate window function is as if the
corresponding aggregate were run over all rows in the "window frame".

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
  <i>--     1|A|A.B</i>
  <i>--     2|B|A.B.C</i>
  <i>--     3|C|B.C.D</i>
  <i>--     4|D|C.D.E</i>
  <i>--     5|E|D.E.F</i>
  <i>--     6|F|E.F.G</i>
  <i>--     7|G|F.G</i>
  <i>-- </i>
  SELECT a, b, group_concat(b, '.') OVER (
    ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) FROM t1;
</codeblock>

<p> In the example above, the window frame consists of all rows between the
previous row ("1 PRECEDING") and the following row ("1 FOLLOWING"), inclusive,
where rows are sorted according to the ORDER BY clause in the
<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'.

<p> The default &lt;frame-specification&gt; is:

<codeblock>
    RANGE BETWEEN UNBOUNDED PRECEDING TO CURRENT ROW
</codeblock>

<p> This means that, after sorting the rows returned by the SELECT according to
the ORDER BY clause in the &lt;window-definition&gt;, the window frame consists
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:

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
  <i>--     1|A|one|A.D.G</i>
  <i>--     2|B|two|A.D.G.C.F.B.E</i>
  <i>--     3|C|three|A.D.G.C.F</i>
  <i>--     4|D|one|A.D.G</i>
  <i>--     5|E|two|A.D.G.C.F.B.E</i>
  <i>--     6|F|three|A.D.G.C.F</i>
  <i>--     7|G|one|A.D.G</i>
  <i>-- </i>
  SELECT a, b, c, group_concat(b, '.') OVER (ORDER BY c) FROM t1 ORDER BY a;
</codeblock>

<h2>Frame Specifications</h2>

<p> A window frame specification consists of three things:

<ul>
  <li> A frame type - either RANGE or ROWS.
  <li> A starting frame boundary, and
  <li> An ending frame boundary.
</ul>

<p> If the frame type is RANGE, then rows with the same values for all ORDER BY
expressions are considered "peers". Or, if there are no ORDER BY expressions,
all rows are peers. Rows that are peers always have the same window frames.

<p> There are five options for frame boundaries:

<table striped=1>
<tr><th>Frame Boundary <th>Description
<tr><td>UNBOUNDED&nbsp;PRECEDING <td> This may only be used as a starting frame
        boundary. In which case the start of the frame is always the first
        row in the set.
<tr><td>&lt;expr&gt; PRECEDING <td> &lt;expr&gt;, which may not refer to any
        table columns, must evaluate to a non-negative integer value. The start
        or end of the frame is &lt;expr&gt; rows before the current row. "0
        PRECEDING" is the same as "CURRENT ROW". This frame boundary type may
        only be used with ROWS frames.
<tr><td>CURRENT ROW <td> The current row. For RANGE frame types, all peers of
        the current row are also included in the window frame, regardless of
        whether CURRENT ROW is used as the starting or ending frame boundary.
<tr><td>&lt;expr&gt; FOLLOWING <td> &lt;expr&gt;, which may not refer to any
        table columns, must evaluate to a non-negative integer value. The start
        or end of the frame is &lt;expr&gt; rows after the current row. "0
        FOLLOWING" is the same as "CURRENT ROW". This frame boundary type may
        only be used with ROWS frames.
<tr><td>UNBOUNDED&nbsp;FOLLOWING <td> This may only be used as an ending
        frame boundary. In which case the end of the frame is always the last
        row in the set.
</table>


<h2>The PARTITION BY Clause</h2>

<h2>The FILTER Clause</h2>

<h2>Adding New Aggregate Window Functions</h2>

<p>
Link to C API docs.

<h1>Built-in Window Functions</h1>