LINUX.ORG.RU
Форум — Admin  

MySQL - подсчет времени

 


0

1 
+-------+----------------------------+---------+
| agent | time                       | event   |
+-------+----------------------------+---------+
| 2038  | 2019-12-12 17:03:50.353069 | PAUSE   |
| 2038  | 2019-12-12 17:03:57.838925 | UNPAUSE |
| 2038  | 2019-12-12 17:04:02.755322 | PAUSE   |
| 2038  | 2019-12-12 17:04:35.352714 | UNPAUSE |
| 2038  | 2019-12-12 17:04:43.897645 | PAUSE   |
| 2038  | 2019-12-12 17:04:47.410228 | UNPAUSE |
| 2038  | 2019-12-13 15:51:33.962705 | PAUSE   |
| 2038  | 2019-12-13 15:51:39.380298 | UNPAUSE |
| 2038  | 2019-12-14 23:48:19.191524 | PAUSE   |
| 2038  | 2019-12-14 23:48:30.946623 | PAUSE   |
| 2038  | 2019-12-15 11:20:00.804379 | UNPAUSE |
| 2038  | 2019-12-15 22:27:11.150670 | PAUSE   |
| 2038  | 2019-12-15 22:27:18.140039 | UNPAUSE |
| 2038  | 2019-12-15 22:27:27.325629 | PAUSE   |
| 2038  | 2019-12-15 22:27:33.711765 | UNPAUSE |
+-------+----------------------------+---------+

Есть такая таблица (ее часть). Как посчитать кол. времени (sum) между всеми событиями PAUSE - UNPAUSE?

Переделать таблицу на

| agent | start_time               | stop_time                | event   |
vvn_black ★★★★★
()
Ответ на: комментарий от goingUp

Сначала подумал, что опрометчиво так много раз прибавлять таймстампы друг к другу, а потом решил проверить – а сколько именно раз можно это сделать (BIGINT UNSIGNED):

> SELECT ~0 DIV timestampdiff(SECOND, DATE '1970-01-01', DATE '2020-01-01');
11691161008
Darth_Revan ★★★★★
()
Последнее исправление: Darth_Revan (всего исправлений: 1)
Ответ на: комментарий от goingUp

Перевести время в таймстемп, посчитать сумму unpause, отнять cумму pause. u1-p1 + u2-p2 = u1+u2 - (p1+p2)

 select agent,time,event,sum(CASE WHEN event='UNPAUSE' THEN UNIX_TIMESTAMP(time) END) AS UNPAUSE, sum(CASE WHEN event='PAUSE' THEN UNIX_TIMESTAMP(time) END) AS PAUSE from queue_log where event like '%PAUSE' and date(time) > CURDATE()-INTERVAL 1 month and agent='2038';
+-------+----------------------------+-------+--------------------+--------------------+
| agent | time                       | event | UNPAUSE            | PAUSE              |
+-------+----------------------------+-------+--------------------+--------------------+
| 2038  | 2019-12-12 17:03:50.353069 | PAUSE | 11033993992.638348 | 12610308939.583187 |
+-------+----------------------------+-------+--------------------+--------------------+
1 row in set (0.01 sec)


 select agent,time,event,sum(CASE WHEN event='UNPAUSE' THEN UNIX_TIMESTAMP(time) END) - (sum(CASE WHEN event='PAUSE' THEN UNIX_TIMESTAMP(time) END)) DIFF from queue_log where event like '%PAUSE' and date(time) > CURDATE()-INTERVAL 1 month and agent='2038';
+-------+----------------------------+-------+--------------------+
| agent | time                       | event | DIFF               |
+-------+----------------------------+-------+--------------------+
| 2038  | 2019-12-12 17:03:50.353069 | PAUSE | -1576314946.944839 |
+-------+----------------------------+-------+--------------------+
1 row in set (0.00 sec)

что-то не то … )

anonymus123
() автор топика
Последнее исправление: anonymus123 (всего исправлений: 1)
Ответ на: комментарий от anonymus123

