Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Fix typos in the windowfunction documentation. |
|---|---|
| Downloads: | Tarball | ZIP archive | SQL archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
ab3b52646c49d4daa9c1cbc7c4862089 |
| User & Date: | drh 2018-08-06 01:42:43 |
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
Changes to pages/windowfunctions.in.
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 .. 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 ... 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 |
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. ................................................................................ <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), ................................................................................ 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> |
| | | | | |
10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 .. 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 ... 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 |
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. ................................................................................ <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), ................................................................................ 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> |