Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add documentation for implementing new aggregate window functions. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA3-256: |
b5a81b3bdf63c0fd0eba42ef6091f825 |
User & Date: | dan 2018-06-25 20:35:46.845 |
Context
2018-06-26
| ||
17:48 | Improve the example of how to create a new aggregate window function in windowfunctions.in. (check-in: 873c1ea4b7 user: dan tags: trunk) | |
2018-06-25
| ||
20:35 | Add documentation for implementing new aggregate window functions. (check-in: b5a81b3bdf user: dan tags: trunk) | |
2018-06-22
| ||
16:14 | Add further examples to windowfunctions.in. (check-in: 84418fef8d user: dan tags: trunk) | |
Changes
Changes to pages/windowfunctions.in.
︙ | ︙ | |||
74 75 76 77 78 79 80 | <b>win2</b> AS (PARTITION BY y ORDER BY x) ORDER BY x; </codeblock> <p>The WINDOW clause, when one is present, comes after any HAVING clause and before any ORDER BY. | | | 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | <b>win2</b> AS (PARTITION BY y ORDER BY x) ORDER BY x; </codeblock> <p>The WINDOW clause, when one is present, comes after any HAVING clause and before any ORDER BY. <h1 tags="aggregate window functions">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' ), |
︙ | ︙ | |||
271 272 273 274 275 276 277 | <i>-- one | 7 | G | A.C.D.F.G </i> <i>-- </i> SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER ( ORDER BY a ) AS group_concat FROM t1 ORDER BY a; </codeblock> | < < < | 271 272 273 274 275 276 277 278 279 280 281 282 283 284 | <i>-- one | 7 | G | A.C.D.F.G </i> <i>-- </i> SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER ( ORDER BY a ) AS group_concat FROM t1 ORDER BY a; </codeblock> <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>. |
︙ | ︙ | |||
473 474 475 476 477 478 479 | lag(b) OVER win AS lag, 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> | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 | lag(b) OVER win AS lag, 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> <h1 tags="user-defined window functions">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: <table striped=1> <tr><th>Callback <th>Description <tr><td>xStep <td> This method is required by both window aggregate and legacy aggregate function implementations. It is invoked to add a row to the current window. The function arguments, if any, corresponding to the row being added are passed to the implementation of xStep. <tr><td>xFinal <td> This method is required by both window aggregate and legacy aggregate function implementations. It is invoked to return the current value of the aggregate (determined by the contents of the current window), and to free any resources allocated by earlier calls to xStep. <tr><td>xValue <td> This method is only required window aggregate functions, not legacy aggregate function implementations. It is invoked to return the current value of the aggregate. Unlike xFinal, the implementation should not delete any context. <tr><td>xInverse <td> This method is only required window aggregate functions, not legacy aggregate function implementations. It is invoked to remove a row from the current window. The function arguments, if any, correspond to the row being removed. </table> <p> The C code below implements a simple window aggregate function named sumint(). This works in the same way as the built-in sum() function, except that it throws an exception if passed an argument that is not an integer value. <codeblock> <tcl> proc C_Code {text} { hd_puts "<pre>\n" set iLine 0 foreach zLine [split [string trim $text "\n"] "\n"] { regsub {^ } $zLine {} zLine set zSubspec {<i>&</i>} regsub {(/\*|\*\*|\*/).*} $zLine $zSubspec zLine if {[regexp {(.*)(sqlite3[a-z0-9_]*)(.*)} $zLine -> one two three]} { hd_puts $one hd_resolve "\[$two\]" hd_puts $three } else { hd_puts $zLine } hd_puts "\n" } hd_puts "</pre>\n" } C_Code { /* ** xStep for sumint(). ** ** Add the value of the argument to the aggregate context (an integer). */ static void sumintStep( sqlite3_context *ctx, int nArg, sqlite3_value *apArg[] ){ sqlite3_int64 *pInt; assert( nArg==1 ); if( sqlite3_value_type(apArg[0])!=SQLITE_INTEGER ){ sqlite3_result_error(ctx, "invalid argument", -1); return; } pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64)); if( pInt ){ *pInt += sqlite3_value_int64(apArg[0]); } } /* ** xInverse for sumint(). ** ** This does the opposite of xStep() - subtracts the value of the argument ** from the current context value. The error checking can be omitted from ** this function, as it is only ever called after xStep() (so the aggregate ** context has already been allocated) and with a value that has already ** been passed to xStep() without error (so it must be an integer). */ static void sumintInverse( sqlite3_context *ctx, int nArg, sqlite3_value *apArg[] ){ sqlite3_int64 *pInt; assert( sqlite3_value_type(apArg[0])==SQLITE_INTEGER ); pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, sizeof(sqlite3_int64)); *pInt -= sqlite3_value_int64(apArg[0]); } /* ** xFinal for sumint(). ** ** Return the current value of the aggregate window function. Because ** this implementation does not allocate any resources beyond the buffer ** returned by sqlite3_aggregate_context, which is automatically freed ** by the system, there are no resources to free. And so this method is ** identical to xValue(). */ static void sumintFinal(sqlite3_context *ctx){ sqlite3_int64 res = 0; sqlite3_int64 *pInt; pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0); if( pInt ) res = *pInt; sqlite3_result_int64(ctx, res); } /* ** xValue for sumint(). ** ** Return the current value of the aggregate window function. Because */ static void sumintValue(sqlite3_context *ctx){ sqlite3_int64 res = 0; sqlite3_int64 *pInt; pInt = (sqlite3_int64*)sqlite3_aggregate_context(ctx, 0); if( pInt ) res = *pInt; sqlite3_result_int64(ctx, res); } /* ** Register sumint() window aggregate with database handle db. */ int register_sumint(sqlite3 *db){ return sqlite3_create_window_function(db, "sumint", 1, SQLITE_UTF8, 0, sumintStep, sumintFinal, sumintValue, sumintInverse, 0 ); } } </tcl> </codeblock> |