它不再可用了。老实说，我不知道它以前是怎么起作用的。我很快会发布一个更新版本，并通知你它已准备就绪。
编辑：@IreneT
这是原始查询的修复版本。我建议忽略那个查询，而是查看我在这个回复中发布的其他查询。如果你对这些查询有任何疑问，或者在向查询添加必需的参数时遇到任何问题，请告诉我。根据今天的测试，我发现保存数据探索器查询后必须刷新页面，才能在查询下方显示参数输入字段。（这可能只是我本地开发站点的一个怪癖。）
-- [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
更改之处在于：
--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 形式的日期。
-- [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”，它将返回非员工用户创建的常规主题的平均员工首次响应时间。
-- [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 中的查询。另外，也许可以把标题更新为类似“组成员的首次响应时间”的内容。