MySQL. Выбор времени работы с - по и общее время

202

25 февраля 2022, 09:50

Необходимо посчитать общее время работы оборудования в разных промежутках. К примеру приходят следующие данные:

03.01.2019 8:39:02  1
03.01.2019 8:40:02  1
03.01.2019 8:40:32  1
03.01.2019 8:41:30  1
03.01.2019 8:42:30  1
03.01.2019 8:42:46  0
03.01.2019 8:42:58  0
03.01.2019 8:46:49  0
03.01.2019 8:47:11  0
03.01.2019 8:48:20  0
03.01.2019 8:49:04  0
03.01.2019 8:53:47  1
03.01.2019 8:54:47  1
03.01.2019 8:55:47  1
03.01.2019 8:56:47  1
03.01.2019 8:57:35  1
03.01.2019 8:58:35  1

1 - работал. 0 - не работал.

Мне нужно из этого получить такой формат - работал с, работал по, общее время работы. Пример:

Работал с 03.01.2019 8:39:02 по 03.01.2019 8:42:30. Общее время работы - 00:03:28. Работал с 03.01.2019 8:53:47 по 03.01.2019 8:58:35. Общее время работы - 00:04:48.

По идее разницу легко получаю с помощью функции TIMEDIFF. Но как сгруппировать данные, чтобы получить именно разницу этих промежутков?

Пример таблицы:

CREATE TABLE gps.zzz_test (
  id INT(11) DEFAULT NULL,
  time_event DATETIME NOT NULL,
  is_work INT(1) NOT NULL
)
ENGINE = INNODB,
CHARACTER SET utf8,
COLLATE utf8_general_ci;
insert into zzz_test (id, time_event, is_work) values(1, '2019-01-03 08:39:02',1), (1, '2019-01-03 08:40:02',1), (1, '2019-01-03 08:40:32',1),(1, '2019-01-03 08:41:30',1),(1, '2019-01-03 08:42:30',1),
  (1, '2019-01-03 08:42:46',0), (1, '2019-01-03 08:42:58',0),(1, '2019-01-03 08:46:49',0),(1, '2019-01-03 08:47:11',0),
(1, '2019-01-03 08:53:47',1),(1, '2019-01-03 08:54:47',1),(1, '2019-01-03 08:55:47',1),(1, '2019-01-03 08:56:47',1),(1, '2019-01-03 08:57:35',1),(1, '2019-01-03 08:58:35',1);

Заранее, большое спасибо!

Версия MySQL 5.6

Answer 1

Решение для версии 8+:

WITH 
cte1 AS ( SELECT id,
                 time_event, 
                 is_work,
                 LAG(is_work) OVER (PARTITION BY id 
                                     ORDER BY time_event ASC) work_prev, 
                 LEAD(is_work) OVER (PARTITION BY id 
                                     ORDER BY time_event ASC) work_next
          FROM test ),
cte2 AS ( SELECT id,
                 time_event,
                 SUM(work_next) OVER (PARTITION BY id
                                      ORDER BY time_event ASC) group_num
          FROM cte1
          WHERE is_work = 1 
            AND COALESCE(work_prev, 0) + COALESCE(work_next, 0) = 1 )
SELECT CONCAT( 'Агрегат ID = ', 
               id, 
               '. Работал с ', 
               MIN(time_event), 
               ' по ',
               MAX(time_event),
               '. Общее время работы - ',
               TIMEDIFF(MAX(time_event), MIN(time_event)),
               ' .') output
FROM cte2
GROUP BY id, group_num
ORDER BY id, group_num;

fiddle

Делать то же для 5.6 - откровенно лениво. Да и монстр будет ещё тот... впрочем, на переменных, может, и ничего будет - ну тогда просто лениво.