score:0

There wouldn't be a need to execute SQL 10 times to get the result, you could get it in one execution something like:

select sum(dayval)
from
( select count(*) / (current_date-day+1) dayval
  from votes
  where story_id = 123
  and day >= current_date - 9
  group by (current_date-day+1)
)

(Actual code varies according to DBMS used).

I'm not claiming this would perform well though!

Perhaps a compromise: calculate and store the "start of day" value in a daily batch process, but then add 1 to the stored value for each vote received during the day?

score:2

Try this one: everyone has one vote. Your vote sticks to the last thing you voted for. The time thing would come from user behaviour.

score:1

there is a logarithmic weighted average that you could do. the advantage of this is that you only ever need to store the "current value" and the weighted average. In your case the "current value" could be number of votes that day, and you could recalculate the weighted average each night.

const float WeightFactor = 0.70; //for example
float PreviousAverage = GetPreviousAverage();
float CurrentValue = GetVoteCountToday();

float NewAverage = (WeightFactor * CurrentValue) + ( (1-WeightFactor) * PreviousAverage);

This only really works if you have a new value that occurs at a set frequency. If you want to recalculate your vote at arbritary times then this wouldn't work.


Related Articles