이것은 사용자 방문 및 사용자 방문(모바일) 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 일일 사용자 방문을 추적하여 사용자 참여도에 대한 통찰력을 제공합니다. 관리자는 날짜 범위, 그룹 멤버십, 기기 유형(모바일 또는 비모바일)에 따라 데이터를 필터링할 수 있습니다.
사용자 방문 패턴을 이해하면 관리자가 사용자 참여도와 커뮤니티 건전성을 향상시키기 위한 현명한 결정을 내리는 데 도움이 됩니다.
-- [params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
-- null text :group_name
-- boolean :mobile_views = false
SELECT uv.visited_at AS date,
COUNT(DISTINCT uv.user_id) AS visits_count
FROM user_visits uv
LEFT JOIN group_users gu ON gu.user_id = uv.user_id
LEFT JOIN groups g ON g.id = gu.group_id AND (:group_name IS NULL OR g.name = :group_name)
WHERE uv.visited_at BETWEEN :start_date AND :end_date
AND (:group_name IS NULL OR g.name IS NOT NULL)
AND (:mobile_views = false OR uv.mobile = true)
GROUP BY uv.visited_at
ORDER BY uv.visited_at ASC
SQL 쿼리 설명
이 SQL 쿼리는 플랫폼에 대한 각 사용자의 방문을 기록하는 user_visits 테이블에서 데이터를 선택하여 작동합니다. 쿼리의 주요 구성 요소는 다음과 같이 설명됩니다:
- 파라미터
:start_date와:end_date는 리포트의 날짜 범위를 정의합니다. 두 날짜 파라미터 모두YYYY-MM-DD형식의 날짜를 허용합니다.:group_name은 특정 사용자 그룹 이름으로 필터링할 수 있도록 합니다. 지정되지 않으면 모든 그룹의 데이터가 포함됩니다.:mobile_views는 모바일 기기 방문(true)만 포함할지, 아니면 모든 방문(false)을 포함할지를 결정합니다.
- 데이터 선택 및 조인 작업:
- 쿼리는 방문 날짜(
visited_at)를 선택하고 고유한 사용자 ID(user_id)를 카운트하여 일일 고유 방문 수를 계산합니다. group_users테이블과LEFT JOIN을 수행하여 사용자 방문을 해당 그룹과 연결하고, 지정된 그룹 이름이 있는 경우groups테이블과 또 다른LEFT JOIN을 수행하여 그룹 이름으로 필터링합니다.
- 쿼리는 방문 날짜(
- 필터링:
WHERE절은 입력 파라미터에 기반하여 필터를 적용합니다:- 지정된 날짜 범위 내의 방문만 포함되도록 합니다.
- 지정된 경우 그룹 이름으로 필터링하고, 해당 그룹에 속하는 사용자의 방문이 카운트되도록 보장합니다.
:mobile_views파라미터가true로 설정된 경우 모바일 방문만 포함합니다.
- 그룹화 및 정렬:
- 쿼리는 방문 날짜(
visited_at)별로 결과를 그룹화하여 일별 방문 수를 집계합니다. - 날짜 기준으로 오름차순으로 결과를 정렬하여 사용자 방문의 시간 순서별 뷰를 제공합니다.
- 쿼리는 방문 날짜(
결과 예시
| date | visits_count |
|---|---|
| 2024-01-06 | 67 |
| 2024-01-07 | 71 |
| 2024-01-08 | 88 |
| 2024-01-09 | 79 |
| 2024-01-10 | 78 |
| … | … |