基于SQL的工单活动中转时差计算逻辑需求

我来帮你搞定这个工单时间差计算的需求！根据你的描述，我们需要精准筛选出首次创建来源为IVR的工单，然后计算IVR创建到转至坐席的时间差，不符合条件的工单直接排除。下面是具体的实现方案：

先明确核心规则 #

• 筛选条件：工单的最早活动记录（按时间排序的第1行）的created by必须是IVR，否则跳过该工单的计算
• 计算规则：对于符合条件的工单，按时间顺序取对应行的时间差（你提到“固定取第2行与第3行的时间差”，我会提供两种版本：一种匹配示例中IVR直接转坐席的场景，另一种严格按第2/3行计算）

第一步：准备示例数据 #

先模拟你的业务数据，方便测试：

-- 创建活动表
CREATE TABLE activity (
    ticket_number VARCHAR(20) NOT NULL,
    created_by VARCHAR(20) NOT NULL,
    date_time DATETIME NOT NULL
);

-- 插入示例数据（包含需要排除和需要计算的工单）
INSERT INTO activity VALUES
('TICKET001', 'PinBased', '2019-07-19 00:40:00'), -- Raj的工单，首次来源PinBased，需排除
('TICKET001', 'IVR', '2019-07-19 00:45:00'),
('TICKET001', 'Agent_Raj', '2019-07-19 00:50:00'),
('TICKET002', 'IVR', '2019-07-19 04:40:00'), -- Ramu的工单，首次来源IVR，需计算
('TICKET002', 'Agent_Ramu', '2019-07-19 05:40:00');

第二步：SQL实现方案 #

版本1：匹配示例场景（IVR创建后直接转坐席） #

这个版本对应你给出的Ramu工单示例：IVR是第1行，坐席分配是第2行，计算这两行的时间差：

WITH ticket_activities AS (
    -- 给每个工单的活动按时间排序，分配行号
    SELECT
        ticket_number,
        created_by,
        date_time,
        ROW_NUMBER() OVER (PARTITION BY ticket_number ORDER BY date_time) AS row_num
    FROM activity
),
first_activity_check AS (
    -- 提取每个工单的首次创建来源
    SELECT
        ticket_number,
        created_by AS first_created_source
    FROM ticket_activities
    WHERE row_num = 1
)
SELECT
    fac.ticket_number,
    -- 计算IVR创建到坐席分配的秒数差
    TIMESTAMPDIFF(SECOND, ta_ivr.date_time, ta_agent.date_time) AS time_diff_seconds
FROM first_activity_check fac
-- 关联IVR创建的行（首次行）
JOIN ticket_activities ta_ivr 
    ON fac.ticket_number = ta_ivr.ticket_number 
    AND ta_ivr.row_num = 1 
    AND ta_ivr.created_by = 'IVR'
-- 关联首次转坐席的行（第2行）
JOIN ticket_activities ta_agent 
    ON fac.ticket_number = ta_agent.ticket_number 
    AND ta_agent.row_num = 2 
    AND ta_agent.created_by LIKE 'Agent_%' -- 假设坐席的created_by格式为Agent_XXX
WHERE fac.first_created_source = 'IVR'; -- 仅保留首次来源为IVR的工单

执行结果：

完全符合你的示例要求，TICKET001因为首次来源是PinBased被排除。

版本2：严格按第2行与第3行计算时间差 #

如果你的业务中IVR创建后还有中间步骤（第2行），第3行才是转坐席，用这个版本：

WITH ticket_activities AS (
    SELECT
        ticket_number,
        created_by,
        date_time,
        ROW_NUMBER() OVER (PARTITION BY ticket_number ORDER BY date_time) AS row_num
    FROM activity
),
first_activity_check AS (
    SELECT
        ticket_number,
        created_by AS first_created_source
    FROM ticket_activities
    WHERE row_num = 1
)
SELECT
    fac.ticket_number,
    TIMESTAMPDIFF(SECOND, ta_row2.date_time, ta_row3.date_time) AS time_diff_seconds
FROM first_activity_check fac
-- 确认首次来源是IVR
JOIN ticket_activities ta_row1 
    ON fac.ticket_number = ta_row1.ticket_number 
    AND ta_row1.row_num = 1 
    AND ta_row1.created_by = 'IVR'
-- 关联第2行
JOIN ticket_activities ta_row2 
    ON fac.ticket_number = ta_row2.ticket_number 
    AND ta_row2.row_num = 2
-- 关联第3行（坐席分配）
JOIN ticket_activities ta_row3 
    ON fac.ticket_number = ta_row3.ticket_number 
    AND ta_row3.row_num = 3 
    AND ta_row3.created_by LIKE 'Agent_%'
WHERE fac.first_created_source = 'IVR';

适配不同数据库的时间差函数 #

• SQL Server：把TIMESTAMPDIFF(SECOND, ...)换成DATEDIFF(SECOND, ...)
• PostgreSQL：换成EXTRACT(EPOCH FROM (ta_agent.date_time - ta_ivr.date_time))::INT

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