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

Исправление Darth_Revan, 02.01.20 02:50 (текущая версия) :

А, ну просто добавляешь это в подзапрос для 'UNPAUSE':

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 CAST(`time` AS DATE) > CURRENT_DATE - INTERVAL '1' MONTH) AS t1
  INNER JOIN (SELECT * FROM queue_log WHERE `event` = 'PAUSE') AS t2
  ON t2.time < t1.time GROUP BY t1.time, t1.agent
) AS t3
GROUP BY `agent` ORDER BY `agent`;

Исправление Darth_Revan, 02.01.20 02:48:

А, ну просто добавляешь это в подзапрос:

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 CAST(`time` AS DATE) > CURRENT_DATE - INTERVAL '1' MONTH) AS t1
  INNER JOIN (SELECT * FROM queue_log WHERE `event` = 'PAUSE') AS t2
  ON t2.time < t1.time GROUP BY t1.time, t1.agent
) AS t3
GROUP BY `agent` ORDER BY `agent`;

Ну или снаружи тоже можно, тогда будет чуть медленнее.

Исходная версия Darth_Revan, 02.01.20 02:45:

А, ну просто добавляешь это в подзапросы:

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 CAST(`time` AS DATE) > CURRENT_DATE - INTERVAL '1' MONTH) AS t1
  INNER JOIN (
    SELECT * FROM queue_log WHERE `event` = 'PAUSE'
    AND CAST(`time` AS DATE) > CURRENT_DATE - INTERVAL '1' MONTH) AS t2
  ON t2.time < t1.time GROUP BY t1.time, t1.agent
) AS t3
GROUP BY `agent` ORDER BY `agent`;