代码之家 › 专栏 › 技术社区 › Alec

坚持mysql和group by

mysql

Alec · 技术社区 · 16 年前

我有一张桌子,上面有一些数据。例子:

date        userId  attempts  good  bad
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

每个用户都必须做一些事情,结果要么是好的要么是坏的。我想知道 相对的 与其他用户相比,每个用户的得分是 在那一天 . 例子:

用户1尝试了5次,其中4次是好的。所以 4 / 5 = 80% 他的努力是好的。当天其他用户分别为60%、50%和25%。因此,当天用户1成功尝试的相对分数为 80 / (80 + 60 + 50 + 25) â 37% .

但我在这一点上陷入困境:

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_good
2010-08-23  1       37%
2010-08-23  2       28% (60 / (80 + 60 + 50 + 25))
etc

或:

userId   score_good_total
1        ...

在哪里? score_good_total 将是所有 score_good 分数,除以天数。

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

2 回复 | 直到 16 年前

Frankie 16 年前

这引出了一点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

下面,您可以使用我用来复制和处理结果的值。

这里需要注意的是里面子查询是一个 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个小时后,还没有答案,好啦!;)

Patrick Szalapski 16 年前

我认为没有比子查询更好的方法了,因为任何创造性的方法都必须对所有行求和。优化器应该使您的子查询运行得相当好,这当然很简单。

如果你真的需要更好的表现,你将不得不运行一个单独的工作,将所有“每日总分”保存在另一个表中,因为它们不会在一天完成后改变。然后,您可以更改查询,仅当它是今天时才计算它;否则,请使用上述“每日总分”表中的数据。