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