力扣550(MySQL)-游戏玩法分析Ⅳ（中等）

题目：

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

查询结果格式如下所示：

 解题思路：

①先将登录日期上移一行，方便筛选出连续登录数；

 select player_id ,event_date, 
            lead(event_date,1) over(partition by player_id order by event_date asc) as date2
 from Activity

②再以player_id分组，筛选出连续两天登录的用户信息；

select  player_id , min(event_date) as date1,date2
    from(
         select player_id ,event_date, 
            lead(event_date,1) over(partition by player_id order by event_date asc)  as date2
         from Activity
    ) t1
group by player_id
having datediff(date2 ,date1)=1

 ③求出连续登录两个的玩家数和玩家总数，进行计算

select round(count(1)/(
    select count(distinct player_id)
    from Activity
),2) fraction
from (
    select  player_id , min(event_date) as date1,date2
    from(
         select player_id ,event_date, 
            lead(event_date,1) over(partition by player_id order by event_date asc)  as date2
         from Activity
    ) t1
    group by player_id
    having datediff(date2 ,date1)=1
) t2

  方法二：

①先求出所有玩家的第一次登录时间

SELECT
   player_id,
   MIN(event_date) AS first_date
FROM Activity
GROUP BY player_id；

 ②将原表与第一步求出来的临时表进行左连接；

select *
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(event_date,first_date) = 1;

 ③统计出左右两个表的数量，进行计算；

SELECT round(count(b.player_id)/count(DISTINCT a.player_id) , 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(event_date,first_date) = 1;

小知识：

①lead() over：

1.点击这里可以看语法 

2.这里也可以

②左右内连接：跳转这里看笔记

③笛卡儿积：两个集合相乘的结果。 

假设集合A={a, b}，集合B={0, 1,2}，则两个集合的笛卡尔积为{(a, 0),(a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。