Atribuímos alguns tópicos a grupos e, em seguida, os grupos (teoricamente) atribuem os tópicos a indivíduos para tratamento.
Criamos um relatório para mostrar o tempo (duração) para reatribuição.
relatório para mostrar tempo de reatribuição
-- objetivo: encontrar o tempo desde a atribuição ao grupo
-- até o fechamento do tópico / desatribuição / reatribuição para sub-equipe ou indivíduo
-- Para postagens que não foram reatribuídas, o relógio ainda está correndo,
-- portanto, use o carimbo de data/hora atual na diferença de datas
--
-- OBSERVE que este relatório omite atribuições ainda pendentes feitas antes do início do período de datas
-- [parâmetros]
-- data :data_inicial = 2023-01-01
WITH
-- encontrar postagens de atribuição de grupo no período de datas
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
),
-- encontrar o número da postagem da menor postagem de "mudança de atribuição" após a atribuição do grupo
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- passar adiante para uso na junção posterior
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' -- provavelmente este não aparecerá aqui, mas para garantir...
, 'closed.enabled','autoclosed.enabled') -- provavelmente redundante com unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- procurar a data de mudança para o número da postagem
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- passar adiante para uso na junção posterior
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- fazer matemática de datas entre atribuição e mudança de atribuição
date_math AS (
SELECT ga.group_name
-- para postagens ainda atribuídas ao grupo, use o carimbo de data/hora atual como "data de reatribuição"
, 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 "Equipe"
, count(*) as "Atribuições"
, round(avg(days)::numeric,2) as "Média de dias para reatribuição"
, round(max(days)::numeric,2) as "Máximo"
, round(min(days)::numeric,5) as "Mínimo"
FROM date_math
GROUP BY group_name
ORDER BY "Média de dias para reatribuição" desc
Isso funcionou bem até que os grupos começaram a se renomear (por … razões.)
Como os registros de atribuição de tópicos armazenam nomes de grupos - no momento da atribuição - em vez de IDs de grupo, agora temos várias linhas no relatório para alguns grupos (dependendo se o período do relatório inclui registros de antes e depois da renomeação).
Gostaria de consolidar os múltiplos e tornar este relatório à prova de futuro (portanto, apenas codificar os conjuntos de sinônimos não servirá).
Existe algum registro em algum lugar de nomes de grupos antigos / renomeações? Ou… alguma outra coisa?
@Falco Se não me falha a memória, a tabela de atribuição contém o estado atual. Se eu quiser dados históricos (e eu quero), preciso olhar em post_custom_fields para obter o valor da atribuição de estados anteriores de atribuição.
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
Ah, entendi agora, você precisa rastrear todas as mudanças históricas no status de atribuição! Este é realmente um problema difícil, e tivemos um cliente que encomendou uma maneira de resolver esse problema adequadamente um ano atrás, então escrevi uma especificação completa para isso:
Este é quase um trabalho de duas partes.
Primeiro, precisamos armazenar as transições de estado de atribuição em uma tabela (nova atribuição, atribuição alterada (responsável ou mudança de status), exclusão de atribuição).
Em seguida, precisamos usar essas informações para alimentar uma nova visualização que é um painel com gráficos e tabelas.
Novas Configurações
habilitar relatórios de atribuição
tipo: bool
padrão: false
Nova Interface do Usuário
Esta será uma nova página, seja em /admin/dashboard/assignments se for extensível ou em /admin/plugins/assign.
Lá, os usuários poderão visualizar gráficos e exportar dados sobre atribuições, filtrando por período, usuário/grupos e status.
Persistindo a transição de estado das atribuições
A tabela assignments já armazena atribuições antigas, mas também precisaremos rastrear o Status da Atribuição, então não tenho certeza se devemos estender essa tabela ou simplesmente criar uma tabela mais simples dedicada aos estados históricos de atribuição. Eu tenderia a seguir a segunda opção para não inchar a tabela que é realmente unida em nossos serializadores ativos.
Estimativa de Esforço
2,5 a 3 semanas para o trabalho completo com a interface do usuário
1 semana apenas para a nova tabela
Infelizmente, o cliente despriorizou este trabalho antes que pudéssemos realizá-lo, mas isso resolveria suas necessidades.
Relatório atualizado para quem estiver interessado. A modificação é a adição da consulta group_aliases (e seu uso):
-- objetivo: encontrar o tempo desde a atribuição ao grupo
-- até o fechamento do tópico / desatribuição / reatribuição para sub-equipe ou indivíduo
-- Para postagens que não foram reatribuídas, o relógio ainda está correndo,
-- portanto, use o timestamp atual na diferença de datas
--
-- OBSERVE que este relatório omite atribuições ainda pendentes feitas antes do início do intervalo de datas
-- [params]
-- date :start_date = 2023-01-01
WITH
group_aliases AS (
SELECT group_id,
-- transformar colunas em linhas
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
),
-- encontrar postagens de atribuição de grupo no intervalo de datas
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
),
-- encontrar o número da postagem de "mudança de atribuição" mais baixo após a atribuição do grupo
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- passar para frente para uso na junção posterior
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' -- provavelmente este não aparecerá aqui, mas para garantir...
, 'closed.enabled','autoclosed.enabled') -- provavelmente redundante com unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- procurar a data de alteração para o número da postagem
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- passar para frente para uso na junção posterior
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- fazer matemática de datas entre atribuição e mudança de atribuição
date_math AS (
SELECT ga.group_id as group_id
-- para postagens ainda atribuídas ao grupo, use o timestamp atual como "data de reatribuição"
, 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 "Atribuições"
, round(avg(days)::numeric,2) as "Média de dias para reatribuição"
, round(max(days)::numeric,2) as "Máximo"
, round(min(days)::numeric,5) as "Mínimo"
FROM date_math
GROUP BY group_id
ORDER BY "Média de dias para reatribuição" desc
