# 小组成员首次响应时间

**URL:** <https://meta.discourse.org/t/time-to-first-response-by-group-members/275118>\
**Category:** Data & reporting\
**Tags:** sql-query\
**Created:** [2018 年2 月 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:** 1

<div class="post-metadata">

作者： ![simon](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/simon/32/339122_2.png) [@simon](https://meta.discourse.org/u/simon)\
发布日期： [2018 年2 月 12 日 13:56 UTC](https://meta.discourse.org/t/time-to-first-response-by-group-members/275118/1 "2018-02-12T13:56:00Z")

</div>

### 在给定时间段内创建的主题首次获得群组响应的时长

返回群组成员对由非该群组成员创建的非“私信”（即“常规”）主题的首次响应时长。查询的 `: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(EPOCH FROM (p.created_at - t.created_at)) / 60 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,
    ROUND(response_time_minutes::numeric, 2) AS 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(EPOCH FROM (p.created_at - t.created_at)) / 60 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,
    ROUND(AVG (response_time_minutes)::numeric, 2) AS average_response_time_minutes,
    COUNT(*) AS num_topics_with_staff_responses
FROM group_response_times
WHERE row_num = 1
GROUP BY category_id

```

---

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