이것은 사용자 간(답변 포함) 및 사용자 간(답변 제외) 대시보드 리포트의 SQL 버전입니다.
이 리포트는 지정된 날짜 범위 내에서 사용자가 매일 보낸 개인 메시지의 수를 집계하여, 개인 소통에서의 사용자 활동에 대한 통찰력을 제공합니다.
--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- boolean :include_replies = false
SELECT
p.created_at::date as day,
COUNT(p.user_id) as personal_messages
FROM posts p
INNER JOIN topics t ON (p.topic_id = t.id)
WHERE archetype = 'private_message'
AND p.user_id > 0
AND t.subtype = 'user_to_user'
AND p.deleted_at ISNULL
AND t.deleted_at ISNULL
AND p.created_at::date BETWEEN :start_date AND :end_date
AND (:include_replies = true OR p.post_number = 1)
GROUP BY p.created_at::date
ORDER BY p.created_at::date
SQL 쿼리 설명
이 SQL 쿼리는 다음 작업을 수행합니다:
- 매개변수:
:start_date와:end_date는 지정된 날짜 범위 내에서 데이터를 필터링하는 데 사용됩니다. 두 날짜 매개변수 모두YYYY-MM-DD형식을 사용합니다.:include_replies는 초기 메시지에 대한 답변을 집계할지 여부를 결정합니다. 기본값은false로 설정되어 사용자 간(답변 제외) 리포트의 동작을 따릅니다. 이 매개변수를true로 설정하면 사용자 간(답변 포함) 리포트의 동작을 따릅니다.
- 데이터 선택:
- 쿼리는
posts테이블에서 각 메시지가 생성된 날짜를 나타내는created_at날짜를 선택합니다.
- 쿼리는
- 조인 작업:
- 쿼리는
posts와topics테이블을id필드를 기준으로 INNER JOIN으로 연결합니다. 이 조인은 각 게시물을 해당 주제(topic)와 연결하여 개인 메시지 대화의 일부인 게시물만 필터링할 수 있게 하므로 매우 중요합니다.
- 쿼리는
- 필터링 기준: 쿼리는 여러 필터를 적용합니다:
archetype이 'private_message’인 주제에 속하는 게시물만 고려하여, 개인 메시지만 집계되도록 합니다.user_id가 0보다 크도록 하여 사용자와 연결되지 않은 게시물을 필터링합니다.subtype이 'user_to_user’인 주제로 결과를 더 좁혀, 개별 사용자 간의 개인 메시지를 나타냅니다.- 두 테이블 모두에서
deleted_at이 NULL인지 확인하여 삭제된 게시물이나 주제를 제외합니다. :start_date와:end_date사이에 생성된 게시물만 포함하도록 날짜 범위 필터를 적용합니다.:include_replies가 false인 경우, 각 주제에서 첫 번째 게시물(p.post_number = 1)만 집계하여 답변을 제외합니다.
- 집계: 쿼리는 게시물이 생성된 날짜별로 결과를 그룹화하여, 각 날짜에 전송된 메시지 수를 집계할 수 있도록 합니다.
- 정렬: 마지막으로, 쿼리는 결과를 날짜 오름차순으로 정렬하여 사용자 간 개인 메시지 활동의 시간적 순서를 제공합니다.
결과 예시
| day | personal_messages |
|---|---|
| 2023-11-11 | 92 |
| 2023-11-12 | 57 |
| 2023-11-13 | 345 |
| 2023-11-14 | 124 |
| 2023-11-15 | 56 |