Table: Activity
+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ (player_id,event_date)是此表的主键(具有唯一值的列的组合)。 这张表显示了某些游戏的玩家的活动情况。 每一行是一个玩家的记录,他在某一天使用某个设备注销之前登录并玩了很多游戏(可能是 0)。
编写解决方案,报告在首次登录的第二天再次登录的玩家的 比率,四舍五入到小数点后两位。换句话说,你需要计算从首次登录日期开始至少连续两天登录的玩家的数量,然后除以玩家总数。
结果格式如下所示:
示例 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 | +-----------+-----------+------------+--------------+ 输出: +-----------+ | fraction | +-----------+ | 0.33 | +-----------+ 解释: 只有 ID 为 1 的玩家在第一天登录后才重新登录,所以答案是 1/3 = 0.33
MySQL查询用于计算玩家在首次登录后的第二天再次登录的比率
select Round(COUNT(a.player_id)/(select count(DISTINCT player_id) from activity),2)
as fraction
from activity as a
left join
(select player_id,min(event_date) as min1 from Activity group by player_id) as b
on a.player_id = b.player_id
where DATEDIFF(a.event_date,min1) = 1
group by a.player_id;
-
主查询:这个部分是整个查询的主体,它执行了几个操作:
Round(COUNT(a.player_id)/(select count(DISTINCT player_id) from activity),2)
:这部分计算了玩家再次登录的比率。它先计算了总的玩家登录次数除以不同玩家的数量,然后用Round(...,2)
四舍五入到两位小数。from activity as a
:这表明查询从名为activity
的表中获取数据,并使用a
作为别名。left join (...) as b on a.player_id = b.player_id
:这是一个左连接,将主查询中的activity
表和一个子查询连接起来,以便获取玩家的首次登录日期。where DATEDIFF(a.event_date,min1) = 1
:这个条件确保我们只考虑在首次登录后的第二天再次登录的情况。group by a.player_id
:这个部分将结果按照玩家ID进行分组,确保每个玩家只出现一次。
-
子查询:在主查询的左连接中使用的子查询执行以下操作:
select player_id,min(event_date) as min1 from Activity group by player_id
:这个子查询计算了每个玩家的首次登录日期。
这道题的思路其实很简单就是一个子查询,只是需要仔细一些,函数使用不要出现低级错误