Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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?

share|improve this question
2  
Please note that, depending on the precision of the average stored, successive stored values will be less accurate the farther they are from the initial value. –  Clockwork-Muse Mar 8 '12 at 0:13

2 Answers 2

up vote 3 down vote accepted

I want to know if any database can do this for the programmer?

What you need is a database supporting triggers, almost any "serious" relational database supports this. Then you can write a stored procedure which does the needed calculations on any insert, delete and update to your payment table automatically. You will also need an additional table (for example, a payment_aggregates table), to hold calculation results. And if you want to do some aggregates for restricting criteria, you need to know the criteria beforehand. You will have to provide one record in your payment_aggregates table for each potential subset of payment defined by your criteria.

If you need this to be done in a more generalized way, you should have a look at the way how OLAP databases are designed. Those databases are optimized for allowing fast queries for aggregates on huge data sets.

share|improve this answer
    
Thanks. I worked as DB dev for 2 years, so I know triggers. I thought I might have overseen something. –  culebrón Mar 7 '12 at 17:11

Denormalization of the DB could be used in this instance.

In other words, for every new insert of a payment, you would update a total payments tracking value.

This is not a built in feature however.

share|improve this answer
1  
+1, Just to add to this, it is good practice to have a repair function that Fixed all this data every now ant then. A good example of this is what is going on now with the Rep Reclacs here on SE. The SE guys claim that the system is so bullet proof that a Recals will never be needed.. We'll see about that. –  Morons Mar 7 '12 at 13:34
    
yep, good call! –  jmo21 Mar 7 '12 at 13:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.