550. Game Play Analysis IV

1、题目描述

来源：力扣（LeetCode）

2、解题思路

思路一

1# 创建子表a1，消除重复distinct player_id,event_date
2# 创建子表a2，找出每个玩家初次登录日期select player_id,min(event_date) as event_date from Activity group by player_id
3# a1,a2 联合查询，找出日期相差为1的记录，数量select count(a1.player_id) as c，条件datediff(a1.event_date,a2.event_date)=1
4# 联合Activity查询，进行数量比z.c / count(distinct a.player_id)

思路二

1# 增加2列记录，1、每个玩家event_date的排序rank，每个日期跟上个日期之差diff，语句如下：
@k:=case when @j=(@j:=player_id) then @k+1 else 1 end as rank
datediff(event_date,@i)*(@i<>(@i:=event_date)) as diff
2# 查询，diff=1，rank=2 的记录与总数比值即可

思路二的坑

原来的思路是一句语句嵌套查询，不需要建立子表。但是，将@i:=event_date与@j:=player_id同时放在select下，@j赋值会出错，例如第二个用例

出现以下情况，变量@i则赋值符合预期，@j则不然，(i_befor,i_after表示赋值前和赋值后的值)：

使用更多的数据测试后发现，j_after会一直重复3
暂时不理解变量传递过程，期待以后填坑…

3、提交记录

思路一

select round(z.c / count(distinct a.player_id),2) as fraction
from Activity a , 
(select count(a1.player_id) as c 
from 
(select distinct player_id,event_date from Activity) a1,
(select player_id,min(event_date) as event_date from Activity group by player_id ) a2
where a1.player_id=a2.player_id and datediff(a1.event_date,a2.event_date)=1) z

522ms

思路二

select round((sum(case when diff=1 and rank=2 then 1 else 0 end) / count(distinct player_id)),2) as fraction 
from(select a.*,datediff(event_date,@i)*(@i<>(@i:=event_date)) as diff,@k:=case when @j=(@j:=player_id) then @k+1 else 1 end as rank
from Activity a ,(select @i:=-1,@j:=-1,@k:=1) b
order by player_id, event_date) z

550ms