일부 스레드를 그룹에 배정하고, 그 후 그룹(이론적으로)이 스레드를 개인에게 배정하여 처리하게 합니다.
재배정까지 걸린 시간(소요 시간)을 보여주는 보고서를 만들었습니다.
재배정까지 걸린 시간을 보여주는 보고서
-- 목표: 그룹 배정 시점부터
-- 토픽 종료 / 배정 해제 / 하위 팀 또는 개인으로 재배정까지의 시간을 찾기
-- 아직 재배정되지 않은 게시글의 경우, 시계가 계속 돌아가고 있으므로
-- 날짜 차이에 현재 타임스탬프를 사용
--
-- 이 보고서는 날짜 범위 시작 전에 이루어진 아직 대기 중인 배정 기록을 제외합니다
-- [params]
-- 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 "Team"
, 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_name
ORDER BY "Avg days to reassignment" desc
이것은 그룹이 스스로 이름을 변경하기 시작할 때까지( … 이유인) 잘 작동했습니다.
스레드 배정 기록은 그룹 ID가 아니라 배정 시점의 그룹 이름을 저장하기 때문에, 이제 일부 그룹에 대해 보고서에 여러 행이 표시됩니다(보고서 기간이 이름 변경 전후의 기록을 모두 포함하는지에 따라 다름).
중복된 항목을 통합하고 이 보고서를 향후에도 유지 가능하게 만들고 싶습니다(따라서 동의어 집합을 하드코딩하는 것만으로는 해결되지 않습니다).
어딘가에 이전 그룹 이름 / 이름 변경 내역에 대한 기록이 있나요? 아니면… 다른 방법이 있을까요?
@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
아, 이제 알겠어요. 모든 과거의 할당 상태 변경 사항을 추적해야 하는 거군요! 이건 정말 어려운 문제이고, 1년 전에 한 고객이 이 문제를 제대로 해결하는 방법을 의뢰한 적이 있어서, 저는 이걸 위해 전체 명세(spec)를 작성해 두었습니다:
이건 거의 두 부분으로 나뉩니다.
먼저, 할당 상태 전이를 테이블에 저장해야 합니다(새 할당, 할당 변경(담당자 또는 상태 변경), 할당 삭제).
그런 다음, 그 정보를 활용하여 그래프와 테이블이 있는 대시보드인 새로운 뷰를 구동해야 합니다.
새 설정
할당 보고서 활성화
타입: bool
기본값: false
새 UI
이는 /admin/dashboard/assignments(확장 가능한 경우) 또는 /admin/plugins/assign 아래에 위치할 새로운 페이지가 될 것입니다.
사용자는 여기서 기간, 사용자/그룹 및 상태로 필터링하여 할당에 대한 그래프를 보고 데이터를 내보낼 수 있습니다.
할당 상태 전이 영속화
assignments 테이블은 이미 이전 할당을 저장하고 있지만, 할당 상태도 추적해야 하므로, 기존 테이블을 확장해야 할지 아니면 과거 할당 상태에 전념하는 더 단순한 테이블을 새로 만드는 것이 좋을지 확실하지 않습니다. 실제로 핫 시리얼라이저(hot serializers)에서 조인되는 테이블이 부풀어 오르지 않도록 하기 위해 후자의 방법을 선택하고 싶습니다.
노력 추정
UI를 포함한 전체 작업: 2.5~3주
새 테이블만: 1주
불행히도 고객이 우리가 작업에 착수하기 전에 이 작업의 우선순위를 낮췄지만, 이 방법은 여러분의 필요를 충족시킬 수 있을 것입니다.
관심 있는 분들을 위해 보고서를 업데이트했습니다. 수정 사항은 group_aliases 쿼리(및 그 사용)를 추가한 것입니다:
-- 목표: 그룹 배정 시점부터 토픽 종료 / 배정 해제 / 하위 팀 또는 개인 재배정까지의 시간 찾기
-- 재배정되지 않은 게시글의 경우 시계가 여전히 작동 중이므로,
-- 날짜 차이 계산에 현재 타임스탬프를 사용
--
-- 참고: 이 보고서는 날짜 범위 시작 이전에 생성되었지만 아직 대기 중인 배정 항목을 제외합니다
-- [params]
-- 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
