История изменений

Исправление Darth_Revan, 01.01.20 23:22 (текущая версия) :

Без оконных функций тяжело…
Что ж, вроде бы, так работает:

SELECT `agent`, SUM(timestampdiff(SECOND, prev_time, `time`)) AS DIFF
FROM (
  SELECT t1.agent, t1.time, MAX(t2.time) AS prev_time
    FROM (SELECT * FROM queue_log WHERE `event` = 'UNPAUSE' AND `agent` = '2038') AS t1
    INNER JOIN (SELECT * FROM queue_log WHERE `event` = 'PAUSE' AND `agent` = '2038') AS t2
    ON t2.time < t1.time GROUP BY t1.time ORDER BY t1.time
) AS t3;

Исходная версия Darth_Revan, 01.01.20 23:20:

Без оконных функций тяжело…
Что ж, вроде бы, так работает:

SELECT `agent`, SUM(timestampdiff(SECOND, prev_time, `time`)) AS DIFF
FROM (
  SELECT t1.agent, t1.time, MAX(t2.time) AS prev_time
    FROM (SELECT * FROM queue_log WHERE event = 'UNPAUSE' AND agent = '2038') AS t1
    INNER JOIN (SELECT * FROM queue_log WHERE event = 'PAUSE' AND agent = '2038') AS t2
    ON t2.time < t1.time GROUP BY t1.time ORDER BY t1.time
) AS t3;