LeetCode（数据库）- 游戏玩法分析 IV

题目链接：点击打开链接

 

题目大意：略。

 

解题思路：

• 解决方案(1)：先用 WITH AS 语句算出经过排序后以及打一个每组第一次登录的标记（mark）；然后下面的查询不用过多解释了吧（分子不需要 DISTINCT 因为可以保证每组只有一个）
• 解决方案(2)：略，太好理解了

 

AC 代码

-- 解决方案(1)
WITH t1 AS(
    WITH t2 AS(SELECT * FROM Activity ORDER BY player_id, event_date)
    SELECT *, IF(@p<>player_id, 1, 0) mark, @p:=player_id
    FROM t2, (SELECT @p:=-1) init
)

SELECT ROUND((SELECT COUNT(tt2.player_id)
FROM t1 tt1, t1 tt2
WHERE tt1.player_id = tt2.player_id AND DATEDIFF(tt1.event_date, tt2.event_date) = 1 AND tt2.mark = 1)
/
(SELECT COUNT(DISTINCT player_id) FROM Activity), 2)
AS fraction

-- 解决方案(2)
SELECT ROUND(avg(a.event_date IS NOT NULL), 2) fraction
FROM 
    (SELECT player_id, min(event_date) AS login
    FROM activity
    GROUP BY player_id) p 
LEFT JOIN activity a 
ON p.player_id = a.player_id AND datediff(a.event_date, p.login) = 1