プログラムで月間ページビューを取得する方法は？できればData Explorerを使用

[Konrad_Sopala]

こんにちは、コミュニティの皆様！

もしかして、Data Explorer を使って月次のページビュー数を取得するクエリを作成された方はいらっしゃいますか？

手動でダッシュボードにアクセスする以外の、プログラム的な方法でこれらの数値を取得する手段はありますか？

[michebs (Michelle)]

Consolidated Pageviews レポートに使用されているクエリです。参考になれば幸いです。

-- [params]
-- date :start_date
-- date :end_date

SELECT
    ar.date,
    CASE 
      WHEN ar.req_type=6 THEN 'Crawler'
      WHEN ar.req_type=7 THEN 'ログインユーザー'
      WHEN ar.req_type=8 THEN '匿名ユーザー'
    END,
    ar.count AS views
FROM application_requests ar
WHERE req_type IN (6,7,8)
    AND ar.date::date BETWEEN :start_date::date
	AND :end_date::date
ORDER BY ar.date ASC, ar.req_type

[Konrad_Sopala]

完璧です！少しは役立ちました！

この数値を取得することは可能でしょうか：

[michebs (Michelle)]

このクエリは、指定された期間の総ページビュー数をリストします。

-- [params]
-- date :start_date
-- date :end_date

WITH data AS (
    SELECT
        ar.date,
        CASE 
          WHEN ar.req_type=6 THEN 'クローラー'
          WHEN ar.req_type=7 THEN 'ログインユーザー'
          WHEN ar.req_type=8 THEN '匿名ユーザー'
        END AS Pageview,
        ar.count AS views
    FROM application_requests ar
    WHERE req_type IN (6,7,8)
        AND ar.date::date BETWEEN :start_date::date
        AND :end_date::date
    ORDER BY ar.date ASC, ar.req_type
)

SELECT Pageview, SUM(views) AS qtt_views
FROM data
GROUP BY Pageview

[Konrad_Sopala]

完璧！それが探していたものです。

[Konrad_Sopala]

実はもう一つ質問があります。それほど手間がかかることではないかもしれません。

sum を使って、以下のような形式で結果を取得する方法はありますか？

[michebs (Michelle)]

はい、可能です。この場合、まず日付フィールドの形式を調整し、その後でグループ化を調整する必要があります。以下は調整済みのクエリです。

-- [params]
-- date :start_date
-- date :end_date

WITH data AS 
    (SELECT
        date_part('month', ar.date) AS month,
        date_part('year', ar.date) AS year,
        CASE 
          WHEN ar.req_type=6 THEN 'Crawlers'
          WHEN ar.req_type=7 THEN 'Logged in users'
          WHEN ar.req_type=8 THEN 'Anonymous users'
        END AS pageview,
        ar.count AS views
    FROM application_requests ar
    WHERE req_type IN (6,7,8)
        AND ar.date::date BETWEEN :start_date::date
        AND :end_date::date
    ORDER BY ar.date ASC, ar.req_type
    )
    
  SELECT 
    month, 
    year, 
    sum(views) AS qtt_views
  FROM  data 
  GROUP BY year, month
  ORDER BY year DESC, month ASC

month	year	count
1	2021	1000
2	2021	500
1	2020	1500
2	2020	2000
3	2020	2500

[Konrad_Sopala]

完璧です！お手伝いいただき、ありがとうございます！