Sorting by weighted rating in SQL?

Question

I have a table of events, and another of ratings. Each event can have many ratings. I need to be able to to pull the top three events from the database by rating, but of course, the ratings need to be averaged, so an event with one five star rating is not rated more highly than one with 4 average and 100 ratings.

Can anyone guide me as to how to create this in SQL?

Thanks

UPDATE Thanks guys I suppose I should have been clearer with how things should be weighted (although I think I need to think more on how exactly I want this to work). SQL provided is a great help though

How do you want to sort these events: 100 ratings, avg 5, 100 ratings, avg 4, 99 ratings, avg 5. — Quassnoi, Dec 10 '09 at 12:30
I suppose I would want them sorted in the order you have given them. need to give this some thought... — Sergio, Dec 10 '09 at 14:44

score 2 · Answer 1 · answered Dec 10 '09 at 12:26

Your algorithm description is very vague.

This query will return events with 100 ratings or less at the bottom:

SELECT  *
FROM    (
        SELECT  e.id, AVG(r.rating) AS ar, COUNT(*) AS cnt
        FROM    event e
        JOIN    rating r
        ON      r.event = e.id
        GROUP BY
                e.id
        ) q
ORDER BY
        CASE WHEN cnt >= 100 THEN 0 ELSE 1 END, ar DESC

Paul · Answer 2 · 2009-12-10T18:31:26.580

As others have said, your algorithm is a little vague. The SQL below will do what you need, but you'll need to fill in the ____________ with whatever ranking function you want.

SELECT TOP 3 intId, fltAvgRating, intRatings
FROM (
    SELECT 
        e.intId,
        AVG(CAST(r.intRating AS float)) AS fltAvgRating,
        COUNT(*) AS intRatings
    FROM Event e
        INNER JOIN Rating r ON r.intEventId = e.intId
    GROUP BY e.intId
) AS T
ORDER BY _________ DESC

An example could be fltAvgRating + LOG(intRatings).

score 1 · Answer 3 · answered Dec 10 '09 at 12:26

1

Since you dont give any specifics about your DBMS, its hard to say exactly how you can do it.

But i think Analytics (ORACLE only) is what you are looking for : http://www.orafaq.com/node/55

answered Dec 10 '09 at 12:26

guigui42

2,441
8
35
48

score 1 · Accepted Answer · answered Dec 10 '09 at 12:27

1

Making some assumptions about your column names and the rules of your rating system (Which is very vague), and also based on an MSSQL background you could do something like:

select * from event where id in (
   select top 3 eventid
   from ratings
   group by eventid having count(*)>100 order by avg(rating) desc
)

You may want to denormalize this rating value into event for performance reasons if you have a lot of ratings coming in.

answered Dec 10 '09 at 12:27

fyjham

7,004
2
32
40

Think this is close to what Ill use (using MS SQL Server) – Sergio Dec 10 '09 at 14:45

score 0 · Answer 5 · answered Dec 10 '09 at 12:31

0

This is somewhat Oracle specific as it uses ROWNUM, but try

SELECT * FROM
  (SELECT EVENT_ID, EVENT_NAME FROM EVENTS) E
INNER JOIN
  (SELECT EVENT_ID, AVG(RATING) AS AVG_RATING
     FROM EVENT_RATINGS
     GROUP BY EVENT_ID) R
ON (EVENT_ID)
ORDER BY AVG_RATING
WHERE ROWNUM <= 3

answered Dec 10 '09 at 12:31

Bob Jarvis - Слава Україні

48,992
9
77
110

I think it communicated the idea, although the syntax was off. How about: SELECT EVENT_ID, EVENT_NAME, AVG_RATING FROM EVENT E INNER JOIN (SELECT EVENT_ID, AVG(RATING) AS AVG_RATING FROM EVENT_RATINGS GROUP BY EVENT_ID) R USING (EVENT_ID) WHERE ROWNUM <= 3 ORDER BY AVG_RATING Share and enjoy. – Bob Jarvis - Слава Україні Dec 11 '09 at 15:23

Sorting by weighted rating in SQL?

5 Answers5

Linked