如何用SQL过滤连续重复消息？PostgreSQL计算服务商平均响应时间

步骤1：过滤连续同类型消息 #

首先筛选每组连续同类型消息的第一条记录，使用PostgreSQL的LAG()窗口函数获取每条记录的上一条发送者类型，仅保留与上一条类型不同或为第一条的记录：

WITH filtered_messages AS (
    SELECT 
        id,
        sender_type,
        date_sent
    FROM (
        SELECT 
            *,
            -- 按发送时间+id排序，确保消息顺序准确
            LAG(sender_type) OVER (ORDER BY date_sent, id) AS prev_sender_type
        FROM messages
    ) sub_query
    -- 保留第一条记录，或与上一条发送者类型不同的记录
    WHERE prev_sender_type IS NULL OR prev_sender_type != sender_type
)

执行该CTE后，将得到你预期的保留id为1、2、3、6的记录集。

步骤2：计算客户消息与对应服务商消息的时间间隔 #

基于过滤后的记录，使用LEAD()窗口函数获取客户消息的下一条服务商消息发送时间，计算两者的天数间隔：

, response_intervals AS (
    SELECT 
        -- 计算下一条服务商消息与当前客户消息的天数差
        (LEAD(date_sent) OVER (ORDER BY date_sent) - date_sent) AS days_interval
    FROM filtered_messages
    WHERE sender_type = 'CUSTOMER'
    -- 确保下一条消息为服务商发送（可选，过滤后消息已交替）
    AND LEAD(sender_type) OVER (ORDER BY date_sent) = 'PROVIDER'
)

步骤3：计算平均响应时间 #

对所有有效时间间隔求平均值：

SELECT AVG(days_interval) AS average_response_days
FROM response_intervals;

完整SQL查询 #

合并以上步骤的完整SQL：

WITH filtered_messages AS (
    SELECT 
        id,
        sender_type,
        date_sent
    FROM (
        SELECT 
            *,
            LAG(sender_type) OVER (ORDER BY date_sent, id) AS prev_sender_type
        FROM messages
    ) sub_query
    WHERE prev_sender_type IS NULL OR prev_sender_type != sender_type
),
response_intervals AS (
    SELECT 
        (LEAD(date_sent) OVER (ORDER BY date_sent) - date_sent) AS days_interval
    FROM filtered_messages
    WHERE sender_type = 'CUSTOMER'
    AND LEAD(sender_type) OVER (ORDER BY date_sent) = 'PROVIDER'
)
SELECT AVG(days_interval) AS average_response_days
FROM response_intervals;

执行该查询后，将返回平均响应时间11.5天，与预期结果一致。

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