Android的 - 简单的/有效的方式来保持与QUOT;累计相加＆QUOT;对于一个SQLite列 [英] Android - easy/efficient way to maintain a "cumulative sum" for a SQLite column

问题描述

什么是保持在SQLite的特定数据列的累计总和的最佳方式？我发现几个例子在网上，但我不是100％肯定我会如何这些方法融入我的的ContentProvider 。

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 these approaches into my ContentProvider.

在previous应用程序，我试图保持的累积数据自己，更新我每次插入新数据表中的数据。例如，在下面的示例code，每次我都会添加一个新的纪录值评分，我会再手动更新<$ C $的价值C> cumulative_score 根据其previous行中值。

In previous applications, I have tried to maintain cumulative data myself, updating the data each time I insert new data into the table. For example, in the sample code below, every time I would add a new record with a value score, I would then manually update the value of cumulative_score based on its value in the previous row.

_id   score   cumulative_score
1     100     100
2     50      150
3     25      175
4     25      200
5     10      210

不过，这是很不理想和处理具有很多列的表时变得非常凌乱。有没有办法给每个我在表中插入/更新记录的时间自动莫名其妙地累积更新数据的过程？我怎么可能这个融入我的的ContentProvider 实施？

我知道一定有办法做到这一点......我只是不知道怎么办。谢谢！

I know there must be a way to do this... I just don't know how. Thanks!

推荐答案

也许最简单的方法是使用SQLite的触发器。这是我所知道的最接近
以自动化。只要有一个INSERT触发器，是以previous
累计总和，将当前得分和将其存储在新的行的累计
和。事情是这样的（假设 _id 是您订购的列）：

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):

CREATE TRIGGER calc_cumulative_score AFTER INSERT ON tablename FOR EACH ROW
BEGIN
    UPDATE tablename SET cumulative_score =
        (SELECT cumulative_score
         FROM tablename
         WHERE _id = (SELECT MAX(_id) FROM tablename))
        + new.score
    WHERE _id = new._id;
END

确保触发器和原刀片都在同一
交易。对于评分列的任意更新，你将不得不
要实现一个递归触发器，不知怎的，找到下一个最高的ID（可能由最小的ID选择
在组行与ID比当前更高），并更新其
累计总和。

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.

如果您反对使用触发器，可以做或多或少同样的事情在
在插入和更新方法ContentProvider的手工，但因为
你是pretty多锁定在Android上的SQLite的，我看不出有任何理由不
使用触发器。

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.

我假设你是想这样做，因为一个优化，否则你可能只是计算需求的总和（ O（N） VS O（1），所以你必须要考虑有多大 N 可能会，以及多久你所需要的款项）。

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).

这篇关于Android的 - 简单的/有效的方式来保持与QUOT;累计相加＆QUOT;对于一个SQLite列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！