Re: [sqlite] Window functions

12:15 PM To: SQLite mailing list Subject: [sqlite] Window functions I ran into this two-part article, probably on Hacker News: <http://www.helenanderson.co.nz/sql-window-functions-part-1/> I tried comparing it with <https://www.sqlite.org/windowfunctions.html> but I don't know enoug

Re: [sqlite] Window functions

f Simon Slavin Sent: Wednesday, September 04, 2019 12:15 PM To: SQLite mailing list Subject: [sqlite] Window functions I ran into this two-part article, probably on Hacker News: <http://www.helenanderson.co.nz/sql-window-functions-part-1/> I tried comparing it with <https://www.sqlite.org

Re: [sqlite] Window functions

On 4/9/62 23:14, Simon Slavin wrote: I ran into this two-part article, probably on Hacker News: I tried comparing it with but I don't know enough to be able to tell whether the

[sqlite] Window functions

I ran into this two-part article, probably on Hacker News: I tried comparing it with but I don't know enough to be able to tell whether the language used in the article is compatible

[sqlite] Window functions in System.Data.Sqlite

Hello, I am sorry in advance for this post but I am really looking forward having the window functions in System.Data.Sqlite. The expected release date went from December 2018 to February 2019 in the news page. Do you have any more info when will the new version of System.Data.Sqlite be

Re: [sqlite] Window functions in sqlite 3.26.0

, 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 >feat

Re: [sqlite] Window functions in sqlite 3.26.0

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 downloa

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.

