针对Sports问题的用户猜测系统

Question

我正在用PHP/MySQL构建一个体育系统。在StackOverFlow上的一些帮助下，我已经建立了它。

我有OW_SPORTS_GAMES表，它有每一场比赛的所有细节与球队的Id和分数。这是结构。

​

​

此外，还有另一个表OW_SPORTS_PREDICTIONS，其中存储用户对游戏结果的预测。用户可以预测哪个队将赢得这场比赛。这是桌子的结构。

​

​

我希望得到的结果，我可以知道多少正确和错误的预测，每个用户已经作出。如果这是一个正确的猜测，用户将获得每个游戏的积分(分数列)。如果两支球队的得分都是0，那么输出应该忽略任何比赛。

下面是我当前的SQL：

SELECT p.userId,
       SUM(IF(g.id IS NOT NULL, 1, 0)) correct,
       SUM(IF(g.id IS NULL, 1, 0)) wrong,
       SUM(IF(g.id IS NOT NULL, g.points, 0)) AS points
  FROM
       (SELECT * FROM ow_sports_games WHERE seasonId = 10 AND (homeTeamScore > 0 OR awayTeamScore > 0) ) g
  RIGHT JOIN ow_sports_predictions p
     ON g.id = p.gameId
    AND p.teamId = IF(g.homeTeamScore > g.awayTeamScore , g.homeTeam, IF(g.homeTeamScore < g.awayTeamScore , g.awayTeam, NULL))
  GROUP BY p.userId ORDER BY points DESC, correct DESC, wrong DESC;

使用这种SQL，我得到错误的统计，其中的游戏没有预测的用户和分数0-0也是考虑。

SQL Fiddle: http://sqlfiddle.com/#!2/f4c9ed/2

在相同的数据中，它应该是两个正确的和两个错误的预测。但它是两个正确的和四个错误的预测。

Answer 1

你的问题是，你是限制加入只返回游戏的预测是正确的；

AND p.teamId = IF(g.homeTeamScore > g.awayTeamScore , g.homeTeam, IF(g.homeTeamScore < g.awayTeamScore , g.awayTeam, NULL))

但是，在计算错误预测数时，您的假设是游戏id将为null：

SUM(IF(g.id IS NULL, 1, 0)) wrong,

但是，如果得分是0-0，则游戏id也将为空。我要简化这一点，首先通过简化联接条件，这样您就可以切换到内部连接：

SELECT  *
FROM    ow_sports_predictions p
        INNER JOIN ow_sports_games g
            ON g.id = p.gameId
WHERE   g.SeasonID = 10

然后，您可以确定您的逻辑，因为如果预测是正确的(这比我以后使用的更详细，但演示的逻辑更好)，

SELECT  *,
        CASE WHEN g.homeTeamScore > g.awayTeamScore AND g.HomeTeam = p.TeamID THEN 'Correct - Home Win'
            WHEN g.awayTeamScore > g.homeTeamScore AND g.AwayTeam = p.Teamid THEN 'Correct - Away Win'
            WHEN g.homeTeamScore + g.awayTeamScore = 0 THEN 'Void (0-0)'
            ELSE 'Lose'
        END AS Result
FROM    ow_sports_predictions p
        INNER JOIN ow_sports_games g
            ON g.id = p.gameId
WHERE   g.SeasonID = 10;

最后，我们可以总结一下：

SELECT  p.userID,
        SUM(IF((g.homeTeamScore > g.awayTeamScore AND g.HomeTeam = p.TeamID) 
            OR (g.awayTeamScore > g.homeTeamScore AND g.AwayTeam = p.TeamID), 1, 0)) AS Correct,
        SUM(IF((g.homeTeamScore < g.awayTeamScore AND g.HomeTeam = p.TeamID) 
            OR (g.awayTeamScore < g.homeTeamScore AND g.AwayTeam = p.TeamID), 1, 0)) AS Wrong,
        SUM(IF((g.homeTeamScore > g.awayTeamScore AND g.HomeTeam = p.TeamID) 
            OR (g.awayTeamScore > g.homeTeamScore AND g.AwayTeam = p.TeamID), g.Points, 0)) AS Points,
        COUNT(*) AS TotalPredictions
FROM    ow_sports_predictions p
        INNER JOIN ow_sports_games g
            ON g.id = p.gameId
WHERE   g.SeasonID = 10
GROUP BY p.UserID;

SQL Fiddle示例

Answer 2

我认为问题的主要根源是在加入预测时使用正确的连接--这将为您不想计算的游戏提供行数据……试着把它改到左边加入，让我们知道。

更新： Ouch被否决了--投票支持指出正确的加入正在吸引额外的记录？还有其他的问题，但我认为这是最大的问题。显然，我应该提供一个完整的答案，下面是我将使用的一个查询，并附带小提琴：

http://sqlfiddle.com/#!2/f4c9ed/49

SELECT s.userId,
  SUM(IF(s.teamId = s.winningTeam, 1, 0)) correct,
  SUM(IF(s.teamId != s.winningTeam, 0, 1)) wrong,
  SUM(IF(s.teamId = s.winningTeam, s.points, 0)) points
FROM
  (SELECT p.*, g.points,
   IF (g.homeTeamScore-g.awayTeamScore > 0, g.homeTeam, g.awayTeam) as winningTeam
   FROM ow_sports_predictions p
    INNER JOIN ow_sports_games g ON g.id = p.gameId AND g.homeTeamScore+g.awayTeamScore > 0) s
GROUP BY s.userId;

Answer 3

这里是快速变体，它计算每个用户的所有数字，当然您只需要其中的两个，计算第三个就足够了：

select
  b.userId,
  count(*) as total_predictions,
  sum(if(b.teamId=q.winteam,1,0)) as correct_predictions,
  sum(if(b.teamId<>q.winteam,1,0)) as wrong_predictions
from ow_sports_predictions as b
inner join
(
select
a.id,
if (a.hometeamscore>a.awayteamscore, a.hometeam, if(a.hometeamscore<a.awayteamscore, a.awayteam, -1)) as winteam
from ow_sports_games as a
)
as q
on (b.gameId=q.id)
group by b.userId;

以下是简化的版本：

select
  b.userId,
  count(*) as total_predictions,
  sum(if(b.teamId=if (q.hometeamscore>q.awayteamscore, q.hometeam, if(q.hometeamscore<q.awayteamscore, q.awayteam, -1)),1,0)) as correct_predictions
from ow_sports_predictions as b
inner join ow_sports_games as q
on (b.gameId=q.id)
group by b.userId;

要计算点数，需要将1中的IF更改为points，例如：

select
  b.userId,
  count(*) as total_predictions,
  sum(if(b.teamId=if (q.hometeamscore>q.awayteamscore, q.hometeam, if(q.hometeamscore<q.awayteamscore, q.awayteam, -1)),q.points,0)) as correct_predictions_mul_points
from ow_sports_predictions as b
inner join ow_sports_games as q
on (b.gameId=q.id)
group by b.userId;