计算所有用户有效挑战中Start与End事件的平均时间差

1. 标题层级 #

二级标题 #

三级标题 #

2. 无序列表 #

• 基础列表项1
• 基础列表项2
  • 嵌套列表项1
  • 嵌套列表项2

3. 文本强调 #

这是斜体强调的内容，这是加粗强调的内容（注：单星号为斜体，双星号为加粗，按需选择）

4. 代码/命令展示 #

执行Python脚本：python main.py
数据库查询语句：SELECT * FROM challenge_events WHERE season_id = 'S2024';

5. 引用文本 #

这是一段引用内容，常用于展示需求说明或参考信息
多行引用可在每一行前添加大于号

6. 内部链接 #

查看系统操作指南请参考内部帮助文档

7. 图片插入 #

用户挑战事件趋势图

需求说明 #

现有Datatable包含3列：Datetime、UserId、Event_name。Event_name仅存"Start"（挑战开始）和"End"（挑战完成）两个值；用户中途退出无"End"事件，重新挑战会生成新"Start"。需计算指定赛季内，所有用户有效挑战的Start与End时间差平均值，规则：连续两个"Start"时忽略第一个（视为中途退出的无效挑战），仅统计有对应"End"的挑战。

实现思路（以SQL为例） #

1. 按用户排序事件：对每个用户的事件按Datetime升序排列，给每个事件标记序号
2. 筛选有效Start：排除掉前面是Start、且后续没有对应End的无效Start事件
3. 匹配Start-End对：给每个有效Start匹配后续第一个属于同一用户的End事件
4. 计算平均时间差：统计所有有效Start-End对的时间差，取平均值

示例代码 #

WITH user_event_seq AS (
    -- 按用户分组，给事件按时间排序并标记序号，同时限定赛季范围
    SELECT
        Datetime,
        UserId,
        Event_name,
        ROW_NUMBER() OVER(PARTITION BY UserId ORDER BY Datetime) AS seq_num
    FROM your_datatable
    WHERE Datetime BETWEEN '2024-01-01 00:00:00' AND '2024-03-31 23:59:59'
),
valid_start_events AS (
    -- 筛选有效Start：要么后面紧跟End，要么不是连续的第二个Start
    SELECT
        u1.Datetime AS start_time,
        u1.UserId
    FROM user_event_seq u1
    LEFT JOIN user_event_seq u2 
        ON u1.UserId = u2.UserId AND u2.seq_num = u1.seq_num + 1
    LEFT JOIN user_event_seq u3 
        ON u1.UserId = u3.UserId AND u3.seq_num = u1.seq_num - 1
    WHERE
        u1.Event_name = 'Start'
        AND NOT (u3.Event_name = 'Start' AND u2.Event_name != 'End')
),
start_end_mapping AS (
    -- 给有效Start匹配对应的第一个End
    SELECT
        vs.start_time,
        MIN(ue.Datetime) AS end_time
    FROM valid_start_events vs
    JOIN user_event_seq ue
        ON vs.UserId = ue.UserId 
        AND ue.Event_name = 'End' 
        AND ue.Datetime > vs.start_time
    GROUP BY vs.start_time, vs.UserId
)
-- 计算平均时间差（单位为秒，可根据需求调整为MINUTE/HOUR等）
SELECT
    AVG(TIMESTAMPDIFF(SECOND, start_time, end_time)) AS avg_valid_challenge_duration
FROM start_end_mapping;

关键注意点 #

• 连续Start处理：通过关联前后事件，精准排除中途退出的无效Start
• 时间范围控制：在第一步就限定赛季时间，减少不必要的计算量
• 数据库适配：不同数据库的时间差函数有差异，比如PostgreSQL可用EXTRACT(EPOCH FROM (end_time - start_time))计算秒数

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