All geek questions in one place

Bayesian Order Database Results

I'm not sure if this is possible, but I need confirmation before making it in the "ugly" way :)

So, “results” are records in a database that are stored as follows:

  • a posts table containing all the important things like id, title, content
  • metadata that contains additional data, such as rating ( this_rating ) and number of votes ( this_num_votes ). This data is stored in pairs, the table has 3 columns: post ID / key / value. This is basically a WordPress table structure.

I want to pull out the highest ratings sorted by this formula:

br = ((avg_num_votes * avg_rating) + (this_num_votes * this_rating)) / (avg_num_votes + this_num_votes )

which I stole the form here .

avg_num_votes and avg_rating are known variables (they are updated at every vote), so they do not need to be calculated.

Can this be done using mysql query? Or do I need to get all messages and sort with PHP?

+4
source share
3 answers

Data exchange:

http://data.stackexchange.com/stackoverflow/s/2137/order-database-results-by-bayesian-rating

 SELECT id,title,( AVG(this_num_votes) * AVG(this_rating) + this_num_votes * this_rating ) / ( AVG(this_num_votes) + this_num_votes ) as br FROM posts LEFT JOIN ( SELECT DISTINCT post_id, (SELECT meta_value FROM postmeta WHERE postmeta.post_id = pm.post_id AND meta_key ='this_num_votes') as this_num_votes, (SELECT meta_value FROM postmeta WHERE postmeta.post_id = pm.post_id AND meta_key ='this_rating') as this_rating FROM postmeta pm ) as newmeta ON posts.ID = newmeta.post_id GROUP BY id,title,this_num_votes,this_rating ORDER BY br DESC
+2
source

this is the beginning:

 // Bayesian Rating Calc $theItem = $_GET['id']; if($theItem) { // all items votes and ratings $result = mysql_query("SELECT AVG(item),AVG(vote) FROM itemvotes WHERE vote>'0′ GROUP BY item") or die(mysql_error()); $row = mysql_fetch_row($result); $avg_num_votes = $row[0]; $avg_rating = $row[1]; // this item votes and ratings $result = mysql_query("SELECT COUNT(item),AVG(vote) FROM itemvotes WHERE item='$theItem' AND vote>'0′") or die(mysql_error()); $row2 = mysql_fetch_row($result); $this_num_votes = $row2[0]; $this_rating = $row2[1]; if(!$row OR !$row2) $br = "_"; else $br = number_format( ((($avg_num_votes * $avg_rating) + ($this_num_votes * $this_rating))/($avg_num_votes + $this_num_votes)), 1, '.' ); } // end of if item selected
+1
source

I changed the received response data and request.

The data is now a sample of data from a 5-star rating system.
Now the function correctly uses the average values ​​of all messages.

The difference is in calculating these means, instead of PHP, they are calculated in SQL for the purpose of the answer.

You can see this in action on SQL Fiddle: http://sqlfiddle.com/#!9/84d8b/2/2

Updated request

New fiddle: http://sqlfiddle.com/#!9/3cdfe/1/2

 SET @avg_total_votes := (SELECT AVG(meta_value) FROM postmeta WHERE meta_key ='this_num_votes'); SET @avg_total_rating := (SELECT AVG(meta_value) FROM postmeta WHERE meta_key ='this_rating'); SELECT posts.ID, posts.title, getmeta_votes.meta_value AS votes, getmeta_rating.meta_value AS rating, ( ( (@avg_total_votes * @avg_total_rating) + (getmeta_votes.meta_value * getmeta_rating.meta_value) ) / ( @avg_total_votes + getmeta_votes.meta_value ) ) AS factor FROM posts LEFT JOIN postmeta AS getmeta_votes ON posts.ID = getmeta_votes.post_id AND getmeta_votes.meta_key = 'this_num_votes' LEFT JOIN postmeta AS getmeta_rating ON posts.ID = getmeta_rating.post_id AND getmeta_rating.meta_key = 'this_rating' WHERE NOT getmeta_votes.meta_value = 0 AND NOT getmeta_rating.meta_value = 0 ORDER BY factor DESC;

I found that building this query was much faster, the previous one worked for 2 hours with a 2000 second dataset (1,000,000+ wp_postmeta lines) until it was complete.

This is done in 0.04 s.

+1
source

Source: https://habr.com/ru/post/1382313/

More articles:


All Articles