LeetCode --- 数据库（会员）
简书专栏：https://www.jianshu.com/nb/42460386

一、题目描述

来源：力扣（LeetCode）

活动表 Activity：

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
表的主键是 (player_id, event_date)。
这张表展示了一些游戏玩家在游戏平台上的行为活动。
每行数据记录了一名玩家在退出平台之前，当天使用同一台设备登录平台后打开的游戏的数目（可能是 0 个）。

二、具体要求

2.1 获取每位玩家 第一次登陆平台的日期

查询结果的格式如下所示：

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  |

要求返回第一次登陆的时间，那么根据ID进行分组，再取出每组中最小的时间即为第一次登陆的时间，sql如下：

select 
    a.player_id as player_id,
    min(a.event_date) as first_login
from 
    Activity a
group by 
    a.player_id

2.2 描述每一个玩家首次登陆的设备名称

查询结果格式在以下示例中：

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         |
+-----------+-----------+

第一问已经得到了初次登陆的时间，那么只需和原表进行关联，即可得到初次登陆设备的ID

SELECT
    t.player_id,
    t.device_id 
FROM
    Activity t,
    ( SELECT a.player_id AS player_id, min( event_date ) AS first_time FROM Activity a GROUP BY a.player_id ) b 
WHERE
    t.player_id = b.player_id 
    AND t.event_date = b.first_time

2.3 编写一个 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 个游戏。
请注意，对于每个玩家，我们只关心玩家的登录日期。

进行自关联，关联条件为id相同且a表日期大于等于b表的日期，然后按照player_id,event_date进行分组，利用sum函数统计即可得到到目前为止的游戏数量

select 
        a.player_id,
        a.event_date,
        sum(b.games_played) as games_played_so_far
from 
    Activity a,Activity b
where 
    a.player_id = b.player_id and a.event_date >= b.event_date
group by
    a.player_id,a.event_date

2.4 编写一个 SQL 查询，报告在首次登录的第二天再次登录的玩家的分数，四舍五入到小数点后两位。换句话说，您需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

（注意是首次登陆后的第二天，首次登陆时间在2.1中已得到解决）

查询结果格式如下所示：

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

目标表自身去关联首次时间的临时表，判断时间相差1天即可得到目标id。再在外层套用一层调用count()函数统计人数，除以总人数，利用round函数四舍五入保留两位小数即得到结果

SELECT
    round(count( * ) / ( SELECT COUNT( * ) FROM ( SELECT DISTINCT player_id FROM Activity ) m ),2) AS fraction 
FROM
    (
SELECT DISTINCT
    a.player_id 
FROM
    Activity a,
    ( SELECT player_id, min( event_date ) first_date FROM activity GROUP BY player_id ) b 
WHERE
    a.player_id = b.player_id 
    AND TIMESTAMPDIFF( DAY, b.first_date, a.event_date ) = 1 
    ) t