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

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

상위 100개가 아닌 특정 날짜 범위의 검색 전체 목록을 가져올 수 있는 방법이 있을까요?

2

이것은 데이터 탐색기 쿼리의 후보가 될 수 있을 것 같습니다. 정확한 쿼리를 작성하려면 조금 둘러봐야겠지만, 데이터는 search_logs 테이블에 있을 것 같습니다.

추가로 도움이 필요하시면 말씀해 주세요. :slight_smile:

5

SQL은 잘 모르지만, 지난 3개월간의 검색 로그를 조회하는 방법에 대해 ChatGPT에게 물었을 때 받은 답변이 있습니다. 이 쿼리가 제대로 동작할까요? (테스트해 볼 수 있는 관리자 권한은 없지만, 관리자에게 전달할 확실한 쿼리를 갖고 싶습니다.)

Discourse Data Explorer를 사용하여 지난 3개월간의 모든 검색 로그 데이터를 가져오려면, 다음 코드를 사용하여 새 쿼리를 생성할 수 있습니다:

-- [params]
-- date :datetime
SELECT
  action_logs.id AS action_log_id,
  action_logs.created_at AS action_log_date,
  action_logs.action AS action_type,
  users.id AS user_id,
  users.username AS user_username,
  users.email AS user_email,
  user_actions.details AS search_query
FROM
  action_logs
JOIN
  user_actions ON user_actions.id = action_logs.action_id
JOIN
  users ON user_actions.user_id = users.id
WHERE
  action_logs.action = 'searched'
  AND action_logs.created_at >= :date - INTERVAL '3 months'
ORDER BY
  action_logs.created_at DESC;

이 쿼리에서는 -- [params] 구문을 사용하여 datetime 타입의 date라는 이름을 가진 매개변수를 정의합니다. 이 매개변수를 사용하면 검색 로그 데이터를 가져오려는 날짜를 지정할 수 있습니다. 쿼리는 이 매개변수를 사용하여 지정된 날짜로부터 지난 3개월간의 검색 로그를 필터링합니다.

Discourse Data Explorer에서 이 쿼리를 사용하려면 다음 단계를 따르세요:

  1. Discourse 사이트의 관리자 대시보드로 이동합니다.
  2. 왼쪽 메뉴에서 "Data Explorer"를 클릭합니다.
  3. 새 쿼리를 생성하려면 "New Query"를 클릭합니다.
  4. 원하는 경우 쿼리에 이름과 설명을 부여합니다.
  5. 위의 SQL 코드를 “Query” 텍스트 영역에 복사하여 붙여넣습니다.
  6. 쿼리를 저장하려면 “Save” 버튼을 클릭합니다.

저장된 후에는 Data Explorer에서 쿼리 이름을 클릭하여 쿼리를 실행할 수 있습니다. date 매개변수 값을 입력하라는 팝업이 표시됩니다. YYYY-MM-DD 형식(예: 2023-07-28)으로 날짜를 제공하고 "Run Query"를 클릭합니다.

쿼리는 지정된 날짜로부터 지난 3개월간의 모든 검색 로그 데이터를 가져오며, 결과는 테이블 형식으로 표시됩니다.

6

그건 맞지 않을 것 같아요. action_logs 테이블이 있는 것 같지 않거든요. :thinking:

한번 살펴보고 뭘 만들어볼지 확인해볼게요. :+1: :slight_smile:

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:

8

정말 멋지네요, 감사합니다! 관리자에게 전달하겠습니다 :grinning:

9

이 쿼리를 실행해 봤는데 완벽하게 잘 작동했습니다. 다시 한번 감사합니다!