-- 目標: グループへの割り当てからトピックのクローズ/解除割り当て/サブチームまたは個人への再割り当てまでの時間を検索する
-- まだ再割り当てされていない投稿については、時計はまだ動いているため、日付差に現在のタイムスタンプを使用します。
--
-- 注意: このレポートでは、日付範囲の開始前に行われた保留中の割り当ては省略されます。
-- [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 "チーム"
, 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
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
-- 目標: 割り当てからグループへの時間を検索
-- トピックのクローズ/割り当て解除/サブチームまたは個人への再割り当てまで
-- 再割り当てされていない投稿については、時計はまだ動いているため、
-- 日付差には現在のタイムスタンプを使用します。
--
-- 注意: このレポートでは、日付範囲の開始前に行われた保留中の割り当ては省略されます。
-- [パラメータ]
-- 日付 :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 "割り当て数"
, round(avg(days)::numeric,2) as "再割り当てまでの平均日数"
, round(max(days)::numeric,2) as "最大"
, round(min(days)::numeric,5) as "最小"
FROM date_math
GROUP BY group_id
ORDER BY "再割り当てまでの平均日数" desc
