대시보드 보고서는 지정된 날짜 범위 내에서 생성되었지만 다른 사용자로부터 답변을 받지 못한 주제의 수를 계산하도록 설계되었습니다. 이 보고서는 특정 범위로 필터링할 수 있으며, 하위 범위를 선택적으로 포함할 수도 있습니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
-- null int :category_id
-- boolean :include_subcategories = false
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date ASC
매개변수
날짜 매개변수:
쿼리는 :start_date와 :end_date라는 두 개의 매개변수를 받아들이며, 이는 보고서의 날짜 범위를 정의합니다. 두 날짜 매개변수 모두 YYYY-MM-DD 형식의 날짜를 받아들입니다.
범주 매개변수:
:category_id: 특정 범주의 ID로 설정하여 해당 범주 내 게시글로 분석을 좁힐 수 있는 정수 매개변수입니다. null로 설정하거나 제공되지 않으면 모든 범주의 주제가 고려됩니다.
:include_subcategories: 지정된 :category_id의 하위 범주에 있는 게시글을 포함할지 여부를 제어하는 불리언 매개변수입니다. true로 설정하면 보고서는 지정된 범주와 그 하위 범주의 게시글 링크를 모두 포함합니다. false인 경우, 지정된 범주만 고려됩니다.
SQL 쿼리 설명
쿼리는 no_response_total이라는 이름의 공통 테이블 표현식(CTE)으로 시작합니다. 이 CTE는 다음 단계를 수행합니다:
주제 선택: topics 테이블에서 모든 주제(t.id)와 그 생성 날짜(t.created_at)를 선택합니다.
게시글과의 Left Join: 각 주제에 대한 첫 번째 답변을 찾기 위해 posts 테이블과 left join을 수행합니다. 조인 조건은 게시글이 주제 생성자가 아닌 것(p.user_id != t.user_id), 게시글이 삭제되지 않은 것(p.deleted_at IS NULL), 그리고 게시글의 유형이 1(일반적으로 표준 답변을 나타냄)인 것을 보장합니다.
주제 필터링: 쿼리는 개인 메시지(t.archetype <> 'private_message') 및 삭제된 주제(t.deleted_at ISNULL)를 필터링하여 제외합니다.
범주 필터링: :category_id가 제공되면, 쿼리는 지정된 범주에 있는 주제만 포함하도록 필터링합니다. :include_subcategories가 true이면, 지정된 범주의 하위 범주에 있는 주제도 포함합니다.
그룹핑 및 최소 게시글 번호: 주제는 ID별로 그룹화되고, 첫 번째 답변을 찾기 위해 최소 게시글 번호(MIN(p.post_number))가 계산됩니다.
답변 없음 필터링: 서브쿼리 tt는 게시글 번호가 2 이상인 첫 번째 답변을 가진 주제를 필터링하여 제외하고, 답변이 없는 주제(tt.first_reply IS NULL) 또는 원본 게시글만 있는 주제(tt.first_reply < 2)만 남깁니다.
no_response_total CTE가 정의된 후, 메인 쿼리는 다음을 수행합니다:
날짜 범위 필터링: 제공된 시작 날짜와 종료 날짜(:start_date와 :end_date)에 따라 CTE의 주제를 필터링합니다.
답변 없는 주제 카운트: 지정된 범위 내 각 날짜별로 답변이 없는 주제의 수를 계산합니다.
날짜별 그룹핑: 결과는 주제 생성 날짜(DATE(nrt.created_at))별로 그룹화됩니다.
네, 파라미터를 사용하지 않고 현재 날짜로부터 7일 전까지를 조회하도록 업데이트된 쿼리를 제공합니다.
이 버전에는 카테고리 또는 하위 카테고리에 따른 필터링이 포함되어 있지 않습니다.
WITH no_response_total AS (
SELECT *
FROM (
SELECT
t.id,
t.created_at,
MIN(p.post_number) AS first_reply
FROM
topics t
LEFT JOIN
posts p
ON
p.topic_id = t.id
AND p.user_id != t.user_id
AND p.deleted_at IS NULL
AND p.post_type = 1
WHERE
t.archetype <> 'private_message'
AND t.deleted_at IS NULL
AND (
t.category_id = :category_id
OR t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
GROUP BY
t.id
) tt
WHERE
tt.first_reply IS NULL
OR tt.first_reply < 2
)
SELECT
DATE(nrt.created_at) AS date,
COUNT(nrt.id) AS topics_without_response
FROM
no_response_total nrt
WHERE
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND CURRENT_DATE
GROUP BY
date
ORDER BY
date ASC
쿼리의 조회 범위를 조정하려면 쿼리의 다음 줄만 변경하면 됩니다:
nrt.created_at::date BETWEEN CURRENT_DATE - INTERVAL '7 days' AND
반응이 없는 토픽을 연도, 월 또는 다른 시간 간격별로 집계할 수 있도록 쿼리를 수정하려면, date_trunc 함수에 원하는 간격을 지정하는 매개변수를 추가하면 됩니다.
예를 들어:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = day -- Options: day, week, month, year
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc(:interval, nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period
ORDER BY period ASC
매개변수를 제거하고 싶다면, 다음과 같은 쿼리를 사용할 수도 있습니다:
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
date_trunc('year', nrt.created_at)::date AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN '2024-01-01' AND '2025-01-01'
GROUP BY period
ORDER BY period ASC
PostgreSQL에서 날짜 형식을 10-22에서 Oct-22로 변경하려면 TO_CHAR 함수를 사용할 수 있습니다. 이 함수를 사용하면 다양한 방식으로 날짜를 포맷팅할 수 있으며, 예를 들어 다음과 같습니다:
SELECT
TO_CHAR(TO_DATE('10-22', 'MM-YY'), 'Mon-YY') AS formatted_date
이 SQL 문에서:
TO_DATE('10-22', 'MM-YY')는 MM-YY 형식을 사용하여 문자열 10-22를 날짜 타입으로 변환합니다.
TO_CHAR(..., 'Mon-YY')는 이 날짜를 축약된 월 이름 뒤에 연도가 오는 형태로 포맷팅하여 Oct-22를 생성합니다.
위에서 공유된 interval 파라미터가 포함된 Topics with No Response 쿼리의 버전 기반으로 한 또 다른 예시는 다음과 같습니다:
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01
-- null int :category_id
-- boolean :include_subcategories = false
-- text :interval = month
WITH no_response_total AS (
SELECT *
FROM (
SELECT t.id, t.created_at, MIN(p.post_number) first_reply
FROM topics t
LEFT JOIN posts p ON p.topic_id = t.id AND p.user_id != t.user_id AND p.deleted_at IS NULL AND p.post_type = 1
WHERE t.archetype <> 'private_message'
AND t.deleted_at ISNULL
AND (
:category_id IS NULL
OR t.category_id = :category_id
OR (
:include_subcategories
AND t.category_id IN (
SELECT id FROM categories WHERE parent_category_id = :category_id
)
)
)
GROUP BY t.id
) tt
WHERE tt.first_reply IS NULL OR tt.first_reply < 2
)
SELECT
TO_CHAR(date_trunc(:interval, nrt.created_at)::date, 'Mon-YY') AS period,
COUNT(nrt.id) AS topics_without_response
FROM no_response_total nrt
WHERE nrt.created_at::date BETWEEN :start_date AND :end_date
GROUP BY period, date_trunc(:interval, nrt.created_at)::date
ORDER BY date_trunc(:interval, nrt.created_at)::date ASC
