题目描述

(通过次数22,225 | 提交次数50,503,通过率44.01%)

Table:Activity
+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id,event_date)是此表的主键。
这张表显示了某些游戏的玩家的活动情况。
每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0)。

编写一个 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

来源:力扣(LeetCode)
链接:https://leetcode.cn/problems/game-play-analysis-iv
//测试数据
Create table If Not Exists Activity (player_id int, device_id int, event_date date, games_played int);

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-03-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');

解题思路

这道题用到的知识点不复杂,但涉及到的知识点比较多,写起来也比较长,层次比较深。

但跟1126. 查询活跃业务(难度:中等)一样,是属于又一道逻辑题。

可能有些小伙伴看到逻辑层次稍微深一些的SQL就头皮发麻,实际上完全不用担心。只要理清了思路,写起来并不难。

那么,根据题目要求,我们可能需要以下几个步骤:

**第一步**:找出所有人首次登录的记录;

这一步可以使用开窗函数、group by + min等实现。

**第二步**:找出首次登录的人中,第二天也登录了的人;

这一步可以比较简单,可以通过关联来实现。

**第三步**:计算第二天登录了的人数;

直接统计第二步中的人数即可。

**第四步**:计算所有登录的人数;

可以从Activity表中去重后汇总得出,也可以直接从第1步的结果汇总得出。

**第五步**:第三步的人数除以第四步的人数,并四舍五入,取2位小数;

在MySQL中,可以使用round函数和convert函数做四舍五入。

对于第五步,直接将两个数字相除并返回,可能会有些人不知道怎么写。因为一般人可能会认为,一个select语句一定要有from子句。

实际上,在最新的MySQL版本中,可以支持不写from子句。这跟在Oracle中“select * from dual”是一样的。仅仅返回一行记录。

参考SQL

未特别说明的情况下,参考SQL为基于MySQL8.0实现。
with
tmp as (
    select
        a.player_id,
        a.event_date,
        row_number() over(partition by a.player_id order by a.event_date) rn
    from Activity a
)
select
    round(
        (
            select
                count(1) cnt_activity
            from tmp a
            inner join Activity b
            on a.player_id = b.player_id
            and date_add(a.event_date,interval 1 day) = b.event_date
            where a.rn = 1
        )/(select count(1) cnt_all from tmp where rn = 1)
        ,2
    ) as fraction;
picture loss