Athena中客服对话场景下非连续行响应时间差计算

要解决这个问题，核心是先把连续的同角色（用户/客服）消息归为一组，然后找到用户组的最后一条消息时间，再匹配后续第一个客服组的第一条消息时间，最终计算两者的时间差。原SQL按sender分区的方式无法关联用户和客服的消息，所以需要调整逻辑：

完整SQL语句 #

WITH grouped_messages AS (
    -- 第一步：将连续的同sender_type消息分为同一组
    SELECT
        message_id,
        created_at,
        sender_type,
        -- 当当前消息的sender_type与上一条不同时，标记为新组的起始
        SUM(CASE WHEN lag_sender_type != sender_type THEN 1 ELSE 0 END) OVER (ORDER BY created_at) AS group_id
    FROM (
        SELECT
            message_id,
            created_at,
            sender_type,
            -- 获取上一条消息的sender_type，用于判断是否开启新组
            LAG(sender_type) OVER (ORDER BY created_at) AS lag_sender_type
        FROM base_table
    ) t
),
group_summary AS (
    -- 第二步：提取每个组的关键信息：角色、组内最早/最晚时间、组的顺序
    SELECT
        group_id,
        sender_type,
        MIN(created_at) AS group_first_time,  -- 组内第一条消息时间
        MAX(created_at) AS group_last_time,   -- 组内最后一条消息时间
        -- 给组按时间排序，方便后续匹配用户组和客服组
        ROW_NUMBER() OVER (ORDER BY MIN(created_at)) AS group_order
    FROM grouped_messages
    GROUP BY group_id, sender_type
)
-- 第三步：匹配用户组和后续第一个客服组，计算响应时间
SELECT
    ug.group_id AS user_group_id,
    cg.group_id AS cs_group_id,
    ug.group_last_time AS user_last_msg_time,
    cg.group_first_time AS cs_first_reply_time,
    DATE_DIFF('second', ug.group_last_time, cg.group_first_time)/60.0 AS response_time_minutes
FROM group_summary ug
-- 关联后续第一个客服组：用户组的顺序+1就是对应的客服组顺序
JOIN group_summary cg ON ug.group_order + 1 = cg.group_order
WHERE ug.sender_type = 'user'  -- 只处理用户发起的组
  AND cg.sender_type = 'cs'    -- 匹配对应的客服回复组
ORDER BY ug.group_last_time;

逻辑解释 #

1. 分组连续消息：通过LAG函数对比当前消息与上一条的角色，不同则生成新组，用SUM累计得到唯一的group_id，确保连续的同角色消息在同一组。
2. 组信息汇总：对每个组提取最早/最晚时间，并按时间给组排序，得到group_order，方便关联用户组和后续的客服组。
3. 匹配计算时间差：将用户组与顺序+1的客服组关联，用用户组的最后一条消息时间减去客服组的第一条消息时间，得到响应时间（转换为分钟）。

这个逻辑会自动处理用户连续发多条、客服连续回复多条的场景，最终得到你预期的3条结果（对应message_id 3&4、7&8、9&10的时间差）。

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