mysql - 计算在给定日期首先出现的相关记录的记录

Question

我有两个表，players 和 games，创建如下:

CREATE TABLE IF NOT EXISTS `players` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `games` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `player` int(11) NOT NULL,
  `played_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

我希望每天提取 3 个值:

1. 当天创建的玩家数量
2. 当天上场的玩家人数
3. 当天第一次玩的玩家数量

因此，假设 players 表如下所示:

+----+--------+---------------------+
| id | name   | created_at          |
+----+--------+---------------------+
|  1 | Alan   | 2016-02-01 00:00:00 |
|  2 | Benny  | 2016-02-01 06:00:00 |
|  3 | Calvin | 2016-02-02 00:00:00 |
|  4 | Dan    | 2016-02-03 00:00:00 |
+----+--------+---------------------+

游戏表如下所示:

+----+--------+---------------------+
| id | player | played_at           |
+----+--------+---------------------+
|  1 |      1 | 2016-02-01 01:00:00 |
|  2 |      3 | 2016-02-02 02:00:00 |
|  3 |      2 | 2016-02-03 14:00:00 |
|  4 |      3 | 2016-02-03 17:00:00 |
|  5 |      3 | 2016-02-03 18:00:00 |
+----+--------+---------------------+

然后查询应该返回类似的东西

+------------+-----+--------+-------+
| day        | new | played | first |
+------------+-----+--------+-------+
| 2016-02-01 | 2   | 1      | 1     |
| 2016-02-02 | 1   | 1      | 1     |
| 2016-02-03 | 1   | 2      | 1     |
+------------+-----+--------+-------+

我有一个解决方案 1(新):

SELECT Date(created_at) AS day,
       Count(*)         AS new
FROM   players
GROUP  BY day;  

这很简单。感谢 MySQL COUNT DISTINCT，我想我也有 2 的解决方案(玩过) :

select Date(played_at) AS day,
       Count(Distinct player) AS played
FROM   games
GROUP  BY day;

但我不知道如何获得 3(第一个)所需的结果。我也不知道如何将所有内容放在一个查询中以节省执行时间(games 表可能包含数百万条记录)。

---

如果您需要，这里有一个插入示例数据的查询:

INSERT INTO `players` (`id`, `name`, `created_at`) VALUES
(1, 'Alan', '2016-02-01 00:00:00'),
(2, 'Benny', '2016-02-01 06:00:00'),
(3, 'Calvin', '2016-02-02 00:00:00'),
(4, 'Dan', '2016-02-03 00:00:00');

INSERT INTO `games` (`id`, `player`, `played_at`) VALUES
(1, 1, '2016-02-01 01:00:00'),
(2, 3, '2016-02-02 02:00:00'),
(3, 2, '2016-02-03 14:00:00'),
(4, 3, '2016-02-03 17:00:00'),
(5, 3, '2016-02-03 18:00:00');

Answer 1

一个版本是将所有相关数据放入联合中并从那里进行分析；

SELECT SUM(type='P') new, 
       COUNT(DISTINCT CASE WHEN type='G' THEN pid END) played, 
       SUM(type='F') first 
FROM (
  SELECT id pid, DATE(created_at) date, 'P' type FROM players 
  UNION ALL 
  SELECT player, DATE(played_at) date,  'G' FROM games 
  UNION ALL 
  SELECT player, MIN(DATE(played_at)),  'F' FROM games GROUP BY player
) z 
GROUP BY date;

在联盟中；

类型为P的记录是玩家创建统计。
G类型的记录是玩家相关的游戏统计。
F 类型的记录是玩家玩第一场比赛的统计数据。