mysql - 获取特定范围内的平均值

Question

首先，我有一个基于体育博彩的数据库。我有一张名为“MATCHES”的表(id_match/sport_id/League_id/team/quota/result)。

想象一下表中有以下值:

id_match | sport_id | League_id | team | quota | result
---------------------------------------------------------
   1     |    x     |  xx       | xx   |  1.4  |  W
---------------------------------------------------------
   2     |    x     |  xx       | xx   |  2.4  |  W
---------------------------------------------------------
   3     |    x     |  xx       | xx   |  2.2  |  L
---------------------------------------------------------
   4     |    x     |  xx       | xx   |  2.35 |  W
---------------------------------------------------------

我想提取按特定范围分组的配额的成功百分比，因此我使用的查询如下:

SELECT  AVG(IF(bet_matches.quota BETWEEN 1.01 and 1.24 and bet_matches.result='W', 100, 0)) as '1 - 1.25' ,
    AVG(IF(bet_matches.quota BETWEEN 1.25 and 1.49 and bet_matches.result='W', 100, 0)) as '1.25 - 1.50' ,
    AVG(IF(bet_matches.quota BETWEEN 1.50 and 1.74 and bet_matches.result='W', 100, 0)) as '1.50 - 1.75' ,
    AVG(IF(bet_matches.quota BETWEEN 1.75 and 1.99 and bet_matches.result='W', 100, 0)) as '1.75 - 2' ,
    AVG(IF(bet_matches.quota BETWEEN 2 and 2.49 and bet_matches.result='W', 100, 0)) as '2 - 2.50' ,
    AVG(IF(bet_matches.quota BETWEEN 2.5 and 2.99 and bet_matches.result='W', 100, 0)) as '2.50 - 3' ,
    AVG(IF(bet_matches.quota BETWEEN 3 and 4 and bet_matches.result='W', 100, 0)) as '3 - 4' ,
    AVG(IF(bet_matches.quota >3.99 and bet_matches.result='W', 100, 0)) as '4 - x' 
FROM bet_matches

当配额的胜率没有充分反射(reflect)在结果中时，问题就出现了。每个范围的胜率显示不正确。这些是我生成的结果:

1 - 1.5 | 2 - 2.5 | 
-------------------
  25%   |   50%

这些是我想要获得的结果:

 1 - 1.5 | 2 - 2.5 | 
-------------------
  100%   |   66%

知道如何获得这个结果吗？提前致谢。

Answer 1

我想你想要:

SELECT  AVG( CASE WHEN bet_matches.quota NOT BETWEEN 1.01 and 1.24 THEN NULL
                  WHEN bet_matches.result = 'W' THEN 100.0
                  ELSE 0
             END) as `1 - 1.25`,
        AVG( CASE WHEN bet_matches.quota NOT BETWEEN 1.25 and 1.49 THEN NULL
                  WHEN bet_matches.result = 'W' THEN 100.0
                  ELSE 0
             END) as `11.25 - 1.50`,
      . . .
FROM bet_matches;

关键是您要计算所有行的平均值。您只想根据范围条件取比例。

请注意，我用标准 CASE 替换了 IF()。并对列名称使用反引号。不要对标识符使用单引号。它们很容易与字符串混淆。