mysql - SQL获取指定用户获胜的民意调查

Question

大家好，提前致谢我有表 accounts、votes 和 contests
一次投票由作者ID、获胜者ID、比赛ID组成，杜绝二次投票
我想为任何给定帐户显示他们赢得比赛的次数、获得第二名的次数以及获得第三名的次数
最快(执行时间)的方法是什么？ (我正在使用 MySQL)

Answer 1

在长期使用 MySQL 之后，我得出的结论是，几乎所有 GROUP BY 的使用都会对性能产生不利影响，因此这里有一个使用几个临时表的解决方案。

CREATE TEMPORARY TABLE VoteCounts (
  accountid INT,
  contestid INT,
  votecount INT DEFAULT 0
);

INSERT INTO VoteCounts (accountid, contestid)
  SELECT DISTINCT v2.accountid, v2.contestid
  FROM votes v1 JOIN votes v2 USING (contestid)
  WHERE v1.accountid = ?; -- the given account

确保您在 votes(accountid, contestid) 上有一个索引。

现在您有一个表格，其中包含给定用户参加的所有比赛，以及参加相同比赛的所有其他帐户。

UPDATE Votes AS v JOIN VoteCounts AS vc USING (accountid, contestid)
SET vc.votecount = vc.votecount+1;

现在您可以计算每个帐户在每场比赛中的票数。

CREATE TEMPORARY TABLE Placings (
  accountid INT,
  contestid INT,
  placing INT
);

SET @prevcontest := 0;
SET @placing := 0;
INSERT INTO Placings (accountid, placing, contestid)
  SELECT accountid, 
    IF(contestid=@prevcontest, @placing:=@placing+1, @placing:=1) AS placing,
    @prevcontest:=contestid AS contestid
  FROM VoteCounts
  ORDER BY contestid, votecount DESC;

现在您有一个表格，每个帐户都与他们在每场比赛中的相应排名配对。很容易获得给定位置的计数:

SELECT accountid, COUNT(*) AS count_first_place
FROM Placings
WHERE accountid = ? AND placing = 1;

并且您可以使用 MySQL 技巧在一个查询中执行所有这三个操作。在 MySQL 中， bool 表达式总是返回一个整数值 0 或 1，因此您可以使用 SUM() 来计算 1 的个数。

SELECT accountid, 
  SUM(placing=1) AS count_first_place,
  SUM(placing=2) AS count_second_place,
  SUM(placing=3) AS count_third_place
FROM Placings
WHERE accountid = ?; -- the given account

---

回复你的评论:

是的，无论从您拥有的规范化数据到您想要的结果如何，这都是一项复杂的任务。您希望它再次聚合(求和)、排名和聚合(计数)。那是一大堆工作! :-)

此外，单个查询并不总是完成给定任务的最快方法。程序员普遍存在一种误解，认为较短的代码显然是更快的代码。

请注意，我尚未对此进行测试，因此您的情况可能会有所不同。

---

关于更新的问题:

这是一种在不使用 GROUP BY 的情况下获取每个帐户的 COUNT() 票数的棘手方法。我添加了表别名 v 和 vc，所以现在可能更清楚了。在 votes 表中，给定帐户/比赛有 N 行。在 votescount 表中，每个帐户/比赛只有一行。当我加入时，更新是针对 N 行进行评估的，因此如果我为这 N 行中的每一行加 1，我将获得存储在 votescount 中的 N 计数，该行对应于每个相应的帐户/比赛。