MySql逻辑顺序 [英] MySql Logical Order By

问题描述

我目前有这样的查询:

SELECT 'id', 'clanid', 'name',
       'level', 'exp', 'warwinpercent',
       'warswon', 'warslost', 'warstied',
       'playercount', 'score'
FROM clans
WHERE warswon >= 100
ORDER BY warwinpercent DESC, warswon DESC;

现在可以正常工作了，但最终它不像我希望的那样合理...

Now that works but in the end it isn't as logical as I would hope it could be...

例如.

比方说，这里有99.5738％的战争获胜率和208战争的获胜率. 另一个具有100％的战争胜利率和103次战争的胜利.

Let's say there is a row which has 99.5738% war win percent and 208 wars won. And another which has 100% war win percent and 103 wars won.

我希望99％的行高于100％的行.我有什么办法让它正常工作吗?

I'd want the 99% row to be above the 100% row. Is there any way for me to get this working?

我想要的等式是:

warinpercent = warswon/(warswon+warstied+warlost)*100
order by warwinpercent
if warwinpercent are in a range of 3% then order by warswon between them.

推荐答案

我建议像

问题:

您需要某种分数"来进行排序.

You need some sort of "score" to sort by.

错误的解决方案#1 :得分=(正面评分)-(负面评分)

WRONG SOLUTION #1: Score = (Positive ratings) - (Negative ratings)

错误的解决方案#2:得分=平均评分=(正面评分)/ (总评分)

WRONG SOLUTION #2: Score = Average rating = (Positive ratings) / (Total ratings)

正确的解决方案:得分=威尔逊得分置信度的下限 贝努利参数的时间间隔

CORRECT SOLUTION: Score = Lower bound of Wilson score confidence interval for a Bernoulli parameter

演示:

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

输出:

╔═════╦═══════╦══════════╦═══════════╦════════════════════╗
║ id  ║ name  ║ warswon  ║ warslost  ║   ci_lower_bound   ║
╠═════╬═══════╬══════════╬═══════════╬════════════════════╣
║  2  ║ bbb   ║     103  ║        0  ║ 0.9640439675800224 ║
║  1  ║ aaa   ║     208  ║        6  ║ 0.9401908847803808 ║
╚═════╩═══════╩══════════╩═══════════╩════════════════════╝

这篇关于MySql逻辑顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！