按贝叶斯评级订购数据库结果

I'm not sure this is even possible, but I need a confirmation before doing it the "ugly" way :)

我不确定这是否可行,但在做“丑陋”的方式之前我需要确认:)

So, the "results" are posts inside a database which are stored like this:

因此,“结果”是数据库中的帖子,其存储方式如下:

• the posts table, which contains all the important stuff, like the ID, the title, the content

posts表,其中包含所有重要的内容,如ID,标题,内容

• the post meta table, which contains additional post data, like the rating (this_rating) and the number of votes (this_num_votes). This data is stored in pairs, the table has 3 columns: post ID / key / value. It's basically the WordPress table structure.

post元表,包含额外的帖子数据,如评级(this_rating)和投票数(this_num_votes)。该数据成对存储,该表有3列:帖子ID /键/值。它基本上是WordPress表结构。

What I want is to pull out the highest rated posts, sorted based on this formula:

我想要的是抽出评分最高的帖子,根据这个公式排序:

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

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

which I stole form here.

我偷了这里的形式。

avg_num_votes and avg_rating are known variables (they get updated on each vote), so they don't need to be calculated.

avg_num_votes和avg_rating是已知变量(它们在每次投票时都会更新),因此不需要计算它们。

Can this be done with a mysql query? Or do I need to get all the posts and do the sorting with PHP?

这可以用mysql查询完成吗?或者我是否需要获取所有帖子并使用PHP进行排序?

3 个解决方案

#1

Data Stack Exchange Link:

数据堆栈交换链接:

http://data.stackexchange.com/*/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

here it's a start to go:

这是一个开始:

// 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

#3

I have altered the accepted answer's data and query.

我已经修改了接受的答案的数据和查询。

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

该数据现在是来自5星评级系统的数据样本。该功能现在可以正确使用所有帖子的平均值。

The difference is in the calculation of those average values, instead PHP, they are calculated in the SQL, for the purpose of the answer.

不同之处在于计算那些平均值,而不是PHP,它们是在SQL中计算的,用于答案。

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

您可以在SQL Fiddle上看到它:http://sqlfiddle.com/#!9/84d8b / 2/2

---

The updated query

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

新的小提琴: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 have found this query construction to be much faster, the previous one was working for 2 hours on a 2,000+ posts data set (1,000,000+ wp_postmeta rows), until terminated.

我发现这个查询构造要快得多,前一个在2,000+帖子数据集(1,000,000+ wp_postmeta行)上工作了2个小时,直到终止。

This one runs in 0.04 sec.

这个运行0.04秒。