[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

Re: [sqlite] Window functions?

I see the point being made by many in this thread. I am not against expanding SQLite's functionality. But, if I might, I will throw out some contrarian ideas. First is that SQLite really is an embedded SQL data base. It is meant to be combined into your application's main executable file. So the

Re: [sqlite] Window functions?

In 2008 I was part of a project which was mostly DB driven using Sybase 12.5. Sybase neither had support for user functions nor window functions & many other features, which other contemporary RDBMS had and same arguments was thrown why you need user functions or window functions if both can be

Re: [sqlite] Window functions

> On Wed, Apr 25, 2018 at 12:04 PM, Charles Leifer wrote: > >> Hi, >> >> I'm sure this has been asked before, but are window functions on the >> roadmap? Is it the authors' experience that the implementation would >> significantly complicate sqlite? Just curious. Thanks so

Re: [sqlite] Window functions

What are 'window functions'? If you mean GUI; that's really outside the scope of Sqlite; and whatever environment you're in can provide your GUI; https://www.npmjs.com/package/sack-gui for instance for Javascript(Node.js). On Wed, Apr 25, 2018 at 12:04 PM, Charles Leifer

[sqlite] Window functions

Hi, I'm sure this has been asked before, but are window functions on the roadmap? Is it the authors' experience that the implementation would significantly complicate sqlite? Just curious. Thanks so much for a fantastic library. Charlie ___

[sqlite] sqlite window functions extension?

I'm disappointed. I killed it after 1.5hrs, and suspected everything you explained in the other thread. It is always true. Que Sera Sera dvn On Fri, Oct 16, 2015 at 2:33 PM, Igor Tandetnik wrote: > On 10/16/2015 3:23 PM, Don V Nielsen wrote: > >> limit ( >>SELECT net_non_pieces

[sqlite] sqlite window functions extension?

On 10/16/2015 3:23 PM, Don V Nielsen wrote: > limit ( >SELECT net_non_pieces >FROM crrt_net_non [b] >WHERE [b].zip = zip AND [b].crrt = crrt Again, I don't think this does what you think it does. Test with at least two rows in crrt_net_non, with different values for

[sqlite] sqlite window functions extension?

Wrapping up this thread. What I am going to go with is the following. I'm using the initial WITH to reduce as much as possible the number of records to be repeatedly searched: maybe 25/30%. The addresses table currently has 4.4mm rows; it will go up to 11.5mm when this goes to prod. I'm

[sqlite] sqlite window functions extension?

The final solution at this point is Ruby. I really want to push everything I can into Sqlite because *it is so freakin fast!* Unfortunately. I I am just not getting it. Also unfortunately, iterating, getting data, and updating data in scripting languages is not efficient. I've implemented a

[sqlite] sqlite window functions extension?

On 10/15/2015 9:36 AM, Don V Nielsen wrote: > limit ifnull( ( >select net_non_pieces from crrt_net_non net >where net.zip=zip and net.crrt=crrt I suspect this WHERE clause is equivalent to "where net.zip=net.zip and net.crrt=net.crrt" - that is,

[sqlite] sqlite window functions extension?

I'm surprised that and extension for this type of functionality has not been been developed by someone with the c/c++. It's seems like a natural fit. I wish I had the kind of ability & smarts to do it. On Thu, Oct 15, 2015 at 9:04 AM, Igor Tandetnik wrote: > On 10/15/2015 9:36 AM, Don V

[sqlite] sqlite window functions extension?

The correlated query was not accepted in the select statement. However, I modified your original UPDATE query, as follows, and it did execute, and passed the proper quantity of rows for the first zip/crrt combination, only. The rows were not updated. It counted correctly, but did not change the

[sqlite] sqlite window functions extension?

On 2015-10-14 11:20 PM, Don V Nielsen wrote: > X has columns zip & crrt, just like crrt_net_non. These form a composite > key identifying groups within x. A value "53001.R501" would be an > example...53001 being the zip code and R501 being the carrier route. There > are 52 rows in X that have

[sqlite] sqlite window functions extension?

On 10/14/2015 9:29 PM, Don V Nielsen wrote: > But I am having problems with the LIMIT statement. It throws an exception > no matter what table alias is used: X or x2. It says "no such column". Ah, interesting. LIMIT clause doesn't appear to allow correlated subqueries; only self-contained

[sqlite] sqlite window functions extension?

The only reason NUM exists in the result set is so I could use it to limit the outer most select. But I am having problems with the LIMIT statement. It throws an exception no matter what table alias is used: X or x2. It says "no such column". Thanks for your time, by the way. On Wed, Oct 14,

[sqlite] sqlite window functions extension?

On 10/14/2015 6:24 PM, Don V Nielsen wrote: > zip crrt version_id NUM segment > [truncated num 1..38] > 53001 R501 0060 39xx > 53001 R501 0060 40xx > 53001 R501 0060 41xx > 53001 R501 0060 42xx > [truncated num 1..24] >

[sqlite] sqlite window functions extension?

On 10/14/2015 5:20 PM, Don V Nielsen wrote: > X has columns zip & crrt, just like crrt_net_non. These form a composite > key identifying groups within x. A value "53001.R501" would be an > example...53001 being the zip code and R501 being the carrier route. There > are 52 rows in X that have

[sqlite] sqlite window functions extension?

Table crrt_net_non_pieces: zip crrt pkg_pieces sel_pieces non_pieces net_non_pieces 53001 R501 52 646 42 53001 R502 34 727 24 Addresses (as I have been referring to as X) is going to have too many rows (86 for

[sqlite] sqlite window functions extension?

On 10/14/2015 4:49 PM, Don V Nielsen wrote: > What am I looking to do? Using a table X, I've built a summary table, > calculating a value called net_non_pieces. net_not_pieces is a qty of rows > from a group rows that needs to be recoded to a different value. So what I > want to do is for each

[sqlite] sqlite window functions extension?

X has columns zip & crrt, just like crrt_net_non. These form a composite key identifying groups within x. A value "53001.R501" would be an example...53001 being the zip code and R501 being the carrier route. There are 52 rows in X that have the key 53001.R501. A calculation determined that I

[sqlite] sqlite window functions extension?

Is there an extension to sqlite the provide window functions such as row_number over and partition? There are plenty of googles seeking said functionality. What am I looking to do? Using a table X, I've built a summary table, calculating a value called net_non_pieces. net_not_pieces is a qty

Re: [sqlite] Window functions?

On Wed, 27 Aug 2014 18:25:28 -0600 "Keith Medcalf" wrote: > >> select id, category_id, name, min(price) as minprice > >>from cat_pictures > >> group by category_id; > > >This peculiar behavior is very unique to SQLite. > > Not really. Sybase, SQL Server and DB2 do

Re: [sqlite] Window functions?

On Wed, 27 Aug 2014 23:04:40 +0200 Petite Abeille wrote: > > On Aug 27, 2014, at 10:57 PM, Eduardo Morras > wrote: > > > Sorry, don't understand why others will throw an exception in the > > group by, perhaps I'm misunderstanding the group by, but

Re: [sqlite] Window functions?

On 28 Aug 2014, at 3:45pm, Richard Hipp wrote: > On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck < > ggrothendi...@gmail.com> wrote: > >> The wording in the cited link is that >> >> "Queries of the form: "SELECT max(x), y FROM table" returns the value >> of y on the same

Re: [sqlite] Window functions?

On Thu, Aug 28, 2014 at 10:38 AM, Gabor Grothendieck < ggrothendi...@gmail.com> wrote: > The wording in the cited link is that > > "Queries of the form: "SELECT max(x), y FROM table" returns the value > of y on the same row that contains the maximum x value." > > There is some question of whether

Re: [sqlite] Window functions?

The wording in the cited link is that "Queries of the form: "SELECT max(x), y FROM table" returns the value of y on the same row that contains the maximum x value." There is some question of whether min(x) is "of the form" max(x). On Thu, Aug 28, 2014 at 10:28 AM, Clemens Ladisch

Re: [sqlite] Window functions?

Adam Devita wrote: >>> select id, category_id, name, min(price) as minprice >>>from cat_pictures >>> group by category_id; >>> > > I'd be reluctant to write that query because it is non standard SQL and I > can't easily (5 minutes of searching) point at a document that tells me the > expected

Re: [sqlite] Window functions?

dbase3 would give an error if you did not include all the non-aggregate fields in the Group By. (One could also step forward/backward in a row-set, so some crude windowing was available if one coded to do that.) on this: > > select id, category_id, name, min(price) as minprice > >from

Re: [sqlite] Window functions?

Aug 27, 2014 5:25 pm Subject: Re: [sqlite] Window functions? On Wednesday, 27 August, 2014 13:17, Petite Abeille said: >On Aug 26, 2014, at 2:09 AM, Keith Medcalf <kmedc...@dessus.com> wrote: >> select id, category_id, name, min(price) as minprice >>from cat

Re: [sqlite] Window functions?

On Wednesday, 27 August, 2014 13:17, Petite Abeille said: >On Aug 26, 2014, at 2:09 AM, Keith Medcalf wrote: >> select id, category_id, name, min(price) as minprice >>from cat_pictures >> group by category_id; >> >> Done. And no need for any windowing functions ...

Re: [sqlite] Window functions?

If the above works, it is a SQLite "feature" or mis-feature. Regards. -Original Message- From: Petite Abeille <petite.abei...@gmail.com> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Wed, Aug 27, 2014 2:04 pm Subject: Re: [sqlite] Window

Re: [sqlite] Window functions?

On Aug 27, 2014, at 10:57 PM, Eduardo Morras wrote: > Sorry, don't understand why others will throw an exception in the group by, > perhaps I'm misunderstanding the group by, but that should work on others > engines. Because not all expressions are accounted for, i.e.:

Re: [sqlite] Window functions?

On Wed, 27 Aug 2014 21:17:05 +0200 Petite Abeille wrote: > > On Aug 26, 2014, at 2:09 AM, Keith Medcalf > wrote: > > > select id, category_id, name, min(price) as minprice > >from cat_pictures > > group by category_id; > > > > Done. And no

Re: [sqlite] Window functions?

On Aug 26, 2014, at 2:09 AM, Keith Medcalf wrote: > select id, category_id, name, min(price) as minprice >from cat_pictures > group by category_id; > > Done. And no need for any windowing functions … This peculiar behavior is very unique to SQLite. Most reasonable

Re: [sqlite] Window functions?

On 2014-08-26 18:00, sqlite-users-requ...@sqlite.org wrote: SELECT employee_name, employee_id, salary, rank() OVER(PARTITION BY dept ORDER BY salary DESC), 100.0*salary/sum(salary) OVER (PARTITION BY dept) FROM employee; I don't know if the above is valid SQL or not. But is

Re: [sqlite] Window functions?

On 25.08.2014 20:47, Richard Hipp wrote: On Mon, Aug 25, 2014 at 1:21 PM, forkandwait wrote: You used the word "immense" which I like - it is an apt description of the knowledge and effort needed to add windowing functions to SQLite (and probably any other database

Re: [sqlite] Window functions?

On Monday, August 25, 2014 09:25:47 PM Stephan Beal wrote: > On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille > > wrote: > > True. But what a quantum leap that would be. Like moving from the > > wheelbarrow to the jet engine. > > For the small percentage of users who

Re: [sqlite] Window functions?

ait >Sent: Monday, 25 August, 2014 11:19 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Window functions? > >Simon Slavin <slavins@...> writes: > > >> Would you care to explain what advantages Window functions would give >us >that VIEWs and sub-SELECTs don'

Re: [sqlite] Window functions?

Hi Stephan, "lite," is not a mathematic definition, and is increasing over time. (at least 5% per year in Sqlite code size, by 30% in smartphone capabilities ) ==> What was "heavy" in 2003 will become "lite" one day. ___ sqlite-users mailing list

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 9:43 PM, forkandwait wrote: > Stephan Beal writes: > > > For the small percentage of users who need it (or would even know how to > > apply it). i've been following this list since 2006 or 2007 and i recall > > this topic having come

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 4:20 PM, forkandwait wrote: > > I would be interested to hear what parts of the full window function spec > are not covered by the example, if someone can describe it easily. > SELECT employee_name, employee_id, salary, rank()

Re: [sqlite] Window functions?

On Aug 25, 2014, at 10:20 PM, forkandwait wrote: > I would be interested to hear what parts of the full window function spec > are not covered by the example, if someone can describe it easily. Well, the exact implementation varies from implementation to implementation,

Re: [sqlite] Window functions?

big stone writes: > - but would a basic subset, like the one described here in March '14 ( > http://sqlite.org:8080/cgi-bin/mailman/private/sqlite-users/2014-March/051635.html For non-subscribers to read: http://thread.gmane.org/gmane.comp.db.sqlite.general/86702 > ) : >

Re: [sqlite] Window functions?

Hi, For one of the few wishing it : - I can understand when Richard writes it's very complex to implement "in full", as I can imagine tricky requests with it, - but would a basic subset, like the one described here in March '14 (

Re: [sqlite] Window functions?

On Aug 25, 2014, at 7:18 PM, forkandwait wrote: > Compare the two SQL examples between Approach 2 and Approach 3 in the linked > page: > > http://hashrocket.com/blog/posts/sql-window-functions Couple more: There was SQL before window functions and SQL after window

Re: [sqlite] Window functions?

Stephan Beal writes: > For the small percentage of users who need it (or would even know how to > apply it). i've been following this list since 2006 or 2007 and i recall > this topic having come up only a small handful of times, which implies that > only a small minority of users

Re: [sqlite] Window functions?

Stephan Beal wrote on Monday, August 25, 2014 3:26 PM > > For the small percentage of users who need it (or would even know how > to apply it). i've been following this list since 2006 or 2007 and i > recall this topic having come up only a small handful of times, which > implies that only a

Re: [sqlite] Window functions?

On Aug 25, 2014, at 9:25 PM, Stephan Beal wrote: > For the small percentage of users who need it (or would even know how to > apply it). i've been following this list since 2006 or 2007 and i recall > this topic having come up only a small handful of times, which implies

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 9:17 PM, Petite Abeille wrote: > True. But what a quantum leap that would be. Like moving from the > wheelbarrow to the jet engine. > For the small percentage of users who need it (or would even know how to apply it). i've been following this

Re: [sqlite] Window functions?

On Aug 25, 2014, at 7:04 PM, Simon Slavin wrote: > Would you care to explain what advantages Window functions would give us that > VIEWs and sub-SELECTs don't give us ? I'm not being contrary, I'd like to > know. Analytics are to sub-selects like cruise missile are to

Re: [sqlite] Window functions?

On Aug 25, 2014, at 7:12 PM, Richard Hipp wrote: > You used the word "immense" which I like - it is an apt description of the > knowledge and effort needed to add windowing functions to SQLite (and > probably any other database engine for that matter). True. But what a quantum

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 1:21 PM, forkandwait wrote: > > You used the word "immense" which I like - it is an apt description of > the > > knowledge and effort needed to add windowing functions to SQLite (and > > probably any other database engine for that matter). > >

Re: [sqlite] Window functions?

> You used the word "immense" which I like - it is an apt description of the > knowledge and effort needed to add windowing functions to SQLite (and > probably any other database engine for that matter). Hehe. I would be interested in any of your specific thoughts on the immensity of it. I can

Re: [sqlite] Window functions?

Simon Slavin writes: > Would you care to explain what advantages Window functions would give us that VIEWs and sub-SELECTs don't > give us ? I'm not being contrary, I'd like to know. I have never compared lines of code between the various approaches, but window functions make it

Re: [sqlite] Window functions?

On Mon, Aug 25, 2014 at 11:43 AM, forkandwait wrote: > > I am not promising anything, but I would be interested in a sketch of it > might take a hacker to add these to SQLite -- what files need to be > touched, > what sections of the lemon parser, etc. > You used the

Re: [sqlite] Window functions?

On 25 Aug 2014, at 4:43pm, forkandwait wrote: > Has anyone thought in some detail about what it would it take to add window > functions to SQLite? Would you care to explain what advantages Window functions would give us that VIEWs and sub-SELECTs don't give us ? I'm

[sqlite] Window functions?

Has anyone thought in some detail about what it would it take to add window functions to SQLite? http://www.postgresql.org/docs/9.4/static/tutorial-window.html For data analysis shops like us (think SAS + baroquely complex Excel + lots of graphs), SQLite with window functions would be immense.

Re: [sqlite] Window functions

I'm using sqlite's count function to emulate OLAP functionality. Unfortunately count function is not exposed in sqlite, nevertheless it eases the pain of not having analytics functions in sqlite (lead, lag, median etc). lefteris Alexey Pechnikov wrote: > Hello! > > В сообщении от Monday 24

Re: [sqlite] Window functions

Hello! В сообщении от Monday 24 November 2008 19:16:46 Constantine Vassil написал(а): > OLAP functionality includes the concept of a sliding *window* that moves > down > through the input rows as they are processed. Additional calculations can > be > > performed on the data in the window as it

[sqlite] Window functions

OLAP functionality includes the concept of a sliding *window* that moves down through the input rows as they are processed. Additional calculations can be performed on the data in the window as it moves, allowing further analysis in a manner that is more efficient than using semantically