时间戳

首页 > 解决方案 > MySQL百分比除以总和

mysql - MySQL百分比除以总和

问题描述

我有一张桌子games，我有字段name，gameStatus和created_at

这是我得到的结果

| name    | total   | percentage |   
| Caleb   | 13992   | 11.8372    |    
| Fabian  | 27570   | 23.3241    |    
| Frank   | 7181    | 6.0751     |    
| Francis | 19897   | 16.8328    |    
| Felix   | 4319    | 3.6539     |    
| Gary    | 1       | 0.0008     |    
| George  | 2405    | 2.0346     |    
| Gavin   | 16144   | 13.6577    |    
| Gab     | 22076   | 18.6762    |    
| Nigel   | 367     | 0.3105     |    
| Peter   | 2465    | 2.0854     |    
| Troy    | 1787    | 1.5118     |

从 SQL 查询

SELECT 
  name,
  total,
  (total/total_sum * 100) AS percentage
FROM    
  (SELECT 
    name,
    COUNT(*) AS total
  FROM
    games 
  WHERE  gameStatus = 'win' 
    AND (
      created_at > '2018-03-01 12:02:26'
    ) 
  GROUP BY name) AS T  
  CROSS JOIN (SELECT 
    COUNT(*) AS total_sum
  FROM
    games 
  WHERE gameStatus = 'win'
    AND (
      created_at > '2018-03-01 12:02:26'
    ) ) TS;

简而言之，我想从所有获胜的游戏中找出每个玩家获胜的百分比。我想这样做而不必运行两个 SQL 子查询。

SQL 小提琴http://sqlfiddle.com/#!9/bbbaa5/3

标签： mysql

解决方案

正如我在评论中指出的那样，这两个部分都不需要是子查询。

  SELECT name
     , COUNT(*) AS total
     , 100 * COUNT(*)/s.total_sum AS percentage
  FROM games 
  CROSS JOIN (
     SELECT COUNT(*) AS total_sum 
     FROM games 
     WHERE gameStatus = 'win' 
       AND created_at > '2018-03-01 12:02:26'
  ) AS s
  WHERE gameStatus = 'win' 
    AND created_at > '2018-03-01 12:02:26'
  GROUP BY name
  ;

不确定这是否真的会更快；如果 MySQL 最终以几乎相同的方式处理它们，我不会感到惊讶。

推荐阅读