이것은 통합 페이지뷰 대시보드 보고서의 SQL 버전입니다.
이 SQL 보고서는 Discourse 사이트에서 로그인한 사용자, 익명 사용자 및 크롤러의 페이지뷰 일별 수를 제공합니다.
-- [params]
-- date :start_date
-- date :end_date
SELECT
ar.date::date AS "date",
SUM(ar.count) AS "Total Views",
SUM(CASE WHEN ar.req_type=6 THEN ar.count ELSE 0 END) AS "Crawler Views",
SUM(CASE WHEN ar.req_type=7 THEN ar.count ELSE 0 END) AS "Logged in User Views",
SUM(CASE WHEN ar.req_type=8 THEN ar.count ELSE 0 END) AS "Anonymous User Views"
FROM application_requests ar
WHERE req_type IN (6,7,8)
AND ar.date::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date
SQL 쿼리 설명
이 쿼리는 포럼에 대한 다양한 유형의 요청을 기록하는 application_requests 테이블에서 작동합니다. req_type 값으로 식별되는 세 가지 특정 유형의 요청에 초점을 맞춥니다:
6: 크롤러 뷰 (Crawler Views)7: 로그인한 사용자 뷰 (Logged in User Views)8: 익명 사용자 뷰 (Anonymous User Views)
쿼리가 수행하는 작업의 단계별 설명은 다음과 같습니다:
- 파라미터: 쿼리는 두 개의 파라미터인
:start_date와:end_date를 받아 사용자가 보고서의 날짜 범위를 지정할 수 있도록 합니다. 두 날짜 파라미터 모두YYYY-MM-DD형식의 날짜를 허용합니다. - 선택: 쿼리는 요청 날짜(
ar.date)를 선택하고 이를date타입으로 변환합니다. 또한 지정된 범위 내 각 날짜에 대해 총 뷰 수와 유형별(크롤러, 로그인한 사용자, 익명 사용자) 뷰 수를 계산합니다. - 합산: 각 뷰 유형에 대해
count열을 합산합니다.CASE문은req_type에 따라 조건부로 합산에 포함할 수 있도록 사용됩니다. - 필터링:
WHERE절은req_type값이 6, 7 또는 8이고 지정된:start_date와:end_date사이에 있는 날짜를 가진 레코드만 포함하도록 필터링합니다. - 그룹핑: 쿼리는 결과를 날짜별로 그룹화하여 각 뷰 유형의 수가 하루 단위로 집계되도록 보장합니다.
- 정렬: 결과는 날짜 순으로 정렬되어 트래픽의 시간적 흐름을 제공합니다.
결과 예시
| date | Total Views | Crawler Views | Logged in User Views | Anonymous User Views |
|---|---|---|---|---|
| 2023-11-18 | 5608 | 3052 | 565 | 1928 |
| 2023-11-19 | 9207 | 6564 | 470 | 2273 |
| 2023-11-20 | 6271 | 2631 | 1016 | 2924 |
