Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
| Comment: | Improve the example of how to create a new aggregate window function in windowfunctions.in. |
|---|---|
| Downloads: | Tarball | ZIP archive |
| Timelines: | family | ancestors | descendants | both | trunk |
| Files: | files | file ages | folders |
| SHA3-256: |
873c1ea4b70a2dc29b5354c853909d55 |
| User & Date: | dan 2018-06-26 17:48:46.425 |
Context
|
2018-06-30
| ||
| 20:23 | Create a change log for version 3.25.0 (check-in: 5697c09027 user: drh tags: trunk) | |
|
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) | |
Changes
Changes to pages/windowfunctions.in.
| ︙ | ︙ | |||
619 620 621 622 623 624 625 626 |
return sqlite3_create_window_function(db, "sumint", 1, SQLITE_UTF8, 0,
sumintStep, sumintFinal, sumintValue, sumintInverse, 0
);
}
}
</tcl>
</codeblock>
| > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 |
return sqlite3_create_window_function(db, "sumint", 1, SQLITE_UTF8, 0,
sumintStep, sumintFinal, sumintValue, sumintInverse, 0
);
}
}
</tcl>
</codeblock>
<p> The following example uses the sumint() function implemented by the above
C code. For each row, the window consists of the preceding row (if any), the current row and the following row (again, if any):
<codeblock>
CREATE TABLE t3(x, y);
INSERT INTO t3 VALUES('a', 4),
('b', 5),
('c', 3),
('d', 8),
('e', 1);
<i>-- Assuming the database is populated using the above script, the </i>
<i>-- following SELECT statement returns:</i>
<i>-- </i>
<i>-- x | sum_y</i>
--------------
<i>-- a | 9 </i>
<i>-- b | 12 </i>
<i>-- c | 16 </i>
<i>-- d | 12 </i>
<i>-- e | 9 </i>
<i>-- </i>
SELECT x, sumint(y) OVER (
ORDER BY x ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS sum_y
FROM t3 ORDER BY x;
</codeblock>
<p>In processing the query above, SQLite invokes the sumint callbacks as
follows:
<p>
<ol>
<li> <b>xStep(4)</b> - add "4" to the current window.
<li> <b>xStep(5)</b> - add "5" to the current window.
<li> <b>xValue()</b> - invoke xValue() to obtain the value of sumint() for
the row with (x='a'). The window currently consists of values 4 and 5,
and so the result is 9.
<li> <b>xStep(3)</b> - add "3" to the current window.
<li> <b>xValue()</b> - invoke xValue() to obtain the value of sumint() for
the row with (x='b'). The window currently consists of values 4, 5 and
3, and so the result is 12.
<li> <b>xInverse(4)</b> - remove "4" from the window.
<li> <b>xStep(8)</b> - add "8" to the current window. The window now consists
of values 5, 3 and 8.
<li> <b>xValue()</b> - invoked to obtain the value for the row with (x='c').
In this case, 16.
<li> <b>xInverse(5)</b> - remove value "5" from the window.
<li> <b>xStep(1)</b> - add value "1" to the window.
<li> <b>xValue()</b> - invoked to obtain the value for row (x='d').
<li> <b>xInverse(3)</b> - remove value "3" from the window. The window now
contains values 8 and 1 only.
<li> <b>xValue()</b> - invoked to obtain the value for row (x='d'). 9.
</ol>
|