Calculating Ratings - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Calculating Ratings

Author

Topic

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-22 : 12:45:31

Hi

i have a table like this:


id  articleID,  userID, rating
1    1             1      2
2    1             2      1
3    1             3      2
4    2             1      2
5    3             2      1

How do I calculate the rating for an article?

Is it: 'total rating'/'Number of users' ?

Also, how would I get the Top 2 articles from the above table?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 12:55:50

TOP 2 regarding what? Total highest rating?

select top 2 q.articleid
from (select articleid from yourtable group by articleid order by sum(rating)) q

You should rely on Bayesian formula here for somewhat reliable result.

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-22 : 12:58:06

SELECT articleID, avg(rating) rating
FROM table
GROUP BY articleID

Like this if you want fractions

SELECT articleID, avg(cast(rating as decimal(5, 2))) rating
FROM table
GROUP BY articleID

Like this for top 2

SELECT TOP 2 articleID, avg(rating) rating
FROM table
GROUP BY articleID
ORDER BY rating desc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 12:59:38

ORDER BY AVG(Rating) DESC

right?

Peter Larsson
Helsingborg, Sweden

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-22 : 13:14:24

quote:
Originally posted by Peso

ORDER BY AVG(Rating) DESC

right?

Peter Larsson
Helsingborg, Sweden

Yes, although mine is OK because I named the aggregated column rating too. I guess I should have said
SELECT TOP 2 articleID, avg(rating) AvgRating
FROM table
GROUP BY articleID
ORDER BY AvgRating desc

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-22 : 17:28:24

Thanks a lot.

Even though, I did not explain fully (because I was in a hurry to leave the desk), you guys understood me correctly.

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-23 : 07:11:40

quote:
Originally posted by snSQL
Yes, although mine is OK because I named the aggregated column rating too. I guess I should have said
SELECT TOP 2 articleID, avg(rating) AvgRating
FROM table
GROUP BY articleID
ORDER BY AvgRating desc

Actually, I need some information again.
The above statement can give a very varied result. E.g the average rating of some articles may be 44, and others may be 10, 8, 5.

In my system I need to display five stars to indicate the ratings of an article. But if the rating can be 44, 10, 3... How do I know whether to display four stars or three stars...

I think I need Bayesian formula to calculate such rating. I did google it, but could not find any information on how to use the Bayesian formula to calculate the ratings.

Would be grateful if you help in this regards as well.
Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 07:16:14

[code]The formula for calculating the Top 250 Rated Titles gives a true Bayesian estimate:

weighted rating (WR) = (v ÷ (v+m)) × R + (m ÷ (v+m)) × C

where:

R = average for the movie (mean) = (Rating)
v = number of votes for the movie = (votes)
m = minimum votes required to be listed in the Top 250
C = the mean vote across the whole report[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 07:24:14

This formula is not easy to truly understand.

Try with this analogy:

For a ranking of 1 (which is the highest ranking), the article is awarded 12 pts.
For a ranking of 2 (which is the second highest), the article is awarded 10 pts.
For a ranking of 3, the article is awarded 8 pts.
For a ranking of 4, the article is awarded 7 pts.
For a ranking of 5, the article is awarded 6 pts.
For a ranking of 6, the article is awarded 5 pts.
For a ranking of 7, the article is awarded 4 pts.
For a ranking of 8, the article is awarded 3 pts.
For a ranking of 9, the article is awarded 2 pts.
For a ranking of 10, the article is awarded 1 pts.

Now sum up all points for every article and sort them by the sum of pts, descending.

Peter Larsson
Helsingborg, Sweden

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-11-23 : 08:39:50

Aha...the "Eurovision" method of scoring!!!!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 08:45:23

You noticed! Great!
Then I think I explained it simple enough...

With the Bayesian estimated, the weighted ranking can never be larger than highest possible score.

Peter Larsson
Helsingborg, Sweden

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-23 : 09:56:32

Hi Peso,

I have just tried to implement your fomula. But I am getting a 0 value. Would be greatful if you could have a look at the code:


Create PROCEDURE [dbo].[CalculateRatingTest2]

@submissionID int,
@rating int output

AS

-- AvgRating = average for the entry (mean) = (Rating) 

-- votes = number of votes for the entry = (votes)

-- minimum = minimum votes required to be listed 

-- wholeAvgRating = the mean vote across the whole report

declare @avgRating int
declare @votes int
declare @minimum int
declare @wholeAvgRating int


SET NOCOUNT ON;

BEGIN

-- average rating of this entry
select @avgRating = avg(cast(intRating as decimal(5, 2)))from Ratings where submissionID= @submissionID

-- number of votes for this item
select @votes = count(*) from Ratings where submissionID= @submissionID

set @minimum = 1

-- average rating of all entries
select @wholeAvgRating = avg(cast(intRating as decimal(5, 2)))from Ratings

set @rating = (@votes / (@votes+@minimum)) * @avgRating + (@minimum / (@votes+@minimum)) * @wholeAvgRating 
END

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 10:00:59

You have declared your Rating as INT.
Declare as NUMERIC(9, 4) instead. This applies to all your variables.

Peter Larsson
Helsingborg, Sweden

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-23 : 10:24:25

Thanks again,
Now it seems to be working.

I have been working with SQL for sometime, but I am still never sure where to use int, money, numeric....

>> minimum votes required to be listed in the Top 250

Can I set this value to 1 so all the votes are counted?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 11:15:16

Of course. You decide the parameters for your ranking.

Peter Larsson
Helsingborg, Sweden

hasanali00
Posting Yak Master

207 Posts

Posted - 2006-11-24 : 04:28:57

I hope this is last ever question on rating.