Re: [sqlite] SQL:2003 -- Window Functions

[Eleytherios Stamatogiannakis]

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
 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

Re: [sqlite] SQL:2003 -- Window Functions

[Gabor Grothendieck]

On Wed, Sep 19, 2012 at 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.
>

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

[Igor Tandetnik]

Mohd Radzi Ibrahim  wrote:
> On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik wrote:
> 
>> 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

Re: [sqlite] SQL:2003 -- Window Functions

[Keith Medcalf]

> > 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

[Pavel Ivanov]

On Wed, Sep 19, 2012 at 7:10 PM, Mohd Radzi Ibrahim  wrote:
> On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik wrote:
>
>> 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

[Mohd Radzi Ibrahim]

On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik wrote:

> 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-**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

[Igor Tandetnik]

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