これは、ブラウザ検出機能付きの統合ページビューのダッシュボードレポートのSQLバージョンです。
このデータエクスプローラーレポートは、指定された期間におけるブラウザ検出機能付きのサイトページビューの日次ビューを提供します。ページビューは次の4つのタイプに分類されます。
- ログイン済みブラウザ
- 匿名ブラウザ
- 既知のクローラー
- その他のページビュー
-- [params]
-- date :start_date
-- date :end_date
SELECT
ar.date::date AS "date",
SUM(CASE WHEN ar.req_type = 15 THEN count ELSE 0 END) AS "Logged In Browser",
SUM(CASE WHEN ar.req_type = 13 THEN count ELSE 0 END) AS "Anonymous Browser",
SUM(CASE WHEN ar.req_type = 6 THEN count ELSE 0 END) AS "Known Crawler",
SUM(
CASE WHEN ar.req_type = 8 THEN count
WHEN ar.req_type = 7 THEN count
WHEN ar.req_type = 13 THEN -count
WHEN ar.req_type = 15 THEN -count
ELSE 0
END
) AS "Other pageviews"
FROM
application_requests ar
WHERE
ar.date::date >= :start_date
AND ar.date <= :end_date
GROUP BY
ar.date
ORDER BY
ar.date ASC
SQLクエリの説明
- パラメータ:
:start_date- レポートの開始日。:end_date- レポートの終了日。- 両方の期間パラメータは
YYYY-MM-DDの日付形式を受け入れます。
- SELECT句:
ar.date::date AS "date":dateフィールドを日付型に変換し、「date」としてラベル付けします。SUM(CASE WHEN ar.req_type = 15 THEN count ELSE 0 END) AS "Logged In Browser":req_typeが 15 の場合のカウントを合計し、結果を「Logged In Browser」としてラベル付けします。SUM(CASE WHEN ar.req_type = 13 THEN count ELSE 0 END) AS "Anonymous Browser":req_typeが 13 の場合のカウントを合計し、結果を「Anonymous Browser」としてラベル付けします。SUM(CASE WHEN ar.req_type = 6 THEN count ELSE 0 END) AS "Known Crawler":req_typeが 6 の場合のカウントを合計し、結果を「Known Crawler」としてラベル付けします。SUM(CASE WHEN ar.req_type = 8 THEN count WHEN ar.req_type = 7 THEN count WHEN ar.req_type = 13 THEN -count WHEN ar.req_type = 15 THEN -count ELSE 0 END) AS "Other pageviews": 他のタイプの要求のカウントを合計し、req_type13 および 15 のカウントを減算して調整し、結果を「Other pageviews」としてラベル付けします。
- FROM句:
FROM application_requests ar:application_requestsテーブルをデータのソースとして指定し、エイリアスarを付けます。
- WHERE句:
ar.date::date >= :start_date AND ar.date <= :end_date:dateが指定された:start_dateおよび:end_dateの範囲内にあるレコードのみを含めるようにレコードをフィルタリングします。
- GROUP BY句:
GROUP BY ar.date: 結果をdateフィールドでグループ化して、各日のカウントを集計します。
- ORDER BY句:
ORDER BY ar.date ASC: 結果をdateフィールドで昇順に並べ替えます。
結果
このクエリの結果は、ブラウザまたはクローラーのタイプ別に分類されたページビューの日次内訳を提供します。結果セットの各行には以下が含まれます。
date: 特定の日付。Logged In Browser: ログイン済みユーザーからのページビューの合計カウント。Anonymous Browser: 匿名ユーザーからのページビューの合計カウント。Known Crawler: 既知のクローラーからのページビューの合計カウント。Other pageviews: その他のタイプのページビューの合計カウント。
結果例
| date | Logged In Browser | Anonymous Browser | Known Crawler | Other pageviews |
|---|---|---|---|---|
| 2024-05-02 | 1238 | 1085 | 2727 | 3983 |
| 2024-05-03 | 1039 | 1068 | 5718 | 3958 |
| 2024-05-04 | 572 | 628 | 3847 | 3761 |
| 2024-05-05 | 447 | 551 | 2093 | 3773 |
）