如何获取机器人发送‘请给我一分钟’后的后续消息并分析时间戳差异

需求说明 #

• 分析机器人发送特定消息（对应西班牙语文本：Dame un minuto para ayudarte 🏃）后的时间戳差异；
• 查看该机器人消息的时间戳，以及该消息之后发送的第一条机器人或操作员消息的时间戳；
• 当后续消息来自操作员时，需显示操作员的ID和姓名。

原SQL存在的问题 #

1. 语法错误：SELECT语句后缺少FROM子句；
2. 会返回指定机器人消息后的所有后续消息，而非需求的第一条；
3. 字段命名不准确（如user_date与实际后续消息来源不符，operator_message无法兼容后续机器人消息）；
4. 未包含操作员ID和姓名的查询字段。

修正后的SQL查询 #

WITH bot_messages AS (
    SELECT 
        session_id,
        creation_time AS bot_msg_time,
        message AS bot_message
    FROM ext_metric_felixpago.message_metrics
    WHERE 
        msg_from = 'bot'
        AND message = 'Dame un minuto para ayudarte 🏃'
        AND creation_time >= '2023-03-27'
),
followup_messages AS (
    SELECT 
        m.session_id,
        m.creation_time AS followup_msg_time,
        m.message AS followup_message,
        m.msg_from AS followup_sender,
        m.operator_id,
        m.operator_name,
        -- 为同会话内的后续消息按时间排序，标记第一条
        ROW_NUMBER() OVER (
            PARTITION BY m.session_id 
            ORDER BY m.creation_time ASC
        ) AS msg_rank
    FROM ext_metric_felixpago.message_metrics m
    JOIN bot_messages b ON m.session_id = b.session_id
    WHERE 
        m.creation_time > b.bot_msg_time
        AND m.msg_from IN ('operator', 'bot') -- 仅保留机器人或操作员的后续消息
)
SELECT 
    b.session_id,
    b.bot_msg_time,
    b.bot_message,
    f.followup_msg_time,
    f.followup_message,
    f.followup_sender,
    -- 仅当发送方为操作员时显示ID和姓名
    CASE WHEN f.followup_sender = 'operator' THEN f.operator_id ELSE NULL END AS operator_id,
    CASE WHEN f.followup_sender = 'operator' THEN f.operator_name ELSE NULL END AS operator_name,
    -- 计算时间戳差异（秒为单位）
    EXTRACT(EPOCH FROM (f.followup_msg_time - b.bot_msg_time)) AS time_diff_seconds
FROM bot_messages b
JOIN followup_messages f ON b.session_id = f.session_id
WHERE f.msg_rank = 1 -- 只取第一条后续消息
ORDER BY b.bot_msg_time DESC;

关键修正说明 #

• 使用CTE（公共表表达式）拆分逻辑，先筛选出所有符合条件的机器人消息，再关联后续消息；
• 用ROW_NUMBER()窗口函数确保只获取每条机器人消息后的第一条后续消息；
• 添加时间差计算字段，直接展示前后消息的时间间隔；
• 按需显示操作员ID和姓名，兼容后续消息来自机器人的场景；
• 修正原查询的语法错误，调整字段命名使其更贴合业务含义。

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