Window functions in sqlite 3.26.0

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Window functions in sqlite 3.26.0

Balaji Ramanathan
Hi,

    Are window functions enabled by default in the sqlite command line
shell program that is available for download on the sqlite website?  I get
the error message "no such function: rownumber()" when I try to use that
window function.  The documentation only mentions that window functions
were added in version 3.25.0, and does not give any indication as to
whether they are enabled or disabled in the command line tool.  Is there
perhaps a pragma setting I have to turn on for window functions to work?
Thank you.

Balaji Ramanathan
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions in sqlite 3.26.0

Dan Kennedy-4
On 12/24/2018 01:43 AM, Balaji Ramanathan wrote:
> Hi,
>
>     Are window functions enabled by default in the sqlite command line
> shell program that is available for download on the sqlite website?  I get
> the error message "no such function: rownumber()" when I try to use that
> window function.


They are in 3.26.0. Try "row_number", with an underscore. Or, if that's
not the problem, please post the failing SQL statement.

Thanks,
Dan.



The documentation only mentions that window functions

> were added in version 3.25.0, and does not give any indication as to
> whether they are enabled or disabled in the command line tool.  Is there
> perhaps a pragma setting I have to turn on for window functions to work?
> Thank you.
>
> Balaji Ramanathan
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions in sqlite 3.26.0

Balaji Ramanathan
In reply to this post by Balaji Ramanathan
Thank you, Dan.  My eyes saw the underscore, but my brain did not process
it!

And many thanks to the SQLite development team for introducing this feature
and making it so efficient.  I had the need to compute running sums and
averages in a query, and I have views which use regular queries and
recursive queries.  The recursive queries perform better, but still take
about 10 minutes to process the data.  I replaced them with queries using
window functions, and the results came back in under a second.  Amazing!

For a related question:  I am trying to calculate a percentile score using
a query like below:

select ID,
(count(Quantity) over Win1) *100.0/count(ID) as Percentile,
from myTable
Window Win1 as (order by Quantityrange between unbounded preceding and
current row)

This gives me the error:  misuse of aggregate: count().

So, I replaced it with the following:

select ID,
(count(Quantity) over Win1) *100.0/(count(ID) over Win2) as Percentile,
from myTable
Window Win1 as (order by Quantity range between unbounded preceding and
current row),
Win2 as (order by ID range between unbounded preceding and unbounded
following)

This works, but use of a window just to get the total count of ID's using a
range "between unbounded preceding and unbounded following" just seems
wrong!  Is there a simpler construct I am missing?  Thank you.

Balaji Ramanathan


> From: Dan Kennedy <[hidden email]>
> To: [hidden email]
> Cc:
> Bcc:
> Date: Mon, 24 Dec 2018 14:40:58 +0700
> Subject: Re: [sqlite] Window functions in sqlite 3.26.0
> On 12/24/2018 01:43 AM, Balaji Ramanathan wrote:
> > Hi,
> >
> >     Are window functions enabled by default in the sqlite command line
> > shell program that is available for download on the sqlite website?  I
> get
> > the error message "no such function: rownumber()" when I try to use that
> > window function.
>
>
> They are in 3.26.0. Try "row_number", with an underscore. Or, if that's
> not the problem, please post the failing SQL statement.
>
> Thanks,
> Dan.
>
>
>
>
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Window functions in sqlite 3.26.0

Keith Medcalf

You can always turn your 100/count(ID) into a scalar expression (so that it is only calculated once):

select ID,
(count(Quantity) over Win1) * (select 100.0/count(ID) from mytable) as Percentile
from myTable
Window Win1 as (order by Quantity range between unbounded preceding and current row);


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Balaji Ramanathan
>Sent: Tuesday, 25 December, 2018 12:24
>To: [hidden email]
>Subject: Re: [sqlite] Window functions in sqlite 3.26.0
>
>Thank you, Dan.  My eyes saw the underscore, but my brain did not
>process
>it!
>
>And many thanks to the SQLite development team for introducing this
>feature
>and making it so efficient.  I had the need to compute running sums
>and
>averages in a query, and I have views which use regular queries and
>recursive queries.  The recursive queries perform better, but still
>take
>about 10 minutes to process the data.  I replaced them with queries
>using
>window functions, and the results came back in under a second.
>Amazing!
>
>For a related question:  I am trying to calculate a percentile score
>using
>a query like below:
>
>select ID,
>(count(Quantity) over Win1) *100.0/count(ID) as Percentile,
>from myTable
>Window Win1 as (order by Quantityrange between unbounded preceding
>and
>current row)
>
>This gives me the error:  misuse of aggregate: count().
>
>So, I replaced it with the following:
>
>select ID,
>(count(Quantity) over Win1) *100.0/(count(ID) over Win2) as
>Percentile,
>from myTable
>Window Win1 as (order by Quantity range between unbounded preceding
>and
>current row),
>Win2 as (order by ID range between unbounded preceding and unbounded
>following)
>
>This works, but use of a window just to get the total count of ID's
>using a
>range "between unbounded preceding and unbounded following" just
>seems
>wrong!  Is there a simpler construct I am missing?  Thank you.
>
>Balaji Ramanathan
>
>
>> From: Dan Kennedy <[hidden email]>
>> To: [hidden email]
>> Cc:
>> Bcc:
>> Date: Mon, 24 Dec 2018 14:40:58 +0700
>> Subject: Re: [sqlite] Window functions in sqlite 3.26.0
>> On 12/24/2018 01:43 AM, Balaji Ramanathan wrote:
>> > Hi,
>> >
>> >     Are window functions enabled by default in the sqlite command
>line
>> > shell program that is available for download on the sqlite
>website?  I
>> get
>> > the error message "no such function: rownumber()" when I try to
>use that
>> > window function.
>>
>>
>> They are in 3.26.0. Try "row_number", with an underscore. Or, if
>that's
>> not the problem, please post the failing SQL statement.
>>
>> Thanks,
>> Dan.
>>
>>
>>
>>
>>
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users