이것은 첫 응답 시간(Time to First Response) 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 지정된 날짜 범위 내에서 토픽에 대한 평균 응답 시간에 대한 통찰을 제공합니다. 이 리포트는 토픽 작성자가 아닌 누군가가 첫 번째 답변을 게시하는 데 걸린 시간을 계산합니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-12-31
-- null category_id :category_id
-- boolean :include_subcategories = false
-- null user_id :user_ids
-- 날짜별 첫 응답 시간
SELECT
t.created_at AS "date",
CAST(AVG(t.hours)::numeric(10,2) AS float) AS "response_time_hours"
FROM (
SELECT
t.id,
t.created_at::date AS created_at,
EXTRACT(EPOCH FROM MIN(p.created_at) - t.created_at)::float / 3600.0 AS "hours"
FROM topics t
JOIN posts p ON p.topic_id = t.id
WHERE
t.created_at >= :start_date
AND t.created_at < :end_date
AND t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND p.deleted_at IS NULL
AND p.post_number > 1
AND p.user_id != t.user_id
AND p.post_type = 1 -- 일반 게시글 유형
AND EXTRACT(EPOCH FROM p.created_at - t.created_at) > 0
AND (:category_id IS NULL OR
CASE WHEN :include_subcategories THEN
t.category_id IN (
WITH RECURSIVE subcategories AS (
SELECT id FROM categories WHERE id = :category_id
UNION
SELECT c.id FROM categories c
JOIN subcategories sc ON sc.id = c.parent_category_id
)
SELECT id FROM subcategories
)
ELSE
t.category_id = :category_id
END
)
AND (:user_ids IS NULL OR p.user_id IN (SELECT unnest(string_to_array(:user_ids, ','))::int))
GROUP BY t.id
) t
GROUP BY t.created_at
ORDER BY t.created_at
SQL 쿼리 설명
이 리포트는 다음을 통해 토픽이 첫 번째 의미 있는 응답을 받는 속도를 측정합니다:
- 응답 시간 계산: 각 토픽에 대해 다음 조건을 충족하는 첫 번째 게시글을 찾습니다:
- 원래 토픽 작성자가 아닌 사람이 작성한 것
- 게시글 번호가 1보다 큰 것 (최초 게시글이 아님)
- 일반 게시글인 것 (post_type = 1)
- 토픽 생성 후에 작성된 것 (양의 시간 차이)
- 삭제되지 않은 것
- 날짜별 그룹화: 토픽이 생성된 날짜별로 이러한 응답 시간을 집계합니다
- 결과 표시:
- “date”: 토픽이 생성된 날짜
- “hours”: 해당 날짜에 생성된 토픽에 대한 첫 응답까지 걸린 평균 시간(시간 단위)
- 제외 항목:
- 개인 메시지는 제외됩니다
- 삭제된 토픽과 게시글은 제외됩니다
- 자기 자신의 답변은 제외됩니다
매개변수
:start_date(date) - 형식:YYYY-MM-DD- 리포트 기간의 시작 날짜
- 이 날짜 이후에 생성된 토픽만 포함됩니다
:end_date(date) - 형식:YYYY-MM-DD- 리포트 기간의 종료 날짜
- 이 날짜 이전에 생성된 토픽만 포함됩니다
:category_id(category_id, nullable)- 제공된 경우, 결과를 특정 카테고리로 필터링합니다
- null인 경우, 모든 카테고리의 토픽을 포함합니다
:include_subcategories(boolean) - 기본값: false- true이고 category_id가 제공된 경우, 모든 하위 카테고리의 토픽을 포함합니다
- false인 경우, 정확히 지정된 카테고리의 토픽만 포함합니다
:user_ids(user_id, nullable)- 제공된 경우, 특정 사용자의 응답만 포함합니다
- null인 경우, 모든 사용자의 응답을 포함합니다
- 쉼표로 구분된 여러 사용자 ID를 받을 수 있습니다
예시 결과
| date | response_time_hours |
|---|---|
| 2023-11-12 | 29.87 |
| 2023-11-13 | 81.52 |
| 2023-11-14 | 5.17 |
| 2023-11-15 | 6.51 |
| 2023-11-16 | 7.75 |
| … | … |