MYSQL｜高频 SQL 50 题｜550. 游戏玩法分析 IV

力扣题目链接
题意分解：
报告在首次登录的第二天再次登录的玩家的 比率，四舍五入到小数点后两位。换句话说，你需要计算从首次登录日期开始至少连续两天登录的玩家的数量，然后除以玩家总数。

马上就有一个简单的思路：

• 首先计算出每个玩家的首次登陆日期，这里我们最好做一个临时表；
• 然后通过 DATEDIFF 函数计算日期差，来判断玩家是否在首次登陆后的第二天再次登陆。
• 通过 LEFT JOIN 讲玩家活动数据与首次登陆日期进行关联，来完成必要的计算。

文章目录

• 1.使用 WITH 子句定义CTE（FirstLogin)
• 2.主查询
• 3. LEFT JOIN 将活动数据与首次登陆数据关联
• 4. 总体代码

1.使用 WITH 子句定义CTE（FirstLogin)

WITH FirstLogin AS (
	SELECT
		player_id, 
		MIN(event_date) AS first_login_date
	FROM
		activity
	GROUP BY
		player_id		
)

• CET(Common Table Expression)：通过 WITH 子句，定义一个公共表达式；
• MIN函数找到每个玩家首次登陆日期；
• 按照 player_id 进行分组。
  可以讲 CTE 理解为一个临时表，它包含了每个玩家的首次登陆日期，供住查询中使用。

2.主查询

SELECT 
	ROUND(
		COUNT(CASE WHEN DATEDIFF(a.event_date, f.first_login_date) = 1 THEN 1 ELSE NULL END) / COUNT(DISTINCT a.player_id), 2
	) AS fraction
FROM activity a
LEFT JOIN FirstLogin f
ON a.player_id = f.player_id

• COUNT 和 CASE WHEN：这里我们使用 COUNT 和 CASE WHEN 来统计玩家是否在首次登陆的第二天进行登录。 DATEDIFF 来计算 activity 表中的 event_date 与 FirstLogin 中的 first_login_date 之间的天数差。如果天数差为1，表示玩家在首次登陆后的第二天再次登陆，则计数为1，否则为 NULL。COUNT 只会计数非 NULL 的值，因此它吃统计满足条件的记录。
• COUNT(DISTINCT a.player_id)：计算所有不同的 player_id，也就是玩家的总数。

3. LEFT JOIN 将活动数据与首次登陆数据关联

LEFT JOIN FirstLogin f
ON a.player_id = f.player_id;

• LEFT JOIN：我们使用 LEFT JOIN 将 activity 表和 FirstLogin 进行连接。连接条件是两个表的 player_id 必须相等。由于我们要找出每个玩家的首次登录日期，并且需要判断该玩家是否在首次登录后的第二天登录，因此通过连接 activity 表的 event_date 与 FirstLogin 表的 first_login_date 来完成这一计算。
• 为什么使用 LEFT JOIN：使用 LEFT JOIN 是为了确保即使某个玩家没有在首次登录后的第二天登录，也能计算出正确的比例。LEFT JOIN 可以保留 activity 表中的所有记录，即使在 FirstLogin 表中没有匹配的记录。

4. 总体代码

WITH FirstLogin AS (
    SELECT 
        player_id,
        MIN(event_date) AS first_login_date
    FROM 
        activity
    GROUP BY 
        player_id
)
SELECT 
    ROUND(
        COUNT(
            CASE WHEN DATEDIFF(
                a.event_date, f.first_login_date
            ) = 1 THEN 1 ELSE NULL END
        ) / COUNT(DISTINCT a.player_id), 2
    ) AS fraction
FROM activity a
LEFT JOIN FirstLogin f
ON a.player_id = f.player_id