mysql - 坚持使用 MySQL 和 GROUP BY

Question

我有一张包含一些统计数据的表格。示例:

date        userId  attempts  good  bad2010-08-23  1       5         4     12010-08-23  2       10        6     42010-08-23  3       6         3     32010-08-23  4       8         2     6

Each user has to do something, and the outcome is either good or bad. I'd like to find out what the relative score is for each user, compared to the other users on that day. Example:

User 1 made 5 attempts, and 4 of those were good. So 4 / 5 = 80% of his attempts were good. For the other users that day it was 60%, 50% and 25%. So the relative score of successful attempts for user 1 on that day is 80 / (80 + 60 + 50 + 25) ≈ 37%.

But I'm stuck at this point:

SELECT
  date,
  userId,
  ( (good / attempts) / x ) * 100 AS score_good
  ( (bad / attempts) / y ) * 100 AS score_bad
FROM stats
GROUP BY date, userId -- ?

其中 x 是所有 (good / attempts) 的总和那天，y 是所有 (bad / attempts) 的总和在同一天。这可以在同一个查询中完成吗？

我希望结果是例如

date        userId  score_good2010-08-23  1       37%2010-08-23  2       28% (60 / (80 + 60 + 50 + 25))etc

或者:

userId   score_good_total1        ...

在哪里score_good_total将是所有 score_good 的总和分数除以天数。

我可以用子查询替换 x 和 y，但这似乎行不通，而且当我希望数据按月份分组或所有可用日期的总分时，可能会导致过多的负载。

Answer 1

这提取了一些 SQL-fu，但它在一个非常简单的查询中是完全可行的。

// this would be the working query
SELECT 
   *,
   @score := good / attempts * 100 AS score,
   @t_score := (SELECT SUM(good / attempts * 100) FROM stats) as t_score ,
   @score / @t_score as relative_score_good
FROM stats

在下面，您可以使用我用来复制和玩弄结果的值。

这里要注意的是 inside 子查询，即 uncorrelated scalar subquery因此，将只对所有行运行一次(只需使用 EXPLAIN 运行查询即可看到这里实际上只有两个查询。

要注意的第二件事(也是非常重要的!) 是 user defined variables写成 @variable。

---

出于复制目的，您可以使用这两个命令重建示例表(如果您可以向社区提供 SQL 以生成演示值，那总是很好)。

// create the demo table
CREATE TABLE `test`.`stats` (
   `date` DATE NOT NULL ,
   `id` INT NOT NULL ,
   `attempts` INT NOT NULL ,
   `good` INT NOT NULL ,
   `bad` INT NOT NULL ,
   INDEX ( `id` , `attempts` , `good` , `bad` ) 
) ENGINE = MYISAM

// inject some values
INSERT INTO `test`.`stats` (`date`,`id`,`attempts` ,`good` ,`bad`)
VALUES 
   ('2010-08-23', '1', '5', '4', '1'), 
   ('2010-08-23', '2', '10', '6', '4'), 
   ('2010-08-23', '3', '6', '3', '3'), 
   ('2010-08-23', '4', '8', '2', '6');

希望对您有所帮助!就在我离开办公室时看到了这个问题，尽管有人会抢先一步。电影院和 4 小时后，还没有答案，万岁! ;)