Как вывести эффективное время работы станка по часам в MySQL?

Question

[Артем Владимиров]

Имеется общая таблица с событиями нескольких станков. Новые записи добавляются, когда статус станка обновляется. Например, Fanuc-0id-01 перешел в состояние ACTIVE в определенное время. Затем он стал "STOPPED" в другое время.

Необходимо, чтобы в запросе MYSQL выводилась эффективность работы определенного станка в процентах по часовым периодам за последние 24 часа. Например, станок Fanuc-0id-01 был в статусе "ACTIVE"
с 13.00 по 13.59 - 80%
с 14.00 по 14.59 - 76%
и так далее.

Самое сложное, что станок может перейти в состояние ACTIVE в одном часу, а перейти в другой статус только через пару часов.

Comments

[Артем Владимиров]

Лентюй, unavailable в идеале должен игнорироваться. MySQL 8.0.17

[Dimonchik]

а какая разница, игнорируется или нет?

[FanatPHP]

Мда, тут конечно нужны как минимум оконные функции, но лично я бы не запаривался с запросом и считал бы не на сервере, а на клиенте, в РНР :)

[Everything_is_not_so_bad]

Мне видятся 2 вещи:
1. использование СУБД временных серий
2. изменение записи журнала в новую таблицу с тем, чтобы было оптимизировано для SELECT.

Кроме вышеуказанного, гуглить: Grafana monitor sensor state

[Дмитрий]

Вы пишете, что запись появляется когда статус станка обновляется, но в вашем примере есть две подряд записи с ACTIVE статусом

[Артем Владимиров]

Если перезапустить агент, который соединяет станок и MySQL, то он по запуску сразу постит текущий статус.

Answer 1

[profesor08]

Самое сложное, что станок может перейти в состояние ACTIVE в одном часу, а перейти в другой статус только через пару часов.

Это не имеет значения. Тебе надо смотреть конкретные часы. На соседние смотреть не надо. Вот станок начал работу в 14:30 и закончил в 16:00, значит эффективность по часам у него 50%, 100%. Тебе нужно только определить период, а это две соседние записи.

Answer 2

[Дмитрий]

Вероятно это примерно то же, что показал выше Лентюй, но не думаю что возможно более эффективное по скорости решение

SELECT 
  timeStart, timeEnd, ROUND(IFNULL(seconds, 0) / 3600 * 100) as percent
FROM
(SELECT 
  timeStart, 
  timeEnd, 
  SUM(3600 - CASE 
    WHEN UNIX_TIMESTAMP(timeStarted) - UNIX_TIMESTAMP(timeStart) < 0 THEN 0 
    ELSE UNIX_TIMESTAMP(timeStarted) - UNIX_TIMESTAMP(timeStart) END - CASE 
    WHEN UNIX_TIMESTAMP(timeEnd) - UNIX_TIMESTAMP(timeStopped) < 0 THEN 0 
    ELSE UNIX_TIMESTAMP(timeEnd) - UNIX_TIMESTAMP(timeStopped) END) as seconds
  FROM (
	SELECT '2020-06-23 00:00:00' as timeStart, '2020-06-23 00:59:59' as timeEnd
	UNION SELECT '2020-06-23 01:00:00', '2020-06-23 01:59:59'  
	UNION SELECT '2020-06-23 02:00:00', '2020-06-23 02:59:59'
    UNION SELECT '2020-06-23 03:00:00', '2020-06-23 03:59:59'
    UNION SELECT '2020-06-23 04:00:00', '2020-06-23 04:59:59'
    UNION SELECT '2020-06-23 05:00:00', '2020-06-23 05:59:59'
    UNION SELECT '2020-06-23 06:00:00', '2020-06-23 06:59:59'
  // и т.д.
) as HoursSet
LEFT JOIN (
   SELECT MAX(IFNULL(StartedPoints.time, '2020-06-23 00:00:00')) as timeStarted, StoppedPoints.time as timeStopped FROM `stanki` as StoppedPoints 
        LEFT JOIN stanki as StartedPoints 
          ON StoppedPoints.name = StartedPoints.name AND
            StartedPoints.value = 'ACTIVE' AND 
            StoppedPoints.time > StartedPoints.time
        WHERE StoppedPoints.name = '01' AND StoppedPoints.value IN ('STOPPED', 'UNAVAILABLE')
        GROUP BY StoppedPoints.time
  UNION ALL
  SELECT time, '2020-06-24 00:00:00'
  FROM (SELECT * FROM stanki WHERE name = '01' ORDER BY time DESC LIMIT 1) as LastRecord
  WHERE value = 'ACTIVE'  
) as ActiveIntervals ON ActiveIntervals.timeStopped > HoursSet.timeStart 
  AND ActiveIntervals.timeStarted <= HoursSet.timeEnd
GROUP BY timeStart, timeEnd) as CalculatedSeconds