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
code and I'll grow.
That is the beauty of groups like this: hobby developers like me get advice
from experts on problems we're facing. Very helpful, not just the code, but
also the on-problem training!
Thanks for your work, all!
gert
2013/3/3 James K. Lowden <jklow...@schemamania.org>
> On Sat, 2 Mar 2013 14:46:40 +0100
> Gert Van Assche <ger...@datamundi.be> 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 group definition is IMO a
> little strange: The first value *after* the breakpoint is included in
> the group. That made the SQL more convoluted than it would be
> otherwise.
>
> That said, the attached SQL does the job in one statement. No triggers
> are needed.
>
> $ sqlite3 db < running_sum.sql
> TextField ValueField CumulativeValue BreakPoint GroupName
> ---------- ---------- --------------- ---------- ----------
> A 2 2 10 1
> B 3 5 10 1
> 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
>
> This lets you define the results in terms of the base data instead of
> keeping a table of derived values. No updates required, just a view.
> Unless you have data-warehouse rowcounts or a very limited machine, it
> should perform just fine, despite what one might guess, because the I/O
> is limited to one table. It should also outperform any per-row
> solution.
>
> Two bits of advice if I may.
>
> Be careful of using triggers to maintain data consistency. Triggers
> are very good for enforcing referential integrity in situations that
> DRI can't handle. Beyond that they get very complex and error prone.
>
> Using triggers to compute derived values signals redundancy in the
> database. That redundancy often is not as desirable as the
> denormalize-for-performance crowd thinks. Better to compute derived
> values on demand (as in a view). If that's demonstrated to be too
> slow, a periodic update in a cron job or similar should provide better
> thoughput than recomputing on every insert/update/delete. You'll also
> do yourself a favor by segregating derived values in a different table
> that can be regenerated at will.
>
> My other suggestion is to eschew abstract nouns in column names. Words
> like data, field, and value add no meaning. After all, it's a
> database. If it's not a value, what is it?
>
> This table,
>
> CREATE TABLE [Test]
> ( Name
> , Value
> , RunningSum
> , Breakpoint /* (one word, small 'p') */ DEFAULT 10
> , GroupName
> , primary key (Name)
> );
>
> is at least as clear, wouldn't you say?
>
> HTH.
>
> --jkl
>
> P.S. In case the listserv strips attachments, here it is in plain
> text.
>
> [SQL]
> select A.TextField, A.ValueField
> , A.total - coalesce(C.GroupTotal, 0) as CumulativeValue
> , A.Breakpoint
> -- , coalesce(C.GroupTotal, 0) as GroupTotal
> , coalesce(B.GroupName, A.GroupName) as GroupName
> from (
> select a.*, sum(b.ValueField) as total
> , 1 + sum(b.ValueField) / (1+a.Breakpoint) as GroupName
> from Test as a join Test as b
> on a.TextField >= b.TextField
> group by a.TextField
> , a.ValueField
> , a.CumulativeValue
> , a.BreakPoint
> ) as A
> left join (
> select a.*, sum(b.ValueField) as total
> , 1 + sum(b.ValueField) / (1 + a.Breakpoint) as GroupName
> from Test as a join Test as b
> on a.TextField >= b.TextField
> group by a.TextField
> , a.ValueField
> , a.CumulativeValue
> , a.BreakPoint
> ) as B
> on B.TextField = (select max(TextField) from Test where TextField <
> A.TextField) left join (
> select 1 + T.GroupName as GroupName
> , max(T.total) as GroupTotal
> from (
> select A.TextField
> , A.ValueField
> , A.total
> , coalesce(B.GroupName, 1) as GroupName
> from (
> select a.*, sum(b.ValueField) as total
> , 1 + sum(b.ValueField) / (1 +
> a.Breakpoint) as GroupName from Test as a join Test as b
> on a.TextField >= b.TextField
> group by a.TextField
> , a.ValueField
> , a.CumulativeValue
> , a.BreakPoint
> ) as A
> left join (
> select a.*, sum(b.ValueField) as total
> , 1 + sum(b.ValueField) / (1 +
> a.Breakpoint) as GroupName from Test as a join Test as b
> on a.TextField > b.TextField
> group by a.TextField
> , a.ValueField
> , a.CumulativeValue
> , a.BreakPoint
> ) as B
> on B.TextField = ( select max(TextField)
> from Test
> where TextField <= A.TextField )
> ) as T
> group by GroupName
> ) as C
> on B.GroupName = C.GroupName
> ;
> [LQS]
> >
> > This is an example table:
> >
> > CREATE TABLE [Test] (TextField, ValueField, CumulativeValue,
> > BreakPoint DEFAULT 10, GroupName);
> > insert into [Test] values('A', '2', null, '10');
> > insert into [Test] values('B', '3', null, '10');
> > insert into [Test] values('C', '2', null, '10');
> > insert into [Test] values('D', '4', null, '10');
> > insert into [Test] values('E', '5', null, '10');
> > insert into [Test] values('F', '1', null, '10');
> > insert into [Test] values('G', '1', null, '10');
> > insert into [Test] values('H', '5', null, '10');
> > insert into [Test] values('I', '11', null, '10');
> > insert into [Test] values('J', '8', null, '10');
> > insert into [Test] values('K', '2', null, '10');
> >
> > I'd like to end up with a table that looks like this:
> >
> > ?TextField ValueField CumulativeValue BreakPoint GroupName
> > A 2 2 10 1
> > B 3 5 10 1
> > 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 2 10 4
> >
> > I spent hours trying to update the CumulativeValue field untill the
> > BreakPoint value is crossed, and restarting the cumulative counter,
> > but I have too little sql knowledge to do this.
> >
> > Could anyone help me?
> >
> > thanks
> >
> > gert
> > _______________________________________________
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
