【SQL】550. 游戏玩法分析 IV (关键点：确定连续两次登录)

前述

常见函数用法示例：

• DATEDIFF(col1, col2) = 1
• DATE_ADD(MIN(col), INTERVAL 1 DAY)
• ROUND(3.1415926,3) ==> 四舍五入得到 3.142

题目描述

leetcode原题：550. 游戏玩法分析 IV

思路

1. 确定连续两次登录
2. 统计，保留两位小数

写法一

关键点：如何确定连续两次登录？

• 增加一列（命名为supposed_sec_load）：event_date的时间上增加一天，查找是否event_date和supposed_sec_load有相同日期的

select ROUND(COUNT(DISTINCT(B.player_id))/COUNT(DISTINCT(A.player_id)), 2) as fraction
from Activity A 
left join (
    select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as supposed_sec_load
    from Activity
    group by player_id
) B
on B.player_id = A.player_id and B.supposed_sec_load = A.event_date

代码过程解析：

加上and B.supposed_sec_load = A.event_date 进行条件过滤

最后再统计一下就可以了。

写法二

select IFNULL(ROUND(COUNT(DISTINCT(C.player_id))/COUNT(DISTINCT(Activity.player_id)), 2), 0) as fraction
from (
    select A.player_id
    from Activity A
    join (
        select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) AS sec_load
        from Activity
        group by player_id
        ) B
    on A.player_id = B.player_id and A.event_date = B.sec_load
) C, Activity

写法三

-- select ROUND(AVG(B.event_date IS NOT NULL), 2) AS fraction
select ROUND(sum(if(B.event_date, 1, 0))/count(*), 2) AS fraction
from (
    select player_id, MIN(event_date) AS first_load
    from Activity
    group by player_id
) A
left join Activity B 
on A.player_id =B.player_id and datediff(B.event_date, A.first_load) = 1

附录：记录自己有时候的白痴错误。

--  聚集函数，分组过滤完之后的，用having;  where 是在分组之前进行过滤的。似不似傻？！
-- select *
-- from Activity
-- group by player_id
-- having datediff(event_date, '2016-03-01') = 1