我建议使用像这样的排序How Not To Sort By Average Rating http://www.evanmiller.org/how-not-to-sort-by-average-rating.html
PROBLEM:
您需要某种“分数”来排序。
错误的解决方案#1:分数=(正面评级)-(负面评级)
错误的解决方案#2:分数 = 平均评分 =(正面评分)/
(总收视率)
正确的解决方案:分数 = Wilson 分数置信度的下限
伯努利参数的区间
Demo:
CREATE TABLE clans(id INT, name VARCHAR(100), warswon INT, warslost INT);
INSERT INTO clans VALUES (1, 'aaa', 208, 6), (2, 'bbb', 103, 0);
SELECT id, name,warswon, warslost,((warswon + 1.9208) / (warswon + warslost) -
1.96 * SQRT((warswon * warslost) / (warswon + warslost) + 0.9604) /
(warswon + warslost)) / (1 + 3.8416 / (warswon + warslost))
AS ci_lower_bound
FROM clans
ORDER BY ci_lower_bound DESC;
SqlFiddleDemo http://sqlfiddle.com/#!9/e91ac/1/0
Output:
╔═════╦═══════╦══════════╦═══════════╦════════════════════╗
║ id ║ name ║ warswon ║ warslost ║ ci_lower_bound ║
╠═════╬═══════╬══════════╬═══════════╬════════════════════╣
║ 2 ║ bbb ║ 103 ║ 0 ║ 0.9640439675800224 ║
║ 1 ║ aaa ║ 208 ║ 6 ║ 0.9401908847803808 ║
╚═════╩═══════╩══════════╩═══════════╩════════════════════╝