대시보드 보고서 - 게시물

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

이것은 게시물에 대한 대시보드 리포트의 SQL 버전입니다.

이 리포트는 지정된 날짜 범위 내에서 생성된 게시물의 일별 수를 제공합니다. 이는 사적인 메시지 및 기타 특수 아키타입의 게시물을 제외하고 일반 토픽의 활동을 추적하도록 설계되었습니다.

--[params]
-- date :start_date
-- date :end_date

SELECT 
    p.created_at::date AS "Day",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at ISNULL
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at ISNULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
GROUP BY p.created_at::date
ORDER BY 1

SQL 쿼리 설명

  • 파라미터:
    • 쿼리는 리포트의 날짜 범위를 정의하는 두 개의 파라미터인 :start_date와 :end_date를 받습니다. 두 날짜 파라미터 모두 YYYY-MM-DD 형식의 날짜를 받아들입니다.

SQL 쿼리는 다음 작업을 수행합니다:

  • 데이터 선택:
    • 각 게시물이 생성된 날짜(created_at::date)를 선택하고 시간 요소를 무시하기 위해 날짜 형식으로 변환합니다.
    • 또한 각 날짜에 생성된 게시물의 수(COUNT(p.id))를 계산합니다.
  • 조인:
    • 쿼리는 INNER JOIN을 사용하여 posts 테이블을 topics 테이블과 조인합니다. 이 조인은 기존 토픽과 연결된 게시물만 고려되도록 보장합니다.
    • 삭제된 토픽(t.deleted_at ISNULL)은 필터링하여 제외합니다.
  • 필터:
    • 지정된 날짜 범위(p.created_at::date BETWEEN :start_date AND :end_date) 내의 게시물만 포함하도록 필터링합니다.
    • 삭제된 게시물(p.deleted_at ISNULL)을 제외합니다.
    • 결과를 일반 토픽(t.archetype = 'regular')의 게시물로 제한합니다.
    • p.post_type = 1인 게시물만 고려하며, 관리자 작업, 속삭임(whispers), small_action 게시물을 제외합니다.
  • 그룹핑 및 정렬:
    • 결과는 게시물 생성 날짜(GROUP BY p.created_at::date) 기준으로 그룹화됩니다.
    • 최종 출력은 날짜의 오름차순(ORDER BY 1)으로 정렬되며, 여기서 1은 SELECT 문장의 첫 번째 열인 날짜를 가리킵니다.

결과 예시

날짜 개수
2023-11-12 25
2023-11-13 35
2023-11-14 38
2023-11-15 47
2023-11-16 36
2023-11-17 79
… …
2

정말 좋습니다.
날짜를 기준으로 실행 시점에 카테고리/하위 카테고리를 정의할 수 있는 방법이 있을까요?
그리고 보너스로, 결과를 사용자별로 나열하거나 사용자도 함께 정의할 수 있을까요?

제가 하려는 것은 지원 스태프가 지원 티켓 영역에서 특정 기간(범위) 내에 얼마나 많은 게시물을 작성하는지 확인하는 것입니다.

3

네, 이를 위해 다음 쿼리를 사용할 수 있습니다:

--[params]
-- date :start_date
-- date :end_date
-- null category_id :category_id 
-- null user_id :user_id
-- boolean :include_subcategories = false

SELECT 
    u.username AS "User",
    p.created_at::date AS "Date",
    COUNT(p.id) AS "Count"
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id AND t.deleted_at IS NULL
INNER JOIN users u ON p.user_id = u.id
LEFT JOIN categories c ON t.category_id = c.id
WHERE p.created_at::date BETWEEN :start_date AND :end_date
    AND p.deleted_at IS NULL
    AND t.archetype = 'regular'
    AND p.post_type = 1
    AND (
        :category_id IS NULL 
        OR t.category_id = :category_id
        OR (:include_subcategories AND c.parent_category_id = :category_id)
    )
    AND (:user_id IS NULL OR p.user_id = :user_id)
GROUP BY u.username, p.created_at::date
ORDER BY p.created_at::date ASC, u.username

파라미터:

  • :start_date & :end_date: 보고 기간을 정의합니다 (필수)
  • :category_id: 특정 카테고리에 대한 선택적 필터
  • :user_id: 특정 사용자에 대한 선택적 필터
  • :include_subcategories: 선택한 카테고리의 하위 카테고리를 포함할지 여부

이 쿼리는 다음을 표시합니다:

  • User: 게시물 작성자의 사용자 이름
  • Date: 게시물이 생성된 날짜
  • Count: 해당 사용자가 해당 날짜에 생성한 게시물 수

예시 데이터:

User Date Count
user 1 2023-01-01 3
user 2 2023-01-01 2
user 3 2023-01-01 1
user 1 2023-01-02 2
user 2 2023-01-02 3
user 1 2023-01-03 1
… … …
4

감사합니다. 정말 큰 도움이 됩니다!