Re: [sqlite] Break on cumulative sum

On Mar 5, 2013, at 9:24 AM, Nico Williams wrote: > +1 re: recursive queries. There is a standard for that (in ANSI SQL-99?): recursive 'with' clause, aka recursive subquery factoring, aka recursive common table expressions.

Re: [sqlite] Break on cumulative sum

On Mar 5, 2013, at 8:19 AM, James K. Lowden wrote: >> Postgresql has also had them for a while. > > Yes, and if I'm not mistaken Ingres's QUEL had them long before that. Yes, many databases have them, from Postgres, MSSQL, Sybase IQ, some flavor of DB2, to Oracle,

Re: [sqlite] Break on cumulative sum

One of the reasons why we have to use PostgreSQL and/or Oracle is for the 'Window Functions' (AKA: Analytic Functions). We use SQLite all the time and love it but this one missing feature bites us. Could we get some advice from the SQLite core team on the feasibility of this? Could it be just

Re: [sqlite] Break on cumulative sum

On Tue, 5 Mar 2013 02:24:53 -0600 Nico Williams wrote: > Lies! :) There's one more type of UDF in SQLite3: virtual tables. > You could have a virtual table where selecting with an equality test > for some column is "passing an argument" to a "table function". I > know,

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 9:47 AM, Nico Williams wrote: > Well, under the hood table functions could use temp, gensym'ed virtual > tables for all I care. The point is I want table functions :) SQLite already has table functions of sort: PRAGMAs... They have their own issues

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 2:44 AM, Dominique Devienne wrote: > On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams wrote: > Right. Virtual Tables are very flexible, but the syntax is indeed not > practical, and it also forces you to name and "instantiate"

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 9:24 AM, Nico Williams wrote: > On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden > > 2. Aggregation. The engine passes the rows to be aggregated to the > > function. After the last row, it calls the function's "xFinal" > > function, which returns a

Re: [sqlite] Break on cumulative sum

On Tue, Mar 5, 2013 at 1:19 AM, James K. Lowden wrote: > Right you are, except it's not a question of efficient but of > possible. A UDF works one of two ways: > > 1. Per row. The engine calls the function for each row. The function > transforms the input into the

Re: [sqlite] Break on cumulative sum

On Mon, 04 Mar 2013 16:51:35 -0500 Ryan Johnson wrote: > On 04/03/2013 2:20 PM, Petite Abeille wrote: > > On Mar 4, 2013, at 1:32 AM, James K. Lowden > > wrote: > > > >> What do you have in mind? I've benn adding some user defined > >>

Re: [sqlite] Break on cumulative sum

On 04/03/2013 2:20 PM, Petite Abeille wrote: On Mar 4, 2013, at 1:32 AM, James K. Lowden wrote: What do you have in mind? I've benn adding some user defined functions and am thinking of creating a repository for them. All the so-called window functions from

Re: [sqlite] Break on cumulative sum

On Mar 4, 2013, at 1:32 AM, James K. Lowden wrote: > What do you have in mind? I've benn adding some user defined functions > and am thinking of creating a repository for them. All the so-called window functions from SQL:2003 (aka analytic functions): "Windowed

Re: [sqlite] Break on cumulative sum

On Sun, 3 Mar 2013 07:19:11 -0600 "Michael Black" wrote: > One thing I'll point out...the trigger solution is almost linear > performance and constant memory where the single SQL statement will > suffer notably as time goes on in both those measures. Also the > single SQL

Re: [sqlite] Break on cumulative sum

On Sun, 3 Mar 2013 18:50:35 +0100 Petite Abeille wrote: > Ah? if only? SQLite had analytical functions? oh, well.. What do you have in mind? I've benn adding some user defined functions and am thinking of creating a repository for them. --jkl

Re: [sqlite] Break on cumulative sum

On Mar 3, 2013, at 2:10 PM, "James K. Lowden" wrote: > There's some cruft, too. I was only demonstrating that it could be > done. If you find a way to simplify it, you'll know you understand > it. Ah… if only… SQLite had analytical functions… oh, well..

Re: [sqlite] Break on cumulative sum

mentioned how big his table is going to be. -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of James K. Lowden Sent: Sunday, March 03, 2013 7:10 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Break on cumulative sum On Sun, 3

Re: [sqlite] Break on cumulative sum

On Sun, 3 Mar 2013 11:46:26 +0100 Gert Van Assche wrote: > My SQL knowledge is not yet good enough to understand what you > proposed, but I hope one day that I will be able to understand and > replicate myself the code you wrote. I started of with a join on the > table

Re: [sqlite] Break on cumulative sum

James, I'm very impressed by your solution. My SQL knowledge is not yet good enough to understand what you proposed, but I hope one day that I will be able to understand and replicate myself the code you wrote. I started of with a join on the table itself as well, but I got stuck. I'll study your

Re: [sqlite] Break on cumulative sum

On Sat, 2 Mar 2013 14:46:40 +0100 Gert Van Assche wrote: > All, I don't know how to achieve this: I need to put the cumulative > sum in a field, and create a group as soon as that cumulative sum is > over a breakpoint value (10). This was a bit of a challenge because the

Re: [sqlite] Break on cumulative sum

Re: [sqlite] Break on cumulative sum Michael, this is working perfectly! I learned a lot with your code. Thanks a lot for your help. gert 2013/3/2 Michael Black <mdblac...@yahoo.com> > I think your "K" row was a typo on the CumulativeValue? > > CREATE TABLE [

Re: [sqlite] Break on cumulative sum

gt; C|2|7|10|1 > D|4|11|10|1 > E|5|5|10|2 > F|1|6|10|2 > G|1|7|10|2 > H|5|12|10|2 > I|11|11|10|3 > J|8|8|10|4 > K|2|10|10|4 > > > -Original Message----- > From: sqlite-users-boun...@sqlite.org [mailto: > sqlite-users-boun...@sqlite.org] On Behalf Of Gert V

Re: [sqlite] Break on cumulative sum

8|10|4 K|2|10|10|4 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Gert Van Assche Sent: Saturday, March 02, 2013 7:47 AM To: sqlite-users Subject: [sqlite] Break on cumulative sum All, I don't know how to achieve this:

[sqlite] Break on cumulative sum

All, I don't know how to achieve this: I need to put the cumulative sum in a field, and create a group as soon as that cumulative sum is over a breakpoint value (10). This is an example table: CREATE TABLE [Test] (TextField, ValueField, CumulativeValue, BreakPoint DEFAULT 10, GroupName); insert