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 statement doesn't seem like a good idea if the data is coming in real time. He never 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 Mar 2013 11:46:26 +0100 Gert Van Assche <ger...@datamundi.be> 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 itself as well, but I got stuck. I'll study your code and I'll > grow. Happy to be service, Gert. The trick is always the same: get your ducks in a row and shoot. Figure out the formula that computes the answer you want, and put the constituent parts on the row, then compute. The first order of business in your case was a running sum. That's done by adding up everything "before" the current row, where "before" means "less than" something, however ordered. select a.*, sum(b.ValueField) as total from Test as a join Test as b on a.TextField >= b.TextField group by a.TextField , a.ValueField , a.CumulativeValue , a.BreakPoint For each row in "a", get all the rows in "b" whose TextField is less than (or equal to, here) to a's. Here it is, live and in color: $ sqlite3 -echo db < self.sql select a.TextField , a.ValueField , a.BreakPoint , sum(b.ValueField) as total from Test as a join Test as b on a.TextField >= b.TextField group by a.TextField , a.ValueField , a.CumulativeValue , a.BreakPoint ; TextField ValueField BreakPoint total ---------- ---------- ---------- ---------- A 2 10 2 B 3 10 5 C 2 10 7 D 4 10 11 E 5 10 16 F 1 10 17 G 1 10 18 H 5 10 23 I 11 10 34 J 8 10 42 K 2 10 44 That formed my building block. Your formula is almost GN = 1 + total / Breakpoint By using integer arithmetic, I could divide "total" by BreakPoint to get a multiple, which is almost what you call a GroupName, except for that "one past" rule I remarked on, where for example 11 is part of the first group. But by joining my block to itself, offset by one, we can slide the multiplier down a row. For CumulativeValue, your formula is CV = total - [max total of previous GN] That requires computing the GN, grouping on it, and finding the maximum total for it, and joining that result back to the main one, where it can be subtracted from the main total. So, yes, there are a lot of joins. But I didn't write the query top-down, and you shouldn't read it that way. Read it inside-out. Each piece can be separately evaluated, and each operand used in the calculation can be exposed in the SELECT clause. 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. Regards, --jkl _______________________________________________ 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
