これは、統合されたページビューのダッシュボードレポートのSQLバージョンです。
このSQLレポートは、Discourseサイトでのログインユーザー、匿名ユーザー、およびクローラーからのページビューの日次カウントを提供します。
-- [params]
-- date :start_date
-- date :end_date
SELECT
ar.date::date AS "date",
SUM(ar.count) AS "Total Views",
SUM(CASE WHEN ar.req_type=6 THEN ar.count ELSE 0 END) AS "Crawler Views",
SUM(CASE WHEN ar.req_type=7 THEN ar.count ELSE 0 END) AS "Logged in User Views",
SUM(CASE WHEN ar.req_type=8 THEN ar.count ELSE 0 END) AS "Anonymous User Views"
FROM application_requests ar
WHERE req_type IN (6,7,8)
AND ar.date::date BETWEEN :start_date AND :end_date
GROUP BY date
ORDER BY date
SQLクエリの説明
クエリは、フォーラムへのさまざまな種類の要求を記録するapplication_requestsテーブルで動作します。これは、req_type値によって識別される3つの特定の要求タイプに焦点を当てています。
6: クローラービュー7: ログインユーザービュー8: 匿名ユーザービュー
クエリが実行する内容のステップバイステップの説明は次のとおりです。
- パラメータ: クエリは、
:start_dateと:end_dateの2つのパラメータを受け入れ、ユーザーがレポートの日付範囲を指定できるようにします。両方のdateパラメータは、YYYY-MM-DDの日付形式を受け入れます。 - 選択: クエリは、要求の日付（
ar.date）を選択し、それをdateタイプにキャストします。また、指定された範囲内の各日付について、合計ビューとビューの内訳（クローラー、ログインユーザー、匿名ユーザー）を計算します。 - 合計: 各ビュータイプの
count列を合計します。CASEステートメントは、req_typeに基づいて条件付きで合計にカウントを含めるために使用されます。 - フィルタリング:
WHERE句は、req_type値が6、7、または8であり、日付が指定された:start_dateと:end_dateの間にあるレコードのみを含めるようにレコードをフィルタリングします。 - グループ化: クエリは結果を日付でグループ化し、各ビュータイプのカウントが日ごとに集計されるようにします。
- 並べ替え: 結果は日付で並べ替えられ、トラフィックの時系列ビューが提供されます。
結果例
| date | Total Views | Crawler Views | Logged in User Views | Anonymous User Views |
|---|---|---|---|---|
| 2023-11-18 | 5608 | 3052 | 565 | 1928 |
| 2023-11-19 | 9207 | 6564 | 470 | 2273 |
| 2023-11-20 | 6271 | 2631 | 1016 | 2924 |
