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 <danielk1...@gmail.com> >> 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
