이것은 트렌딩 검색어 대시보드 리포트의 SQL 버전입니다.
이 리포트는 지정된 날짜 범위 내에서 사이트에서 사용된 검색어에 대한 통찰력을 제공하며, 어떤 검색어가 가장 자주 검색되는지와 사용자가 검색 결과에 얼마나 자주 클릭하는지 이해하는 데 도움이 됩니다.
--[params]
-- date :start_date
-- date :end_date
SELECT
lower(term) AS search_term,
count(id) AS term_count,
(COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate,
SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM search_logs sl
WHERE sl.created_at::date BETWEEN :start_date AND :end_date
GROUP BY lower(term)
ORDER BY term_count DESC
SQL 쿼리 설명
쿼리의 세부 사항은 다음과 같습니다:
- 매개변수:
:start_date- 리포트의 날짜 범위 시작일.:end_date- 리포트의 날짜 범위 종료일.- 두 날짜 매개변수 모두
YYYY-MM-DD형식을 지원합니다.
- 두 날짜 매개변수 모두
- 데이터 선택:
lower(term) AS search_term: 일관된 그룹화를 위해 검색어를 소문자로 변환하고, 결과 컬럼 이름을search_term으로 지정합니다.count(id) AS term_count: 각 검색어에 대한 총 검색 횟수를 집계하고 이 컬럼 이름을term_count로 지정합니다.(COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate: 각 검색어에 대한 클릭률(CTR)을 계산합니다. 클릭으로 이어진 검색의 비율을 결정하여 계산하며,search_result_type이NULL(클릭이 없음을 의미)인 경우 0을, 그렇지 않은 경우 1을 할당합니다. 그 결과 값을 해당 검색어의 총 검색 횟수로 나누어 비율을 구한 후, 100을 곱하여 백분율로 변환합니다.SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count: 각 검색어에 대해 검색 결과가 클릭된 횟수를 합산합니다.
- 필터링:
WHERE sl.created_at::date BETWEEN :start_date AND :end_date:created_at날짜가 지정된 시작일과 종료일 사이에 포함되는 항목만 포함하도록 검색 로그를 필터링합니다.
- 그룹화 및 정렬:
GROUP BY lower(term): 각 고유 검색어에 대한 집계된 검색 횟수와 클릭 수를 계산하기 위해 소문자 검색어로 결과를 그룹화합니다.ORDER BY term_count DESC: 결과를term_count기준 내림차순으로 정렬하여 가장 자주 검색된 검색어가 상단에 표시되도록 합니다.
결과 예시
| search_term | term_count | click_through_rate | click_count |
|---|---|---|---|
| example search term | 756 | 0 | 0 |
| #category search term | 545 | 0.3669724770642202 | 2 |
| in:personal order:latest | 70 | 0 | 0 |
| test | 59 | 5.084745762711865 | 3 |
| search | 57 | 14.03508771929824 | 8 |
| … | … | … | … |