I have an SQL query to pull items out of my DB and I'm stuck at the ORDER BY stage.
The quantitative parameters I have stored in the db are:
rating - A score from 0-10 based on any number of votes (increments of 0.5)
ratingvotes - The number of people who have voted on this item to generate the rating score
views - The number of people who have viewed this item
lastupdated - unix timestamp of the last time the item was updated
Right now I made a simple algoritm to calculate the "popularity" of the item as follows:
$sqlsort = " ORDER BY ((rating*ratingvotes) / ratingvotes) * greatest(ratingvotes,10) DESC";
This seems to work at a basic level of giving me the most popular items, but the results seem weighted heavily to items which have the highest number of votes, thus the list has remained very stale and unchanging. It seems like the rich just keep getting richer and my result list hasnt changed in the last 6 months. It also seems to unevenly poorly weight items with low number of votes but very high scores.
So, it seems my algorithm isn't that great at truly reflect an items popularity. As an extra twist I wanted to weight it a little bit to account for items which were more recently updated to atleast break the staleness of my result list.
I've played with various random algorithms Ive made up in my head and each performs worse then the last, with the above algo performing the best, but as mentioned, its just very stale and unevenly gives power to items with alot of votes.
Any ideas how I could do better?
EDIT: I'm trying this formula now which seems to perfom a little better:
$sqlsort = $groupby . " ORDER BY ((rating*ratingvotes) * (1.98/ratingvotes)) + (LOG(ratingvotes)*2) + (lastupdated/50000000) DESC";