# 小组成员首次响应时间

**URL:** <https://meta.discourse.org/t/time-to-first-response-by-group-members/275118>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2018年二月12日 13:56 UTC](https://meta.discourse.org/t/time-to-first-response-by-group-members/275118 "2018-02-12T13:56:00Z")\
**Posts on this page:** 1\
**Showing post:** 3

<div class="post-metadata">

**Author:** ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)\
**Post date:** [2023年九月27日 18:50 UTC](https://meta.discourse.org/t/time-to-first-response-by-group-members/275118/3 "2023-09-27T18:50:10Z")

</div>

> [@IreneT](#):
>
> Is this code still viable or is there an updated version?

它不再可用了。老实说，我不知道它以前是怎么起作用的。我很快会发布一个更新版本，并通知你它已准备就绪。

编辑：@IreneT  
这是原始查询的修复版本。我建议忽略那个查询，而是查看我在这个回复中发布的其他查询。如果你对这些查询有任何疑问，或者在向查询添加必需的参数时遇到任何问题，请告诉我。根据今天的测试，我发现保存数据探索器查询后必须刷新页面，才能在查询下方显示参数输入字段。（这可能只是我本地开发站点的一个怪癖。）

```sql
-- [params]
-- int :months_ago = 1

WITH query_period AS (
SELECT
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' as period_start,
date_trunc('month', CURRENT_DATE) - INTERVAL ':months_ago months' + INTERVAL '1 month' - INTERVAL '1 second' as period_end
),
staff_responses AS (
SELECT
DISTINCT ON (p.topic_id)
p.topic_id,
p.created_at,
t.category_id,
EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time
FROM posts p
JOIN topics t
ON t.id = p.topic_id
AND t.category_id = ANY ('{46,25,43,40,44,35,22,7,20,17,6,12}'::int[])
JOIN users u
ON u.id = p.user_id
WHERE p.post_number > 1
AND u.admin = 't' OR u.moderator = 't'
ORDER BY p.topic_id, p.created_at
),
user_topics AS (
SELECT
t.id
FROM topics t
JOIN users u
ON u.id = t.user_id
WHERE u.admin = 'f' AND u.moderator = 'f'
)

SELECT
sr.category_id,
AVG(sr.response_time) AS "Average First Response Time",
COUNT(1) AS "Topics Responded to"
FROM staff_responses sr
JOIN query_period qp
ON sr.created_at >= qp.period_start
AND sr.created_at <= qp.period_end
JOIN user_topics t
ON t.id = sr.topic_id
GROUP BY sr.category_id

```

更改之处在于：

```sql
--DATE_TRUNC('minute', p.created_at - t.created_at) AS response_time
EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time

```

我很惊讶我会写出旧的查询而不进行测试。无论如何，更新后的版本现在可以按预期工作了。

据我回忆，这个查询是为特定站点编写的，并不打算发布在 Meta 上。以下是另外两个用于获取有关员工响应时间信息的有用查询：

### 特定时间段内创建主题的首次组响应时间

返回指定组成员对非该组成员用户创建的“常规”（非个人消息）主题的首次响应时间。查询的 `:group_name` 参数默认为“staff”。使用此值，它将给出员工的首次响应时间。你可以更改此参数的值以获取不同组的响应时间。例如“customer\_support”。

请注意，日期在技术上应以 `yyyy-mm-dd` 的形式提供，但查询也接受 `dd-mm-yyyy` 形式的日期。

```sql
-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    topic_id,
    staff_user_id,
    response_time_minutes
FROM group_response_times
WHERE row_num = 1
ORDER BY category_id, response_time_minutes

```

### 每个类别的平均首次组响应时间：

使用与上一个查询相同的逻辑，但返回指定组成员对非该组成员用户在 `:start_date` 和 `:end_date` 参数设定的时间段内创建的主题的首次响应的\_平均\_时间。与上一个查询一样，如果 `:group_name` 参数保留其默认值“staff”，它将返回非员工用户创建的常规主题的平均员工首次响应时间。

```sql
-- [params]
-- date :start_date
-- date :end_date
-- string :group_name = staff

WITH group_response_times AS (
    SELECT
        t.category_id,
        t.id AS topic_id,
        EXTRACT(MINUTE FROM (p.created_at - t.created_at)) AS response_time_minutes,
        p.user_id AS staff_user_id,
        ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY p.created_at) AS row_num
    FROM posts p
    JOIN topics t ON t.id = p.topic_id
    WHERE t.user_id NOT IN (SELECT user_id
                                FROM group_users gu JOIN groups g ON g.id = gu.group_id
                                WHERE gu.user_id > 0 AND g.name = :group_name)
        AND p.user_id IN (SELECT user_id
                              FROM group_users gu JOIN groups g ON g.id = gu.group_id
                              WHERE gu.user_id > 0 AND g.name = :group_name)
        AND t.archetype = 'regular'
        AND t.deleted_at IS NULL
        AND p.post_type = 1
        AND p.deleted_at IS NULL
        AND t.created_at BETWEEN :start_date AND :end_date
)

SELECT
    category_id,
    AVG (response_time_minutes) AS average_response_time_minutes,
    COUNT(*) AS num_topics_with_staff_responses
FROM group_response_times
WHERE row_num = 1
GROUP BY category_id

```

@JammyDodger，也许可以用这个帖子中最后两个查询替换 OP 中的查询。另外，也许可以把标题更新为类似“组成员的首次响应时间”的内容。

---

_[View the full topic](https://meta.discourse.org/t/time-to-first-response-by-group-members/275118)._