이것은 Notify User 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 사용자가 자신의 게시물 중 하나에 달린 플래그로 인해 개인적으로 알림을 받은 횟수의 일별 수치를 제공합니다.
-- [params]
-- date :start_date = 2023-12-15
-- date :end_date = 2024-01-16
SELECT
DATE(created_at) AS day,
COUNT(1) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
AND subtype = 'notify_user'
AND created_at BETWEEN :start_date AND :end_date
AND deleted_at IS NULL
AND user_id > 0
GROUP BY DATE(created_at)
ORDER BY day
SQL 쿼리 설명
이 쿼리는 topics 테이블에서 데이터를 추출하여 작동합니다. 구체적으로는 주어진 시간 범위 내에서 notify_user 하위 유형을 가진 사용자에게 보내진 개인 메시지에 해당하는 항목들을 선택합니다. 내용을 자세히 살펴보면 다음과 같습니다:
- 날짜 매개변수:
- 쿼리는 리포트의 날짜 범위를 정의하는 두 개의 매개변수인
:start_date와:end_date를 받습니다. 두 날짜 매개변수 모두YYYY-MM-DD형식의 날짜를 허용합니다.
- 쿼리는 리포트의 날짜 범위를 정의하는 두 개의 매개변수인
- 선택 및 집계:
DATE(created_at)은created_at타임스탬프를 날짜 형식으로 변환하고 이를day라고 명명합니다. 이를 통해 메시지가 생성된 날짜별로 그룹화하고 집계할 수 있습니다.COUNT(1)은 각 날짜에 해당하는 항목의 수를 계산하는 데 사용되며, 이 집계 값을notifications_count라고 명명합니다.
- 필터링 기준:
- 쿼리는
archetype이'private_message'인 항목만 필터링하여, 다른 유형의 토픽이 아닌 오직 메시지만 확인하도록 합니다. subtype은'notify_user'로 제한되어, 사용자가 플래그가 달린 게시물에 대해 알림을 받은 경우만 분리합니다.- 메시지의
created_at타임스탬프가 지정된:start_date와:end_date사이에 있는지 확인합니다. - 삭제된 메시지(
deleted_at IS NULL)는 집계에서 제외하여 리포트가 활성 통신만 반영하도록 합니다. - 마지막으로,
user_id > 0을 확인함으로써 메시지가 유효한 사용자와 연결되어 있음을 보장합니다.
- 쿼리는
- 그룹화 및 정렬:
- 쿼리는
GROUP BY DATE(created_at)을 사용하여 지정된 날짜 범위 내의 각 날짜별로 결과를 집계합니다. - 그런 다음
ORDER BY day를 사용하여 결과를 시간순으로 정렬하여 리포트를 읽고 해석하기 쉽게 만듭니다.
- 쿼리는
결과 예시
| day | notifications_count |
|---|---|
| 2023-12-17 | 1 |
| 2023-12-18 | 1 |
| 2023-12-21 | 1 |
| 2023-12-23 | 1 |
| 2023-12-26 | 1 |