Documentation Source Text

Check-in [ab3b52646c]
Login

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

Overview
Comment:Fix typos in the windowfunction documentation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256: ab3b52646c49d4daa9c1cbc7c4862089ec18b39ec0389058081c119b7f9da4da
User & Date: drh 2018-08-06 01:42:43.913
Context
2018-08-08
17:36
Fixes to the CGI handling in althttpd.c. (check-in: 6faf1e47b9 user: drh tags: trunk)
2018-08-06
01:42
Fix typos in the windowfunction documentation. (check-in: ab3b52646c user: drh tags: trunk)
2018-08-01
07:08
In althttpd.c, make sure all file descriptors other than 0, 1, and 2 are closed prior to launching CGI. (check-in: c84d76df63 user: drh tags: trunk)
Changes
Unified Diff Show Whitespace Changes Patch
Changes to pages/windowfunctions.in.
︙︙
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

a "window" of one or more rows in the results set of a SELECT statement.


<tcl>
RecursiveBubbleDiagram window-function-invocation
</tcl>

<p>Window functions are distingished from ordinary SQL functions by the
presence of an OVER clause.  If a function invocation has an OVER clause
then it is a window function, and if lacks a OVER clause it is an ordinary
function.  Window functions might also have a FILTER
clause in between the function and the OVER clause.

<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.








|








10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

a "window" of one or more rows in the results set of a SELECT statement.


<tcl>
RecursiveBubbleDiagram window-function-invocation
</tcl>

<p>Window functions are distinguished from ordinary SQL functions by the
presence of an OVER clause.  If a function invocation has an OVER clause
then it is a window function, and if lacks a OVER clause it is an ordinary
function.  Window functions might also have a FILTER
clause in between the function and the OVER clause.

<p>Unlike ordinary functions, window functions
cannot use the DISTINCT keyword.
︙︙
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

  <i>-- </i>
  SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;
</codeblock>

<p>The example uses the built-in window function row_number().
The row_number() window function
assigns a monotonically increasing integer to each
row in order of the the "ORDER BY" clause within the
<yynonterm>window-defn</yynonterm> (in this case "ORDER BY y"). Note that 
this does not affect the order in which results are returned from
the overall query.  The order of the final output is
is still governed by the ORDER BY clause attached to the SELECT
statement (in this case "ORDER BY x").

<p>Named <yynonterm>window-defn</yynonterm> clauses may also be added to a SELECT 
statement using a WINDOW clause and then refered to by name within window
function invocations. For example, the following SELECT statement contains
two named <yynonterm>window-defs</yynonterm> clauses, "win1" and "win2":

<codeblock>
  SELECT x, y, row_number() OVER <b>win1</b>, rank() OVER <b>win2</b> 
  FROM t0 
  WINDOW <b>win1</b> AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),








|



|



|








46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68

  <i>-- </i>
  SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x;
</codeblock>

<p>The example uses the built-in window function row_number().
The row_number() window function
assigns a monotonically increasing integer to each
row in order of the "ORDER BY" clause within the
<yynonterm>window-defn</yynonterm> (in this case "ORDER BY y"). Note that 
this does not affect the order in which results are returned from
the overall query.  The order of the final output is
still governed by the ORDER BY clause attached to the SELECT
statement (in this case "ORDER BY x").

<p>Named <yynonterm>window-defn</yynonterm> clauses may also be added to a SELECT 
statement using a WINDOW clause and then referred to by name within window
function invocations. For example, the following SELECT statement contains
two named <yynonterm>window-defs</yynonterm> clauses, "win1" and "win2":

<codeblock>
  SELECT x, y, row_number() OVER <b>win1</b>, rank() OVER <b>win2</b> 
  FROM t0 
  WINDOW <b>win1</b> AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
︙︙
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452

         dense_rank() OVER win    AS dense_rank,
         percent_rank() OVER win  AS percent_rank,
         cume_dist() OVER win     AS cume_dist
  FROM t2
  WINDOW win AS (ORDER BY a);
</codeblock>

<p>The example below uses ntile() to divde the six rows into two groups (the
ntile(2) call) and into four groups (the ntile(4) call). For ntile(2), there
are three rows assigned to each group. For ntile(4), there are two groups of
two and two groups of one. The larger groups of two appear first.

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>








|








438
439
440
441
442
443
444
445
446
447
448
449
450
451
452

         dense_rank() OVER win    AS dense_rank,
         percent_rank() OVER win  AS percent_rank,
         cume_dist() OVER win     AS cume_dist
  FROM t2
  WINDOW win AS (ORDER BY a);
</codeblock>

<p>The example below uses ntile() to divide the six rows into two groups (the
ntile(2) call) and into four groups (the ntile(4) call). For ntile(2), there
are three rows assigned to each group. For ntile(4), there are two groups of
two and two groups of one. The larger groups of two appear first.

<codeblock>
  <i>-- The following SELECT statement returns:</i>
  <i>-- </i>
︙︙