Aggregate difference for Average calcualtion
I've wondered for years if you could recalculate the average over some values if you only knew the change to one value. It turns out that you can as long as you know the total number of values. The derivation is fairly trivial, too. I just never did it until I actually had to do it for a client. I spend too much speculating, I guess. You can also calculate the new average if new values are added. Formulas and derivation below.
To calculate the change to the Average aggregation you need to keep the prior total count N and the prior total T.
If the value of one component of the aggregation changed by d the new total T' is calculated as:
T' = (NT + d)/N
If an additional component is created and it's value is v then new aggregation T' is calculated as:
T' = (NT + v) / (N + 1)
This can be generalized for a sum s of n values as:
T' = (NT + s) / (N + n)
Derivation:
For sum S of N values the average T is:
[1] S/N = T
For a change d to a value in S we have the new average T' as:
[2] (S + d)/N = T'
From [1] we have
[3] S = NT
Plugging that into [2] we have:
[4] (NT + d)/N = T' QED
If n values with a sum of s are added to the aggregation you have:
[5] (S + s)/(N + n) = T'
Plugging [3] into [5] we have:
[6] (NT + s)/(N + n) = T' QED