MySQL分组特定聊天事件的SQL查询需求及解决方案

需要实现的逻辑：针对每个Agent Engagement Accepted事件，依次匹配后续的首个cust-message，以及该用户消息之后的首个agent-message，循环处理直到没有可匹配的消息对。之前使用LEAD函数时，遇到多条连续用户/客服消息的场景，时间匹配结果不准确。

测试用例1 #

输入 #

chat_id agent_login chat_date_of_event  chat_event  rn
986339  jbr 2024-02-20 17:09:23 Agent Engagement Accepted   1
986339  jbr 2024-02-20 17:10:08 cust-message    1
986339  jbr 2024-02-20 17:11:10 agent-message   1
986339  jbr 2024-02-20 17:12:01 cust-message    2
986339  jbr 2024-02-20 17:12:10 agent-message   2
986339  jbr 2024-02-20 17:12:15 cust-message    3
986339  jbr 2024-02-20 17:12:22 cust-message    4
986339  jbr 2024-02-20 17:12:38 cust-message    5
986339  jbr 2024-02-20 17:12:52 agent-message   3
986339  jbr 2024-02-20 17:13:29 agent-message   4
986339  jbr 2024-02-20 17:13:39 agent-message   5
986339  jbr 2024-02-20 17:16:50 agent-message   6
986339  jbr 2024-02-20 17:18:59 agent-message   7

输出 #

986339  jbr 2024-02-20 17:10:08 2024-02-20 17:11:10 
986339  jbr 2024-02-20 17:12:01 2024-02-20 17:12:10 
986339  jbr 2024-02-20 17:12:15 2024-02-20 17:12:52

测试用例2 #

输入 #

chat_id agent_login chat_date_of_event  chat_event  rn
98781   rmar    2024-02-21 21:31:45 Agent Engagement Accepted   1
98781   rmar    2024-02-21 21:32:01 Agent Post  1
98781   rmar    2024-02-21 21:32:35 cust-message    1
98781   rmar    2024-02-21 21:33:15 agent-message   2
98781   rmar    2024-02-21 21:35:23 agent-message   3
98781   rmar    2024-02-21 21:35:44 cust-message    2
98781   rmar    2024-02-21 21:36:32 cust-message    3
98781   jto 2024-02-21 21:40:23 Agent Engagement Accepted   1
98781   jto 2024-02-21 21:41:29 agent-message   1
98781   jto 2024-02-21 21:43:32 agent-message   2
98781   jto 2024-02-21 21:44:34 cust-message    1
98781   jto 2024-02-21 21:45:47 agent-message   3
98781   jto 2024-02-21 21:47:48 agent-message   4

输出 #

98781   rmar    2024-02-21 21:32:35 2024-02-21 21:33:15
98781   jto 2024-02-21 21:44:34 2024-02-21 21:45:47

原尝试的SQL代码 #

WITH Events AS (
    SELECT 
        chat_id, 
        agent_login, 
        chat_date_of_event, 
        chat_event,
        LEAD(chat_event) OVER (PARTITION BY chat_id ORDER BY chat_date_of_event) AS next_event,
        LEAD(chat_date_of_event) OVER (PARTITION BY chat_id ORDER BY chat_date_of_event) AS next_event_time
    FROM 
        your_table_name
    WHERE 
        chat_event IN ('Agent Engagement Accepted', 'cust-message', 'agent-message')
),
EndUserPosts AS (
    SELECT 
        chat_id,
        agent_login,
        chat_date_of_event AS end_user_post_time,
        CASE
            WHEN next_event = 'agent-message' THEN next_event_time
            ELSE NULL
        END AS potential_agent_post_time
    FROM 
        Events
    WHERE 
        chat_event = 'cust-message'
),
FirstAgentPostPerUserPost AS (
    SELECT 
        e.chat_id,
        e.agent_login,
        e.end_user_post_time,
        MIN(a.potential_agent_post_time) OVER (PARTITION BY e.chat_id, e.agent_login, e.end_user_post_time) AS first_agent_post_time
    FROM 
        EndUserPosts e
    LEFT JOIN 
        Events a ON e.chat_id = a.chat_id AND e.agent_login = a.agent_login AND a.chat_date_of_event > e.end_user_post_time AND a.chat_event = 'agent-message'
    GROUP BY 
        e.chat_id, e.agent_login, e.end_user_post_time
)
SELECT DISTINCT
    chat_id,
    agent_login,
    end_user_post_time,
    first_agent_post_time
