Imagine a database that has billions of rows with, say, payments. You need to calculate an average payment sum. That will be
SELECT avg(amount) FROM payments;
Now, if you need to re-calculate this figure every day, running this formula on the whole dataset is an overkill.
Bicycles have speedometers that calculate every second the average speed and the average cadence (pedal RPMs), even though they don't keep the speed for each second, and you may ride hours, days and months without resetting the average counter.
This is done in a simple way: the speedometer keeps the average value and the number of seconds. So when you add a new item to the sum, it just does this:
new_average = (current_speed + old_average * count) / (count + 1)
If you know maths, it's clear that any sum or product can be calculated incremententially. Even updates can be done the same way: substract the old value from the aggregate, add the new value.
I want to know if any database can do this for the programmer?
I understand that the query in the DB may be different and have criteria:
SELECT avg(amount) FROM payments WHERE condition_1 AND condition_2;
Yet, it is possible to cache averages with more information (what rows were in the sequence and what was the filtering condition).
Is this done anywhere?