LINUX.ORG.RU

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

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

Посоветую почти то же самое, что посоветовал в Подсчитать время в БД (комментарий)

WITH t1 AS (
  SELECT `agent`, `event`, `time`,
    LAG(`time`) OVER (ORDER BY `time`) AS prev_time,
    LAG(`event`) OVER (ORDER BY `time`) AS prev_event
  FROM queue_log WHERE `agent` = '2038'
) SELECT `agent`, SUM(timestampdiff(SECOND, prev_time, `time`))
  AS DIFF FROM t1 WHERE `event` LIKE '%UNPAUSE' AND prev_event LIKE '%PAUSE';

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

Посоветую почти то же самое, что посоветовал в Подсчитать время в БД (комментарий)

WITH t1 AS (
  SELECT `agent`, `event`, `time`,
    LAG(`time`) OVER (ORDER BY `time`) AS prev_time,
    LAG(`event`) OVER (ORDER BY `time`) AS prev_event
  FROM queue_log WHERE agent = '2038'
) SELECT agent, SUM(timestampdiff(SECOND, prev_time, `time`))
  AS DIFF FROM t1 WHERE `event` LIKE '%UNPAUSE' AND prev_event LIKE '%PAUSE';

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

Посоветую почти то же самое, что посоветовал в Подсчитать время в БД (комментарий)

WITH t1 AS (
  SELECT `event`, `time`,
    LAG(`time`) OVER (ORDER BY `time`) AS prev_time,
    LAG(`event`) OVER (ORDER BY `time`) AS prev_event
  FROM queue_log WHERE agent = '2038'
) SELECT agent, SUM(timestampdiff(SECOND, prev_time, `time`))
  AS DIFF FROM t1 WHERE `event` LIKE '%UNPAUSE' AND prev_event LIKE '%PAUSE';

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

Посоветую почти то же самое, что посоветовал в Подсчитать время в БД (комментарий)

WITH t1 AS (
  SELECT `event`, `time`,
    LAG(`time`) OVER (ORDER BY `time`) AS prev_time,
    LAG(`event`) OVER (ORDER BY `time`) AS prev_event
  FROM queue_log WHERE agent = '2038'
) SELECT SUM(timestampdiff(SECOND, prev_time, `time`))
  AS total FROM t1 WHERE `event` LIKE '%UNPAUSE' AND prev_event LIKE '%PAUSE';

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

Посоветую почти то же самое, что посоветовал в Подсчитать время в БД (комментарий)

WITH t1 AS (
  SELECT `event`, `time`,
    LAG(`time`) OVER (ORDER BY `time`) AS prev_time,
    LAG(`event`) OVER (ORDER BY `time`) AS prev_event
  FROM queue_log
) SELECT SUM(timestampdiff(SECOND, prev_time, `time`))
  AS total FROM t1 WHERE `event` LIKE '%UNPAUSE' AND prev_event LIKE '%PAUSE';