mysql - 汇总子选择返回错误的值

我正在处理一个查询，该查询计算特定班次类型在一个时间段内（超时时间戳-时间戳时钟）进行的交互，并且我将子选择中的小时数相加，因为没有连接表的方法。当我这样做时，它不会计算正确的小时数。似乎它是在计算该日期的所有小时数，而不考虑轮班类型（在查询中，轮班类型称为“时间表”）。这是查询：

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

我知道对于第一个座席，“计时时间”列应为.82小时（结果将以时间戳格式显示），而对于第二个座席，结果应为.32小时。

为什么会这样呢？

最佳答案

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)

调试了用于计算花费时间的子查询后，可以将其与其他表连接以获得报表结果集。

我希望这有帮助。