검색 로그 - 특정 날짜 범위의 전체 목록을 가져오는 방법?

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

이런 식으로 수정하면 더 나을 것 같습니다:

-- [params]
-- date :start_date
-- date :end_date


WITH results AS (


SELECT LOWER(term) AS term,
       COUNT(*) AS searches,
       SUM(CASE WHEN search_result_id IS NOT NULL THEN 1 ELSE 0 END) AS click_through
FROM search_logs
WHERE created_at BETWEEN :start_date AND :end_date
GROUP BY term

)


SELECT term AS "Search Term",
       searches AS "Searches",
       click_through AS "Clicks-Through",
       CEIL ((click_through/searches::float) * 100) || '%' AS "CTR"
FROM results
ORDER BY searches DESC

도움이 되었기를 바랍니다. 조정하고 싶은 부분이 있으면 알려주세요. :slight_smile: