About storing the whole result set: Note that in postgresql-derivatives
(and Oracle? maybe Teradata?), this is valid:
select lag(a0, a0) over () from a;
whereas many other servers (which I won't name) require the second
argument of lag() to be constant. If it is constant (even in
postgresql-derivatives), a better window bound than the implicit
"unbounded preceding" can be computed by the optimizer, and this may be
essential for an efficient implementation of the most common case,
because then the window buffer (per-group) can be guaranteed to fit into
(pre-allocated) RAM.
S.
On 04/26/2018 07:44 AM, Hick Gunter wrote:
"Window functions" aka "scrollable cursors" require that the whole result set
ist stored somewhere. Or at least the current position of all of the tables/indices involved in
producing it, provided that such positions are settable. You then need to keep track of how many
records need to be produced to satisfy the window position.
If you really need scrollable cursors, you are free to implement them, maybe by
CREATE TEMP TABLE query_results AS SELECT...
Followed by
SELECT * FROM query_results WHERE rowid BETWEEN ?start AND ?end;
And cleaning up with
DROP TABLE query_results;
Note that you will be producing the complete result set before you are able to
return even only the first row. This defeats any gain from having the query
return results in the desired order without requiring a sort, i.e. choosing the
indices that makes the desired order coincide with the natural order.
-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Charles Leifer
Gesendet: Mittwoch, 25. April 2018 21:05
An: General Discussion of SQLite Database <sqlite-users@mailinglists.sqlite.org>
Betreff: [EXTERNAL] [sqlite] Window functions
Hi,
I'm sure this has been asked before, but are window functions on the roadmap?
Is it the authors' experience that the implementation would significantly
complicate sqlite? Just curious. Thanks so much for a fantastic library.
Charlie
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___________________________________________
Gunter Hick | Software Engineer | Scientific Games International GmbH |
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43
1 80100 - 0
May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
