我想根据票数创建一个权重的评级。
因此,1 票 5 票不可能比 4 票 4 票更好。
我找到了这个数学形式:
bayesian = ( (avg_num_votes * avg_rating) + (this_num_votes * this_rating) ) / (avg_num_votes + this_num_votes)
我如何进行 MySQL SELECT 来获取最佳评分图像的 ID。
我有一个用于图像的表和一个用于投票的表
VOTING:
id
imageID
totalVotes
avgVote
我想我必须用 SELECT in SELECT 来做到这一点,但是怎么做呢?
第一步是计算avg_num_votes
and avg_rating
:
SELECT
SUM(totalVotes)/COUNT(*) AS avg_num_votes,
SUM(avgVote)/COUNT(*) AS avg_rating
FROM voting;
如果您可以忍受小错误,那么偶尔计算一下就足够了。
现在,使用您的公式和上面的值,您可以运行称重查询。作为一个小的优化我预先计算avg_num_votes * avg_rating
并称之为avg_summand
SELECT
voting.*, -- or whatever fields you need
($avg_summand+totalVotes*avgVote)/($avg_num_votes+totalVotes) AS bayesian
FROM voting
ORDER BY bayesian DESC
LIMIT 1;
Edit
您可以将其作为联接运行:
SELECT
voting.*, -- or whatever fields you need
(avg_num_votes*avg_rating+totalVotes*avgVote)/(avg_num_votes+totalVotes) AS bayesian
FROM voting,
(
SELECT
SUM(totalVotes)/COUNT(*) AS avg_num_votes,
SUM(avgVote)/COUNT(*) AS avg_rating
FROM voting AS iv
) AS avg
ORDER BY bayesian DESC
LIMIT 1;
但这将计算每个查询的总和和平均值——称之为性能炸弹。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)