如何获取指定时段内由多名不同客服处理的通话记录

AI 小北

2026-8-15

问题描述

我需要查询指定时间范围内，由两名及以上不同客服代表处理的所有通话。当前使用HAVING COUNT语句可筛选出部分结果，但会包含同一客服多次参与同一场通话的情况，需过滤此类结果，仅保留由不同客服处理的通话。

当前查询语句

SELECT * 
FROM call_legs
WHERE call_id IN (
SELECT DISTINCT(call_id)
FROM call_legs
    WHERE call_id IN (
        SELECT 
        call_id
        FROM call_legs
        WHERE 
            from_name IN (SELECT name FROM directory WHERE include = 'Yes' AND discription = 'Customer Care')
            AND call_legs.start_time >= '10/09/2022 12:00 AM' AND call_legs.start_time <= '10/16/2022 11:59 PM' 
            AND result = 'Call connected'
            GROUP BY call_id
            HAVING COUNT (from_name) > 1
    )
)
AND from_name IN (SELECT name FROM directory WHERE include = 'Yes' AND discription = 'Customer Care')
AND result = 'Call connected'
ORDER BY start_time ASC

解决方案

要过滤掉同一客服多次参与的通话，核心是统计不同客服的数量而非总参与次数。将原查询中的COUNT(from_name)替换为COUNT(DISTINCT from_name)即可实现需求，同时可以简化嵌套查询结构，避免冗余：

SELECT cl.*
FROM call_legs cl
WHERE cl.call_id IN (
    SELECT call_id
    FROM call_legs
    WHERE from_name IN (SELECT name FROM directory WHERE include = 'Yes' AND description = 'Customer Care')
      AND start_time >= '10/09/2022 00:00:00' 
      AND start_time <= '10/16/2022 23:59:59'
      AND result = 'Call connected'
    GROUP BY call_id
    HAVING COUNT(DISTINCT from_name) >= 2
)
AND cl.from_name IN (SELECT name FROM directory WHERE include = 'Yes' AND description = 'Customer Care')
AND cl.result = 'Call connected'
ORDER BY cl.start_time ASC;