Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Remove the "expr FOLLOWING" case from frame-specs that only describe the starting boundary. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
5eff6feebcd4cdd7940cdfa571dd17a9 |
User & Date: | drh 2019-03-28 20:22:07.130 |
Context
2019-03-29
| ||
16:40 | Mention the ON parsing ambiguity of UPSERT on the UPSERT documentation page. (check-in: c3bcaaf1e5 user: drh tags: trunk) | |
2019-03-28
| ||
20:22 | Remove the "expr FOLLOWING" case from frame-specs that only describe the starting boundary. (check-in: 5eff6feebc user: drh tags: trunk) | |
15:23 | In the window-defn bubble diagram, change "existing-window-name" to "base-window-name" and make it an indentifier instead of a non-terminal. (check-in: 67d1075ce3 user: drh tags: trunk) | |
Changes
Changes to art/syntax/bubble-generator-data.tcl.
︙ | ︙ | |||
542 543 544 545 546 547 548 | {line expr FOLLOWING} {line UNBOUNDED FOLLOWING} } } {or {line UNBOUNDED PRECEDING} {line expr PRECEDING} {line CURRENT ROW} | < | 542 543 544 545 546 547 548 549 550 551 552 553 554 555 | {line expr FOLLOWING} {line UNBOUNDED FOLLOWING} } } {or {line UNBOUNDED PRECEDING} {line expr PRECEDING} {line CURRENT ROW} } } } { line {or nil {line EXCLUDE NO OTHERS} {line EXCLUDE CURRENT ROW} {line EXCLUDE GROUP} |
︙ | ︙ |
Changes to art/syntax/frame-spec.gif.
cannot compute difference between binary files
Changes to pages/windowfunctions.in.
︙ | ︙ | |||
494 495 496 497 498 499 500 | SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER ( ORDER BY a ) AS group_concat FROM t1 ORDER BY a; </codeblock> | < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < < | 494 495 496 497 498 499 500 501 502 503 504 505 506 507 | 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 builtins {built-in window functions} {built-ins}</tcl> <h1>Built-in Window Functions</h1> <p> As well as aggregate window functions, SQLite features a set of built-in window functions based on <a href=https://www.postgresql.org/docs/10/static/functions-window.html> those supported by PostgreSQL</a>. |
︙ | ︙ | |||
742 743 744 745 746 747 748 | first_value(b) OVER win AS first_value, last_value(b) OVER win AS last_value, nth_value(b, 3) OVER win AS nth_value_3 FROM t1 WINDOW win AS (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) </codeblock> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | | 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 | first_value(b) OVER win AS first_value, last_value(b) OVER win AS last_value, nth_value(b, 3) OVER win AS nth_value_3 FROM t1 WINDOW win AS (ORDER BY b ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) </codeblock> <tcl>hd_fragment wchaining {window chaining}</tcl> <h1>Window Chaining</h1> <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: <codeblock> SELECT group_concat(b, '.') OVER ( win ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) FROM t1 WINDOW win AS (PARTITION BY a ORDER BY c) </codeblock> <p> the window used by the group_concat() function is equivalent to "PARTITION BY a ORDER BY c ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW". In order to use window chaining, all of the following must be true: <ul> <li><p>The new window definition must not include a PARTITION BY clause. The PARTITION BY clause, if there is one, must be supplied by the base window specification. <li><p>If the base window has an ORDER BY clause, it is copied into the new window. In this case the new window must not specify an ORDER BY clause. If the base window has no ORDER BY clause, one may be specified as part of the new window definition. <li><p>The base window may not specify a frame specification. The frame specification can only be given in the new window specification. </ul> <p>The two fragments of SQL below are similar, but not entirely equivalent, as the latter will fail if the definition of window "win" contains a frame specification. <codeblock> SELECT group_concat(b, '.') OVER win ... SELECT group_concat(b, '.') OVER (win) ... </codeblock> <tcl>hd_fragment udfwinfunc {user-defined window functions} \ {application-defined window functions}</tcl> <h1>User-Defined Aggregate Window Functions</h1> <p> User-defined aggregate window functions may be created using the [sqlite3_create_window_function]() API. Implementing an aggregate window function is very similar to an ordinary aggregate function. Any user-defined aggregate window function may also be used as an ordinary aggregate. To implement a user-defined aggregate window function the application must supply four callback functions: |
︙ | ︙ |