BigQueryで配列関数を利用して値をだす

BigQueryは配列関数をサポートしており、それを使ってセッション数やPV数を出すこともできます。例えばユーザー単位でvisitidを配列にして、そのを数えると、ユーザーごとのセッション数を出すことができます。ARRAY_LENGHはドキュメントを見ると「配列のサイズを返します」と書いてあります。配列の中にnullが含まれると、nullもカウントします。

　SELECT
　　fullvisitorid,
　　ARRAY_AGG(visitid) AS visitid,
　　ARRAY_LENGTH(ARRAY_AGG(visitid)) AS session_count
　FROM
　　 `{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`
　WHERE
　　_table_suffix between 'yyyymmdd' and 'yyyymmdd'
　GROUP BY
　　1　　
　;

これを一般公開のGoogleアナリティクスデータで実行すると以下のような結果が返ってきます。

同様に配列関数を使ってセッション中のPV数と訪問別PV数をを出してみます。

　SELECT
　　visitid,
　　ARRAY_AGG(hits.page.pagepath) AS pagepath,
　　ARRAY_LENGTH(ARRAY_AGG(hits.page.pagepath)) AS pageviews,
　　ARRAY_LENGTH(ARRAY_AGG(DISTINCT hits.page.pagepath)) AS unique_pageviews
　FROM
　　`{bigqueryプロジェクトID}.{データセット}.{ga_sessions_*}`,
　　UNNEST(hits) AS hits
　WHERE
　　_table_suffix BETWEEN 'yyyymmdd' AND 'yyyymmdd'
　GROUP BY
　　1　　
　;

 UNNEST関数でhits.page.pageapathをフラットにした上で、ARRAY_AGG関数で配列に戻していますので、通常はこのようにする必要はまったくないと思いますが、ご参考まで。

一般公開データのGoogleアナリティクスデータを使ってクエリを実行すると下のような結果が返ってきます。