Как мы рассчитываем время выполнения задачи, рабочие часы между двумя датами

Измерение количества рабочих часов между двумя датами с использованием SQL — это одна из тех классических задач, которая звучит просто, но мучает аналитиков с незапамятных времен.

Эта задача возникает в нескольких местах в dbt Labs:

• Расчет времени, необходимого для решения заявки в службу поддержки
• Измерение производительности команды в соответствии с соглашениями об уровне обслуживания (SLA) по времени ответа

Внутри компании мы называем это "время выполнения задачи", и это может быть критически важной точкой данных для команд, работающих с клиентами. К счастью, наши инструменты для расчета времени выполнения задачи немного улучшились с 2006 года.

Тем не менее, вам придется выполнить довольно сложные SQL или dbt-манипуляции, чтобы сделать это правильно, включая:

1. Определение, как исключить ночи и выходные из ваших SQL-расчетов
2. Учет праздников с использованием пользовательского календаря праздников
3. Приспособление к изменениям в расписании рабочих часов

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

Две стратегии расчета времени выполнения задачи

1. Универсальное решение с вложенными макросами

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

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

2. Индивидуальное и настраиваемое решение с подзапросом

Наш текущий расчет времени выполнения задачи может быть как мощным, так и гибким благодаря использованию конструкции, которую вы редко видите в dbt Labs - [ах] . Используя часовой уровень таблицы дат , вы можете стандартизировать уникальное определение рабочих часов вашей организации по сравнению с нерабочими часами полностью настраиваемым способом.

Вы можете найти пример кода для каждого из этих подходов в примере репозитория.

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

Мы составили серию вопросов, которые вы должны задать себе, чтобы убедиться, что вы оптимизируете время выполнения задачи для решения тех проблем, для которых оно лучше всего подходит.

Универсальное решение: вложенные макросы

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

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

Предположим, что ваша команда поддержки клиентов всегда работает с понедельника по пятницу, с 8 утра до 8 вечера, и ваше расписание выглядит примерно так:

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

Наша метрика, учитывающая расписание, должна захватывать только несерое время:

Как мы можем этого добиться? Для любой из этих заявок общая формула для получения нужного нам ответа сводится к вычитанию нерабочего времени из общего количества времени между датами (т.е. обычный datediff):

Эти блоки нерабочего времени можно разбить на две части: ночное время и выходные. Но как мы можем динамически подсчитать количество ночей или выходных дней? Встречайте макрос для будних дней!

Как исключить выходные?

Основываясь на отличной работе смелых сотрудников Looker, мы создали макрос, который возвращает количество будних дней между двумя датами. Он работает, вычисляя количество календарных дней между двумя временными метками, а затем вычитая количество суббот и воскресений из этого результата. Таким образом, для заявки, созданной в понедельник и закрытой во вторник, макрос weekdays_between возвращает 1. Для заявки, открытой в четверг и закрытой в следующий понедельник, этот макрос возвращает 2!

Это оказывается полезным дважды - результат макроса для будних дней совпадает с количеством ночей между двумя датами, что фактически является первой половиной нашей формулы нерабочего времени. Умножение количества будних дней между двумя датами на дневное окно нерабочего времени дает нам количество ночных часов (в нашем примере это окно с 8 вечера до 8 утра, или 12 часов).

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

Вы, возможно, уже заметили огромную оговорку здесь — в этом расчете заложено постоянное расписание, и это обычно не так. Добавьте государственный праздник или нового сотрудника в другом часовом поясе, и внезапно эти расчеты перестают отражать реальность! Некоторые крайние случаи и как мы с ними справляемся:

Что если заявка поступает вне рабочих часов?

Ранее у нас было обходное решение, встроенное в саму таблицу заявок! Мы поддерживаем модель all_business_hours в нашем проекте, используя макрос date_spine из dbt_utils. Это создает таблицу на уровне часа, и мы добавляем пользовательский булевый столбец, который указывает, находится ли этот час в нашем рабочем окне с 8 утра до 8 вечера. Затем мы соединяем это с нашими данными о заявках и для каждой временной метки заявки создаем новый столбец, который возвращает следующий доступный рабочий час.

Таким образом, для любой временной метки, которая уже находится в рабочих часах, как в приведенном выше примере, столбец timestamp_business идентичен, но для любой заявки, которая поступает вне рабочих часов, он возвращает первый рабочий час следующего дня - т.е. заявка, сделанная поздно вечером в среду, имеет временную метку start_business в 8:00 утра в четверг. Это позволяет нам выполнять эти расчеты только на временных метках, которые появляются в наших рабочих часах.

А как насчет праздников?

Мы поддерживаем файл семян в нашем проекте, который содержит даты праздников на следующие 5 лет или около того - мы соединяем это с нашей таблицей date_dim на уровне часа и включаем праздники в упомянутый выше булевый столбец. Таким образом, любая заявка, поступившая в праздничный день, быстро перемещается к началу следующего рабочего дня. Это не идеальное решение, поэтому интересно услышать, как это решается в других местах!

