Re: [sqlite] SQL:2003 -- Window Functions
On Wed, Sep 19, 2012 at 12:51 PM, joe.fis...@tanguaylab.com joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. SQL Window Functions is the number one feature that I could use as well. In R, sqlite can be used for manipulating R data frames via the sqldf package and this sort of functionality would be very useful. (sqldf also handles PostgreSQL which does have windowing functions but PostgreSQL requires more set up than sqlite so its not as accessible to users.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
While i haven't needed this functionality, i have been thinking about how it could be solved in a performant way. IMHO, the solution would be to create a virtual table that takes, for example, as input a query that produces the following rows: C1 | C2 a1 | b1 a2 | b2 a3 | b3 and outputs the following: C1 | C2 | C1prev | C2prev -- a1 | b1 | null | null a2 | b2 | a1 | b1 a3 | b3 | a2 | b2 So in essence the window gets transformed to a single row. In a similar way, bigger windows could also be transformed. l. On 20/09/12 14:46, Gabor Grothendieck wrote: On Wed, Sep 19, 2012 at 12:51 PM, joe.fis...@tanguaylab.com joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. SQL Window Functions is the number one feature that I could use as well. In R, sqlite can be used for manipulating R data frames via the sqldf package and this sort of functionality would be very useful. (sqldf also handles PostgreSQL which does have windowing functions but PostgreSQL requires more set up than sqlite so its not as accessible to users.) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] SQL:2003 -- Window Functions
Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. Joe Fisher Oregon State University ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. The easiest approach is to maintain the window in your application code, as you iterate over a simple SELECT statement. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik itandet...@mvps.orgwrote: On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. The easiest approach is to maintain the window in your application code, as you iterate over a simple SELECT statement. -- Igor Tandetnik Could it not be done with inner select of ROWID-n and ROWID+n to get the LEAD and LAG row ? select logtime as timeNow, (select logtime from logtable where rowid=a.rowid-1) as timeBefore, (select logtime from logtable where rowid=a.rowid+1) as timeAfter from logtime; best regards, Radzi. __**_ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**usershttp://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
On Wed, Sep 19, 2012 at 7:10 PM, Mohd Radzi Ibrahim imra...@gmail.com wrote: On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik itandet...@mvps.orgwrote: On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. The easiest approach is to maintain the window in your application code, as you iterate over a simple SELECT statement. -- Igor Tandetnik Could it not be done with inner select of ROWID-n and ROWID+n to get the LEAD and LAG row ? select logtime as timeNow, (select logtime from logtable where rowid=a.rowid-1) as timeBefore, (select logtime from logtable where rowid=a.rowid+1) as timeAfter from logtime; This will work only if the logtime table has consecutive rowids which is almost never the case. Pavel ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
Could it not be done with inner select of ROWID-n and ROWID+n to get the LEAD and LAG row ? select logtime as timeNow, (select logtime from logtable where rowid=a.rowid-1) as timeBefore, (select logtime from logtable where rowid=a.rowid+1) as timeAfter from logtime; This will work only if the logtime table has consecutive rowids which is almost never the case. create temporary table tlogtime as select logtime from logtime order by rowid; select logtime as timeNow, (select logtime from tlogtable where rowid=a.rowid-1) as timeBefore, (select logtime from tlogtable where rowid=a.rowid+1) as timeAfter from tlogtime as a; drop temp.tlogtime; That is, create a temporary table with the data correctly ordered you want in sequentially numbered rowid's, then the correlated subqueries will work. Performance will be entirely dependant on how many rows you are dealing with in the temp table. --- () ascii ribbon campaign against html e-mail /\ www.asciiribbon.org ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL:2003 -- Window Functions
Mohd Radzi Ibrahim imra...@gmail.com wrote: On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik itandet...@mvps.orgwrote: On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote: Too bad SQLite doesn't yet support SQL Window Functions. Are there any SQLite Extension Libraries that support SQL:2003 type Window Functions? I specifically need LEAD and LAG to calculate an event integer timestamp delta between consecutive rows. I've played with some self-join code but that's proving to be complicated. The easiest approach is to maintain the window in your application code, as you iterate over a simple SELECT statement. Could it not be done with inner select Yes it could, and in fact the OP has stated that he played with some self-join but was unhappy with the approach. Such complicated queries also tend to run noticeably slower than a straightforward linear pass. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users