mysql - 在子选择中求和返回不正确的值

Question

我正在处理一个查询，该查询计算特定类次类型在一段时间内完成的交互(下类时间戳 - 下类时间戳)，我正在对子选择中的小时数求和，因为没有办法加入表格。当我这样做时，它没有计算正确的时间。似乎在不考虑轮类类型的情况下计算该日期的所有小时数(在查询中，轮类类型称为“计划”)。这是查询:

select a.sp_id, sum(s.ints_sent) as 'ints sent', 
(
select SEC_TO_TIME(SUM(UNIX_TIMESTAMP(t.out_time) - UNIX_TIMESTAMP (t.in_time))) 
from bi.support_sp_timeclocks t
join bi.support_agents_list sal
on t.agent_sp_id = sal.sp_id
where t.agent_sp_id = a.sp_id
) 
AS 'time clocked'
from bi.cc_agents_hourly_stats s
    join bi.support_agents_list a 
        on s.desk_id = a.desk_id
    where date_sent = '2014-01-04' 
and exists 
(
    select *
    from bi.support_sp_timeclocks t2
    join bi.support_sp_shifts_scheduled ss 
    on t2.shift_id = ss.pk_id
    join bi.support_agents_list sal 
    on sal.sp_id = ss.agent_sp_id
    where sal.desk_id = a.desk_id
    and timestamp(s.date_sent, maketime(s.hour_sent,00,00)) >= t2.in_time and    
    timestamp(s.date_sent, maketime(s.hour_sent,00,00)) < t2.out_time
    and  schedule = 'SMS'
)
group by date_sent, a.public_name

它返回这个:

Agent ID  Interactions      time clocked
750705    16                420:47:21
418736    4             838:59:59

我知道对于第一个代理，“计时”列应该是 0.82 小时(结果将采用时间戳格式)，第二个代理的结果应该是 0.32 小时。

关于为什么会发生这种情况有什么想法吗？

Answer 1

838:59:59 是 SEC_TO_TIME() 输入溢出的信号。

让我们看一下您的子查询。您有此聚合查询(它使用 SUM())但没有任何 GROUP BY。

select SEC_TO_TIME(SUM(UNIX_TIMESTAMP(t.out_time) - UNIX_TIMESTAMP (t.in_time))) 
  from bi.support_sp_timeclocks t
  join bi.support_agents_list sal on t.agent_sp_id = sal.sp_id
 where t.agent_sp_id = a.sp_id

看起来您正在计算从数据库中的时间开始以来代理记录的全部时间。如果这就是您想要的，原因尚不清楚。

如果你单独运行这个子查询，你会得到什么？ (您不需要 join 操作。)

select  t.agent_sp_id,
        SEC_TO_TIME(SUM(UNIX_TIMESTAMP(t.out_time) 
                 - UNIX_TIMESTAMP (t.in_time)))   AS time_clocked
  from bi.support_sp_timeclocks t
 group by t.agent_sp_id

它是否为您的应用程序提供了更有意义的东西？ (我对此表示怀疑。)

试试这个会更有意义吗？

select  t.agent_sp_id,
        DATE(t.out_time) AS day,
        SEC_TO_TIME(SUM(UNIX_TIMESTAMP(t.out_time) 
                 - UNIX_TIMESTAMP (t.in_time)))   AS time_clocked
  from bi.support_sp_timeclocks t
 group by t.agent_sp_id, DATE(t.out_time)

这将为您提供每个代理每天花费的时间。

如果您想将查询限制在特定的一天，您可以试试这个。

select  t.agent_sp_id,
        DATE(t.out_time) AS day,
        SEC_TO_TIME(SUM(UNIX_TIMESTAMP(t.out_time) 
                 - UNIX_TIMESTAMP (t.in_time)))   AS time_clocked
  from bi.support_sp_timeclocks t
 where t.out_time >= '2014-01-04'
   and t.out_time <  '2014-01-04' + INTERVAL 1 DAY
 group by t.agent_sp_id, DATE(t.out_time)

一旦调试了计算所用时间的子查询，就可以将其连接到其他表以获取报告结果集。

希望对您有所帮助。