대시보드 보고서 - 시스템

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

시스템 대시보드 보고서의 SQL 버전입니다.

이 대시보드 보고서는 시스템이 자동으로 보낸 개인 메시지의 일별 수를 제공합니다.

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

SELECT
  DATE(created_at) AS day,
  COUNT(*) AS notifications_count
FROM topics
WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  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 테이블에서 데이터를 추출하여 작동합니다. 구체적으로, 지정된 시간 범위 내에서 system_message 하위 유형을 가진 사용자에게 전송된 개인 메시지에 해당하는 레코드를 대상으로 합니다. 세부 내용을 살펴보면 다음과 같습니다:

  • 날짜 매개변수:
    • 쿼리는 보고서의 날짜 범위를 정의하는 :start_date와 :end_date라는 두 가지 매개변수를 받습니다. 두 날짜 매개변수 모두 YYYY-MM-DD 형식의 날짜를 허용합니다.
  • SELECT: 쿼리는 두 개의 필드를 선택합니다:
    • DATE(created_at) AS day: created_at 타임스탬프에서 날짜 부분을 추출하여 레코드가 생성된 날짜별로 그룹화합니다.
    • COUNT(*) AS notifications_count: 각 날짜별로 시스템이 생성한 개인 메시지(PM)의 총 수를 계산합니다.
  • FROM: 모든 토픽, 개인 메시지를 포함한 레코드를 포함하는 topics 테이블을 데이터 소스로 지정합니다.
  • WHERE: 데이터셋을 좁히기 위한 여러 필터를 포함합니다:
    • archetype = 'private_message': 개인 메시지인 항목만 포함합니다.
    • subtype = 'system_message': 선택 범위를 시스템이 생성한 메시지로만 더 좁힙니다.
    • created_at BETWEEN :start_date AND :end_date: 매개변수로 지정된 범위 내에서 생성된 개인 메시지로 필터링합니다.
    • deleted_at IS NULL: 삭제된 메시지를 제외합니다.
    • user_id > 0: 메시지가 시스템 또는 익명 계정이 아닌 실제 사용자 계정에 연결되어 있는지 확인합니다.
  • GROUP BY: 생성된 날짜 기준으로 결과를 그룹화합니다.
  • ORDER BY: 최종 결과 집합을 날짜 기준으로 오름차순으로 정렬하여 일별 집계 값의 시간 순서를 보장합니다.

결과 예시

day notifications_count
2024-01-01 5
2024-01-02 7
2024-01-03 11
2024-01-04 14
2024-01-05 8
2

이것이 특정 PM, 예를 들어 환영 PM과 연결될 수 있을까요?

하루에 그중 몇 개가 전송되는지 알고 싶습니다.

의견이 있으신가요?

3

네, 이를 추가하는 가장 좋은 방법은 쿼리의 WHERE 절에 섹션을 추가하여 토픽 title로 필터링하는 것입니다.

예를 들어:

WHERE archetype = 'private_message'
  AND subtype = 'system_message'
  AND created_at BETWEEN :start_date AND :end_date
  AND title = 'Greetings!'

이렇게 하면 모든 Greetings! 메시지를 찾을 수 있습니다.

일부 시스템 메시지는 실제 사용자를 포함하지 않으므로, 이러한 유형의 쿼리에서는 AND user_id > 0 줄을 제거할 필요가 있을 수 있습니다.

사용자가 삭제할 수 있는 환영 메시지도 계산에 포함하려면 AND deleted_at IS NULL도 제거하는 것이 좋습니다.

유사한 제목을 가진 토픽을 일치시키기 위해 정규식(regex)을 사용할 수 있습니다.

PostgreSQL에서 정규식을 사용하여 제목으로 토픽을 일치시키려면 문자열과 정규식을 비교하는 ~ 연산자를 사용할 수 있습니다. 쿼리 구조는 다음과 같습니다:

SELECT *
FROM topics
WHERE title ~ 'YourRegexPatternHere'

'YourRegexPatternHere'를 title 필드와 일치시키고자 하는 실제 정규식 패턴으로 대체하세요.

예를 들어, 제목에 "Welcome"라는 단어가 포함된 토픽(대소문자 구분 없이)을 찾고자 한다면 다음과 같이 사용할 수 있습니다:

SELECT *
FROM topics
WHERE title ~* 'Welcome'

~* 연산자는 대소문자를 구분하지 않는 매칭에 사용됩니다.

4

와, 정말 최고네요. 정말 감사합니다! 한번 시도해 볼게요!