php - mysql查询按两列排序，每列都有权重

Question

我有表 Foo(id, name, rateAvg, rateNum)。 rateAvg 介于 1 到 5 之间，rateNum 是用户的费率数。

我用 mysql 查询表并按最受好评的 Foos 排序，如下所示:

SELECT * FROM Fooo ORDER BY rateAVG DESC, rateNum DESC

但这不够公平，例如，一行的 rateAvg 为 4.8，比率为 1000，而另一行的 rateAvg 为 5 和 10，根据我的查询项目，两个排在第一位。

编辑:通过@kingkero 的评论，我发现 rateAvg 和 rateNum 中的每一个都应该有一定的排序权重，如何在我的查询中应用它

Answer 1

您可以尝试申请bayesian average , 但您应该预先计算此评级并存储在您的字段之一中。

b(r) = [ W(a) * a + W(r) * r ] / (W(a) + W(r)]

r = average rating for an item
W(r) = weight of that rating, which is the number of ratings
a = average rating for your collection
W(a) = weight of that average, which is an arbitrary number,
       but should be higher if you generally expect to have more ratings
       for your items; 100 is used here, for a database which expects
       many ratings per item
b(r) = new bayesian rating

例如你的情况:

a = (4.8 * 1000 + 5 * 10) / 1010 = 4.8019
r1 = 4.8
r2 = 5
W(a) = 100 // arbitrary average weight
W(r1) = 1000 // weight of first raiting
W(r2) = 10 // weight of second rating

b(r1) = (100 * 4.8019 + 1000 * 4.8) / (100 + 1000) = 4.8001
b(r2) = (100 * 4.8019 + 10 * 5) / (100 + 10) = 4.8199

因此您可以看到这些值彼此接近，您可以尝试更改平均权重以针对您的情况设置此贝叶斯平均评分。