CODEWITHSUNDEEP
phpmysql
lesssugar
lesssugar

Reputation: 16181

Counting weighted score in MySQL - star rating and votes

I have posts rated with stars, in a 1-to-5 scale. I would like to sort the posts by rating, including the factor of votes number (let's call it - popularity).

I know similar questions were asked before, but I don't see a clear solution. Would be perfect to get to know a formula that can be used for this purpose, as well as it's MySQL equivalent.

Examples below will hopefully explain what I need:

Does anyone have experience in solving this problem? I already tried some simple formulas (like this), but they fail to "recognize" that the circumstances are different in the set of [5, 2.5] and (2.5, 0]. I think that the function to use could be logarithmic, but I can't seem to get to it the proper way.

Any ideas will be appreciated.

Upvotes: 0

Views: 351

Answers (1)

Andrius
Andrius

Reputation: 5939

Something as simple as adding some weights to each of the star count would work, I'd say.

I've created a simple table:

╔════╦════════╦════════╦════════╦════════╗
║ Id ║ Name   ║ Stars3 ║ Stars2 ║ Stars1 ║
╠════╬════════╬════════╬════════╬════════╣
║ 1  ║ Item 1 ║ 1      ║ 0      ║ 0      ║
║ 2  ║ Item 2 ║ 0      ║ 1      ║ 2      ║
║ 3  ║ Item 3 ║ 0      ║ 2      ║ 5      ║
║ 4  ║ Item 4 ║ 4      ║ 0      ║ 2      ║
╚════╩════════╩════════╩════════╩════════╝

Basically, StarsX shows the amount of the specific star.

What I do then, think of some random number for making it fair-ish.

  • 3 stars is worth 5 points;
  • 2 stars is worth 2 points;
  • 1 star is worth 1 point;

Now, a simple query to get the total score:

SELECT *,(stars3*5+stars2*2+stars1) as total_score FROM `posts_with_scores`

And I get this:

╔════╦════════╦════════╦════════╦════════╦═════════════╗
║ Id ║ Name   ║ Stars3 ║ Stars2 ║ Stars1 ║ Total Score ║
╠════╬════════╬════════╬════════╬════════╬═════════════╣
║ 1  ║ Item 1 ║ 1      ║ 0      ║ 0      ║ 5           ║
║ 2  ║ Item 2 ║ 0      ║ 1      ║ 2      ║ 4           ║
║ 3  ║ Item 3 ║ 0      ║ 2      ║ 5      ║ 9           ║
║ 4  ║ Item 4 ║ 4      ║ 0      ║ 2      ║ 22          ║
╚════╩════════╩════════╩════════╩════════╩═════════════╝

A similar system was sometimes used by news papers and such to calculate the score of countries based on the medals they received. Something like 3 points for a gold medal, 2 points for a silver medal and a point for a bronze medal.

Upvotes: 2

Related Questions