我有两个表,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个值:
因此,例如假设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;
这很容易。我想我也有2(播放)的解决方案,这要感谢MySQL COUNT DISTINCT:
select Date(played_at) AS day,
Count(Distinct player) AS played
FROM games
GROUP BY day;
但是我不知道如何获得3(first)所需的结果。我也不知道如何将所有内容放在单个查询中,以节省执行时间(该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');
一种版本是将所有相关数据放入一个联合并从那里进行分析。
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
是玩家玩第一场游戏的统计信息。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句