Microsoft SQL Server如何分组计算坐席处理同一interaction id的持有时长

当前我们的数据库中存储有如下数据：

我们需要对坐席处理同一interaction id的每个时段，分组统计对应的开始时间和结束时间。
预期输出结果如下：

要求仅在坐席处理该会话的对应时段内，取最小时间作为开始时间、最大时间作为结束时间。
请问该需求如何通过SQL实现？

这个需求属于SQL经典的**孤岛（gaps and islands）**问题，核心是识别同一个坐席处理同一会话过程中的连续时段，将连续操作归为同一组后聚合取时间极值即可实现，以下是不同场景的实现方案：

方案1：支持窗口函数的数据库（MySQL8.0+/PostgreSQL/Oracle等主流新版数据库通用） #

假设你的业务表名为agent_operation_log，核心字段为agent_id（坐席ID）、interaction_id（会话ID）、operate_time（操作时间），实现代码如下：

WITH mark_new_period AS (
    SELECT
        agent_id,
        interaction_id,
        operate_time,
        -- 自定义连续时段的间隔阈值，示例为10分钟：同坐席同会话下相邻操作间隔超过10分钟则算新时段开始
        CASE
            WHEN LAG(operate_time) OVER (PARTITION BY agent_id, interaction_id ORDER BY operate_time) IS NULL THEN 1
            WHEN TIMESTAMPDIFF(MINUTE, LAG(operate_time) OVER (PARTITION BY agent_id, interaction_id ORDER BY operate_time), operate_time) > 10 THEN 1
            ELSE 0
        END AS is_new
    FROM agent_operation_log
),
gen_group_id AS (
    SELECT
        agent_id,
        interaction_id,
        operate_time,
        -- 对新时段标记累加，生成每个连续时段的唯一分组ID
        SUM(is_new) OVER (PARTITION BY agent_id, interaction_id ORDER BY operate_time) AS group_id
    FROM mark_new_period
)
SELECT
    agent_id,
    interaction_id,
    MIN(operate_time) AS period_start_time,
    MAX(operate_time) AS period_end_time
FROM gen_group_id
GROUP BY agent_id, interaction_id, group_id
ORDER BY agent_id, interaction_id, period_start_time;

方案2：低版本MySQL（不支持窗口函数） #

可以通过用户变量实现同等逻辑：

SELECT
    agent_id,
    interaction_id,
    MIN(operate_time) AS period_start_time,
    MAX(operate_time) AS period_end_time
FROM (
    SELECT
        t.*,
        @group_id := IF(
            @last_agent = agent_id 
            AND @last_interaction = interaction_id 
            AND TIMESTAMPDIFF(MINUTE, @last_time, operate_time) <= 10,
            @group_id,
            @group_id + 1
        ) AS group_id,
        @last_agent := agent_id,
        @last_interaction := interaction_id,
        @last_time := operate_time
    FROM agent_operation_log t,
    (SELECT @last_agent := NULL, @last_interaction := NULL, @last_time := NULL, @group_id := 0) AS init_vars
    ORDER BY agent_id, interaction_id, operate_time
) AS temp
GROUP BY agent_id, interaction_id, group_id
ORDER BY agent_id, interaction_id, period_start_time;

注意事项 #

• 代码中10分钟的间隔阈值可以根据你的业务规则调整，比如业务定义坐席离开20分钟回来就算重新处理会话，把10改成20即可。
• 如果业务不需要判断间隔，只要是同坐席同会话的所有操作都算同一个时段，直接GROUP BY agent_id, interaction_id取MIN(operate_time)和MAX(operate_time)即可。

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