FROM 
    FirstAgentPostPerUserPost
WHERE 
    end_user_post_time < COALESCE(first_agent_post_time, '9999-12-31')
ORDER BY 
    chat_id, 
    agent_login, 
    end_user_post_time;

修正后的SQL代码及思路 #

核心思路 #

1. 按chat_id和agent_login分组，标记每个Agent Engagement Accepted对应的会话段，避免跨会话配对。
2. 为每个会话段内的用户消息、客服消息分别按时间排序编号。
3. 通过编号关联，确保每个用户消息匹配之后的首个未被占用的客服消息。

修正代码 #

WITH SessionSegments AS (
    -- 标记每个Agent Engagement Accepted后的会话段
    SELECT 
        chat_id,
        agent_login,
        chat_date_of_event,
        chat_event,
        SUM(CASE WHEN chat_event = 'Agent Engagement Accepted' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY chat_id ORDER BY chat_date_of_event) AS session_id
    FROM your_table_name
),
FilteredEvents AS (
    -- 过滤仅保留需要的消息类型
    SELECT 
        chat_id,
        agent_login,
        chat_date_of_event,
        chat_event,
        session_id
    FROM SessionSegments
    WHERE chat_event IN ('cust-message', 'agent-message')
),
CustMessages AS (
    -- 为每个会话内的用户消息按时间编号
    SELECT 
        chat_id,
        agent_login,
        chat_date_of_event AS cust_time,
        session_id,
        ROW_NUMBER() OVER (PARTITION BY chat_id, agent_login, session_id ORDER BY chat_date_of_event) AS cust_seq
    FROM FilteredEvents
    WHERE chat_event = 'cust-message'
),
AgentMessages AS (
    -- 为每个会话内的客服消息按时间编号
    SELECT 
        chat_id,
        agent_login,
        chat_date_of_event AS agent_time,
        session_id,
        ROW_NUMBER() OVER (PARTITION BY chat_id, agent_login, session_id ORDER BY chat_date_of_event) AS agent_seq
    FROM FilteredEvents
    WHERE chat_event = 'agent-message'
)
-- 配对用户消息和后续首个客服消息
SELECT 
    c.chat_id,
    c.agent_login,
    c.cust_time,
    MIN(a.agent_time) AS agent_time
FROM CustMessages c
LEFT JOIN AgentMessages a 
    ON c.chat_id = a.chat_id
    AND c.agent_login = a.agent_login
    AND c.session_id = a.session_id
    AND a.agent_time > c.cust_time
    AND a.agent_seq = (
        SELECT MIN(agent_seq) 
        FROM AgentMessages 
        WHERE chat_id = c.chat_id 
            AND agent_login = c.agent_login 
            AND session_id = c.session_id 
            AND agent_time > c.cust_time
    )
GROUP BY c.chat_id, c.agent_login, c.cust_time, c.cust_seq
HAVING MIN(a.agent_time) IS NOT NULL
ORDER BY c.chat_id, c.agent_login, c.cust_time;

代码说明 #

• SessionSegments：通过累计Agent Engagement Accepted事件生成会话ID，确保消息配对仅在同一会话内进行。
• FilteredEvents：剔除无关事件，聚焦用户消息和客服消息。
• CustMessages/AgentMessages：为两类消息按时间排序编号，方便后续配对。
• 最终关联查询：通过子查询锁定当前用户消息之后的首个客服消息，避免多条连续消息导致的匹配错误。

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