多表分组统计查询需求：联系人与拒绝记录统计及TotalCount异常问题

数据表结构 #

contacts表 #

refusals表 #

需求结果 #

需要得到每个坐席的总联系次数和拒绝次数：

当前问题 #

你提供的SQL能正确统计拒绝数，但TotalCount计算错误，且查询逻辑未匹配需求中的表结构：

SELECT
CallData.Agent_Name
,count(*) as RefusalCount
--,count(*) * 100.0 / sum(count(*)) over() as Percentage
,sum(count(*)) over() as TotalCount
FROM disposition as CallData
left outer join refusals as refusals on CallData.Contact_ID = refusals.contact_id
where 
  parse_timestamp("%m/%d/%Y %I:%M:%S %p", refuse_time) > '2024-01-01 00:00:00 UTC'
  AND refusals.Reason in ('NoAnswer','Dropped')
  AND users.Status = 'Active'
group by CallData.Agent_Name
order by RefusalCount desc

问题分析 #

1. 左连接失效：where子句中添加了refusals表的过滤条件，直接将left outer join转为了内连接，会过滤掉没有拒绝记录的坐席（如果存在）。
2. 统计逻辑偏差：当前查询未统计总联系次数，且sum(count(*)) over()仅计算分组后拒绝数的总和，并非需求中的总联系次数。

解决方案 #

基于你给出的contacts/refusals表的正确查询 #

以contacts表为基础左连接refusals表，按坐席分组统计：

SELECT
  c.agent_name,
  COUNT(c.agent_name) AS countofcontacts,
  COUNT(r.agent_name) AS countofrefusals,
  -- 可选：所有坐席的总联系次数
  SUM(COUNT(c.agent_name)) OVER() AS total_contacts,
  -- 可选：所有坐席的总拒绝次数
  SUM(COUNT(r.agent_name)) OVER() AS total_refusals
FROM contacts c
LEFT JOIN refusals r 
  ON c.agent_name = r.agent_name
GROUP BY c.agent_name
ORDER BY countofrefusals DESC;

针对你现有disposition表的修正版本 #

如果实际业务用disposition（联系记录）和refusals（拒绝记录），需调整过滤条件保留左连接逻辑：

SELECT
  CallData.Agent_Name,
  COUNT(CallData.Contact_ID) AS countofcontacts,
  COUNT(refusals.contact_id) AS countofrefusals,
  SUM(COUNT(CallData.Contact_ID)) OVER() AS total_contacts,
  SUM(COUNT(refusals.contact_id)) OVER() AS total_refusals
FROM disposition AS CallData
LEFT JOIN refusals 
  ON CallData.Contact_ID = refusals.contact_id
LEFT JOIN users 
  ON CallData.Agent_Name = users.Agent_Name -- 假设users表通过坐席名关联
WHERE 
  users.Status = 'Active'
  -- 将refusals的过滤条件改为兼容null的逻辑，避免左连接失效
  AND (refusals.refuse_time IS NULL OR parse_timestamp("%m/%d/%Y %I:%M:%S %p", refusals.refuse_time) > '2024-01-01 00:00:00 UTC')
  AND (refusals.Reason IS NULL OR refusals.Reason IN ('NoAnswer','Dropped'))
GROUP BY CallData.Agent_Name
ORDER BY countofrefusals DESC;

关键修正点 #

• 将refusals表的过滤条件调整为兼容null的逻辑，保证左连接保留所有联系记录。
• 分别基于两张表的主键统计次数，避免count(*)因连接导致的统计错误。
• 用SUM(COUNT(...)) OVER()正确计算全局总数，先分组统计单坐席数据，再通过窗口函数求和得到全局值。

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