아니요. 솔직히 말해서, 이게 어떻게 작동했는지도 잘 모르겠습니다. 곧 업데이트된 버전을 게시하고 준비가 되었다고 알려드리겠습니다.
수정: @IreneT
원래 쿼리의 수정된 버전입니다. 그 쿼리는 무시하고 이 답변에서 게시한 다른 쿼리들을 참고해 주세요. 쿼리에 대해 질문이 있거나 쿼리에 필요한 매개변수를 추가하는 과정에서 문제가 발생하면 알려 주세요. 오늘 테스트해 본 결과, Data Explorer 쿼리를 저장한 후 매개변수 입력 필드가 쿼리 아래에 표시되려면 페이지를 새로고침해야 하는 것 같습니다. (그것은 제 로컬 개발 사이트의 특이한 현상일 수도 있습니다.)
-- [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(원문 게시글)의 쿼리를 이 게시글의 마지막 두 쿼리로 교체하는 것이 좋을 것 같습니다. 또한 제목을 "그룹 멤버의 첫 번째 응답까지의 시간"과 같은 내용으로 업데이트하는 것이 좋겠습니다.