Skip to content Skip to sidebar Skip to footer

Android - Easy/efficient Way To Maintain A "cumulative Sum" For A Sqlite Column

What is the best way to maintain a 'cumulative sum' of a particular data column in SQLite? I have found several examples online, but I am not 100% certain how I might integrate the

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"