Android - Easy/efficient Way To Maintain A "cumulative Sum" For A Sqlite Column
Solution 1:
Probably the easiest way is with a SQLite trigger. That is the closest I know
of to "automation". Just have an insert trigger that takes the previous
cumulative sum, adds the current score and stores it in the new row's cumulative
sum. Something like this (assuming _id
is the column you are ordering on):
CREATETRIGGER calc_cumulative_score AFTER INSERTON tablename FOREACHROWBEGINUPDATE tablename SET cumulative_score =
(SELECT cumulative_score
FROM tablename
WHERE _id = (SELECTMAX(_id) FROM tablename))
+ new.score
WHERE _id = new._id;
END
Making sure that the trigger and the original insert are in the same
transaction. For arbitrary updates of the score
column, you would have to
have to implement a recursive trigger that somehow finds the next highest id (maybe by selecting by the min id
in the set of rows with an id greater than the current one) and updates its
cumulative sum.
If you are opposed to using triggers, you can do more or less the same thing in
the ContentProvider in the insert
and update
methods manually, though since
you're pretty much locked into SQLite on Android, I don't see much reason not to
use triggers.
I assume you are wanting to do this as an optimization, as otherwise you could just calculate the sum on demand (O(n)
vs O(1)
, so you'd have to consider how big n
might get, and how often you need the sums).
Post a Comment for "Android - Easy/efficient Way To Maintain A "cumulative Sum" For A Sqlite Column"