Где застревают согласования: длительность этапов процесса в SQL

Согласование возвращается из финансового отдела к юристам, затем снова уходит на проверку. Если взять только первую и последнюю дату этапа, время других отделов попадёт внутрь его длительности. Надёжнее восстановить каждое отдельное пребывание в состоянии по журналу событий и только затем агрегировать.

Определите смысл события журнала

Пусть строка означает вход заявки в новый этап. Следующее событие завершает предыдущее пребывание. Статус done закрывает процесс и сам не считается рабочим этапом. Для незавершённого этапа используем явно заданный момент среза.

Время в примере календарное, без исключения ночей и выходных. Если регламент требует рабочие минуты, каждое восстановленное пребывание надо пересечь с производственным календарём. Сначала решается задача границ этапов, затем применяется правило времени.

Восстановите интервалы через LEAD

Окно разбивается по заявке, порядок задаётся временем и ID события. LEAD получает начало следующего этапа. Если следующего события нет, конец равен моменту среза, а интервал отмечается открытым.

Запрос выполняется в SQLite. unixepoch считает разницу в секундах, затем результат делится на 60.0. События после среза исключены до окна, чтобы будущая информация не изменила видимое состояние процесса. ID разрешает равные времена только при подтверждённом порядке источника.

WITH events(id,request_id,at,stage) AS (
 VALUES (1,1,'2026-11-02 09:00:00','legal'),(2,1,'2026-11-02 10:00:00','finance'),
 (3,1,'2026-11-02 11:00:00','legal'),(4,1,'2026-11-02 12:00:00','done'),
 (5,2,'2026-11-02 09:00:00','legal'),(6,2,'2026-11-02 11:00:00','finance')
), ordered AS (
 SELECT *,LEAD(at) OVER(PARTITION BY request_id ORDER BY at,id) AS next_at
 FROM events WHERE at<='2026-11-02 13:00:00'
), intervals AS (
 SELECT stage,next_at IS NULL AS is_open,
 (unixepoch(COALESCE(next_at,'2026-11-02 13:00:00'))-unixepoch(at))/60.0 AS minutes
 FROM ordered WHERE stage<>'done'
)
SELECT stage,COUNT(*) AS visits,SUM(minutes) AS total_minutes,
 ROUND(AVG(minutes),2) AS mean_visit_minutes,SUM(is_open) AS open_visits
FROM intervals GROUP BY stage ORDER BY total_minutes DESC,stage;

Сохраните повторные посещения

Первая заявка проходит legal с 09:00 до 10:00, finance до 11:00, снова legal до 12:00 и завершается. Вторая находится в legal два часа, затем в finance до среза в 13:00. Юристы суммарно получают 240 минут за три посещения, финансы — 180 за два.

Среднее считается по посещению этапа, а не по заявке. Для среднего вклада отдела на заявку сначала суммируйте все посещения одной заявки и лишь затем усредняйте. Эти показатели отвечают на разные вопросы о процессе.

Покажите незавершённую работу

У finance одно открытое посещение. Его длительность на срезе включена в суммарное наблюдаемое время, но это ещё не завершённый срок. Для сравнения завершённых этапов посчитайте отдельный показатель, сохранив открытую очередь рядом. Иначе отдел с долгими текущими задачами может выглядеть искусственно быстрым.

Суммарное время показывает объём накопленного ожидания, а среднее — типичную длительность выбранной единицы. Максимум помогает найти единичные зависания. Для вывода об узком месте полезно смотреть эти показатели вместе с числом поступающих заявок, а не ранжировать отделы по одной цифре.

Проверьте повтор одного статуса, равные времена и пропущенное событие. Два последовательных входа в legal могут быть отдельными техническими записями, которые надо свернуть, либо реальными повторными назначениями. Правило определяется журналом системы. Пропущенный переход нельзя восстановить одним LEAD без дополнительного источника.

Для регулярной витрины сохраняйте события и момент расчёта. Руководитель сможет открыть конкретные посещения этапа и увидеть, откуда взялось ожидание. Тогда обсуждение ускорения процесса опирается на отдельные интервалы, повторные возвраты и текущую очередь.

От запроса к рабочей витрине

В платном курсе вы разберёте последовательности событий, оконные функции и временные интервалы в SQLite. Собственные запросы проверяются на равных датах, дублях и пропусках. Это помогает собрать витрину этапов процесса, где повторные посещения и открытая работа сохраняют свой смысл.

Откройте программу платного курса «Продвинутый SQL: сложные запросы и задачи для собеседований».

Продолжить разбор: Как разбить события на сессии в SQL по паузе между действиями.