基于PostgreSQL 14.7计算项目聊天中客户平均等待响应时长

假设表结构 #

基于业务场景定义三张表的核心字段（若实际表结构有差异，可对应调整字段名）：

users (
    user_id INT PRIMARY KEY,
    role VARCHAR(50) -- 取值：'client'（客户）、'project_manager'（项目经理）
);

conversations (
    conv_id INT PRIMARY KEY,
    conv_type VARCHAR(50), -- 取值：'project'（项目类）、'task'（任务类）
    created_at TIMESTAMP
);

messages (
    msg_id INT PRIMARY KEY,
    conv_id INT REFERENCES conversations(conv_id),
    user_id INT REFERENCES users(user_id),
    sent_at TIMESTAMP, -- 消息发送时间
    content TEXT
);

解决方案SQL #

WITH msg_with_context AS (
    -- 关联消息、对话与用户角色，筛选项目类对话，同时获取下一条消息的上下文
    SELECT
        m.sent_at,
        u.role,
        -- 获取同对话内下一条消息的角色和发送时间
        LEAD(u.role) OVER (PARTITION BY m.conv_id ORDER BY m.sent_at) AS next_msg_role,
        LEAD(m.sent_at) OVER (PARTITION BY m.conv_id ORDER BY m.sent_at) AS next_msg_time
    FROM messages m
    JOIN conversations c ON m.conv_id = c.conv_id
    JOIN users u ON m.user_id = u.user_id
    WHERE c.conv_type = 'project' -- 仅统计项目类对话
),
valid_client_wait_times AS (
    -- 筛选符合条件的记录：客户连续消息的最后一条，且后续有项目经理回复
    SELECT
        sent_at,
        -- 计算等待时长（转成小时）
        EXTRACT(EPOCH FROM (next_msg_time - sent_at)) / 3600 AS wait_hours
    FROM msg_with_context
    WHERE role = 'client'
      AND next_msg_role = 'project_manager'
      AND next_msg_time IS NOT NULL -- 排除无项目经理回复的客户消息
)
-- 按月份分组计算平均等待时长
SELECT
    TO_CHAR(sent_at, 'YYYY-MM') AS month,
    ROUND(AVG(wait_hours), 2) AS avg_wait_hours
FROM valid_client_wait_times
GROUP BY TO_CHAR(sent_at, 'YYYY-MM')
ORDER BY month;

关键逻辑说明 #

• 筛选项目类对话：通过conversations.conv_type = 'project'排除任务类对话。
• 识别客户连续消息的最后一条：利用LEAD()窗口函数，判断当前客户消息的下一条消息是否来自项目经理——如果是，说明这条是客户连续消息的最后一条，需要统计等待时长。
• 计算等待时长：将时间差转成秒后除以3600，得到以小时为单位的等待时长。
• 按月份分组：用TO_CHAR(sent_at, 'YYYY-MM')将消息时间格式化成年月，分组后计算平均值。

示例验证 #

若示例数据中符合条件的等待时长为2小时和3小时，执行上述SQL后会得到avg_wait_hours = 2.5，与预期结果一致。

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