대시보드 보고서 - 게시물

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

This is an SQL version of the Dashboard Report for Posts.

This report provides a daily count of posts created within a specified date range. It is designed to track the activity in regular topics, excluding posts from private messages and other special archetypes.

--[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 Query Explanation

  • Parameters:
    • The query accepts two parameters, :start_date and :end_date, which define the date range for the report. Both date parameters accept the date format of YYYY-MM-DD.

The SQL query performs the following operations:

  • Data Selection:
    • It selects the date (created_at::date) when each post was created and casts it to a date format to ignore the time component.
    • It also counts the number of posts (COUNT(p.id)) created on each date.
  • Joins:
    • The query joins the posts table with the topics table using an INNER JOIN. This join ensures that only posts associated with existing topics are considered.
    • It filters out any topics that have been deleted (t.deleted_at ISNULL).
  • Filters:
    • It filters posts to include only those within the specified date range (p.created_at::date BETWEEN :start_date AND :end_date).
    • It excludes deleted posts (p.deleted_at ISNULL).
    • It restricts the results to posts from regular topics (t.archetype = 'regular').
    • It considers only p.post_type = 1 posts, excluding moderator actions, whispers, and small_action posts.
  • Grouping and Ordering:
    • The results are grouped by the date of post creation (GROUP BY p.created_at::date).
    • The final output is ordered by the date in ascending order (ORDER BY 1), where 1 refers to the first column in the SELECT statement, which is the date.

Example Results

Day Count
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

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