SQL多条件子查询分组计算异常：AVG结果重复问题排查

需求 #

• 当column2不为空时，计算total_tat的平均值（对应NICE_TIME）
• 当column2为空时，计算total_tat的总和（对应Good_Hours）
• 按user_name分组，统计每个用户的missed_calls（column2不为空的数量），并按用户维度展示结果

现有问题 #

当前SQL的Good_Hours（总和）计算正常，但NICE_TIME（平均值）仅返回第一条用户的结果，并在所有用户行重复显示。

错误SQL示例 #

SELECT T1.user_name, T1.Good_Hours, T2.*
FROM
(
   SELECT user_name, SEC_TO_TIME( SUM( TIME_TO_SEC( total_tat ) ) ) as Good_Hours
   FROM Table1
   WHERE etc_type = "YES"
   -- 错误：重复使用WHERE，应改为AND
   WHERE date_time BETWEEN "(DATETIME example Only)" AND "(DATETIME example Only)"
   AND column2 IS NULL
   GROUP BY user_name
) as T1,
(
   SELECT COUNT(column2) as missed_calls, SEC_TO_TIME(AVG(TIME_TO_SEC(total_tat))) as NICE_TIME
   FROM Table1
   WHERE etc_type = "YES"
   -- 错误：重复使用WHERE，应改为AND
   WHERE date_time BETWEEN "(DATETIME example Only)" AND "(DATETIME example Only)"
   AND column2 IS NOT NULL
   GROUP BY user_name
) as T2
-- 错误：未通过user_name关联T1和T2，导致笛卡尔积

问题原因 #

1. 笛卡尔积问题：两个子查询T1和T2之间没有通过user_name建立关联条件，数据库会将T1的每一行与T2的每一行进行匹配，最终T2的第一条记录会被重复匹配到T1的所有行，导致NICE_TIME值重复。
2. 语法错误：单个WHERE子句中重复使用WHERE关键字，应改为AND连接多个条件；主查询中字段引用格式错误。

修正后的SQL #

使用条件聚合，仅需一次表扫描即可完成所有统计，避免关联问题：

SELECT 
    user_name,
    -- 统计column2不为空的数量（missed_calls）
    COUNT(CASE WHEN column2 IS NOT NULL THEN column2 END) AS missed_calls,
    -- column2为空时，计算total_tat的总和
    SEC_TO_TIME(SUM(CASE WHEN column2 IS NULL THEN TIME_TO_SEC(total_tat) ELSE 0 END)) AS Good_Hours,
    -- column2不为空时，计算total_tat的平均值；无符合条件记录时返回NULL，可按需替换为00:00:00
    SEC_TO_TIME(AVG(CASE WHEN column2 IS NOT NULL THEN TIME_TO_SEC(total_tat) ELSE NULL END)) AS NICE_TIME
FROM Table1
WHERE 
    etc_type = 'YES'
    AND date_time BETWEEN '起始时间' AND '结束时间'
GROUP BY user_name;

说明 #

• 条件聚合通过CASE语句在聚合函数内部筛选符合条件的记录，确保每个聚合计算仅针对对应场景的数据。
• 避免了多子查询未关联导致的性能问题和结果错误，同时代码更简洁易维护。

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