通知 网站从因情语写改为晴雨,这个网站的模板也从calmlog_ex改为 whimurmur

leetcode数据库 系列题 游戏玩法分析

362人浏览 / 0人评论 / | 作者:因情语写  | 分类: 数据库  | 标签: 数据库  /  leetcode

作者:因情语写

链接:https://www.qingyu.blue/article/634

声明:请尊重原作者的劳动,如需转载请注明出处


    511. 游戏玩法分析 I

活动表 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

     简单按player_id分组,取最小日期 (group by   min)

    512. 游戏玩法分析 II

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-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 a1.player_id, a1.device_id
from Activity a1,
    (
        select min(event_date) event_date, player_id
        from Activity a2
        group by player_id
    ) a2
where a1.player_id = a2.player_id and a1.event_date = a2.event_date

    用上一题的方法求出首次登陆日期,再查该天登陆的设备(group by... min....内联接)

    再看一种比较快的解法

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

    与上面解法的区别是把内联接换成了in(双参数in)

    534. 游戏玩法分析 III

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-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 a1.player_id, a1.event_date, (
    select sum(a2.games_played)
    from activity a2
    where a2.player_id = a1.player_id and a2.event_date <= a1.event_date 
) games_played_so_far
from activity a1
group by a1.player_id, a1.event_date

    当年写的奇葩代码,select中嵌套select,是可以这样使用的,找出当前日期及之前日期所玩游戏的总和,作为结果的一列

    再看一种较快的解法

SELECT C.player_id, date_format(C.event_date,'%Y-%m-%d') as "event_date", C.games_played_so_far
FROM
(
    SELECT player_id, event_date,
        @cnt := IF(@prev_id = player_id, @cnt+games_played, games_played) AS games_played_so_far,
        @prev_id := player_id
    FROM Activity, (SELECT @prev_id := NULL, @cnt := 0) p
    ORDER BY player_id, event_date
) C;

    用了两个变量,一个保存状态,一个计数,学一下变量的定义方法(select),使用方法(可以像列一样用逗号分隔,并不占列),order by排序,date_format日期转字符串

    550. 游戏玩法分析 IV

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
select round(count(a1.player_id) / a2.total_player, 2) fraction
from 
    (
        select player_id, min(event_date) first_date
        from activity
        group by player_id
    ) a1,
    (
        select count(distinct player_id) total_player
        from activity
    ) a2
where  exists
            (
                select player_id
                from activity
                where event_date = date_add(a1.first_date, interval 1 day) and player_id = a1.player_id
            )

    两张表,一张求首次登陆日期,一张求总玩家数,where中通过exists要求第二天也登陆,思路很清楚,学一下count distinct, date_add, group by... min的用法,还有round(count)这种函数的嵌套

    再看一种比较快的解法

select round(sum(case when datediff(a.event_date,b.first_time)=1 then 1 else 0 end)/count(distinct a.player_id),2) as fraction
from Activity a,
(select player_id,
       min(event_date) as first_time
from Activity 
group by player_id
)b
where a.player_id=b.player_id

    这里没有exists这种耗时操作,而是sum case代替,学习一下sum case的用法,round函数

    1097. 游戏玩法分析 V

Activity 活动记录表

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

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

我们还将某个日期 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
#技巧:使用left join,a1的所有结果都会保存下来,所以可以对a1查询的结果进行操作;通过id关联,是一对一关联,相当在a1每条结果基础上拼a2的一条结果(可能为空,记录数不会增加)
#为空的是第二天未登陆的,不为空的是第二天登陆的,
#通过group by install_dt 与 count(a2.event_date)(安装时间分组,且第二天登陆的数量)就可知首登第二日的留存率
select a1.install_dt, count(a1.player_id) installs, round(count(a2.event_date) / count(a1.player_id), 2) Day1_retention
from
    (
        select player_id, min(event_date) install_dt
        from activity
        group by player_id
    ) a1 left join activity a2
        on a1.player_id = a2.player_id and a2.event_date = date_add(a1.install_dt, interval 1 day)
group by install_dt;

    注释里说了left join的使用技巧,count求行记录数,round(count)嵌套,date_add函数

    再看一种比较快的解法

select cc.install_dt, cc.installs, round(ifnull(retention_num/installs,0),2) as Day1_retention from

    (select install_dt, count(distinct player_id) as installs from

    (select player_id, min(event_date) as install_dt from Activity group by player_id) tt group by install_dt) cc

left join

    (select install_dt, ifnull(count(bb.event_date),0) as retention_num from

    (select player_id, min(event_date) as install_dt from Activity group by player_id) aa

    join 

    (select player_id, event_date from Activity) bb

    on aa.player_id = bb.player_id where datediff(bb.event_date,aa.install_dt) = 1 group by install_dt) dd

on cc.install_dt = dd.install_dt

    这个答案中比较快,但写得很复杂,查了好多次表,一步步来的,思路很清楚,left join联接了一个join的联接,写法也很奇葩了


自己写的文章声明标题示例:


点赞(0) 打赏

全部评论

还没有评论!