Asignamos algunos hilos a grupos y luego los grupos (en teoría) asignan los hilos a las personas para que los manejen.
Hemos creado un informe para mostrar el tiempo (duración) hasta la reasignación.
informe para mostrar el tiempo hasta la reasignación
-- objetivo: encontrar el tiempo desde la asignación al grupo
-- hasta el cierre del tema / desasignación / reasignación a subequipo o individuo
-- Para las publicaciones que no han sido reasignadas, el reloj sigue en marcha,
-- así que usa la marca de tiempo actual en la diferencia de fechas
--
-- NOTA: este informe omite las asignaciones pendientes realizadas antes del inicio del período de fechas
-- [parámetros]
-- fecha :fecha_inicio = 2023-01-01
WITH
-- encontrar publicaciones de asignación de grupo en el período de fechas
grupo_asignacion AS (
SELECT p.topic_id
, p.created_at as fecha_asignacion
, pcf.value as nombre_grupo
, 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 >= :fecha_inicio AND p.created_at < :fecha_inicio::date + INTERVAL '90 day'
ORDER BY topic_id
),
-- encontrar el número de publicación del post de "cambio de asignación" más bajo después de la asignación de grupo
numero_post_cambio_asignacion AS (
SELECT min(p.post_number) as numero_post
, p.topic_id
, ga.post_number as asignacion_inicial -- pasar adelante para usar en la unión posterior
FROM posts p
JOIN grupo_asignacion 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' -- probablemente este no aparecerá aquí, pero por si acaso...
, 'closed.enabled','autoclosed.enabled') -- probablemente redundante con unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- buscar la fecha de cambio para el número de publicación
fecha_cambio_asignacion AS (
SELECT p.created_at as fecha_cambio
, p.topic_id
, acpn.asignacion_inicial -- pasar adelante para usar en la unión posterior
FROM posts p
JOIN numero_post_cambio_asignacion acpn on p.topic_id=acpn.topic_id and p.numero_post=acpn.numero_post
),
-- hacer matemáticas de fechas entre asignación y cambio de asignación
matematicas_fechas AS (
SELECT ga.nombre_grupo
-- para publicaciones todavía asignadas al grupo, usa la marca de tiempo actual como "fecha de reasignación"
, extract(epoch from (coalesce(acd.fecha_cambio, NOW()) - ga.fecha_asignacion)/86400) as dias
FROM grupo_asignacion ga
LEFT JOIN fecha_cambio_asignacion acd on acd.topic_id = ga.topic_id
AND ga.post_number=acd.asignacion_inicial
)
SELECT nombre_grupo as "Equipo"
, count(*) as "Asignaciones"
, round(avg(dias)::numeric,2) as "Promedio de días hasta la reasignación"
, round(max(dias)::numeric,2) as "Máx"
, round(min(dias)::numeric,5) as "Mín"
FROM matematicas_fechas
GROUP BY nombre_grupo
ORDER BY "Promedio de días hasta la reasignación" desc
Esto funcionó bien hasta que los grupos comenzaron a renombrarse (por … razones).
Dado que los registros de asignación de hilos almacenan los nombres de los grupos, en el momento de la asignación, en lugar de los IDs de los grupos, ahora tenemos múltiples filas en el informe para algunos grupos (dependiendo de si el período del informe incluye registros de antes y después del cambio de nombre).
Me gustaría consolidar los duplicados y hacer que este informe sea a prueba de futuro (por lo que simplemente codificar los conjuntos de sinónimos no servirá).
¿Hay algún registro en algún lugar de nombres de grupos antiguos / renombramientos? ¿O… algo más?
¿Lo hace? La tabla original assignment solo tiene IDs, no nombres. ¿Quizás es un efecto secundario de su informe DE que utiliza un CTE basado en el nombre?
@Falco Si mal no recuerdo, la tabla de asignaciones contiene el estado actual. Si quiero datos históricos (y los quiero) necesito mirar post_custom_fields para obtener el valor de asignación de estados de asignación anteriores.
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
Ohhh ya veo, ¡necesitas rastrear todos los cambios históricos en el estado de las asignaciones! Este es, de hecho, un problema difícil, y tuvimos un cliente que encargó una forma de abordar este problema adecuadamente hace un año, así que escribí una especificación completa para ello:
Este es casi un trabajo de dos partes.
Primero, necesitamos almacenar las transiciones de estado de las asignaciones en una tabla (nueva asignación, asignación cambiada (asignado o cambio de estado), eliminación de asignación).
Luego, necesitamos usar esa información para potenciar una nueva vista que sea un panel con gráficos y tablas.
Nuevos Ajustes
habilitar informes de asignación
tipo: booleano
predeterminado: falso
Nueva Interfaz de Usuario
Esta será una nueva página, ya sea en /admin/dashboard/assignments si es extensible o en /admin/plugins/assign.
Allí, los usuarios podrán ver gráficos y exportar datos sobre las asignaciones, filtrando por período de tiempo, usuario/grupos y estado.
Persistencia de la transición de estado de las asignaciones
La tabla assignments ya almacena asignaciones antiguas, pero también necesitaremos rastrear el Estado de la Asignación, por lo que no estoy seguro de si deberíamos extenderla o simplemente idear una tabla más simple dedicada a los estados históricos de las asignaciones. Me inclinaría por esta última opción para no hinchar la tabla que se une en nuestros serializadores activos.
Estimación de esfuerzo
2.5 a 3 semanas para el trabajo completo con interfaz de usuario
1 semana solo para la nueva tabla
Desafortunadamente, el cliente despriorizó este trabajo antes de que pudiéramos abordarlo, pero esto resolvería sus necesidades.
Informe actualizado para quien esté interesado. La modificación es la adición de la consulta group_aliases (y su uso):
-- objetivo: encontrar el tiempo desde la asignación al grupo
-- hasta el cierre del tema / desasignación / reasignación a subequipo o individuo
-- Para las publicaciones que no han sido reasignadas, el reloj sigue funcionando,
-- así que usa la marca de tiempo actual en la diferencia de fechas
--
-- TENGA EN CUENTA que este informe omite las asignaciones pendientes realizadas antes del inicio del rango de fechas
-- [params]
-- date :start_date = 2023-01-01
WITH
group_aliases AS (
SELECT group_id,
-- convertir columnas en filas
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 publicaciones de asignación de grupo en el rango de fechas
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 el número de publicación de la "publicación de cambio de asignación" más baja después de la asignación de grupo
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- pasar adelante para su uso en uniones posteriores
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' -- probablemente este no aparecerá aquí, pero por si acaso...
, 'closed.enabled','autoclosed.enabled') -- probablemente redundante con unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- buscar la fecha de cambio para el número de publicación
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- pasar adelante para su uso en uniones posteriores
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- hacer matemáticas de fechas entre asignación y cambio de asignación
date_math AS (
SELECT ga.group_id as group_id
-- para publicaciones todavía asignadas al grupo, usa la marca de tiempo actual como "fecha de reasignación"
, 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 "Asignaciones"
, round(avg(days)::numeric,2) as "Días promedio hasta reasignación"
, round(max(days)::numeric,2) as "Máx"
, round(min(days)::numeric,5) as "Mín"
FROM date_math
GROUP BY group_id
ORDER BY "Días promedio hasta reasignación" desc
