基于日期差判定玩家状态：如何实现目标输出？

现有两张数据表：

• player表：包含player-id（玩家ID）、date_played（游戏日期）字段
• milestone表：包含player_id（玩家ID）、milestonedate（里程碑日期）字段

需要按照以下规则对比两表数据，判定每个玩家的churn_status（流失状态）和player_status（玩家状态）：

1. 若玩家在里程碑日期后60天内无游戏记录，则churn_status为60 day churner；若该玩家60天后有回归游戏记录，则player_status为Returner；
2. 若玩家在里程碑日期后完全没有回归游戏，则player_status为Churner；
3. 若玩家在里程碑日期后60天内有游戏记录，则churn_status为Active player，此时若后续有游戏记录则player_status为Returner。

输入数据表 #

player表 #

milestone表 #

目标输出表 #

可以通过SQL语句实现需求，核心思路是先统计每个玩家在里程碑日期后的游戏行为标记，再根据规则映射状态。以下以MySQL语法为例：

WITH player_post_milestone AS (
    SELECT 
        m.player_id,
        m.milestonedate AS milestone_date,
        -- 标记：里程碑后60天内是否有游戏记录（含里程碑当天）
        MAX(CASE WHEN p.date_played >= m.milestonedate AND p.date_played <= DATE_ADD(m.milestonedate, INTERVAL 60 DAY) THEN 1 ELSE 0 END) AS has_play_within_60d,
        -- 标记：里程碑60天后是否有回归游戏记录
        MAX(CASE WHEN p.date_played > DATE_ADD(m.milestonedate, INTERVAL 60 DAY) THEN 1 ELSE 0 END) AS has_return_after_60d
    FROM milestone m
    LEFT JOIN player p ON m.player_id = p.`player-id`
    GROUP BY m.player_id, m.milestonedate
)
SELECT 
    player_id,
    milestone_date,
    -- 判定churn_status
    CASE 
        WHEN has_play_within_60d = 1 THEN 'Active player'
        ELSE '60 day churner'
    END AS churn_status,
    -- 判定player_status
    CASE 
        WHEN has_play_within_60d = 1 THEN 'Returner'
        WHEN has_return_after_60d = 1 THEN 'Returner'
        ELSE 'Churner'
    END AS player_status
FROM player_post_milestone;

代码说明 #

1. CTE子查询player_post_milestone：
   • 关联milestone和player表，按玩家ID+里程碑日期分组
   • 用MAX(CASE...)生成两个关键标记，统计玩家的游戏行为：
     • has_play_within_60d：1表示里程碑后60天内有游戏记录，0表示无
     • has_return_after_60d：1表示里程碑60天后有回归记录，0表示无
2. 主查询：
   • 根据has_play_within_60d直接映射churn_status
   • 结合两个标记的组合结果，映射最终的player_status

适配不同数据库的调整 #

• PostgreSQL：将DATE_ADD(m.milestonedate, INTERVAL 60 DAY)替换为m.milestonedate + INTERVAL '60 days'
• SQL Server：替换为DATEADD(day, 60, m.milestonedate)
• 若日期字段是字符串类型，需先转换为日期格式（如MySQL用STR_TO_DATE(p.date_played, '%m/%d/%Y')）

内容的提问来源于stack exchange，提问作者Yash