Re: [sqlite] Window functions in sqlite 3.26.0
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- >boun...@mailinglists.sqlite.org] On Behalf Of Balaji Ramanathan >Sent: Tuesday, 25 December, 2018 12:24 >To: sqlite-users@mailinglists.sqlite.org >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 >> To: sqlite-users@mailinglists.sqlite.org >> 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 >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
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 > To: sqlite-users@mailinglists.sqlite.org > 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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. 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 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
[sqlite] Window functions in sqlite 3.26.0
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
