Ask HN: How to implement a custom rating system (with a declining date relevance)?

[rksprst]

I'm working on a site similar to digg in the respect that users can submit "stories".

I keep track of how many "votes" and "similar adds" each item got. Similar adds are defined as two users adding the same "link".

Here is part of the algorithm (essentially the most important):

   y = day number 
   sy = number of adds on day y

   ∑ y[1:10]  sy / y

So basically calculate the number of "similar adds" on a specified day and divide by the number of seconds since the content was posted originally. Do this for the past 10 days (as an example).

However, I'm not sure how I would go about implementing this with sql.

The only way I can think of implementing this is by calculating the number of adds for the past 10 days for each item submitted, which will take forever. (so a sql command with a group by date, where date >= current_date - 9 and then a sum of that).

Even if I keep a table that I update once a day (and run the above sql in the background), that will still be ridiculously slow once the database gets large. Plus the rating will be "outdated" since it's not live (e.g. breaking news "items" will never reach the top).

Does anyone have any experience of how to go about doing this?

Thanks.

(I posted on stack overflow first, but reposted here since thought you guys might have more experience with this topic... http://stackoverflow.com/questions/368369/how-to-implement-a-custom-rating-system-with-a-declining-date-relevance )

[olefoo]

Why not just calculate it as an insert trigger but only look at the current day and have a table that stores a running total per item/day. That way you never have to go back to calculate days that are done.

And yes, this is more expensive up front, since you're taking a hit per insert; but your data base is always current.