MySQL计算健身应用周重复用户：有效Workout判定及去重需求

核心思路拆解 #

要解决你的问题，我们需要分三步处理：筛选有效Workout记录、避免配对重复、验证注册周期与7天周期条件，最终统计符合要求的用户数。

分步实现代码 #

WITH valid_workouts AS (
    -- 第一步：先筛选时长符合要求的Workout，并标记同一天的上一个Workout结束时间
    SELECT
        user_id,
        workout_id,
        start_time,
        end_time,
        TIMESTAMPDIFF(MINUTE, start_time, end_time) AS duration_minutes,
        LAG(end_time) OVER (PARTITION BY user_id, DATE(start_time) ORDER BY start_time) AS prev_end
    FROM workouts
    WHERE TIMESTAMPDIFF(MINUTE, start_time, end_time) BETWEEN 10 AND 120
),
filtered_workouts AS (
    -- 第二步：过滤掉同日间隔不足30分钟的Workout（仅保留符合间隔要求的）
    SELECT
        user_id,
        workout_id,
        start_time,
        end_time
    FROM valid_workouts
    WHERE prev_end IS NULL -- 当天第一个Workout默认有效
       OR TIMESTAMPDIFF(MINUTE, prev_end, start_time) >= 30
),
workout_pairs AS (
    -- 第三步：生成无重复的用户Workout配对（通过workout_id大小关系去重）
    SELECT
        fw1.user_id,
        fw1.start_time AS workout1_start,
        fw2.start_time AS workout2_start
    FROM filtered_workouts fw1
    JOIN filtered_workouts fw2
        ON fw1.user_id = fw2.user_id
        AND fw1.workout_id < fw2.workout_id -- 关键：避免A-B和B-A的重复配对
),
user_reg_window AS (
    -- 获取用户注册后的7天时间窗口
    SELECT
        user_id,
        DATE_ADD(register_time, INTERVAL 7 DAY) AS reg_7day_limit
    FROM users
)
-- 最终统计符合所有条件的用户
SELECT COUNT(DISTINCT wp.user_id) AS weekly_return_users
FROM workout_pairs wp
JOIN user_reg_window urw
    ON wp.user_id = urw.user_id
    -- 两个Workout都在注册后的7天内
    AND wp.workout1_start <= urw.reg_7day_limit
    AND wp.workout2_start <= urw.reg_7day_limit
    -- 两个Workout属于同一7天周期（连续7天内）
    AND GREATEST(wp.workout1_start, wp.workout2_start) <= DATE_ADD(LEAST(wp.workout1_start, wp.workout2_start), INTERVAL 7 DAY);

关键细节说明 #

1. 有效Workout过滤：

   • 先通过valid_workouts筛选时长10-120分钟的记录；
   • 再用LAG窗口函数获取用户同一天的上一个Workout结束时间，过滤掉间隔不足30分钟的无效记录。

2. 配对去重：

   • 自连接时通过fw1.workout_id < fw2.workout_id强制配对顺序，彻底避免36-37和37-36这类重复记录。

3. 周期验证：

   • 注册7天内：确保两个Workout的开始时间都不晚于用户注册后的第7天；
   • 同一7天周期：通过GREATEST和LEAST函数判断两个Workout的时间差不超过7天，保证它们落在同一个连续7天窗口内。

可调整的适配点 #

• 如果你的7天周期指自然周（如周一至周日），可以把周期验证条件替换为：YEARWEEK(wp.workout1_start) = YEARWEEK(wp.workout2_start)；
• 如果表中已有现成的时长字段（如duration，单位分钟），可以直接替换TIMESTAMPDIFF计算，提升查询效率；
• 若需求中的“同一7天周期”就是指用户注册后的7天窗口本身，可以去掉最后一行的周期验证条件，因为已经通过注册时间过滤了范围。

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