이것은 신뢰 수준 성장에 대한 대시보드 보고서의 SQL 버전입니다.
이 대시보드 보고서는 지정된 날짜 범위 내에서 매일 신뢰 수준 1, 2, 3, 4에 도달한 사용자의 수를 집계하며, 디스커스(Discourse) 커뮤니티에서 관리자가 사용자 참여도와 신뢰 수준 진행 상황을 추적하는 데 도움을 줍니다.
-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2024-02-01
SELECT
date(created_at) AS date,
COUNT(*) FILTER (WHERE previous_value::integer < 1 AND new_value = '1') AS tl1_reached,
COUNT(*) FILTER (WHERE previous_value::integer < 2 AND new_value = '2') AS tl2_reached,
COUNT(*) FILTER (WHERE previous_value::integer < 3 AND new_value = '3') AS tl3_reached,
COUNT(*) FILTER (WHERE previous_value::integer < 4 AND new_value = '4') AS tl4_reached
FROM user_histories
WHERE created_at >= :start_date
AND created_at <= :end_date
AND (action = 2 OR action = 15)
GROUP BY date(created_at)
ORDER BY date(created_at)
SQL 쿼리 설명
이 쿼리는 사용자 상태 변경 사항(신뢰 수준 포함)을 기록하는 user_histories 테이블에서 레코드를 선택하는 방식으로 작동합니다.
쿼리의 세부 내용은 다음과 같습니다:
- 날짜 매개변수: 쿼리는 보고서의 날짜 범위를 정의하는 두 개의 매개변수인
:start_date와:end_date를 받습니다. 두 날짜 매개변수 모두YYYY-MM-DD형식의 날짜를 허용합니다. date(created_at) AS date: SELECT 문구의 이 부분은created_at타임스탬프에서 날짜를 추출하여 각 레코드에 대해 ‘date’라고 라벨링합니다. 이는 결과를 날짜별로 그룹화하는 데 사용됩니다.COUNT(*) FILTER (WHERE previous_value::integer < X AND new_value = 'Y') AS tlY_reached: 이 섹션은 더 낮은 신뢰 수준(X 미만)에서 특정 신뢰 수준(Y)으로 전진한 사용자의 수를 집계합니다.FILTER절은 지정된 조건에 맞는 행만 포함하도록 사용됩니다.::integer캐스팅은 비교를 위해previous_value를 정수로 변환하는 데 사용됩니다. 신뢰 수준 1부터 4까지 각 수준에 해당하는 네 개의 집계 열이 있습니다.FROM user_histories: 데이터를 가져올 테이블을 지정합니다.WHERE created_at >= :start_date AND created_at <= :end_date: 지정된 날짜 범위 내의 레코드만 포함하도록 필터링합니다.AND (action = 2 OR action = 15): 액션이 신뢰 수준 변경을 나타내는 레코드만 포함하도록 필터링합니다. 신뢰 수준 변경에 해당하는 특정 액션은 숫자 2와 15로 표현됩니다.GROUP BY date(created_at): 결과를 날짜별로 그룹화하여 지정된 범위 내 각 날짜의 신뢰 수준 변경 수를 얻습니다.ORDER BY date(created_at): 결과를 날짜순으로 정렬합니다.
결과 예시
| date | tl1_reached | tl2_reached | tl3_reached | tl4_reached |
|---|---|---|---|---|
| 2024-01-01 | 1 | 3 | 1 | 0 |
| 2024-01-02 | 5 | 1 | 0 | 1 |
| 2024-01-03 | 8 | 2 | 3 | 0 |
| … | … | … | … | … |