|
首先给出四道题共同用到的活动表 Activity：
+
| Column Name | Type |
+
| player_id | int |
| device_id | int |
| event_date | date |
| games_played | int |
+
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。
写一条 SQL 查询语句获取每位玩家 第一次登陆平台的日期。
查询结果的格式如下所示：
Activity 表：
+
| player_id | device_id | event_date | games_played |
+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+
Result 表：
+
| player_id | first_login |
+
| 1 | 2016-03-01 |
| 2 | 2017-06-25 |
| 3 | 2016-03-02 |
+
提交答案：
select player_id, min(event_date) 'first_login'
from Activity
group by player_id
解析：分组+min函数。
请编写一个 SQL 查询，描述每一个玩家首次登陆的设备名称
查询结果格式在以下示例中：
Activity table:
+
| player_id | device_id | event_date | games_played |
+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+
Result table:
+
| player_id | device_id |
+
| 1 | 2 |
| 2 | 3 |
| 3 | 1 |
+
提交答案：
select a.player_id, a.device_id
from Activity a JOIN
(select player_id, min(event_date) 'first_login'
from Activity group by player_id) t
ON a.player_id=t.player_id and a.event_date = t.first_login;
select player_id, device_id
from activity a1
where a1.event_date<=all(select a2.event_date from activity a2 where a1.player_id=a2.player_id);
解析：
-
方案1：要先找到首次登录日期，然后使用这个临时表t和活动表a做内连接即可； -
方案2：利用all连接符将当前event_date值和子查询返回的player_id相同的even_date比较（小于等于所有的值就是查找最小值） WHERE column_name comparison_operator ALL (subquery)
编写一个 SQL 查询，同时报告每组玩家和日期，以及玩家到目前为止玩了多少游戏。也就是说，在此日期之前玩家所玩的游戏总数。详细情况请查看示例。
查询结果格式如下所示：
Activity table:
+-----------+-----------+------------+--------------+
| player_id | device_id | event_date | games_played |
+-----------+-----------+------------+--------------+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 1 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+-----------+-----------+------------+--------------+
Result table:
+-----------+------------+---------------------+
| player_id | event_date | games_played_so_far |
+-----------+------------+---------------------+
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
+-----------+------------+---------------------+
对于 ID 为 1 的玩家，2016-05-02 共玩了 5+6=11 个游戏，2017-06-25 共玩了 5+6+1=12 个游戏。
对于 ID 为 3 的玩家，2018-07-03 共玩了 0+5=5 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。
提交答案：
select player_id, event_date, sum(games_played) over(partition by player_id order by event_date) 'games_played_so_far'
from Activity
解析：
- over函数里的
partition by player_id表示sum累加函数根据player_id进行分组； - over函数里的
order by累加的时候日期升序排列从第一行累加到当前分组的最后一行； - 假设使用
sum(games_played) over(partition by player_id)，则sum函数计算的当前用户玩的总局数； - 假设不使用partition by只用order by，则sum() over()默认累加从第一行到当前行！
编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的比率，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。
查询结果格式如下所示：
Activity table:
+
| player_id | device_id | event_date | games_played |
+
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-03-02 | 6 |
| 2 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
+
Result table:
+
| fraction |
+
| 0.33 |
+
只有 ID 为 1 的玩家在第一天登录后才重新登录，所以答案是 1/3 = 0.33
提交答案：
select ROUND(sum(CASE WHEN a.event_date is NULL THEN 0 ELSE 1 END)/count(*), 2) 'fraction'
from
(select player_id, min(event_date) 'first_login'
from Activity
group by player_id) t
LEFT JOIN
Activity a
ON DATE_ADD(t.first_login, INTERVAL 1 DAY)=a.event_date and a.player_id=t.player_id
解析：
-
首先我们的临时表t的含义是：每个player对应的id以及首次登录日期； (select player_id, min(event_date) 'first_login'
from Activity
group by player_id) t
-
临时表t LEFT JOIN左连接activity a。连接条件是a.日期=t.首次登录日期+1 player的id相同。使用LEFT JOIN的原因：select里还需要统计所有用户的数量； -
count(*)统计player人数，sum(CASE WHEN a.event_date is NULL THEN 0 ELSE 1 END)中，右表的event_date不为空才被计算；
-
ROUND(cal, 2)保留两位小数。
以上是游戏玩法分析Ⅰ到Ⅳ四道sql的解法，如有考虑错误或者解法问题，请指正。🙂
|