대시보드 보고서 - 주제 보기 통계

커뮤니티 구축 데이터 및 보고
, ,
1

이것은 주제 보기 통계 대시보드 보고서의 SQL 버전입니다.

이 보고서는 주어진 날짜 범위 내에서 익명 및 로그인된 사용자의 주제 보기 통계를 수집하고 집계합니다. 또한 카테고리별로 필터링할 수 있으며, 선택적으로 하위 카테고리를 포함할 수도 있습니다.

:discourse: 이 쿼리와 topic_view_stats 데이터베이스 테이블은 2024년 5월에 Discourse에 추가되었으며, 이 시점 이전의 데이터는 표시되지 않습니다.

-- [params]
-- date :start_date
-- date :end_date
-- int :category_id = 0
-- boolean :include_subcategories = false

WITH category_filter AS (
  SELECT
    id
  FROM categories
  WHERE
    (:category_id = 0 OR id = :category_id)
    OR (:include_subcategories AND parent_category_id = :category_id)
)

SELECT
  topic_view_stats.topic_id,
  topics.title AS topic_title,
  SUM(topic_view_stats.anonymous_views) AS total_anonymous_views,
  SUM(topic_view_stats.logged_in_views) AS total_logged_in_views,
  SUM(topic_view_stats.anonymous_views + topic_view_stats.logged_in_views) AS total_views
FROM topic_view_stats
INNER JOIN topics ON topics.id = topic_view_stats.topic_id
WHERE
  topic_view_stats.viewed_at BETWEEN :start_date AND :end_date
  AND (
    :category_id = 0
    OR topics.category_id IN (SELECT id FROM category_filter)
  )
GROUP BY topic_view_stats.topic_id, topics.title
ORDER BY total_views DESC
LIMIT 100

SQL 쿼리 설명

매개변수

  • :start_date (date): 분석 범위의 시작 날짜 (포함).
  • :end_date (date): 분석 범위의 종료 날짜 (포함).
  • :category_id (integer, 선택 사항): 카테고리로 주제를 필터링합니다. 사용 방법:
    • 모든 카테고리를 포함하려면 0을 사용합니다.
    • 특정 카테고리로 필터링하려면 해당 카테고리의 ID를 사용합니다.
  • :include_subcategories (boolean, 선택 사항): 지정된 카테고리의 하위 카테고리를 포함할지 여부를 선택합니다.
    • false: 선택된 카테고리만 포함합니다.
    • true: 하위 카테고리도 포함합니다.

카테고리 필터링
category_filter 공통 테이블 표현식(CTE)은 :category_id 매개변수에 따라 선택된 카테고리 ID를 가져옵니다. 매개변수가 0이면 모든 카테고리가 포함됩니다. 하위 카테고리를 포함해야 하는 경우, parent_category_id가 지정된 :category_id와 일치하는 카테고리를 찾습니다.

주제 통계 가져오기
주제 보기 통계는 topic_view_stats 테이블에서 집계되며, 주제 제목을 가져오기 위해 topics 테이블과 조인됩니다.

날짜 및 카테고리 필터 적용
쿼리는 topic_view_stats 데이터를 필터링합니다:

  • 제공된 날짜 범위(:start_date와 :end_date)에 따라.
  • category_filter의 ID와 일치하는 카테고리로.

총 조회수 계산
쿼리는 다음을 합산합니다:

  • anonymous_views: 익명 사용자의 조회수.
  • logged_in_views: 로그인된 사용자의 조회수.
  • 두 값의 합으로 계산된 총 조회수.

랭킹 및 제한:
쿼리는 주제를 total_views 기준으로 랭킹하고 결과를 상위 100개 주제로 제한합니다.

쿼리 결과
쿼리는 다음 컬럼을 반환합니다:

컬럼 이름 설명
topic_id 주제의 고유 식별자.
topic_title 주제의 제목.
total_anonymous_views 익명 사용자의 총 조회수.
total_logged_in_views 로그인된 사용자의 총 조회수.
total_views 총 조회수 (익명 + 로그인).

결과 예시

topic_id topic_title total_anonymous_views total_logged_in_views total_views
12345 “예제 주제 1” 500 300 800
67890 “예제 주제 2” 450 350 800
3개의 좋아요
Admin dashboard report reference guide