如何用SQL识别未获人工客服回复的废弃对话？

问题背景 #

我需要从包含聊天机器人与客服消息的数据集中识别废弃对话：若客户结束对话前，分配的人工客服未发送任何消息，则判定为废弃对话。

• 核心规则：Dialogid为对话ID，Sequence为消息发送顺序；'You are now chatting to'是系统分配人工客服的自动提示消息，若该消息之后无人工客服发送的真实消息，此对话即为废弃对话（如示例中的2D）。
• 当前查询问题：误将系统自动分配消息计为客服消息，虽已通过and not upper(substring(msg_text from 26 for 7)) = 'CHATBOT'排除机器人，但未解决核心需求——验证分配提示后是否存在真实客服消息。

现有查询代码 #

SELECT *
FROM (SELECT flag1.dialogid, sum(flag1.transferindicator) as sum_transfer, sum(flag1.advisorfirstmsg) as sum_advisorfirstmsg
from (select dialogid, message_time, 
case when ((upper(substring(msg_text from 7 for 49)))= 'TRANSFERRING YOU THROUGH TO SOMEONE WHO CAN HELP.')
or (upper(substring(msg_text for 41)) = 'OK, TYPE YOUR MESSAGE NOW AND PRESS SEND.' )
or (sentby = 'Consumer' and upper(substring(msg_text for 13)) = 'LEAVE MESSAGE')
then 1 else 0 end as transferindicator, 
case when ((upper(substring(msg_text for 24)) = 'YOU ARE NOW CHATTING TO '
    and not upper(substring(msg_text from 25 for 7)) = 'CHATBOT'
    or (upper(substring(msg_text for 25)) = 'YOU ARE NOW CONNECTED TO '
    and not upper(substring(msg_text from 26 for 7)) = 'CHATBOT'
then 1 else 0 end as advisorfirstmsg
from chat.raw_messagerecords
where CAST(message_time at time zone 'UTC' as date) >= current_date -49) as flag1 group by dialogid) as flag2;

优化方案 #

核心思路：先定位每个对话中系统分配人工客服提示消息的最大Sequence，再检查同对话内是否存在人工客服发送且Sequence更大的真实消息，以此精准标记废弃对话。

完整优化查询 #

WITH msg_with_flags AS (
    SELECT 
        dialogid,
        sequence,
        sentby,
        msg_text,
        -- 标记系统分配人工客服的提示消息
        CASE 
            WHEN (UPPER(SUBSTRING(msg_text FOR 24)) = 'YOU ARE NOW CHATTING TO ' 
                  AND NOT UPPER(SUBSTRING(msg_text FROM 25 FOR 7)) = 'CHATBOT')
                 OR (UPPER(SUBSTRING(msg_text FOR 25)) = 'YOU ARE NOW CONNECTED TO ' 
                     AND NOT UPPER(SUBSTRING(msg_text FROM 26 FOR 7)) = 'CHATBOT')
            THEN 1 
            ELSE 0 
        END AS is_assignment_msg,
        -- 标记人工客服发送的真实消息（需根据实际业务调整sentby的判断条件）
        CASE 
            WHEN sentby = 'Advisor' -- 替换为实际的人工客服标识值
            THEN 1 
            ELSE 0 
        END AS is_advisor_msg,
        -- 保留原有的transferindicator标记逻辑
        CASE 
            WHEN (UPPER(SUBSTRING(msg_text FROM 7 FOR 49)) = 'TRANSFERRING YOU THROUGH TO SOMEONE WHO CAN HELP.')
                 OR (UPPER(SUBSTRING(msg_text FOR 41)) = 'OK, TYPE YOUR MESSAGE NOW AND PRESS SEND.')
                 OR (sentby = 'Consumer' AND UPPER(SUBSTRING(msg_text FOR 13)) = 'LEAVE MESSAGE')
            THEN 1 
            ELSE 0 
        END AS transferindicator
    FROM chat.raw_messagerecords
    WHERE CAST(message_time AT TIME ZONE 'UTC' AS DATE) >= CURRENT_DATE - 49
),
dialog_metrics AS (
    SELECT 
        dialogid,
        SUM(transferindicator) AS sum_transfer,
        -- 获取当前对话中分配提示消息的最大Sequence
        MAX(CASE WHEN is_assignment_msg = 1 THEN sequence ELSE NULL END) AS max_assignment_seq,
        -- 判断是否存在分配提示后发送的人工客服消息
        BOOL_OR(CASE WHEN is_advisor_msg = 1 AND sequence > MAX(CASE WHEN is_assignment_msg = 1 THEN sequence ELSE 0 END) THEN TRUE ELSE FALSE END) AS has_advisor_after_assignment,
        -- 标记废弃对话：有分配提示，但后续无人工客服消息
        CASE 
            WHEN MAX(is_assignment_msg) = 1 
                 AND BOOL_OR(CASE WHEN is_advisor_msg = 1 AND sequence > MAX(CASE WHEN is_assignment_msg = 1 THEN sequence ELSE 0 END) THEN TRUE ELSE FALSE END) = FALSE
            THEN 1 
            ELSE 0 
        END AS is_abandoned_dialog
    FROM msg_with_flags
    GROUP BY dialogid
)
SELECT * FROM dialog_metrics;

关键说明 #

1. 消息类型标记：通过CTEmsg_with_flags拆分三类消息标记，避免原查询中将系统自动消息误判为客服消息的问题。
2. Sequence对比逻辑：用MAX(CASE...)提取分配提示消息的最大Sequence，再通过BOOL_OR判断后续是否有客服消息，解决了无法在CASE中整合Sequence判断的问题。
3. 业务适配：需根据实际数据集调整sentby = 'Advisor'的条件，确保准确识别人工客服发送的真实消息。

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