Настраиваемый вариант: индивидуальный календарь + подзапрос

Наш первый подход к измерению недавно потребовал корректировки, когда изменились наши рабочие часы. Сразу стало очевидно, что эта башня из макросов была слишком сложной для анализа и не позволяла легко вносить необходимые изменения. Мы решили отказаться от всего этого и упростить с помощью редкого (для нас!) использования коррелированного подзапроса.

Как мы учли изменения в рабочих часах

Мы поняли, что, поскольку мы уже поддерживаем таблицу измерений дат на уровне часа, используя макрос datespine из dbt_utils, как упоминалось выше, мы могли бы настроить булевый is_business_hour, чтобы отразить изменения в расписаниях с течением времени. Затем мы можем использовать таблицу на уровне часа, чтобы правильно контролировать агрегацию без чрезмерно сложных макросов. Варианты для достижения этого были:

1. Присоединиться непосредственно к таблице на уровне часа, агрегировать после факта

2. Использовать подзапрос для выполнения агрегации

Учитывая, что мы фактически рассчитывали несколько из этих метрик на одной таблице заявок (например, время до первого касания, время до первого закрытия, время до последнего касания и т.д.), прямое соединение вызвало бы много конкурирующего разветвления, которое мы решили было бы слишком сложно управлять.

Как правило, команда dbt Labs предпочитает использовать CTE, а не подзапросы, но это был один из немногих случаев, когда преимущества, казалось, перевешивали недостатки. Подзапрос позволил нам выполнять расчет рабочих часов на любых двух полях даты без изменения уровня нашей модели заявок.

Вот пример, чтобы объяснить наш подход с подзапросом: если рабочие часы для нашей команды с 8:00 до 20:00, и заявка была открыта в 8:46 утра во вторник, закрыта в 1:13 дня в среду по расписанию с 8 до 8, чтобы измерить рабочие часы с момента открытия до закрытия, вам нужно включить:

• 14 минут (8:46 утра - 9:00 утра) +

• 16 часов (9:00 утра - 8:00 вечера во вторник + 8:00 утра - 1:00 дня в среду) +

• 13 минут (1:00 дня - 1:13 дня)

Что сокращается до:

select

    (60 - extract(minute from start_time) +

    ( select count_if(is_business_hour) * 60 from all_business_hours where date_hour > start_date  and date_hour < end_date ) +

   ( extract(minute  from end_time)

from table

Какова ценность точного измерения рабочих часов?

Давайте отступим и подумаем, какую пользу эти метрики действительно имеют. В чем преимущество наличия метрики, чувствительной к расписанию, по сравнению с простым использованием функции date_diff() и нахождением общего времени, прошедшего с момента начала?

Я обнаружил, что меры времени, скорректированные с учетом бизнеса, полезны в нескольких отношениях:

1. Метрики SLA

   1. Ответы на вопросы типа "какой процент заявок обрабатывается в течение 15 рабочих минут?" — отличные примеры применения этих метрик!

2. Эффективно ли мои сотрудники используют свое время?

3. Какова пропускная способность одного сотрудника для выполнения имеющейся у нас работы?

4. Сколько еще сотрудников мне нужно нанять, чтобы справиться с входящей работой?

5. Оказало ли изменение процесса/инструментов, которое я сделал, значительное влияние на то, насколько хорошо моя команда может выполнять свою работу?

Что не скажут вам меры времени, скорректированные с учетом бизнеса

1. Был ли мой клиент разочарован тем, сколько времени это заняло? (Заявка могла занять у вашей команды пару часов рабочего времени, но если общее время прошло ночью или в выходные, клиент все равно чувствует, что это заняло столько времени!)

   1. Если они были, достаточно ли хорошо я сообщаю клиентам ожидания по времени ответа?

Так есть ли польза в измерении этих метрик таким образом, чтобы учитывать рабочие часы? Я думаю, что да! Но, как упоминалось в нашем сообществе Slack некоторое время назад, делать это в изоляции не дает полной картины того, что чувствуют ваши клиенты при взаимодействии с вашей командой. Отличная цитата от члена сообщества Джеймса Уикли:

"Любые меры, которые учитывают расписание команды поддержки, являются мерами процесса, а не мерами результата. Другими словами, они очень полезны для планирования рабочей силы или улучшения процессов, но любой, кто регистрировал заявки в службу поддержки крупных компаний, знает, что они не коррелируют с удовлетворенностью клиентов, так как их слишком легко манипулировать."

Я полностью согласен с Джеймсом здесь и считаю, что эти метрики наиболее полезны, когда они отвечают на конкретные вопросы о ваших инструментах и процессах. Они не могут много сказать о том, что чувствует клиент, поэтому их следует сочетать с метриками, не зависящими от расписания, которые действительно показывают, как это выглядит с точки зрения клиента (а также с прямыми мерами удовлетворенности клиентов, такими как опросы!).

Особая благодарность Клэр Кэрролл и Эрике Луи за помощь в этой работе! Некоторые образцы данных и кода для обоих подходов можно найти в этом репозитории.