0

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";
Mark
  • 3,653
  • 10
  • 30
  • 62
  • Asking about SQL post SQL code, not PHP one. – Akina Dec 22 '22 at 07:33
  • *Any ideas how I could do better?* When you need to rate your objects then take the statistic for some period only (for example, for last 12 months) with different weights (old data have less normalizing weight, for example, the weight may be inversed row age in days). Also you may use some pre-calculations (for example, monthly). – Akina Dec 22 '22 at 07:41
  • Just multiply the 'ratingvotes' and 'rating': `ORDER BY (ratingvotes*rating) DESC` – Neeraj Kumar Dec 22 '22 at 10:27

0 Answers0