如何识别会话中客服与客户对每条对方消息的首条回复？

原有代码问题 #

你原有的查询使用PARTITION BY CONVERSATIONID, SENTBY分区，仅能标记整个会话中全量客服消息的第一条、全量客户消息的第一条，无法满足「每次对方发言后首条回复标记为1」的需求。

实现逻辑 #

1. 对同一会话内的所有消息按时间戳升序排序
2. 通过LAG()窗口函数获取同一会话中上一条消息的发送方身份
3. 若当前发送方和上一条发送方不一致，或当前为会话第一条消息，则标记为1，否则标记为0

正确查询代码 #

WITH HAVE AS
   (SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929267942 AS TIMEL UNION ALL 
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929298918 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929307192 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929313065 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929317717 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929333779 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929337240 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929404611 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "CONSUMER" AS SENTBY, 1631929448033 AS TIMEL UNION ALL
    SELECT "A" AS CONVERSATIONID, "AGENT"    AS SENTBY, 1631929477379 AS TIMEL
)
SELECT 
    CONVERSATIONID,
    SENTBY,
    TIMEL,
    CASE 
        WHEN LAG(SENTBY) OVER (PARTITION BY CONVERSATIONID ORDER BY TIMEL ASC) IS NULL 
            OR LAG(SENTBY) OVER (PARTITION BY CONVERSATIONID ORDER BY TIMEL ASC) != SENTBY 
        THEN 1
        ELSE 0
    END AS FIRST_MESSAGE
FROM HAVE
ORDER BY CONVERSATIONID, TIMEL

运行结果验证 #

执行上述查询后得到的FIRST_MESSAGE字段值完全匹配需求的标记规则，每次发送方切换后的第一条消息都会被标记为1，同发送方连续发送的后续消息标记为0。

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