Documentation Source Text

Check-in [dac96e63bd]
Login

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

Overview
Comment:Fix problems in windowfunctions.in.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: dac96e63bd98d15932e9476fd94ac1c2fcc150041bd1128e09baaa88bf3d894a
User & Date: dan 2019-03-27 19:02:38
Context
2019-03-28
00:46
Work on the window function documentation. check-in: 4b4ef30fa6 user: drh tags: trunk
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
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to pages/windowfunctions.in.

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
...
168
169
170
171
172
173
174





175
176
177
178
179
180
181
182
183
184


185
186
187
188
189
















190
191
192
193
194
195
196
...
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
previous row ("1 PRECEDING") and the following row ("1 FOLLOWING"), inclusive,
where rows are sorted according to the ORDER BY clause in the
<yynonterm>window-defn</yynonterm> (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> This means that, after sorting the rows returned by the SELECT according to
the ORDER BY clause in the <yynonterm>window-definition</yynonterm>,
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>--   a | b | c | group_concat</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) AS group_concat 
  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>

................................................................................
  <li> A starting frame boundary,
  <li> An ending frame boundary,
  <li> An EXCLUDE clause.
</ul>

<p>The ending frame boundary can be omitted, in which case it defaults
to CURRENT ROW.






<p>The default <yynonterm>frame-spec</yynonterm> is:

<codeblock>
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
</codeblock>

<p>The default means that aggregate window functions read all
rows from the beginning of the partition up to and including the
current row and its peers.



<p> If the frame type is RANGE or GROUPS, then rows with the same values for
all ORDER BY expressions are considered "peers". Or, if there are no ORDER BY
terms, 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> The start of the frame is the first
        row in the set.
................................................................................

<tr><td>CURRENT ROW <td> The current row. For RANGE and GROUPS 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; is a constant expression
        that must evaluate to a non-negative numeric value. It is handled
        in similarly to "&lt;expr&gt; PRECEDING".
<tr><td>UNBOUNDED&nbsp;FOLLOWING <td> The end of the frame is the last
        row in the set.
</table>

<p> The ending frame boundary must not take a form that appears higher in
the above list than the starting frame boundary.








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







 







>
>
>
>
>









|
>
>

<
<
|
|
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 







|







117
118
119
120
121
122
123



























124
125
126
127
128
129
130
...
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
177
178
179
180
181
182
183
184
185
186
187
188
189
190
...
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
previous row ("1 PRECEDING") and the following row ("1 FOLLOWING"), inclusive,
where rows are sorted according to the ORDER BY clause in the
<yynonterm>window-defn</yynonterm> (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> 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>

................................................................................
  <li> A starting frame boundary,
  <li> An ending frame boundary,
  <li> An EXCLUDE clause.
</ul>

<p>The ending frame boundary can be omitted, in which case it defaults
to CURRENT ROW.

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

<p>The default <yynonterm>frame-spec</yynonterm> is:

<codeblock>
    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
</codeblock>

<p>The default means that aggregate window functions read all
rows from the beginning of the partition up to and including the
current row and its peers. 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>--   a | b | c | group_concat</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) AS group_concat 
  FROM t1 ORDER BY a;
</codeblock>


<p> There are five options for frame boundaries:

<table striped=1>
<tr><th>Frame Boundary <th>Description
<tr><td>UNBOUNDED&nbsp;PRECEDING <td> The start of the frame is the first
        row in the set.
................................................................................

<tr><td>CURRENT ROW <td> The current row. For RANGE and GROUPS 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; is a constant expression
        that must evaluate to a non-negative numeric value. It is handled
        similarly to "&lt;expr&gt; PRECEDING".
<tr><td>UNBOUNDED&nbsp;FOLLOWING <td> The end of the frame is the last
        row in the set.
</table>

<p> The ending frame boundary must not take a form that appears higher in
the above list than the starting frame boundary.