LeetCode-511、512、534、550、1097 游戏玩法分析 I 、II 、 III 、IV、V

约定不等于承诺〃 2022-12-01 12:26 140阅读 0赞

> 来源：力扣（LeetCode）  
> 链接：https://leetcode-cn.com/problemset/database/

### 文章目录 ###

*  SQL结构：
 *   *  511. 游戏玩法分析 I（简单）
     *  512. 游戏玩法分析 II（简单）
     *  534. 游戏玩法分析 III（中等）
     *  550. 游戏玩法分析 IV（中等）
     *  1097. 游戏玩法分析 V（困难）

# SQL结构： #

Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int)
    Truncate table Activity
    insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')
    insert into Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6')
    insert into Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1')
    insert into Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0')
    insert into Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')

活动表 `Activity`：

## 511. 游戏玩法分析 I（简单） ##

需求：写一条 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 | first_login |
    +-----------+-------------+
    | 1         | 2016-03-01  |
    | 2         | 2017-06-25  |
    | 3         | 2016-03-02  |
    +-----------+-------------+

-- 解法一： 根据player_id分组，取最小日期
    SELECT
        player_id,
        MIN(event_date) AS first_login
    FROM Activity
    GROUP BY player_id;

## 512. 游戏玩法分析 II（简单） ##

需求：请编写一个 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         |
    +-----------+-----------+

-- 解法一： 每位玩家首次登陆 + 内连接 主键是(player_id, event_date)
    SELECT
        a.player_id,
        a.device_id
    FROM Activity AS a
         INNER JOIN
         (
             SELECT
                 player_id,
                 MIN(event_date) AS first_login
             FROM Activity
             GROUP BY player_id
         ) AS b
         ON a.player_id = b.player_id AND a.event_date = b.first_login;
    )

-- 解法二： 联合查询，根据联合主键，用in匹配每位玩家首次登陆
    SELECT
        player_id,
        device_id
    FROM Activity
    WHERE (player_id, event_date) IN (
        SELECT
            player_id,
            MIN(event_date) event_date
        FROM Activity
        GROUP BY player_id
    )

-- 解法三： 利用窗口函数rank()系列，取排行的第一个
    SELECT
        t1.player_id,
        t1.device_id
    FROM (
             SELECT
                 player_id,
                 device_id,
                 dense_rank() OVER (PARTITION BY player_id ORDER BY event_date) rk
             FROM Activity
         ) AS t1
    WHERE t1.rk = 1

## 534. 游戏玩法分析 III（中等） ##

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

-- 解法一： 通过内连接，过滤出时间在自身前面的数据，再对联合主键进行group by，配合sum(games_played)
    SELECT
    	a.player_id,
    	a.event_date,
    	SUM(b.games_played) AS games_played_so_far
    FROM
    	Activity AS a INNER JOIN Activity AS b
    ON a.player_id = b.player_id AND a.event_date >= b.event_date
    GROUP BY
    	a.player_id, a.event_date

-- 解法二： 窗口函数 
    SELECT
        player_id,
        event_date,
        SUM(games_played) OVER (PARTITION BY player_id ORDER BY event_date) AS games_played_so_far
    FROM Activity

## 550. 游戏玩法分析 IV（中等） ##

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

查询结果格式如下所示：

-- 解法一：1.先求出每位玩家下一次登录的时间，并且按照登录时间排行
    --		  2.再过滤出首次登录，并且event_date + 1 = nextDate，才说明是连续两天登录
    SELECT
        ROUND(SUM(CASE
                      WHEN event_date + 1 = nextDate THEN 1
                      ELSE 0
            END) / COUNT(*), 2) AS fraction
    FROM (
             SELECT
                 player_id,
                 event_date,
                 lead(event_date, 1) OVER (PARTITION BY player_id ORDER BY event_date) AS nextDate,
                 row_number() OVER (PARTITION BY player_id ORDER BY event_date) AS rk
             FROM Activity
         ) AS t1
    WHERE t1.rk = 1

-- 解法二：1.先求出玩家第一次登陆的数据，然后把这个作为临时表（b），
    --		  2.然后用原始表（a）和临时表（b）左连接进行比较，如果日期相差1天，即为符合条件的玩家，
    --		    用这部分的玩家数量除以所有玩家的数量，然后保留两位小数就可以得出结果了
    SELECT
        ROUND(COUNT(DISTINCT (b.player_id)) / (SELECT COUNT(DISTINCT (player_id)) FROM Activity), 2) AS fraction
    FROM Activity a
         LEFT JOIN
         (
             SELECT
                 player_id,
                 MIN(event_date) AS first_date
             FROM Activity
             GROUP BY player_id
         ) AS b
    ON a.player_id = b.player_id AND datediff(a.event_date, b.first_date) = 1;

## 1097. 游戏玩法分析 V（困难） ##

我们将玩家的安装日期定义为该玩家的第一个登录日。

我们还将某个日期 X 的第 1 天留存时间定义为安装日期为 X 的玩家的数量，他们在 X 之后的一天重新登录，除以安装日期为 X 的玩家的数量，四舍五入到小数点后两位。

需求：编写一个 SQL 查询，报告每个安装日期、当天安装游戏的玩家数量和第一天的留存时间。

查询结果格式如下所示：

Activity 表：
    +-----------+-----------+------------+--------------+
    | 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-01 | 0            |
    | 3         | 4         | 2016-07-03 | 5            |
    +-----------+-----------+------------+--------------+
    
    Result 表：
    +------------+----------+----------------+
    | install_dt | installs | Day1_retention |
    +------------+----------+----------------+
    | 2016-03-01 | 2        | 0.50           |
    | 2017-06-25 | 1        | 0.00           |
    +------------+----------+----------------+
    玩家 1 和 3 在 2016-03-01 安装了游戏，但只有玩家 1 在 2016-03-02 重新登录，所以 2016-03-01 的第一天留存时间是 1/2=0.50
    玩家 2 在 2017-06-25 安装了游戏，但在 2017-06-26 没有重新登录，因此 2017-06-25 的第一天留存时间为 0/1=0.00

-- 解法一：1.先用窗口函数，求出 每个玩家 的首次安装的时间install_dt，即定义为 每个玩家 第一次登录的日期
    --		  2.再根据install_dt分组，统计每组 日存留率人数 和 总人数；
    --			 a.（判断event_date-install_dt =1 就是1日留存率，有则为1个，否则为0），再sum求和，这样统计出1日存留率的个数；
    --			 b. 用count(distinct play_id)求出分组的总人数，因为play_id有重复的，所以记得去重；
    --			 c. 最后（日存留率人数 / 总人数）保留两位小数即可
    
    SELECT
        install_dt,
        count(DISTINCT player_id) AS installs,
        round(sum(if(datediff(event_date, install_dt) = 1, 1, 0)) / count(DISTINCT player_id), 2) AS Day1_retention
    FROM
        (
            SELECT
                player_id,
                event_date,
                min(event_date) OVER (PARTITION BY player_id) AS install_dt
            FROM
                activity
        ) t1
    GROUP BY
        install_dt;