これは、WebクローラーのユーザーエージェントのダッシュボードレポートのSQLバージョンです。
このダッシュボードレポートは、ページビュー数でソートされた上位のWebクローラーユーザーエージェントをリストし、どのクローラーがサイトで最もアクティブであるかについての洞察を提供します。
--[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の2つのパラメータを受け入れます。両方の日付パラメータはYYYY-MM-DDの形式を受け入れます。 - SELECT: クエリは、フォーラムへのWebクローラーによるリクエストを記録する
web_crawler_requestsテーブルからデータを取得します。このテーブルの各レコードには、クローラーのuser_agentとページビューのcountが含まれます。 - SUM:
SUM(count)関数は、指定された日付範囲内の各user_agentのページビューの合計数を計算します。 - フィルタリング:
WHERE句は、date列を使用して、指定された日付範囲内にあるレコードのみを含めるようにレコードをフィルタリングします。 - 集計:
GROUP BY句は、結果をuser_agentでグループ化し、各Webクローラーのデータが個別に要約されるようにします。 - ソート:
ORDER BY句は、結果をpageviewsの降順でソートし、最もアクティブなWebクローラーをレポートの上部に配置します。
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 |
| … | … |
