P Kishor <[email protected]> wrote: > I am trying to develop a weighted rating system. I have the following > schema -- > > CREATE TABLE ideas ( > idea_id INTEGER PRIMARY KEY, > idea_stars REAL DEFAULT 10, -- creator's 10 out of 10 stars; > fractional possible > idea_votes INTEGER DEFAULT 1 -- creator's vote > ); > > Every item above, upon creation, gets 10 out 10 possible stars from > its creator, and 1 vote of its creator. The creator's vote is stored > in the following table -- > > CREATE TABLE votes ( > user_id INTEGER, > idea_id INTEGER, > num_of_stars INTEGER, > PRIMARY KEY (user_id, idea_id) > ); > > Note: user_id is from a users table not shown here. > > Everytime a user rates an 'idea', the following happens > > INSERT INTO votes (:user_id, :idea_id, :num_of_stars); > > Now, I want to fire a trigger that calculates a weighted rating for > the item just inserted into 'votes' for every subsequent user who > comes by and votes. The cumulative rating of every entry in 'ideas' > (stored in ideas.idea_stars) should be weighted by the number of votes > cast for that entry (stored in ideas.idea_votes). I believe this would > be a Bayesian ranking, no?
Unless I'm missing something in your description, this is regular unweighted rating. An item with just a single +10 vote will jump ahead and be considered "the best". The whole point of Bayesian rating is to weigh the vote by the average vote on _all_ items, so that items with just a few votes are weighted down. For details, see http://www.thebroth.com/blog/118/bayesian-rating > CREATE TRIGGER update_ideas_votes AFTER INSERT ON stars > BEGIN > UPDATE ideas > SET > idea_stars = ??, > idea_votes = idea_votes + 1 > WHERE idea_id = new.idea_id; > END; > > I have been reading a bit about Bayesian ranking, but ?? above has me > lost for words. So, I come to you. For simple rating you described, that would be idea_stars = (idea_stars * idea_votes + new.num_of_stars) / (idea_votes + 1) It may be easier to store the total number of stars given by all votes. Then you update it simply as idea_total_stars = idea_total_stars + new.num_of_stars Whenever you need the average rating, just do idea_total_stars / idea_votes. Igor Tandetnik _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
