최초 응답 시간과 유사하게, 선택된 카테고리 목록 내 주제에서 마지막 두 응답 사이의 평균 시간(어떤 기간에 걸쳐든)을 결정하는 방법을 찾고 있습니다.
이것의 사용 사례는 자동 종료 설정이 적절한지 판단하는 것입니다.
데이터 탐색기 플러그인은 설치되어 있지만, 데이터베이스 스키마에 대해 충분히 알지 못합니다(또는 솔직히 말해, 90년대 초 대학에서 배운 데이터베이스/SQL 수업 내용을 거의 기억하지 못합니다) 스스로 보고서를 만들 수 없습니다.
이 데이터를 가져오는 방법에 대한 지침과 제안을 부탁드립니다.
ETA: 접근 방식은 주제와 게시글을 조인(join)하여(카테고리로 필터링할 수 있도록) 최신 업데이트 타임스탬프를 가진 두 개의 게시글을 찾아 그 차이를 구하는 것인 것으로 보입니다.
연습의 목적은 최신 게시글이 두 번째로 최신 게시글에 대한 답변인지 여부에 좌우되지 않으며, 주제가 종료될 만큼 오래되었는지 확인하기 위해(구체적인 관계에 관계없이) 두 개의 가장 최근 답변 사이의 최대 시간 차이를 결정하고, 그런 다음 모든 카테고리(또는 카테고리별, 이는 아마도 더 유용할 것입니다)에 걸쳐 평균을 계산하는 것입니다.
좀 더 생각해보니, 이는 rake topics:auto_close 프로세스에서 사용되는 쿼리의 변형일 수 있는 것 같습니다(차이는 현재 시간을 사용하는 반면, 여기서는 주제 내 두 개의 가장 최근 게시글의 차이를 구한다는 것입니다).
관심 있는 분들을 위해, 이렇게 하면 되는 것 같습니다:
-- [params]
-- null date :start_date
-- null date :end_date
-- null int_list :category_ids
WITH RankedPosts AS (
SELECT
p.topic_id,
p.created_at,
ROW_NUMBER() OVER (PARTITION BY p.topic_id ORDER BY p.post_number DESC) AS rank
FROM
posts p
WHERE
p.created_at BETWEEN :start_date AND :end_date
AND EXISTS (
SELECT 1
FROM topics t
WHERE t.id = p.topic_id
AND t.category_id IN (:category_ids)
)
),
FilteredPosts AS (
SELECT
topic_id,
created_at,
rank
FROM
RankedPosts
WHERE
rank <= 2
),
PostDifferences AS (
SELECT
topic_id,
EXTRACT(days FROM (MAX(created_at) FILTER (WHERE rank = 1) - MAX(created_at) FILTER (WHERE rank = 2)))::numeric(9,2) AS days_difference
FROM
FilteredPosts
GROUP BY
topic_id
)
SELECT
t.category_id,
AVG(pd.days_difference) AS avg_days_difference,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pd.days_difference) AS median_days_difference
FROM
topics t
JOIN
PostDifferences pd ON t.id = pd.topic_id
GROUP BY
t.category_id
ORDER BY
avg_days_difference DESC
이 쿼리는 생성 타임스탬프 사이의 시간 차이의 평균과 중앙값을 반환해야 합니다. 업데이트 타임스탬프는 문제가 되는 것 같았습니다(단순히 평균만 계산했을 때 일부 값이 음수로 나오는 이상한 현상이 발생했기 때문입니다).
쿼리를 작성할 때 AI의 도움을 받았기 때문에 오류가 있을 수 있습니다. 개선 방법이나 오류 수정에 대한 제안이 있다면 환영합니다. 작은 그룹에 대한 검증을 통해 얻은 결과들은 합리적으로 보였으며, 날짜 범위를 넓혔을 때 더 큰 데이터 샘플에서 기대했던 추세와 일치하는 변화가 나타났습니다.