CODEWITHSUNDEEP
sql
Sergio
Sergio

Reputation: 9917

Sorting by weighted rating in SQL?

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

Upvotes: 2

Views: 3608

Answers (5)

Paul
Paul

Reputation: 16843

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).

Upvotes: 2

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

Reputation: 50017

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

Upvotes: 0

fyjham
fyjham

Reputation: 7034

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.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425311

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

Upvotes: 2

guigui42
guigui42

Reputation: 2500

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

Upvotes: 1

Related Questions