Расчёт времени работы оборудования по статусам в SQL

В этой статье разберём, как рассчитать время работы оборудования на основе таблицы статусов в Oracle SQL. На примере покажем, как учитывать непрерывные периоды, когда оборудование работает без новых записей, и корректно фильтровать данные по заданному интервалу дат.

Исходные данные и задача

Предположим, есть таблица statuses с полями: id (номер записи), transfer (дата начала работы), equipment (идентификатор оборудования). Каждая запись означает начало нового статуса. Оборудование работает непрерывно от даты transfer до следующей записи или до текущего момента. Например, для оборудования 1 запись от 03.08.2024 - это начало работы, которая длится до сих пор без новых записей. Задача: рассчитать время работы (в часах) для каждого оборудования в заданном диапазоне дат (например, с 01.10.2024 по 14.10.2024), учитывая, что часть периода может выходить за границы интервала.

Решение с использованием LEAD и фильтрацией по датам

Основная идея - для каждого статуса определить дату начала (dt_start) и дату окончания (dt_end) с помощью оконной функции LEAD. Для последней записи оборудования, если следующей нет, используем SYSDATE как конец. Затем фильтруем записи, у которых dt_start попадает в интервал, и вычисляем разницу в часах: (dt_end - dt_start) * 24.

Пример SQL-запроса

WITH statuses AS (
  SELECT 1 AS id, TO_DATE('03.08.2024', 'dd.mm.yyyy') AS transfer, 1 AS equipment FROM dual
  UNION ALL
  SELECT 2, TO_DATE('01.10.2024', 'dd.mm.yyyy'), 2 FROM dual
  UNION ALL
  SELECT 3, TO_DATE('03.10.2024', 'dd.mm.yyyy'), 2 FROM dual
  UNION ALL
  SELECT 4, TO_DATE('14.10.2024', 'dd.mm.yyyy'), 3 FROM dual
),
t_report AS (
  SELECT t.id,
         t.transfer AS dt_start,
         LEAD(t.transfer, 1, SYSDATE) OVER (PARTITION BY t.equipment ORDER BY t.transfer) AS dt_end,
         t.equipment
  FROM statuses t
)
SELECT t.equipment, t.dt_start, t.dt_end,
       ROUND((t.dt_end - t.dt_start) * 24, 1) AS hours
FROM t_report t
WHERE t.dt_start BETWEEN TO_DATE('01.10.2024', 'dd.mm.yyyy') AND TO_DATE('14.10.2024', 'dd.mm.yyyy');

Проблема: непрерывные периоды без новых записей

В исходных данных оборудование 1 имеет единственную запись от 03.08.2024. Оно работает непрерывно до сегодняшнего дня (SYSDATE). Однако при фильтрации по интервалу (01.10.2024 - 14.10.2024) запись с id=1 не попадает в условие WHERE dt_start BETWEEN ..., так как её начало (03.08.2024) находится за пределами интервала. В результате время работы за октябрь для оборудования 1 не учитывается.

Корректный подход: обрезка границ периода

Чтобы учесть непрерывные периоды, нужно для каждой записи вычислять фактическое время работы внутри заданного интервала. Для этого используем функции GREATEST и LEAST для обрезки дат начала и конца по границам интервала. Вместо SYSDATE для последней записи берём конец интервала, если он меньше.

Исправленный запрос

WITH statuses AS (
  -- исходные данные
  SELECT 1 AS id, TO_DATE('03.08.2024', 'dd.mm.yyyy') AS transfer, 1 AS equipment FROM dual
  UNION ALL
  SELECT 2, TO_DATE('01.10.2024', 'dd.mm.yyyy'), 2 FROM dual
  UNION ALL
  SELECT 3, TO_DATE('03.10.2024', 'dd.mm.yyyy'), 2 FROM dual
  UNION ALL
  SELECT 4, TO_DATE('14.10.2024', 'dd.mm.yyyy'), 3 FROM dual
),
intervals AS (
  SELECT t.id,
         t.transfer AS dt_start,
         LEAD(t.transfer, 1, TO_DATE('14.10.2024', 'dd.mm.yyyy')) OVER (PARTITION BY t.equipment ORDER BY t.transfer) AS dt_end,
         t.equipment
  FROM statuses t
)
SELECT t.equipment,
       GREATEST(t.dt_start, TO_DATE('01.10.2024', 'dd.mm.yyyy')) AS effective_start,
       LEAST(t.dt_end, TO_DATE('14.10.2024', 'dd.mm.yyyy')) AS effective_end,
       ROUND((LEAST(t.dt_end, TO_DATE('14.10.2024', 'dd.mm.yyyy')) - GREATEST(t.dt_start, TO_DATE('01.10.2024', 'dd.mm.yyyy'))) * 24, 1) AS hours
FROM intervals t
WHERE t.dt_start < TO_DATE('14.10.2024', 'dd.mm.yyyy')  -- период начинается до конца интервала
  AND t.dt_end > TO_DATE('01.10.2024', 'dd.mm.yyyy');   -- период заканчивается после начала интервала

Как это работает

• GREATEST - берёт максимальную дату из начала статуса и начала интервала, чтобы не учитывать время до 01.10.2024.
• LEAST - берёт минимальную дату из конца статуса и конца интервала (14.10.2024), чтобы не учитывать время после.
• Условие WHERE отбирает только те статусы, которые пересекаются с заданным диапазоном.

Таким образом, для оборудования 1 с записью от 03.08.2024 и без следующей, LEAD подставит 14.10.2024 (конец интервала). После обрезки получится период с 01.10.2024 по 14.10.2024, что даст корректное количество часов за октябрь.

Важные замечания по оптимизации

• Если таблица большая, убедитесь, что есть индекс по полям equipment и transfer.
• Для Oracle используйте SYSDATE только если нужно учитывать текущее время; в противном случае явно задавайте конец интервала.
• Функции GREATEST и LEAST работают как с датами, так и с числами - это удобно для обрезки.