Хотя обнаружил две PAUSE подряд (в первом посте видны). Убрав одну получается похожий на правду результат (проверю с калькулатором):

select agent,TRUNCATE((sum(CASE WHEN event='UNPAUSE' THEN UNIX_TIMESTAMP(time) END) - (sum(CASE WHEN event='PAUSE' THEN UNIX_TIMESTAMP(time) END))),0) DIFF from queue_log where event like '%PAUSE' and date(time) > CURDATE()-INTERVAL 1 month and agent='2038'and time!='2019-12-14 23:48:30.946623';
+-------+-------+
| agent | DIFF  |
+-------+-------+
| 2038  | 41564 |
+-------+-------+
1 row in set (0.00 sec)

Появился новый вопрос, как сделать проверку на парность (PAUSE->UNPAUSE)?

Например, если идет несколько PAUSE (или UNPAUSE) подряд - брать только первую.

anonymus123
() автор топика
Последнее исправление: anonymus123 (всего исправлений: 1)
Ответ на: комментарий от anonymus123

у тебя проблема XY. консистентность информации в бд - задача другого рода, разбирайся почему идет некорректная запись

Anoxemian ★★★★★
()
Ответ на: комментарий от anonymus123

еще раз. ты пытаешься решить задачу не теми инструментами. у тебя неконсистентность во все поля. единственный толковый комментарий - самый первый.

Anoxemian ★★★★★
()
Ответ на: комментарий от Anoxemian

а еще бывает данность, которую изменить нельзя … И исходя из данных условий нужно решить задачу. Уверен - она решаема!

anonymus123
() автор топика
Последнее исправление: anonymus123 (всего исправлений: 1)
Ответ на: комментарий от anonymus123

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

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 ★★★★★
()
Последнее исправление: Darth_Revan (всего исправлений: 4)
Ответ на: комментарий от anonymus123

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

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 ★★★★★
()
Последнее исправление: Darth_Revan (всего исправлений: 1)
Ответ на: комментарий от Darth_Revan

Спасибо! Работает!

Теперь, если еще подскажете, как это сделать для всех имеющихся agent (убрать из условия agent=‘2038’) с фильтром по времени - будет совсем гуд …

И - С Новым Годом! :)

anonymus123
() автор топика
Последнее исправление: anonymus123 (всего исправлений: 1)
Ответ на: комментарий от anonymus123

Фильтр по времени? В смысле, WHERE `time` >= TIMESTAMP '2019-12-12 12:00:00'?

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') 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 ★★★★★
()
Ответ на: комментарий от anonymus123

Вроде-бы так работает:

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') AS t1     INNER JOIN (SELECT * FROM queue_log WHERE `event` = 'PAUSE') AS t2     ON t2.time < t1.time GROUP BY t1.time,t1.agent ORDER BY t1.time ) AS t3 where  date(time) > CURDATE()-INTERVAL 2 week group by agent order by agent;

Это правильно? Или это условие еще куда-то нужно вставлять?

anonymus123
() автор топика
Ответ на: комментарий от anonymus123

А, ну просто добавляешь это в подзапрос для '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 ★★★★★
()
Последнее исправление: Darth_Revan (всего исправлений: 2)
Ответ на: комментарий от anonymus123

Например, если идет несколько PAUSE (или UNPAUSE) подряд - брать только первую.

Если дело в том, что в таком случае берётся не первая, а последняя, то это принципиальная особенность подхода: берётся каждый 'UNPAUSE' и из него вычитается 'PAUSE', что стоит перед ним по времени.
К слову, если где-то пропущен 'UNPAUSE', то это проблема, и которую без LAG/LEAD я не знаю, как решить.

Darth_Revan ★★★★★
()
Последнее исправление: Darth_Revan (всего исправлений: 1)
Ответ на: комментарий от Darth_Revan

Есть такое. Да event могут идти подряд одинаковые. Нужно брать первое вхождение каждого и между ними считать время. А еще, если задан временной интервал для выборки и первый event «UNPAUSE» то вместо отсутствующего, первого «PAUSE» нужно брать время начала интервала выборки. А в конце интервала, если после «PAUSE» нет «UNPAUSE» - то вместо отсутствующего последнего «UNPAUSE» нужно брать время окончания интервала выборки. )

Вот более полный вид:

 select time,agent,event from queue_log where agent='2038' and event like '%PAUS%';
+----------------------------+-------+------------+
| time                       | agent | event      |
+----------------------------+-------+------------+
| 2019-12-12 17:03:50.347362 | 2038  | PAUSEALL   |
| 2019-12-12 17:03:50.353069 | 2038  | PAUSE      |
| 2019-12-12 17:03:57.830454 | 2038  | UNPAUSEALL |
| 2019-12-12 17:03:57.838925 | 2038  | UNPAUSE    |
| 2019-12-12 17:04:02.749672 | 2038  | PAUSEALL   |
| 2019-12-12 17:04:02.755322 | 2038  | PAUSE      |
| 2019-12-12 17:04:35.345739 | 2038  | UNPAUSEALL |
| 2019-12-12 17:04:35.352714 | 2038  | UNPAUSE    |
| 2019-12-12 17:04:43.891641 | 2038  | PAUSEALL   |
| 2019-12-12 17:04:43.897645 | 2038  | PAUSE      |
| 2019-12-12 17:04:47.403480 | 2038  | UNPAUSEALL |
| 2019-12-12 17:04:47.410228 | 2038  | UNPAUSE    |
| 2019-12-13 15:51:33.962705 | 2038  | PAUSE      |
| 2019-12-13 15:51:39.380298 | 2038  | UNPAUSE    |
| 2019-12-14 23:48:19.191524 | 2038  | PAUSE      |
| 2019-12-14 23:48:30.946623 | 2038  | PAUSE      |
| 2019-12-15 11:20:00.798159 | 2038  | UNPAUSEALL |
| 2019-12-15 11:20:00.804379 | 2038  | UNPAUSE    |
| 2019-12-15 22:27:11.150670 | 2038  | PAUSE      |
| 2019-12-15 22:27:18.140039 | 2038  | UNPAUSE    |
| 2019-12-15 22:27:27.325629 | 2038  | PAUSE      |
| 2019-12-15 22:27:33.711765 | 2038  | UNPAUSE    |
+----------------------------+-------+------------+

То, что добавились *ALL - вроде не проблема (LIKE ‘PAUSE%’, LIKE ‘UNPAUSE%’). Но они могут идти подряд (в моем случае ‘PAUSEALL’=‘PAUSE’ по смыслу, по времени могут отличаться. То же и с «UNPAUSE» и «UNPAUSEALL»)

anonymus123
() автор топика
Последнее исправление: anonymus123 (всего исправлений: 2)
Ответ на: комментарий от anonymus123

Породил вот такое чудовище:

SELECT `agent`, SUM(timestampdiff(SECOND, prev_time, `time`)) AS DIFF
FROM (
  SELECT t1.agent, t1.time, MAX(COALESCE(t3.time, t2.time)) AS prev_time
  FROM (
    SELECT `agent`, `time` FROM queue_log WHERE `event` LIKE 'UNPAUSE%'
    AND CAST(`time` AS DATE) > CURRENT_DATE - INTERVAL '1' MONTH
  ) AS t1
  INNER JOIN (SELECT `time` FROM queue_log WHERE `event` LIKE 'PAUSE%') AS t2
  LEFT OUTER JOIN (SELECT `time` FROM queue_log WHERE `event` LIKE 'PAUSE%') AS t3
  ON t2.time > t3.time AND t1.time > t3.time AND t1.time <= t2.time
  GROUP BY t1.time, t1.agent
) AS t4
GROUP BY `agent` ORDER BY `agent`;

Даёт такой же результат, что и

