mysql - 从当前行的时间戳字段和子查询中下一行的相同字段中检索时间差

Question

我想从当前行的时间戳字段和子查询中下一行的相同字段中检索时间差。但仅在用户相同且时间差大于 10 分钟的情况下。返回值按天分组。为什么返回值比 60hours 高很多，这是不对的，因为一天只有 24 小时。我错过了什么？

请帮忙:

select DATE_FORMAT( last_call, '%d' ) AS 'day',
    COUNT(call_id) as id,

   SEC_TO_TIME(SUM((select timestampdiff(second, calls.last_call, c2.last_call)
    from calls c2
    where c2.calling_agent = calls.calling_agent and
          c2.last_call > calls.last_call and
          timestampdiff(second, calls.last_call, c2.last_call) > 600 and
          c2.last_call
    order by c2.last_call
    limit 1
   ))) brakes from calls WHERE calling_agent =9  AND 
last_call > DATE_SUB( now( ) , INTERVAL 12 MONTH ) 
GROUP BY EXTRACT( DAY FROM last_call )

Answer 1

问题出在计算时差的嵌套查询中。

让我们创建一个示例表来解释:

create table calls (call_id int, calling_agent int, last_call datetime);
insert into calls VALUES (1, 9, "2015-01-26 14:00:00");
insert into calls VALUES (2, 9, "2015-01-26 14:05:00");
insert into calls VALUES(3, 9, "2015-01-26 14:12:00");
select * from calls;

| CALL_ID | CALLING_AGENT |                      LAST_CALL |
|---------|---------------|--------------------------------|
|       1 |             9 | January, 26 2015 14:00:00+0000 |
|       2 |             9 | January, 26 2015 14:05:00+0000 |
|       3 |             9 | January, 26 2015 14:12:00+0000 |

看数据的话，两次休息时间都不到10分钟(5分钟和7分钟)。因此总和应为 0。但是您的查询将连接第 1 行和第 3 行并将中断时间计算为 12。

select DATE_FORMAT( last_call, '%d' ) AS 'day',
    COUNT(call_id) as id,

   SEC_TO_TIME(SUM((select timestampdiff(second, calls.last_call, c2.last_call)
    from calls c2
    where c2.calling_agent = calls.calling_agent and
          c2.last_call > calls.last_call and
          timestampdiff(second, calls.last_call, c2.last_call) > 600 and
          c2.last_call
    order by c2.last_call
    limit 1
   ))) brakes from calls WHERE calling_agent =9  AND 
last_call > DATE_SUB( now( ) , INTERVAL 12 MONTH ) 
GROUP BY EXTRACT( DAY FROM last_call );

| DAY | ID |    BRAKES |
|-----|----|-----------|
|  26 |  3 |  00:12:00 |

你可以玩这个 here .

由于此错误，所有休息时间的总和也可能超过 24 小时。我创建了一个示例 here .

要真正解决这个问题，我建议创建一个临时表，其中的排名如 here 所述。 .

CREATE TEMPORARY TABLE c1
SELECT    call_id, calling_agent, last_call,
          @curRank := @curRank + 1 AS rank
FROM      calls, (SELECT @curRank := 0) r
ORDER BY last_call;

然后你就可以使用这个查询了。

CREATE TEMPORARY TABLE c2 SELECT * FROM c1; -- create copy because sqlize doesn't allow joins with the same table.

SELECT
    DATE_FORMAT( t.last_call, '%d' ) AS 'day',
    COUNT(t.call_id) as id,
    SEC_TO_TIME(SUM(t.BreakTimeInSeconds)) AS TotalBreakTime
FROM
(
    SELECT
        c1.call_id, c1.calling_agent, c1.last_call, TIMESTAMPDIFF(SECOND, c1.last_call, c2.last_call) AS BreakTimeInSeconds
    FROM c1,c2
    WHERE c1.calling_agent = c2.calling_agent
    AND c1.rank + 1 = c2.rank
    HAVING BreakTimeInSeconds > 600
) AS t
WHERE t.calling_agent = 9 AND 
    t.last_call > DATE_SUB( now( ) , INTERVAL 12 MONTH ) 
GROUP BY EXTRACT( DAY FROM t.last_call );

运行它 here .

您也可以在没有临时表的情况下执行此操作，但查询可能会变得过于复杂。