Order by rating
If you want to sort anything, like products or movies, by user rating, the simple method may jump into mind:
- SELECT * FROM products ORDER BY rating DESC;
Yes, it will work, but tell me, which product you perceive as better: the one with a single 5 star rating, or the other with thousands of 5 star ratings and just a few 1 stars? Because the example above will favor the first one.
The easiest remedy is to exclude those with less than a certain number of ratings, but what if you don't want to? Then you may user Bernoulli parameter, optionally with a Wilson score confidence interval.
Incorrect:
SELECT name, positive, negative, positive/total x FROM productz ORDER BY x DESC;
By the way, I'm not sure that the Wilson correction gives any better results than a one standard deviation lower bound for the positive score:
SELECT name, positive, negative, positive/total - sqrt(positive*negative/total)/total x FROM productz ORDER BY x DESC;
Score = Lower bound of Wilson score confidence interval for a Bernoulli parameter:
SELECT name, positive, negative, ((positive + 1.9208) / (positive + negative) -
1.96 SQRT((positive negative) / (positive + negative) + 0.9604) /
(positive + negative)) / (1 + 3.8416 / (positive + negative))
AS ci_lower_bound
FROM productz
WHERE positive + negative > 0
ORDER BY ci_lower_bound DESC;
Single 1 0 1
Good new 5 0 1
Excellent 23400 23 0.9988134107340426
Quite good 520 200 0.7055298549839887
Exc-ish 5 1 0.6811881781678829
Some shitty 1000 2000 0.3247267033417656
Quite bad 28 82 0.21301210856756564
Bad-ish 1 2 0.06116780616017409
Single bad 0 1 0
Single 1 0 1.0000
Good new 5 0 1.0000
Excellent 23400 23 0.9990
Exc-ish 5 1 0.8333
Quite good 520 200 0.7222
Some shitty 1000 2000 0.3333
Bad-ish 1 2 0.3333
Quite bad 28 82 0.2545
Single bad 0 1 0.0000
Excellent 23400 23 0.998
Quite good 520 200 0.688
Good new 5 0 0.565
Exc-ish 5 1 0.436
Some shitty 1000 2000 0.316
Single 1 0 0.206
Quite bad 28 82 0.182
Bad-ish 1 2 0.061
Single bad 0 1 4.586
rating = (((positive + 1.9208) / (positive + negative) - 1.96 Math.Sqrt(((positive negative) / (positive + negative)) + 0.9604) / (positive + negative)) / (1 + 3.8416 / (positive + negative)));
Qarate
I like watching TV series, my list of favorites is quite long. I also like to re-watch them, from time to time, but sometimes only the good episodes. I had a list of good ones on my personal wiki, but it was boring to note them every time (and when I watch multiple episodes in a row, I simply forget to note it and then I forget which one was the good one).
I did some research and found Trakt and became a happy user, because rating was quick and easy enough for me to actually do it.
Trakt logo
One day Trakt creators upgraded the website and rating system was still in “to-do”, so now I had nowhere to rate. I tried to find any suitable replacement, but without any success. I also use EpisodeCalendar, which only tracks watched episodes – without rating.
So, out of frustration, I added a new table “rating” to my personal wiki. It allowed me to rate not only episodes, but anything else – movies, songs, cars, vacations, etc. I was so pleased I thought this might be a nice service for others to use as well.
The biggest drawback was, that I had to enter the thing I was rating, mostly episode identification. I don’t want my users to add anything. First of all, it’s not quick and easy. Second of all, it will get wrong and duplicate (sorry, users...). It would require something, where all such data already is. Oh, wait!
I’m building qb for several years now and it will be the perfect marriage: a big database with “everything”, plus a website, that allows you to rate anything. Sounds great.
I looked for a fitting name, which starts with “Q” and contains “rate” and in the shower (which is a common place for ideas for many people, according to one article I read) I thought of “Qarate”.
You can pronounce it as “Karate” (martial art) or “Carat” (unit of mass for gems and pearls) or “Karat” (fineness of gold). Therefore users may form dojos and level as gem rarity and belt colors (like blue diamond is more, than black sapphire).
Rating system will consist of 5 grades with matching score for that entity: +2 (excellent), +1 (good), 0 (meh), -1 (bad) and -2 (terrible). User may have limited +3 grades (the best), which can be used to mark a notable thing of it’s kind, like one episode in a TV series season.
And it will have positive effect on qb as well, because in the original release I included user generated content into the database, like comments or ratings, which I didn't like. Now I can separate data from user contaent into two different products.
Qarate
I like watching TV series, my list of favorites is quite long. I also like to re-watch them, from time to time, but sometimes only good episodes. I had a list of good ones on my personal wiki, but it was boring to note them each time (and when I watch multiple episodes in a row, I simply forget to note it and then I forget which one was the good one). So I discovered Trakt and was happy about it. Rating was quick and easy enough for me to actually do it.
One day Trakt creators upgraded the website and rating system was still in “to-do”, so now I had nowhere to rate. I tried to google some replacement, but without any success. So, out of frustration, I added a new table “rating” to my personal wiki. It allowed me to rate not only episodes, but anything else – movies, songs, cars, vacations, etc. I was so pleased I thought this might be a nice service for others to use as well.
Qarate Logo. Hajime!
The biggest drawback was, that I had to enter the thing I was rating, mostly episode identification. I don’t want my users to add anything. First of all, it’s not quick and easy. Second of all, it will get wrong and duplicate (sorry, users...). It would require something, where all such data already is. Oh, wait!
I’m building qb for several years now and it will be the perfect marriage: a big database with “everything”, plus a website, that allows you to rate anything. Sounds great.
I looked for a fitting name, which starts with “Q” and contains “rate” and in the shower (which is a common place for ideas for many people, according to one article I read) I thought of “Qarate”. You can pronounce it as “Karate” (martial art) or “Carat” (unit of mass for gems and pearls). Therefore users may form dojos and level as gem rarity and belt colors (like blue diamond is more, than black sapphire).
Rating system will consist of 5 grades with matching score for that entity: +2 (excellent), +1 (good), 0 (meh), -1 (bad) and -2 (terrible). User may have limited +3 grades (the best), which can be used to mark notable things of it’s kind, like one episode in a TV series season.