WITH t1 AS (
  SELECT `agent`, `event`, `time`,
    LAG(`time`) OVER (PARTITION BY `agent` ORDER BY `time`) AS prev_time,
    LAG(`event`) OVER (PARTITION BY `agent` ORDER BY `time`) AS prev_event
  FROM queue_log
  WHERE CAST(`time` AS DATE) > CURRENT_DATE - INTERVAL '1' MONTH
) SELECT `agent`, SUM(timestampdiff(SECOND, prev_time, `time`))
  AS DIFF FROM t1 WHERE `event` LIKE 'UNPAUSE%' AND prev_event LIKE 'PAUSE%'
  GROUP BY `agent`;

на проверяемой выборке.
Но даже я уже не совсем уверен, как оно работает, потому ¯_(ツ)_/¯.

Darth_Revan ★★★★★
()
Ответ на: комментарий от Darth_Revan 
SELECT `agent`, SUM(timestampdiff(SECOND, prev_time, `time`)) AS DIFF
    -> FROM (
    ->   SELECT t1.agent, t1.time, MAX(COALESCE(t3.time, t2.time)) AS prev_time
    ->   FROM (
    ->     SELECT `agent`, `time` FROM queue_log WHERE `event` LIKE 'UNPAUSE%'
    ->     AND CAST(`time` AS DATE) > CURRENT_DATE - INTERVAL '1' MONTH
    ->   ) AS t1
    ->   INNER JOIN (SELECT `time` FROM queue_log WHERE `event` LIKE 'PAUSE%') AS t2
    ->   LEFT OUTER JOIN (SELECT `time` FROM queue_log WHERE `event` LIKE 'PAUSE%') AS t3
    ->   ON t2.time > t3.time AND t1.time > t3.time AND t1.time <= t2.time
    ->   GROUP BY t1.time, t1.agent
    -> ) AS t4
    -> GROUP BY `agent` ORDER BY `agent`;
+-------+----------+
| agent | DIFF     |
+-------+----------+
| 1011  | -4171048 |
| 2001  |      263 |
| 2010  |       28 |
| 2012  |      143 |
| 2014  |      178 |
| 2017  |       40 |
| 2018  |       48 |
| 2019  |       12 |
| 2020  |      590 |
| 2025  |       33 |
| 2026  |       14 |
| 2034  |       49 |
| 2035  |    82968 |
| 2037  |        7 |
| 2038  |    83079 |
| 2043  |        5 |
| 2101  |       50 |
| 2203  |        7 |
| 2302  |      838 |
| 2404  |       10 |
| 2405  |        7 |
| 2506  |        3 |
+-------+----------+
22 rows in set (54.69 sec)

54 сек.

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` LIKE 'UNPAUS%'     AND CAST(`time` AS DATE) > CURRENT_DATE() - INTERVAL '1' MONTH) AS t1   INNER JOIN (     SELECT * FROM queue_log WHERE `event` LIKE 'PAUS%'     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`;
+-------+-------+
| agent | DIFF  |
+-------+-------+
| 1011  |    74 |
| 2001  |   263 |
| 2010  |    28 |
| 2012  |   143 |
| 2014  |   178 |
| 2017  |    40 |
| 2018  |    48 |
| 2019  |    12 |
| 2020  |   590 |
| 2025  |    33 |
| 2026  |    14 |
| 2034  |    49 |
| 2035  | 82968 |
| 2037  |     7 |
| 2038  | 83079 |
| 2043  |     5 |
| 2101  |    50 |
| 2203  |     7 |
| 2302  |   838 |
| 2404  |    10 |
| 2405  |     7 |
| 2506  |     3 |
+-------+-------+
22 rows in set (0.08 sec)

Это «старый запрос» с подправленным ‘PAUS%’, ‘UNPAUS%’

anonymus123
() автор топика
Ответ на: комментарий от anonymus123

М-да, значит, попытка неудачная.
Могу только посоветовать обновиться до MariaDB 10.2+ или MySQL 8.0+, чтобы получить оконные функции.

Darth_Revan ★★★★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
Admin