Nous attribuons certains fils de discussion à des groupes, puis les groupes (théoriquement) attribuent les fils de discussion à des individus pour traitement.
Nous avons créé un rapport pour afficher le temps (durée) de réaffectation.
rapport pour afficher le temps de réaffectation
-- objectif : trouver le temps entre l'attribution à un groupe et la clôture du sujet / désaffectation / réaffectation à une sous-équipe ou à un individu
-- Pour les publications qui n'ont pas été réaffectées, l'horloge tourne toujours,
-- donc utilisez l'horodatage actuel dans la différence de dates
--
-- NOTEz que ce rapport omet les affectations toujours en attente effectuées avant le début de la période
--
-- [params]
-- date :start_date = 2023-01-01
WITH
-- trouver les publications d'attribution de groupe dans la période
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
),
-- trouver le numéro de publication du plus bas "changement d'attribution" après l'attribution du groupe
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- transmettre pour utilisation dans la jointure ultérieure
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' -- probablement que celui-ci n'apparaîtra pas ici, mais par sécurité...
, 'closed.enabled','autoclosed.enabled') -- probablement redondant avec unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- rechercher la date de changement pour le numéro de publication
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- transmettre pour utilisation dans la jointure ultérieure
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- faire le calcul de dates entre l'attribution et le changement d'attribution
date_math AS (
SELECT ga.group_name
-- pour les publications toujours attribuées au groupe, utiliser l'horodatage actuel comme "date de réaffectation"
, 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 "Équipe"
, count(*) as "Affectations"
, round(avg(days)::numeric,2) as "Moyenne jours avant réaffectation"
, round(max(days)::numeric,2) as "Max"
, round(min(days)::numeric,5) as "Min"
FROM date_math
GROUP BY group_name
ORDER BY "Moyenne jours avant réaffectation" desc
Cela a bien fonctionné jusqu’à ce que les groupes commencent à se renommer (pour des raisons…).
Étant donné que les enregistrements d’attribution de fils de discussion stockent les noms de groupe - au moment de l’attribution - plutôt que les identifiants de groupe, nous avons maintenant plusieurs lignes dans le rapport pour certains groupes (selon que la période du rapport inclut des enregistrements d’avant et d’après le renommage).
Je voudrais à la fois consolider les doublons et rendre ce rapport pérenne (donc coder en dur les ensembles de synonymes ne suffira pas).
Existe-t-il un enregistrement quelque part des anciens noms de groupe / renommages ? Ou… autre chose ?
Est-ce que c’est le cas ? La table d’origine assignment ne contient que des identifiants, pas des noms. Est-ce peut-être une conséquence de votre rapport DE utilisant une CTE basée sur le nom ?
@Falco Si mes souvenirs sont bons, la table d’affectation contient l’état actuel. Si je veux des données historiques (et c’est le cas), je dois regarder dans post_custom_fields pour obtenir la valeur d’affectation des états d’affectation précédents.
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 je vois maintenant, vous devez suivre toutes les modifications historiques de l’état des affectations ! C’est en effet un problème difficile, et nous avons eu un client qui a commandé une solution pour résoudre ce problème il y a un an, j’ai donc rédigé une spécification complète à ce sujet :
C’est presque un travail en deux parties.
Premièrement, nous devons stocker les transitions d’état des affectations dans une table (nouvelle affectation, affectation modifiée (changement d’assigné ou de statut), suppression d’affectation).
Ensuite, nous devons utiliser ces informations pour alimenter une nouvelle vue qui est un tableau de bord avec des graphiques et des tables.
Nouveaux paramètres
activer les rapports d’affectation
type : bool
défaut : false
Nouvelle interface utilisateur
Ce sera une nouvelle page, soit sous /admin/dashboard/assignments si cela est extensible, soit sous /admin/plugins/assign.
Là, les utilisateurs pourront consulter des graphiques et exporter des données sur les affectations, en filtrant par période, utilisateur/groupes et statut.
Persistance de la transition d’état des affectations
La table assignments stocke déjà les anciennes affectations, mais nous devrons également suivre le Statut de l’affectation, donc je ne suis pas sûr si nous devrions l’étendre ou simplement proposer une table plus simple dédiée aux états historiques des affectations. Je serais tenté de choisir cette dernière option afin de ne pas alourdir la table qui est effectivement jointe à nos sérialiseurs “chauds”.
Estimation de l’effort
2,5 à 3 semaines pour un travail complet avec interface utilisateur
1 semaine pour la nouvelle table uniquement
Malheureusement, le client a dépriorisé ce travail avant que nous puissions nous y atteler, mais cela résoudrait vos besoins.
Rapport mis à jour pour toute personne intéressée. La modification est l’ajout de la requête group_aliases (et son utilisation) :
-- objectif : trouver le temps entre l'affectation au groupe et la clôture du sujet / désaffectation / réaffectation à une sous-équipe ou à un individu
-- Pour les publications qui n'ont pas été réaffectées, le temps continue de s'écouler,
-- donc utilisez l'horodatage actuel dans la différence de dates
--
-- NOTEz que ce rapport omet les affectations toujours en attente effectuées avant le début de la période spécifiée
-- [params]
-- date :start_date = 2023-01-01
WITH
group_aliases AS (
SELECT group_id,
-- transformer les colonnes en lignes
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
),
-- trouver les publications d'affectation de groupe dans la période spécifiée
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
),
-- trouver le numéro de publication de la plus basse publication de "changement d'affectation" après l'affectation au groupe
assignment_change_post_number AS (
SELECT min(p.post_number) as post_number
, p.topic_id
, ga.post_number as initial_assignment -- transmettre pour utilisation dans la jointure ultérieure
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' -- probablement celui-ci n'apparaîtra pas ici, mais par sécurité...
, 'closed.enabled','autoclosed.enabled') -- probablement redondant avec unassign*...
GROUP BY p.topic_id, ga.post_number
),
-- rechercher la date de changement pour le numéro de publication
assignment_change_date AS (
SELECT p.created_at as change_date
, p.topic_id
, acpn.initial_assignment -- transmettre pour utilisation dans la jointure ultérieure
FROM posts p
JOIN assignment_change_post_number acpn on p.topic_id=acpn.topic_id and p.post_number=acpn.post_number
),
-- effectuer des calculs de dates entre l'affectation et le changement d'affectation
date_math AS (
SELECT ga.group_id as group_id
-- pour les publications toujours affectées au groupe, utiliser l'horodatage actuel comme "date de réaffectation"
, 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 "Affectations"
, round(avg(days)::numeric,2) as "Jours moyens avant réaffectation"
, round(max(days)::numeric,2) as "Max"
, round(min(days)::numeric,5) as "Min"
FROM date_math
GROUP BY group_id
ORDER BY "Jours moyens avant réaffectation" desc
