웹 크롤러 사용자 에이전트를 위한 대시보드 리포트의 SQL 버전입니다.
이 대시보드 리포트는 페이지뷰 기준으로 정렬된 상위 웹 크롤러 사용자 에이전트를 나열하여, 사이트에서 가장 활발하게 활동하는 크롤러가 무엇인지에 대한 통찰을 제공합니다.
--[params]
-- date :start_date = 2024-01-06
-- date :end_date = 2024-02-07
SELECT
user_agent,
SUM(count) AS pageviews
FROM web_crawler_requests
WHERE date BETWEEN :start_date AND :end_date
GROUP BY user_agent
ORDER BY pageviews DESC
SQL 쿼리 설명
- 파라미터: 이 쿼리는 리포트의 시간 범위를 정의하는 두 가지 파라미터인
:start_date와:end_date를 받습니다. 두 날짜 파라미터 모두YYYY-MM-DD형식을 사용합니다. - SELECT: 이 쿼리는
web_crawler_requests테이블에서 데이터를 가져옵니다. 이 테이블은 웹 크롤러가 포럼에 보낸 요청을 기록하며, 각 레코드에는 크롤러의user_agent와 페이지뷰count가 포함됩니다. - SUM:
SUM(count)함수는 지정된 날짜 범위 내에서 각user_agent의 총 페이지뷰 수를 계산합니다. - 필터링:
WHERE절은date컬럼을 사용하여 지정된 날짜 범위에 해당하는 레코드만 포함하도록 필터링합니다. - 집계:
GROUP BY절은 결과를user_agent별로 그룹화하여, 각 웹 크롤러에 대해 데이터가 개별적으로 요약되도록 보장합니다. - 정렬:
ORDER BY절은 결과를pageviews내림차순으로 정렬하여, 가장 활발한 웹 크롤러가 리포트 상단에 위치하도록 합니다.
web_crawler_requests테이블의 레코드는 30일 후 자동으로 삭제됩니다. 따라서 이 리포트의 결과는 리포트 실행 시점 기준 지난 30일 동안만 사용할 수 있습니다. 다만, 이 쿼리의 데이터 파라미터를 사용하여 지난 30일 이내의 결과를 필터링할 수는 있습니다.
결과 예시
| user_agent | pageviews |
|---|---|
| Mozilla/5.0 (compatible; SeekportBot; +https://bot.seekport.com) | 1406 |
| Mozilla/5.0 (compatible; YandexBot/3.0; +http://yandex.com/bots) | 724 |
| Mozilla/5.0 (compatible; DataForSeoBot/1.0; +https://dataforseo.com/dataforseo-bot) | 533 |
| … | … |
