MySQL - 使用子查询进行分组

Question

我在子查询和某些分组方面遇到问题。子查询是从整个表中选择，而不仅仅是各个组...我的代码

SELECT SEC_TO_TIME(TIME_TO_SEC(call_start) - TIME_TO_SEC(call_start)%(30*60)) AS intervals, 
       COUNT(*) AS OFFERED, 
       SUM(agent_duration) AS AGENT_SUM, 
       SUM(TIME_TO_SEC(TIMEDIFF(dequeue_time, enqueue_time))) AS ANS_TIME_SUM, 
       COUNT(DISTINCT agent_username) AS UNIQUE_AGENTS, 
       (SELECT COUNT(*) FROM call_detail
        WHERE TIME_TO_SEC(TIMEDIFF(dequeue_time, enqueue_time)) < 40) AS SLA, 
       SUM(queue_duration) AS TOTAL_QUEUE_TIME 
FROM call_detail
WHERE DATE(call_start) = CURDATE()
GROUP BY intervals

我的目标是让该子查询仅返回该特定时间间隔内 TIMEDIFF 结果小于 40 的记录数

谢谢。

Answer 1

我认为您不需要为此使用子查询。只需进行条件聚合即可:

SELECT SEC_TO_TIME(TIME_TO_SEC(call_start) - TIME_TO_SEC(call_start)%(30*60)) AS intervals, 
       COUNT(*) AS OFFERED, 
       SUM(agent_duration) AS AGENT_SUM, 
       SUM(TIME_TO_SEC(TIMEDIFF(dequeue_time, enqueue_time))) AS ANS_TIME_SUM, 
       COUNT(DISTINCT agent_username) AS UNIQUE_AGENTS, 
       sum(case when TIME_TO_SEC(TIMEDIFF(dequeue_time, enqueue_time)) < 40 then 1 else 0 end) as SLA,
       SUM(queue_duration) AS TOTAL_QUEUE_TIME 
FROM call_detail
WHERE DATE(call_start) = CURDATE()
GROUP BY intervals;

您将使用子查询来获取所有记录的总计，而不是受where子句或group by影响的记录。