瑶池数据库SQL-问题二的解决方案
- 为什么选问题二
- 问题二
- 准备工作
- 解决方案
- 第一步
- 第二步
- 初步尝试
- 再次尝试
- 主表自关联
- 查询满足条件数据
- 解题感受
为什么选问题二
个人没有详细的看三个题目的具体内容，只是看了三个题目的题目名称，
最后觉得问题二比较有意思，然后就选择了问题二进行解答。
问题二
首先来看一下阿里云数据库SQL挑战赛赛题二：游戏游玩情况的问题描述，首先有一张表，表名Activity
表中的字段就是以上四个字段，建表语句
CREATE TABLE `Activity` (
`player_id` int(11) NOT NULL,
`device_id` int(11) NOT NULL,
`event_date` date NOT NULL,
`games_played` int(11) NOT NULL,
PRIMARY KEY (`player_id`,`event_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
这张表的业务就是记录某些游戏的玩家的活动情况。每一行是一个玩家在指定日期的游玩记录，包含了设备信息，以及总共玩了多少款游戏。那么问题二来了，问：【注册首周内至少有两次登录的玩家占总玩家的比例，四舍五入到小数点后两位】
准备工作
个人的阿里云账号已经没有试用资格了，只能在我自己的本地数据库测试了，然后下载了为赛题准备的数据集，先在本地数据库建表
然后通过数据库连接工具navicat 导入数据，导入数据的具体步骤这里就不再演示了，直接看导入数据后的结果
解决方案
数据导入之后，就可以开始着手分析问题了，根据题目要求，查询【注册首周内至少有两次登录的玩家占总玩家的比例，四舍五入到小数点后两位】，那么问题应该分为两步处理：
1.获取当前表中总玩家数；
2.获取当前表中注册首周内至少有两次登录的玩家数；
分析完成之后我们开始按步骤处理，查询对应的数据。
第一步
首先需要查询当前表中总玩家数，查询语句
SELECT COUNT(DISTINCT player_id) FROM Activity;
执行结果
可以看到总玩家数量是1000；
第二步
首先我们先观察一下数据结构
可以看到相同player_id下event_date默认就是升序排列的，那么我们就不用再单独进行升序来获取注册时间了。下面我们只需要获取每一个player_id的前两条记录，并且比较这两条记录的event_date是否在一周内，那么这样统计出来的数据就是【注册首周内至少有两次登录记录的玩家数了】。
初步尝试
考虑到这里需要取每个player_id下的前面两条记录，那么我们可以写sql
SELECT t.player_id,t.event_date FROM Activity t WHERE
( SELECT COUNT(*) FROM Activity t1 WHERE t1.player_id = t.player_id AND t1.event_date < t.event_date ) < 2
ORDER BY t.player_id;
执行结果如图
根据执行结果可以看到我们是获取了每个player_id下面的前面两条记录，但是在此基础上再进行event_date日期的比较在一周内的话难以执行，那么又想了另外一种方案。
再次尝试
对于上面无法进行event_date日期比较的境况，后来又考虑了一种方向，既然要进行event_date日期字段的比较，那么首先要确保当前player_id下的两条记录是在一条记录上，那么后续通过比较event_date日期字段是否在一周内就可以直接判断当前player_id是满足条件的数据了。那么下面就按这个思路来写sql。
主表自关联
首先主表自关联，将后续的时间都挪到上一条记录的后面，方便后续进行event_date日期字段的比较
SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_id;
执行结果如图
这里可以看到我们想要的数据已经出现了，这个时候其实只要GROUP BY t.player_id就可以每个player_id 下获取一条数据，但是目前的sql获取的是第一条
1 2015-02-14 2015-02-14
这样的数据，并不是我们想要的第二条符合要求的数据，那么我们可以排除第一条数据就可以了，改写sql,同时直接加上GROUP BY t.player_id
SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_id AND t.event_date != t1.event_date GROUP BY t.player_id;
执行结果如图
这样我们就得到了按player_id分组，并且前两次登录的时间在同一行数据的结果了，下面只需要对当前数据按照event_date进行比较就可以得到【当前表中注册首周内至少有两次登录的玩家】
查询满足条件数据
根据上面的分析我们继续改写sql
SELECT * FROM (
SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_id AND t.event_date != t1.event_date GROUP BY t.player_id ) t2
WHERE TIMESTAMPDIFF(DAY,t2.event_date,t2.event_date2) < 7;
执行结果如图
这里我们就可以看到所有满足【当前表中注册首周内至少有两次登录的玩家】条件的玩家了，下面统计数量的话把查询字段的换成COUNT()即可
SELECT COUNT(*) FROM (
SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_id AND t.event_date != t1.event_date GROUP BY t.player_id ) t2
WHERE TIMESTAMPDIFF(DAY,t2.event_date,t2.event_date2) < 7;
执行结果如图
那么整个问题二到这里也就结束了，问题二的结果就是
-- 0.014
SELECT 14/1000;
结果如图
到这里整个问题二的解答就完工了。
解题感受
最初是因为对这个题的名称比较敢兴趣，后来点进去看了题目详细内容之后，就更有兴趣了，工作中由于工作方向的不同，不太容易遇到类似的场景，因此刚开始解题确实绕路了。这里就不再写出来了，毕竟绕路不是什么开心的事哈。
为什么觉得这个是经典SQL，过去的业务逻辑，写sql的话有时候一时想不起来的，基本上没多久也就写完了，这次写SQL，如果对数据结构没有分析到位的话，还是很容易绕路的，比较有误导性，当时当你绕到正路上的时候，你再看这个SQL又回觉得特别简单，没什么难度。其实这就是写SQL的乐趣，需要针对需求，结合数据结构深入分析，才能快速完成业务功能，完成之后回头再看又比较简单，哈哈。以上就是个人解题感悟，敬请指导。
