Мы назначаем некоторые потоки группам, а затем группы (теоретически) распределяют потоки между отдельными исполнителями для обработки.
Мы создали отчёт, показывающий время (длительность) до повторного назначения.
Отчёт о времени до повторного назначения
-- цель: найти время от назначения группе
-- до закрытия темы / снятия назначения / повторного назначения подгруппе или исполнителю
-- Для постов, которые ещё не были переназначены, отсчёт продолжается,
-- поэтому используем текущую временную метку в разнице дат
--
-- ОБРАТИТЕ ВНИМАНИЕ: этот отчёт не включает ещё ожидающие назначения, сделанные до начала диапазона дат
-- [параметры]
-- date :start_date = 2023-01-01
WITH
-- найти посты назначения группе в диапазоне дат
group_assignment AS (
SELECT p.topic_id
, p.created_at as assign_date
, pcf.value as group_name
, post_number
FROM posts p
JOIN post_custom_fields pcf on pcf.post_id = p.id
WHERE p.action_code in ('assigned_group', 'reassigned_group')
AND p.created_at >= :start_date AND p.created_at < :start_date::date + INTERVAL '90 day'
ORDER BY topic_id
),
-- найти номер поста с наименьшим «изменением назначения» после назначения группе
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- передаём дальше для использования в последующем соединении
FROM posts p
JOIN group_assignment ga ON ga.topic_id = p.topic_id AND ga.post_number < p.post_number
WHERE p.action_code in ('reassigned_group', 'reassigned', 'unassigned', 'unassigned_group'
, 'assigned' -- вероятно, этот вариант здесь не появится, но на всякий случай...
, 'closed.enabled','autoclosed.enabled') -- вероятно, избыточно с unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- найти дату изменения для номера поста
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- передаём дальше для использования в последующем соединении
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- выполнить расчёт даты между назначением и изменением назначения
date_math AS (
SELECT ga.group_name
-- для постов, всё ещё назначенных группе, используем текущую временную метку как «дату повторного назначения»
, extract(epoch from (coalesce(acd.change_date, NOW()) - ga.assign_date)/86400) as days
FROM group_assignment ga
LEFT JOIN assignment_change_date acd on acd.topic_id = ga.topic_id
AND ga.post_number=acd.initial_assignment
)
SELECT group_name as "Команда"
, count(*) as "Назначения"
, round(avg(days)::numeric,2) as "Среднее кол-во дней до повторного назначения"
, round(max(days)::numeric,2) as "Максимум"
, round(min(days)::numeric,5) as "Минимум"
FROM date_math
GROUP BY group_name
ORDER BY "Среднее кол-во дней до повторного назначения" desc
Это работало хорошо, пока группы не начали переименовывать себя (по … причинам).
Поскольку записи о назначении потоков хранят названия групп — на момент назначения — а не идентификаторы групп, теперь в отчёте для некоторых групп появляется несколько строк (в зависимости от того, включает ли временной диапазон отчёта записи как до, так и после переименования).
Я хочу как объединить дублирующиеся записи, так и сделать этот отчёт устойчивым к будущим изменениям (простое жёсткое кодирование наборов синонимов здесь не подойдёт).
Существует ли где-то запись старых названий групп / переименований? Или … что-то ещё?
Так ли это? В исходной таблице assignment есть только идентификаторы, а не имена. Возможно, это побочный эффект вашего отчета DE, использующего CTE на основе имени?
@Falco, насколько я помню, таблица назначений хранит текущее состояние. Если мне нужны исторические данные (а они мне нужны), мне нужно обратиться к post_custom_fields, чтобы получить значение назначения для предыдущих состояний.
SELECT p.topic_id
, p.created_at as assign_date
, pcf.value as group_name
, post_number
FROM posts p
JOIN post_custom_fields pcf on pcf.post_id = p.id
WHERE p.action_code in ('assigned_group', 'reassigned_group')
AND p.created_at >= :start_date AND p.created_at < :start_date::date + INTERVAL '90 day'
ORDER BY topic_id
О, теперь я понял: вам нужно отслеживать все исторические изменения статусов назначений! Это действительно сложная задача. Год назад один из клиентов заказал решение этой проблемы, поэтому я подготовил подробную спецификацию:
Это практически задача в два этапа.
Сначала нужно сохранять переходы состояний назначений в таблицу (новое назначение, изменение назначения — смена исполнителя или статуса, удаление назначения).
Затем использовать эти данные для создания нового представления — панели управления с графиками и таблицами.
Новые настройки
enable assign reports
тип: bool
значение по умолчанию: false
Новый интерфейс
Это будет отдельная страница, либо по адресу /admin/dashboard/assignments (если этот путь расширяемый), либо по адресу /admin/plugins/assign.
Пользователи смогут просматривать графики и экспортировать данные о назначениях, применяя фильтры по периоду времени, пользователям/группам и статусу.
Сохранение переходов состояний назначений
Таблица assignments уже хранит старые назначения, но нам также потребуется отслеживать статус назначения. Поэтому я не уверен, стоит ли расширять существующую таблицу или создать отдельную, более простую таблицу специально для хранения исторических состояний назначений. Мне больше нравится второй вариант, чтобы не раздувать таблицу, которая активно используется в наших горячих сериализаторах.
Оценка трудозатрат
2,5–3 недели на полную реализацию с интерфейсом
1 неделя только на создание новой таблицы
К сожалению, клиент отложил эту работу в сторону до того, как мы успели приступить к ней, но это решение полностью закроет ваши потребности.
Обновленный отчет для всех заинтересованных лиц. Изменение заключается в добавлении запроса group_aliases (и его использования):
-- цель: найти время от назначения группы
-- до закрытия темы / отмены назначения / повторного назначения подгруппе или отдельному лицу
-- Для постов, которые не были переназначены, отсчет времени продолжается,
-- поэтому используйте текущую метку времени в разнице дат
--
-- ОБРАТИТЕ ВНИМАНИЕ, что в этом отчете опущены все еще ожидающие назначения, сделанные до начала диапазона дат
-- [параметры]
-- date :start_date = 2023-01-01
WITH
group_aliases AS (
SELECT group_id,
-- преобразуем столбцы в строки
UNNEST (array[prev_value, new_value]) AS "alias"
FROM group_histories
WHERE action = 1 AND subject='name'
UNION
SELECT id as group_id
, name
FROM groups
),
-- найти посты назначения группы в диапазоне дат
group_assignment AS (
SELECT p.topic_id
, p.created_at as assign_date
, ga.group_id as group_id
, post_number
FROM posts p
JOIN post_custom_fields pcf on pcf.post_id = p.id
JOIN group_aliases ga on pcf.value = ga.alias
WHERE p.action_code in ('assigned_group', 'reassigned_group')
AND p.created_at >= :start_date AND p.created_at < :start_date::date + INTERVAL '90 day'
ORDER BY topic_id
),
-- найти номер поста с наименьшим изменением "назначения" после назначения группы
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- передаем дальше для использования в последующем соединении
FROM posts p
JOIN group_assignment ga ON ga.topic_id = p.topic_id AND ga.post_number < p.post_number
WHERE p.action_code in ('reassigned_group', 'reassigned', 'unassigned', 'unassigned_group'
, 'assigned' -- вероятно, этот вариант здесь не появится, но на всякий случай...
, 'closed.enabled','autoclosed.enabled') -- вероятно, избыточно с unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- найти дату изменения для номера поста
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- передаем дальше для использования в последующем соединении
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- выполнить вычисления с датами между назначением и изменением назначения
date_math AS (
SELECT ga.group_id as group_id
-- для постов, все еще назначенных группе, используем текущую метку времени как "дату переназначения"
, extract(epoch from (coalesce(acd.change_date, NOW()) - ga.assign_date)/86400) as days
FROM group_assignment ga
LEFT JOIN assignment_change_date acd on acd.topic_id = ga.topic_id
AND ga.post_number=acd.initial_assignment
)
SELECT date_math.group_id
, count(*) as "Assignments"
, round(avg(days)::numeric,2) as "Avg days to reassignment"
, round(max(days)::numeric,2) as "Max"
, round(min(days)::numeric,5) as "Min"
FROM date_math
GROUP BY group_id
ORDER BY "Avg days to reassignment" desc
