By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,918 Members | 1,557 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,918 IT Pros & Developers. It's quick & easy.

Need help with query involving automatically decaying values

P: 1
Hi,

I'm trying to put together a query that will order entries in a table by the rating values which decay over time. I'm not too well versed in the intricacies of Postgres, or complex queries, but I've cobbled the following together:

Expand|Select|Wrap|Line Numbers
  1. select * from entries order by (
  2.    select sum(-rating_value * exp(ln(0.5)/43200 * 
  3.                     extract(epoch from now() - rating_time))) 
  4.    from ratings where ratings.entry_id = entries.id)
  5.  
There are two tables - entries and ratings. The ratings table stores a few values such as the time of the rating, the user who contributed the rating, the initial value, the related entry id, etc. The -rating_value was just thrown in while I was testing and could just as easily be replaced by a DESC. The ln(0.5)/43200 causes the rating to lose half its current value every 12 hours.

It seems that as the tables grow, this query could start taking quite a bit of time (it's already quite a bit slower than my average query), so I had a few questions:

1.) Is there a much better way to do this, via JOINs or something else? I was unsuccessful in constructing a working JOIN.

2.) Is there any way to reduce the number of selects? This will run a select for each entry in the database to grab a rating value for ordering.

3.) Is there a better formula that would prevent the need for a SUM on every select everytime the query is run? It is important to retain the shown behavior so that an entry that receives 500 good ratings today will show up as "popular" for the moment, but if nobody ever votes on it again we don't have to wait until the 500 vote record is broken to see the more recent popular items. Basically, the items will slowly settle into their "overall" popular positions after enjoying the spotlight for a few days.

I appreciate any help. Thanks in advance!

-Dave
Apr 28 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.