组分配按组名存储。如何处理组重命名？

[ganncamp (G Ann Campbell)]

我们为一些线程分配了组，然后（理论上）由组将线程分配给个人处理。

我们创建了一个报告来显示重新分配的时间（持续时间）。

显示重新分配时间的报告

-- 目标：查找从分配到组到主题关闭/取消分配/重新分配给子团队或个人的时间
-- 对于尚未重新分配的帖子，计时器仍在运行，
-- 因此请在日期差异中使用当前时间戳
--
-- 请注意，此报告省略了日期范围开始之前进行的仍待处理的分配


-- [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

直到小组开始（出于…原因）重命名自己之前，这都运行良好。

由于线程分配记录存储的是分配时的组名，而不是组 ID，因此报告中会出现多个组的行（取决于报告时间跨度是否包含重命名之前和之后记录）。

我想合并这些重复项并使此报告具有未来适应性（因此仅硬编码同义词集是不够的）。

是否有记录显示旧组名/重命名？或者……其他什么？

[Falco]

@ganncamp

线程分配记录存储组名——在分配时——而不是组ID

是吗？原始表 assignment 表只有ID，没有名称。也许这是你的DE报告使用基于名称的CTE的附带结果？

[ganncamp (G Ann Campbell)]

@Falco IIRC，assignment 表保存当前状态。如果我想要历史数据（我确实想要），我需要查看 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

或者我找错地方了？

[Falco]

哦，我现在明白了，你需要跟踪所有的历史分配状态变更！这确实是一个难题，一年前我们有一个客户委托我们来妥善解决这个问题，所以我为此写了一份完整的规范：

这几乎是一项分两部分的工作。

首先，我们需要将分配状态转换存储到一个表中（新建分配、分配变更（受让人或状态变更）、分配删除）。

然后，我们需要利用这些信息来支持一个新的视图，该视图是一个带有图表和表格的仪表板。

新设置

• 启用分配报告
• 类型：布尔值
• 默认值：false

新用户界面

这是一个新页面，如果 /admin/dashboard/assignments 可扩展，则在该路径下，或者在 /admin/plugins/assign 下。

在这里，用户可以查看关于分配的图表并导出数据，按时间段、用户/组和状态进行筛选。

持久化分配的状态转换

assignments 表已经存储了旧的分配信息，但我们也需要跟踪分配状态，所以我不知道我们是应该扩展它，还是应该创建一个更简单的表来专门存储历史分配状态。我倾向于后者，这样我们就不会膨胀实际在热门序列化器中连接的表。

工作量估算

包括用户界面的全部工作需要 2.5 到 3 周。
仅新表需要 1 周。

不幸的是，在我们可以着手这项工作之前，客户已经降低了它的优先级，但这个方案可以满足你的需求。

[ganncamp (G Ann Campbell)]

除了资助一个月的定制开发外，还有其他方法可以查找以前的组名吗？

[JammyDodger]

我认为它们在 group_histories 表中，如果这对您有帮助的话？

[ganncamp (G Ann Campbell)]

感谢 @JammyDodger！我查看过那个表，但根据操作码得出是关于会员资格的。

你让我重新看了一下，我发现 action=1 and subject='name' 正是我想要的。谢谢！

[ganncamp (G Ann Campbell)]

更新后的报告，供感兴趣的人参考。修改之处在于添加了 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