Re: [sqlite] i Know i should use 'AS', but ....
Luuk wrote: >sqlite> .mode column >sqlite> .headers on >sqlite> select 1 as X,date() as d union all select 2,date() union all >select 3,datetime(); >X d >-- -- >1 2018-12-25 >2 2018-12-25 >3 2018-12-25 The value is longer than the column with (which is probably based on the values in the first line). Try ".width 10 20". Regards, Clemens ___ 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
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] [OT] Re: i Know i should use 'AS', but ....
On 24-12-2018 21:25, Shawn Wagner wrote: Using '.mode column" in conjunction with ".headers on" you're already using makes it a lot more obvious. like this: SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> select 1 as X,date() as d union all select 2,date() union all select 3,datetime(); 1|2018-12-25 2|2018-12-25 3|2018-12-25 18:56:41 sqlite> .mode column sqlite> .headers on sqlite> select 1 as X,date() as d union all select 2,date() union all select 3,datetime(); X d -- -- 1 2018-12-25 2 2018-12-25 3 2018-12-25 sqlite> select 1 as X,datetime() as d union all select 2,date() union all select 3,datetime(); X d -- --- 1 2018-12-25 18:57:11 2 2018-12-25 3 2018-12-25 18:57:11 sqlite> the first result seems correct (datetime after the 3) in the secondresult the third line is casted to date? (or misses time?) the above seems to be not correct when looking at the last query. Same result in version 'SQLite version 3.26.0' ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Typo
Sadly by the time you get here it will be over for another year. Still seasons greetings from the UK. On Tue, 25 Dec 2018 at 13:45, Richard Hipp wrote: > On 12/25/18, Mike King wrote: > > It’s > > Christmas Day so surely you deserve a mince pie and a fine single malt :) > > Sounds great! Send me your address and I'll be right over! ;-) > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Typo
On 12/25/18, Mike King wrote: > It’s > Christmas Day so surely you deserve a mince pie and a fine single malt :) Sounds great! Send me your address and I'll be right over! ;-) -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Typo
Surely I speak for all of when I say I hope you have a fantastic Christmas Richard and thanks for everything you do. Do you ever stop though? It’s Christmas Day so surely you deserve a mince pie and a fine single malt :) Cheers On Tue, 25 Dec 2018 at 11:18, Richard Hipp wrote: > Thanks. Fixed here: https://www.sqlite.org/src/info/a1f50d57ce76f6c0 > > On 12/25/18, gwenn wrote: > > Hi, > > There is a typo here: > > https://sqlite.org/session/sqlite3changeset_op.html > >> If pbIncorrect is not NULL, then *pbIndirect is set to true > > Should be pbIndirect instead of pbIncorrect. > > Regards. > > ___ > > sqlite-users mailing list > > sqlite-users@mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > > -- > D. Richard Hipp > d...@sqlite.org > ___ > 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] Typo
Thanks. Fixed here: https://www.sqlite.org/src/info/a1f50d57ce76f6c0 On 12/25/18, gwenn wrote: > Hi, > There is a typo here: > https://sqlite.org/session/sqlite3changeset_op.html >> If pbIncorrect is not NULL, then *pbIndirect is set to true > Should be pbIndirect instead of pbIncorrect. > Regards. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Typo
Hi, There is a typo here: https://sqlite.org/session/sqlite3changeset_op.html > If pbIncorrect is not NULL, then *pbIndirect is set to true Should be pbIndirect instead of pbIncorrect. Regards. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
