🔥 Live Coding: время выполнения задачи в Jira по статусам

Задача: Есть таблица jira_status_history:

• task_id • status • status_time

Нужно для каждой задачи посчитать время выполнения.

Что считаем временем выполнения: • start_time — первый переход в статус "В процессе" • end_time — последний переход в статус "Завершено" после start_time • duration — разница между ними

Важно: • если "В процессе" был несколько раз — берем самый первый • если "Завершено" был несколько раз — берем последний, но только после start_time • промежуточные статусы игнорируем • если нет "В процессе" или нет "Завершено" после начала — такую задачу не берем

На выходе хотим получить:

• task_id • start_time • end_time • duration

Решение:

with started as ( select task_id, min(status_time) as start_time from jira_status_history where status = 'В процессе' group by task_id ),

finished as ( select s.task_id, s.start_time, max(j.status_time) as end_time from started s inner join jira_status_history j on s.task_id = j.task_id where j.status = 'Завершено' and j.status_time >= s.start_time group by s.task_id, s.start_time )

select task_id, start_time, end_time, dateDiff('second', start_time, end_time) as duration from finished order by task_id

🧠 Как здесь думаем:

Сначала отдельно находим момент старта работы.

Для этого берем: • только строки со статусом "В процессе" • по каждой задаче выбираем минимальный status_time

Это и есть первый момент, когда задача реально ушла в работу ✅

Дальше ищем завершение. Но не просто любое "Завершено", а только то, которое произошло после start_time.

Поэтому: • джойним задачи со стартом обратно к истории статусов • оставляем только статус "Завершено" • фильтруем j.status_time >= s.start_time • берем max(status_time)

Почему max? Потому что по условию нужен именно последний переход в "Завершено" после начала работы.

После этого считаем duration как разницу между end_time и start_time.

⚠️ Где можно ошибиться:

Взять первое "Завершено", а не последнее

По условию нужно именно последнее завершение после начала работы. Если взять min(status_time), логика будет неверной ❌

Не отфильтровать "Завершено" после start_time

Тогда можно случайно подобрать завершение, которое было раньше старта. Это сломает расчет.

Искать последнее событие вообще, а не последнее "Завершено"

Нам не важно, что было последним статусом в истории задачи. Нам важно найти последний статус "Завершено" после первого входа в "В процессе".

Не учесть задачи без завершения

Если у задачи есть старт, но нет "Завершено" после него, она не должна попадать в результат.

💡 Если нужна длительность в часах, можно так:

dateDiff('hour', start_time, end_time) as duration_hours

А если нужна более точная метрика, лучше оставить секунды и уже потом переводить в нужный формат.

💡 Более компактная логика та же самая: • сначала определяем точку старта • потом определяем финальную точку завершения после старта • потом считаем разницу

🎯 Вывод:

Это типовая задача на event history и бизнес-логику по статусам.

Главная мысль: нужно не просто взять min и max по всей задаче, а правильно привязать конец процесса к моменту начала работы.

Именно такие задачи часто встречаются при расчете: • SLA • cycle time • lead time • времени обработки задач в Jira / CRM / Service Desk 📊