On Mon, Oct 5, 2009 at 6:29 PM, Igor Tandetnik <[email protected]> wrote: > 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.
Igor, you are correct in that, my description and implementation of DEFAULT values for stars and votes in the ideas tables was wrong and misapplied. These DEFAULTs have place in the stars table if I go with the assumption that a user will submit a new idea only because that user thinks the world of that idea. However, the actual ranking of that idea will really be very low to begin with because it has only one vote. > For details, see > > http://www.thebroth.com/blog/118/bayesian-rating Yes, I read that, and was trying to create triggers based upon that, but got horribly lost. > >> 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. Good advice on storing often used values to speed up the computation, but, as I noted above, I *am* looking for a Bayesian (weighted?) ranking system, not the one I ended up inadvertently implying above. > > Igor Tandetnik > > > > _______________________________________________ > sqlite-users mailing list > [email protected] > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org Carbon Model http://carbonmodel.org Charter Member, Open Source Geospatial Foundation http://www.osgeo.org Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor Nelson Institute, UW-Madison http://www.nelson.wisc.edu ----------------------------------------------------------------------- Assertions are politics; backing up assertions with evidence is science ======================================================================= Sent from Madison, WI, United States _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
