Of course you can trade memory space for code complexity/size/speed (see my reference to "current position" being re-settable). Neither is available on embedded systems. Which would make the code another #define to be tested (correct results with and without the optional code), and supported from then on to eternity.
-----Ursprüngliche Nachricht----- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Shevek Gesendet: Donnerstag, 26. April 2018 10:36 An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: Re: [sqlite] [EXTERNAL] Window functions 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 ___________________________________________ 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
