编写SQL查询计算工单企业回复距上一条客户消息的时间差

实现思路 #

• 核心需求是为每条企业侧消息匹配同工单下时间最近的前置客户侧消息，通过带条件的窗口聚合函数即可实现，天然支持连续多条企业消息复用同一条最近客户消息的场景
• 先对所有消息按工单分组、按创建时间升序排序，用窗口函数提取每条消息对应的最近一条客户消息的创建时间
• 过滤出企业侧消息后，直接计算两个时间的差值即可

最终SQL实现 #

窗口函数方案（推荐，性能高） #

WITH message_with_prev_client AS (
    SELECT
        ticket_id,
        id AS message_id,
        created_at AS company_msg_created_at,
        source,
        -- 按工单分组，取当前消息之前所有客户消息的最大创建时间（即最近的前置客户消息时间）
        MAX(CASE WHEN source = 'client' THEN created_at END) OVER (
            PARTITION BY ticket_id 
            ORDER BY created_at ASC
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) AS last_client_created_at
    FROM messages
)
SELECT
    ticket_id AS "ticket id",
    message_id AS "message id",
    -- 时间差计算可根据使用的数据库调整，以下为不同数据库的示例：
    -- PostgreSQL 写法：
    AGE(company_msg_created_at, last_client_created_at) AS "Time diff since previous 'client' message"
    -- MySQL 写法（返回秒级差值，可按需调整时间单位）：
    -- TIMESTAMPDIFF(SECOND, last_client_created_at, company_msg_created_at) AS "Time diff since previous 'client' message"
    -- SQL Server 写法（返回秒级差值，可按需调整时间单位）：
    -- DATEDIFF(SECOND, last_client_created_at, company_msg_created_at) AS "Time diff since previous 'client' message"
FROM message_with_prev_client
WHERE 
    source = 'company'
    -- 过滤掉没有前置客户消息的企业消息，若需要保留这类数据可删除该行，时间差字段会返回NULL
    AND last_client_created_at IS NOT NULL
ORDER BY ticket_id, message_id ASC;

老版本数据库兼容方案（关联子查询，性能较低仅作备选） #

如果使用的数据库不支持窗口函数，可以用关联子查询实现：

SELECT
    m.ticket_id AS "ticket id",
    m.id AS "message id",
    AGE(m.created_at, (
        SELECT MAX(created_at) 
        FROM messages m2 
        WHERE m2.ticket_id = m.ticket_id 
        AND m2.source = 'client' 
        AND m2.created_at < m.created_at
    )) AS "Time diff since previous 'client' message"
FROM messages m
WHERE 
    m.source = 'company'
    AND EXISTS (
        SELECT 1 FROM messages m2 
        WHERE m2.ticket_id = m.ticket_id 
        AND m2.source = 'client' 
        AND m2.created_at < m.created_at
    )
ORDER BY m.ticket_id, m.id ASC;

效果验证 #

以你给出的工单4528的消息序列为例，以上SQL返回结果和你预期的完全一致：

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