대시보드 보고서 - 북마크

[SaraDev (Sara Devlaeminck)]

이것은 북마크 대시보드 리포트의 SQL 버전입니다.

이 대시보드 리포트는 지정된 날짜 범위 내에서 생성된 북마크의 일일 수를 제공합니다.

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

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

SQL 쿼리 설명

파라미터

리포트를 생성하려면 :start_date와 :end_date라는 두 가지 파라미터를 제공해야 합니다. 이 파라미터들은 북마크 활동을 분석하고자 하는 날짜 범위를 정의합니다. 두 날짜 파라미터 모두 YYYY-MM-DD 날짜 형식을 사용합니다.

Bookmark_Counts (CTE)

bookmark_counts라는 이름의 공통 테이블 표현식(CTE)을 사용하여 최종 선택 전에 데이터를 정리합니다. CTE는 다음 작업을 수행합니다:

• 날짜 필터링: 제공된 시작일과 종료일(:start_date과 :end_date) 사이에 생성된 북마크만 포함하도록 북마크를 생성 날짜 기준으로 필터링합니다.
• 날짜 그룹화: 북마크의 생성 날짜 기준으로 결과를 그룹화하여 집계된 수치가 일일 단위로 집계되도록 보장합니다.
• 북마크 카운팅: DISTINCT를 사용하여 각 북마크가 한 번만 카운트되도록 보장하면서, 주제(Topic)와 게시물(Post)의 북마크 수를 각각 계산합니다. 이는 CASE 문을 사용한 조건부 집계를 통해 수행됩니다:
  • topic_bookmarks는 bookmarkable_type이 ‘Topic’인 북마크를 카운트합니다.
  • post_bookmarks는 bookmarkable_type이 ‘Post’인 북마크를 카운트합니다.

메인 쿼리

CTE가 데이터를 정리한 후, 최종 SELECT 문은 다음 컬럼을 가져옵니다:

1. bookmark_date: 집계된 수치가 해당하는 날짜.
2. total_bookmarks: 각 날짜별 주제 및 게시물 북마크의 합계.
3. topic_bookmarks: 각 날짜별 주제 북마크의 수. 예시:
   
   
4. post_bookmarks: 각 날짜별 게시물 북마크의 수. 예시:
   
   

그 후 결과는 bookmark_date 기준으로 정렬되어 데이터가 시간 순서대로 표시되도록 합니다.

결과 예시

bookmark_date	total_bookmarks	topic_bookmarks	post_bookmarks
2023-12-01	16	1	15
2023-12-02	4	1	3
2023-12-03	8	0	8
2023-12-04	19	9	10
2023-12-05	18	3	15
…	…	…	…