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

Имеется общая таблица с событиями нескольких станков. Новые записи добавляются, когда статус станка обновляется. Например, Fanuc-0id-01 перешел в состояние ACTIVE в определенное время. Затем он стал "STOPPED" в другое время.
5ef033838573c422545594.png
Необходимо, чтобы в запросе MYSQL выводилась эффективность работы определенного станка в процентах по часовым периодам за последние 24 часа. Например, станок Fanuc-0id-01 был в статусе "ACTIVE"
с 13.00 по 13.59 - 80%
с 14.00 по 14.59 - 76%
и так далее.
5ef034e171ecb788968799.png
Самое сложное, что станок может перейти в состояние ACTIVE в одном часу, а перейти в другой статус только через пару часов.
  • Вопрос задан
  • 584 просмотра
Пригласить эксперта
Ответы на вопрос 3
BojackHorseman
@BojackHorseman Куратор тега MySQL
...в творческом отпуске...
SELECT 
 _data.`_begin`,
 _data.`name`,
 ROUND(SUM(UNIX_TIMESTAMP(_data.`_right`) - UNIX_TIMESTAMP(_data.`_left`) + 1) / 3600, 2) * 100 AS _availability
FROM (
	SELECT DISTINCT
	 calendar.`_begin`,
	 periods.`name`, 	 
	 GREATEST(calendar.`_begin`, periods.`_begin`) AS _left,
     LEAST(calendar.`_end`, periods.`_end`) AS _right
	FROM (
		SELECT
		 CURDATE() + INTERVAL (HOUR(NOW()) - iter.hours) * 3600 SECOND AS _begin,
		 CURDATE() + INTERVAL (HOUR(NOW()) - iter.hours + 1) * 3600 - 1 SECOND AS _end
		FROM (
			SELECT 24 AS hours UNION SELECT 23 UNION SELECT 22 UNION SELECT 21 UNION SELECT 20 UNION SELECT 19 UNION  
			SELECT 18 UNION SELECT 17 UNION SELECT 16 UNION SELECT 15 UNION SELECT 14 UNION SELECT 13 UNION  
			SELECT 12 UNION SELECT 11 UNION SELECT 10 UNION SELECT 9 UNION SELECT 8 UNION SELECT 7 UNION  
			SELECT 6 UNION SELECT 5 UNION SELECT 4 UNION SELECT 3 UNION SELECT 2 UNION SELECT 1 
		) iter
	) calendar
	JOIN (
		SELECT 
		 s.`name`, 
		 s.`time` AS _begin,
		 COALESCE((SELECT s1.`time` FROM `status_log` s1 WHERE s1.`value`= 'STOPPED' AND s1.`name` = s.`name` AND s1.`time` > s.`time` ORDER BY s1.`time` LIMIT 1), NOW()) AS _end
		FROM `status_log` s
		WHERE s.value = 'ACTIVE'
	) periods ON (calendar.`_begin` <= periods.`_begin` AND (calendar.`_end` >= periods.`_end`))
		OR (calendar.`_begin` >= periods.`_begin` AND (calendar.`_begin` <= periods.`_end`))
		OR (calendar.`_end` >= periods.`_begin` AND (calendar.`_end` <= periods.`_end`))
) _data
GROUP BY _data.`_begin`, _data.`name`


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

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

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
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы