BigQueryのクエリの可読性と保守性を高めたい

この記事は [MIXI DEVELOPERS Advent Calendar 2022 - Qiita](https://qiita.com/advent-calendar/2022/mixi) の8日目です。

エンジニアからPOにジョブチェンジしてちょうど1年になりました。開発からもすっかり手離れし、もはやコードらしきものを書くのはBigQueryぐらいです。
解析をするときはなぜか時間に追われていることが多いためついつい雑な書き方になりがち。後から何がしたかったのか思い出せないクエリになっていることもしばしば……。
エンジニアらしく可読性と保守性の高いクエリにしていきたい！という気持ちをこめて、よく使う変換式や関数をまとめてみました。
初歩的なところも多く記載していますが、改めてなにか一つ学びがあれば嬉しいです。

# はじめに

## 記事の対象者
- 基本的なSQLは理解している人
- 毎回検索してコピペでなんとかしてしまいしがちな人
- GoogleAnalytics4（以下GA4）＋BigQueryなデータを解析することが多い人
- GA4のコンソールよりも精緻な数値を取得したい人
- （もしくはGA4のコンソールを使いこなせない人←私）

## 注意事項
- この記事内ではコードの可読性を（比較的）重視しており、そのためクエリのパフォーマンスへの配慮は特にしておりません
- 同様に課金額への考慮もしておりません。BigQueryでは実行時にジョブ情報欄から「処理されたバイト数」「課金されるバイト数」等を見ることができますので状況に応じてご利用ください

## 説明のために使用するデータ
GA4の公開データ `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` を利用します。
導入方法: [Google アナリティクス 4 e コマースウェブ実装向けの BigQuery サンプル データセット  |  Google Analytics BigQuery Export  |  Google Developers](https://developers.google.com/analytics/bigquery/web-ecommerce-demo-dataset)

# よく使う、基本的なフィールド加工〜コンソールの使い方を添えて〜

## ユーザーの閲覧環境でセグメント分けする

ここでは「ユーザーの閲覧環境ごとのPV、UU、PV/UUを計測する」というお題をベースに、BigQuery コンソールに備わった機能を合わせて紹介していきます。

### まずはデータを知るところから
BigQueryのコンソールでは、「プレビュー」を使うことで、それぞれのカラムにどんなデータが入っているのかすぐに確認できます。
また、「スキーマ」というタブではデータの型が記載されています。なんとなくでやっていくよりも型を理解したほうが早いです。
慣れないうちはこちらの画面を別タブで開いたままにしておき、確認しながら進めるといいかもしれません。

![貼り付けた画像_2022_12_07_0_18.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/0122854b-3db7-9db2-ed16-ff1e3cd58e06.png)

![貼り付けた画像_2022_12_07_0_12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/d365720f-17b0-4a68-554c-174fce2302a3.png)

スキーマについては記事の後半でも紹介します。

### クエリを書く
基本的なSQLと大きく変わるところはありませんので割愛します。
以下のようなクエリができあがったとしましょう。

```sql
SELECT
  device.category,
  platform,
  COUNT(user_pseudo_id) AS pv,
  COUNT(DISTINCT user_pseudo_id) AS uu,
  COUNT(user_pseudo_id)/COUNT(DISTINCT user_pseudo_id) AS pv_per_uu
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE event_name in ('page_view', 'screen_view') -- page_viewはwebのPV、screen_viewはアプリのPVに相当するもの
GROUP BY
  device.category,
  platform
ORDER BY pv DESC
```

#### memo: コメント
クエリにコメントを書くには以下の方法があります。
適宜コメントを残してクエリの可読性を担保しましょう。

```sql
-- このコメントはシングルラインコメントです
SELECT -- このコメントはインラインコメントです
  /*
    このコメントはマルチラインコメントです。
　　　　　　　　複数行のコメントを書けます。
  */
  hoge_item
FROM hoge_table
WHERE　/* このコメントはインラインコメントです */ hoge_param = true 
```

#### memo: フォーマット
クエリを書いたら適宜右上の「展開＞クエリを書式設定」を使ってフォーマットをかけましょう。
文法の誤りに気が付きやすくなります。

![貼り付けた画像_2022_12_07_0_32.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/3ee2dd4c-104c-b9b7-eb42-3868f7daef1b.png)

### クエリを実行してみる
さて、先ほどのクエリを実行してみます。

**実行結果**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/096e3021-dc61-c7cd-9f2a-ca34d5140ed1.png)

ユーザーの閲覧環境はGA4で標準的に取得される以下のフィールドから得られます。

- device: デバイス。使っているスマホやPCがどういったものか
  - category: 大まかなカテゴリ。mobile, tablet, desktop, smart tv
  - 他、メーカー、OSバージョンブラウザなどetc...
- platform: プラットフォーム。サービス側の提供しているどのプラットフォームのものを使っているか。WEB, IOS, ANDROID

サンプルデータセットにはアプリのデータは含まれていないようなので、参考に別PJのデータで実行した結果はこちらです。platformにANDROID, IOSが増えていますね。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/10e0f049-648b-42ee-a70b-bdce23ba92cb.png" width="500">

### 扱いやすいようにまとめてみる

一般的なwebサービスの集計でよく使うパターンだとこのような感じでしょうか。
パターンが多すぎても扱いづらいので、iOS, Android, Web(PC), Web(SP) にまとめてみました。
複数カラムの結果を1つの情報にまとめる際に　CASE 文は便利です。

```sql
SELECT
  (CASE platform
      WHEN 'IOS' THEN 'iOS'
      WHEN 'ANDROID' THEN 'Android'
      WHEN 'WEB' THEN ( CASE device.category
        WHEN 'mobile' THEN 'WEB(SP)'
        WHEN 'desktop' THEN 'WEB(PC)'
      ELSE
      'WEB(others)'
    END
      )
    ELSE
    'unknown'
  END
    ) AS custom_device,
  COUNT(user_pseudo_id) AS pv,
  COUNT(DISTINCT user_pseudo_id) AS uu,
  COUNT(user_pseudo_id)/COUNT(DISTINCT user_pseudo_id) AS pv_per_uu
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
GROUP BY
  custom_device
ORDER BY pv DESC
```

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/4037d907-6638-b251-b2a1-26aedfad88f8.png" width="350">

それぞれの項目にどのようなデータが入るかはGA4やFirebaseのヘルプにまとめられています（プラットフォーム / デバイス の項目）。
- [[GA4] アナリティクスのディメンションと指標 - アナリティクス ヘルプ](https://support.google.com/analytics/answer/9143382?hl=ja#attribution&demographics&ecommerce&event&gaming&general&geography&link&other&page-screen&platform-device&publisher&time&traffic-source&user&user-lifetime&video&zippy=%2C%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC%E5%B1%9E%E6%80%A7%2C%E5%85%A8%E8%88%AC%2C%E3%83%97%E3%83%A9%E3%83%83%E3%83%88%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A0-%E3%83%87%E3%83%90%E3%82%A4%E3%82%B9%2C%E3%83%A6%E3%83%BC%E3%82%B6%E3%83%BC)
- [[GA4] BigQuery Export スキーマ - Firebase ヘルプ](https://support.google.com/firebase/answer/7029846?hl=ja)

#### memo: よく使うクエリは保存しておこう
よく使うクエリは、実行ボタンの右隣の「保存＞クエリを保存」から保存しておきましょう。
分かりやすい名前をつけておきましょう。
また、チームメンバーにも使ってほしい場合は公開設定を「プロジェクト」にすると他の人もクエリが扱えるようになります。見えるだけでなく編集もできてしまう点は注意ですが、うまく情報共有して活用してください。

![貼り付けた画像_2022_12_07_0_45.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/63d85495-543e-13a1-99fa-f5424931522b.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/f265f220-936c-ac65-8197-ac0e933d38cb.png)

保存したクエリはサイドバーの「保存したクエリ」の中にあります。
<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/f5be8b39-b21b-0f58-379a-271a565a8ddf.png" width="300">

#### memo: `AS alias` のできること

`SELECT hoge_long_name AS hoge FROM...`

といった形で `AS` を使ってフィールドにエイリアスを設定することは多々あると思いますが、どんな場面で使えるか確認しておきます。

- `AS` を使える箇所は `SELECT` と `FROM` 句
- `AS` は省略可能
- エイリアスには英数字とアンダースコア（ `_` ）からなる300文字以内の文字列のみ設定可能（日本語使いたいな…）
- `SELECT` で設定したエイリアスを利用できる箇所は `GROUP BY` 句、 `ORDER BY` 句、`HAVING` 句（上のクエリで `ORDER BY pv DESC` とできたように）
- `FROM` 句で設定したエイリアスを利用できる箇所は `FROM` 句でその後に続くパス式のみ（パス式とは `t.hoge_field` のように子要素にアクセスする形式のこと/この説明は不正確かもしれません…）
  - [語彙の構造と構文  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical?hl=ja#path_expressions)

詳細: [クエリ構文  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#using_aliases)

## 時間表記を変換する

様々な時間表記を変換してみます。

### その前に基本をおさらい

BigQuery向けの標準SQLには以下の4つの型が用意されています。

#### memo： タイムゾーンについて事前補足
[List of tz database time zones - Wikipedia](https://en.wikipedia.org/wiki/List_of_tz_database_time_zones)
こちらの表の `TZ database name` 列の表記を使うのが間違いないです。abbreviation（略語）の方は関数によって使えたり使えなかったりするようでした。

#### memo: DATE 型（日付）
`2022-12-03` のような形式。タイムゾーン未指定の場合はUTCとなります。

```sql
SELECT
  CURRENT_DATE() AS utc,
  CURRENT_DATE('Asia/Tokyo') AS jst, -- 'JST' でも ok
  CURRENT_DATE('HST') AS hst; -- 'Pacific/Honolulu' のこと
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/2c1f50f5-83e3-a78b-970f-8cbe365befc6.png)

#### memo: TIME 型（時間）
`07:07:24.231025` のような形式。タイムゾーン未指定の場合はUTCとなります。

```sql
SELECT
  CURRENT_TIME() AS utc,
  CURRENT_TIME('Asia/Tokyo') AS jst,
  CURRENT_TIME('HST') AS hst;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/cd834f66-328e-ada2-c9bf-78650373f3ea.png)

#### memo: DATETIME 型（日時）
`2022-12-03T07:04:53.215916` のような形式。タイムゾーン未指定の場合はUTCとなります。

```sql
SELECT
  CURRENT_DATETIME() AS utc,
  CURRENT_DATETIME('Asia/Tokyo') AS jst,
  CURRENT_DATETIME('HST') AS hst;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/f3eca288-a4d9-11d4-5664-8e872bb3ad7d.png)

#### memo: TIMESTAMP 型（タイムスタンプ）
タイムゾーンなどに関係なく、絶対的な時刻を返します。
日付としての実用上は `DATETIME` でも `TIMESTAMP` でも変わらないことが解析の現場では多いのではないでしょうか。
とはいえ値の扱い方は大きく変わるので注意が必要です。

```sql
SELECT
  CURRENT_TIMESTAMP() AS utc
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/4088a589-f2ec-71de-b9f6-85f2491fea5a.png)

#### memo: TIMESTAMP 型を日本時間表記にする

フォーマットの書き方はこちらを参照
[形式設定要素  |  BigQuery  |  Google Cloud]![貼り付けた画像_2022_12_07_1_08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/5c5993cc-30ed-4e96-2beb-f1dd52314515.png)
(https://cloud.google.com/bigquery/docs/reference/standard-sql/format-elements?hl=ja#format_elements_date_time)

なお、以下のようにフォーマットした後の型は `STRING` となるため、他の時間情報と比較する際などに必要なタイムゾーン情報などは失われています。フォーマットは最後にするように心がけた方がよさそうです。

```sql
SELECT
  CURRENT_TIMESTAMP() AS utc,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', CURRENT_TIMESTAMP(), 'UTC') AS formatted_utc,
  FORMAT_TIMESTAMP('%F %T', CURRENT_TIMESTAMP(), 'UTC') AS formatted_utc_2, -- %F は %Y-%m-%d, %T は %H:%M:%S と同義
  FORMAT_TIMESTAMP('%F %T', CURRENT_TIMESTAMP(), 'Asia/Tokyo') AS formatted_jst,
  FORMAT_TIMESTAMP('%F %T', CURRENT_TIMESTAMP(), 'HST') AS formatted_hst;
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/3f35c348-cd5f-14d3-77fd-29ba20e438a6.png)

### evnet_timestamp `1612069510766593` → `2021-01-31 05:05:10` （日本時間）
ここからが本題です。
前述の `TIMESTAMP` 型とまぎらわしいのですが、GA4のイベントに含まれる `event_timestamp` というフィールドは `INTEGER` 型であり、UNIXTIMEにおけるマイクロ秒を表しています。
これを見やすい日本時間表記にするには、以下のように2段階の変換を行う必要があります。

1. INTEGER -> TIMESTAMP
2. TIMESTAMP -> STRING

```sql
SELECT
  event_timestamp,
  TIMESTAMP_MICROS(event_timestamp) AS utc,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_MICROS(event_timestamp), 'UTC') AS formatted_utc,
  FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(event_timestamp), 'UTC') AS formatted_utc_2,
  -- %F は %Y-%m-%d, %T は %H:%M:%S と同義
  FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(event_timestamp), 'Asia/Tokyo') AS formatted_jst,
  FORMAT_TIMESTAMP('%F %T', TIMESTAMP_MICROS(event_timestamp), 'HST') AS formatted_hst
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
LIMIT
  1
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/2830d1ab-9452-c01b-c892-4a7b3af6973d.png)

### event_timestamp を使って時間の比較
この時間帯にどれくらいのアクセスがあったか、といった数値を調べるのに使える比較です。
クエリを書く側にとって理解しやすいように、タイムゾーンを考慮しつつ `TIMESTAMP` 形式にして比較し、 `CASE`文で時間帯をまとめています。

```sql
SELECT
  CASE
    WHEN TIMESTAMP_MICROS(event_timestamp) < TIMESTAMP('2021-01-31 00:00:00', 'Asia/Tokyo') THEN '2021-01-30'
    WHEN TIMESTAMP_MICROS(event_timestamp) BETWEEN TIMESTAMP('2021-01-31 00:00:00', 'Asia/Tokyo') AND TIMESTAMP('2021-01-31 12:00:00', 'Asia/Tokyo') THEN '2021-01-31 AM'
    WHEN TIMESTAMP_MICROS(event_timestamp) BETWEEN TIMESTAMP('2021-01-31 12:00:00', 'Asia/Tokyo') AND TIMESTAMP('2021-02-01 00:00:00', 'Asia/Tokyo') THEN '2021-01-31 PM'
    ELSE '2021-02-01'
    END
    AS period,
  COUNT(DISTINCT user_pseudo_id) AS uu
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_2021013*` -- このデータセットはUS時間なので、日本時間の1月31日のAM/PMを正確に得るために30日、31日の2日分のデータを使ってみました
GROUP BY period
ORDER BY period
```

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/80266171-6d35-9836-72a7-ccd2991aa2b3.png" width="400">

個人的な時短ワザとして、このあたりのよく使う日付変換は IME に登録してしまっています。最近は IME の辞書ツールにかなり長い文字列も登録できるので助かります。

<img src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/dd022ebb-2aef-8058-b1f0-2700f0d4edc8.png" width="400">


### event_date `20210131` → `2021-01-31`
`event_date` は、YYYYmmddの8桁で表された文字列形式の日付です。パーティションの日付と同じ日付が入っています。

`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
と指定したならそのデータセットの `event_date` はすべて `20210131` のはずです。

`event_date` をハイフンつなぎなど読みやすい形に変換する場合は以下のように2段階で変換する必要があります。
※こんなことをするよりは `event_timestamp` を利用したほうがいいと思いますが。。。

1. STRING -> DATE （PARSE_DATE）
2. DATE -> STRING （FORMAT_DATE）

```sql
SELECT
  event_date,
  PARSE_DATE('%Y%m%d', event_date) AS parse_date,
  FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%Y%m%d', event_date)) AS format_date
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
LIMIT
  1
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/c21fc127-a9c7-d286-5969-b23fc3f55a74.png)

#### memo: BigQueryのデータのタイムゾーンについて
日本国内を中心としたプロダクトであれば日本時間の日付でエクスポートされるよう設定してあるはずです。グローバルなプロダクトの場合はUTCかもしれませんので、GA4のレポートのタイムゾーンまたはBigQueryコンソールから「データセット情報＞データのロケーション」を確認してみてください。

[[GA4] アナリティクスで新しいウェブサイトまたはアプリのセットアップを行う - アナリティクス ヘルプ](https://support.google.com/analytics/answer/9304153#webproperty)
![貼り付けた画像_2022_12_07_1_08.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/3e9940bf-4a18-5a80-ca07-1e9697817574.png)

# よく使う、ちょっとややこしいフィールド加工

## UNNESTを理解してevent_paramsを扱う

BigQueryのデータの扱いでいちばん最初にハマるのが `event_params` だと思います。

### `event_params` の型

`event_params` は `RECORD` 型の構造体で、[GAにイベントを送る時に追加した任意の情報](https://developers.google.com/analytics/devguides/collection/ga4/event-parameters?client_type=gtag)が乗ってくるのもこのフィールドです。

`RECORD` 型といえば初めに紹介した `device` も `RECORD` 型ですが、あちらは `device.category` のように簡単にアクセスすることができました。
ですが `event_params` はそういった書き方ができません。
いったい何が違うのでしょうか。

|event_params|device
|:-:|:-:
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/63e1b16d-bccc-cf68-9528-acc04448f3af.png)![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/76a04054-367b-272e-6106-bb5db4ec559b.png)|![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/63e1b16d-bccc-cf68-9528-acc04448f3af.png)![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/3ea1a3fc-d0e9-aa77-c382-98d03121d0d7.png)

「種類」は同じ `RECORD` ですが、「モード」が `REPEATED` と `NULLABLE` で違うのが気になりますね。
`REPEATED` は繰り返し列であるということを意味しています。つまり `device` がただの単一の構造体であるのに対し、 `event_params` は **構造体の配列** だということです。

この画面（スキーマタブ）だと分かりづらいので、エラーメッセージを出して（力技）、本来の型を調べるとイメージしやすくなります。

```sql:event_paramsの型
ARRAY<
  STRUCT<
    key STRING,
    value STRUCT<
      string_value STRING,
      int_value INT64,
      float_value FLOAT64,
      double_value FLOAT64
    >
  >
>

つまりイメージは…:
[{key_1: {string_value: 'hoge', int_value: null, ...}}, {key_2: {string_value: null, int_value: 123, ...}}, ...]
```

```sql:deviceの型
STRUCT<
  category STRING,
  mobile_brand_name STRING,
  mobile_model_name STRING,
  ...
>

つまりイメージは…:
{category: 'mobile', mobile_brand_name: 'apple'...}
```

実際のデータはこんな感じです。1レコードの情報量が多い。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/e0f240c5-54c1-6c14-e897-d33e8291bf67.png)

データを1件 `SELECT` してきて、「クエリ結果＞JSON」を見てみるとより理解できるかもしれません。

<details><summary>event_paramsとdeviceをJSONで見てみる</summary>

```json
[{
  "event_params": [{
    "key": "gclid",
    "value": {
      "string_value": null,
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "gclsrc",
    "value": {
      "string_value": null,
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "debug_mode",
    "value": {
      "string_value": null,
      "int_value": "1",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "ga_session_number",
    "value": {
      "string_value": null,
      "int_value": "1",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "all_data",
    "value": {
      "string_value": null,
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "page_location",
    "value": {
      "string_value": "https://shop.googlemerchandisestore.com/",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "entrances",
    "value": {
      "string_value": null,
      "int_value": "1",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "session_engaged",
    "value": {
      "string_value": "0",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "ga_session_id",
    "value": {
      "string_value": null,
      "int_value": "661084800",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "clean_event",
    "value": {
      "string_value": "gtm.js",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "engaged_session_event",
    "value": {
      "string_value": null,
      "int_value": "1",
      "float_value": null,
      "double_value": null
    }
  }, {
    "key": "page_title",
    "value": {
      "string_value": "Home",
      "int_value": null,
      "float_value": null,
      "double_value": null
    }
  }],
  "device": {
    "category": "mobile",
    "mobile_brand_name": "Apple",
    "mobile_model_name": "iPhone",
    "mobile_marketing_name": "\u003cOther\u003e",
    "mobile_os_hardware_model": null,
    "operating_system": "Web",
    "operating_system_version": "\u003cOther\u003e",
    "vendor_id": null,
    "advertising_id": null,
    "language": "en-us",
    "is_limited_ad_tracking": "No",
    "time_zone_offset_seconds": null,
    "web_info": {
      "browser": "Safari",
      "browser_version": "13.1"
    }
  }
}]
```
</details>

参考: [テーブル スキーマでネストされた列と繰り返し列を指定する  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/nested-repeated)

### `UNNEST` 方式

構造を理解した上で、集計をしてみましょう。
`event_params` を使って集計をする際の基本的な使い方は、以下のように `UNNEST` した一時テーブルを作り、必要な `key` でフィルタリングすることです。`UNNEST` は `SELECT` 内でも `WHERE` 内でも行えます。

```sql
SELECT
  event_name,
  event_params,
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'page_location') AS page_location,
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
LIMIT
  1
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/185506ef-dabe-94fa-a575-c59867d8a5da.png)

具体的な使用例はこんな感じでしょうか。
`page_location` ごとの PV、UU、PV/UUをPV降順で上位10件取得しています。

```sql
SELECT
  (
  SELECT
    value.string_value
  FROM
    UNNEST(event_params)
  WHERE
    key = 'page_location') AS page_location,
  COUNT(user_pseudo_id) AS pv,
  COUNT(DISTINCT user_pseudo_id) AS uu,
  COUNT(user_pseudo_id)/COUNT(DISTINCT user_pseudo_id) AS pv_per_uu
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE
  event_name = 'page_view'
GROUP BY
  page_location
ORDER BY
  pv DESC
LIMIT 10
```

前述の通り、 `SELECT` 内で定義したエイリアスは `GROUP BY` や `ORDER BY` でも使えますので、そこまで複雑なクエリにならずにすみました。


ちなみに、UNNESTする際は
```sql
SELECT
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') AS page_location,`
...
```
のように1行で書いた方がコード全体を俯瞰しやすい、というのが個人的な感覚としてあるのですが、コンソール標準搭載のフォーマッター（展開＞クエリを書式設定）をかけると、上のコードのように細かく改行されて読みにくくなってしまうんですよね。1行にまとめてくれていいのに。フォーマッターに抗いたくもないので悩ましいところです。

### `CROSS JOIN`　方式

ただし、 `WHERE` 内で `page_location` による絞り込みを行いたい場合は、エイリアスを使うことができません。
エイリアスを使わず前述のように `UNNEST` してもいいのですが、`SELECT` と `WHERE` で同じ変換を書くのは冗長に感じます。

こういった場合、おそらく以下のように書くのが最もシンプルになると思われます（ただしこれは `event_params` 内の1要素のみで絞り込めればいい場合のみ有効です）。

`CROSS JOIN` の説明は [クエリ構文  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#cross_join) にゆずります。

```sql
SELECT
  ep.value.string_value AS page_location,
  COUNT(user_pseudo_id) AS pv,
  COUNT(DISTINCT user_pseudo_id) AS uu,
  COUNT(user_pseudo_id)/COUNT(DISTINCT user_pseudo_id) AS pv_per_uu
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
CROSS JOIN UNNEST(event_params) AS ep
  -- `CROSS JOIN` は `,` に置き換え可能、テーブルに対する `AS` も省略可能なので
  -- `, UNNEST(event_params) ep` とも書けます
WHERE
  event_name = 'page_view'
  AND ep.key = 'page_location' AND CONTAINS_SUBSTR(ep.value.string_value, 'Apparel') -- page_location に Apparel という文字列を含むものに絞る
GROUP BY
  page_location
ORDER BY
  pv DESC
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/304ad9d8-775e-3ffc-d8f7-3dbaf26603f1.png)

# よく使う、クエリの可読性を上げる方法

## 一時テーブルを活用する
先ほど紹介した `CROSS JOIN` では一度に1つの条件で絞り込むことしかできませんので、多くの場合は `UNNEST` 方式を取ることになるでしょう。
ですが、実務の中で `event_params` を取り出して更に条件を入れて他のテーブルと結合して…といったことをやると、クエリがどんどん長く複雑になっていきます。

そんなときには `WITH AS` 句を使いましょう。

以下のクエリでは、
- まず `t1` として `page_view` イベントに絞り込んだあと、 `event_params` のフィールドを取り出しています
- 次に `t2` として `t1` テーブルから `page_location` が `Apparel` の文字列を含みかつ `engagement_time_msec（ページ滞在時間）` が `10より大きい` データのみ残しています
- 最後に `t2` のデータを `page_location` でグルーピングして `page_view` の総数を出しています

```sql
WITH
  t1 AS(
  SELECT
    event_timestamp,
    user_pseudo_id,
    (
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = 'page_location') AS page_location,
    (
    SELECT
      value.int_value
    FROM
      UNNEST(event_params)
    WHERE
      key = 'engagement_time_msec') AS engagement_time_msec
  FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
  WHERE
    event_name = 'page_view' ),
  t2 AS (
  SELECT
    *
  FROM
    t1
  WHERE
    CONTAINS_SUBSTR(page_location, 'Apparel')
    AND engagement_time_msec > 10 )
SELECT
  page_location,
  COUNT(user_pseudo_id) AS pv
FROM
  t2
GROUP BY
  page_location
ORDER BY
  pv DESC
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/7557a90e-baf9-47f3-5c09-827c92874707.png)

正直、これくらいの変換であればこんなに何段階も一時テーブルを作る必要はないのですが、 `WITH AS` による定義は前に定義したテーブル（ `t2` にとっての `t1` ）も使っていけるということを説明するためこのようにしてみました。

## ユーザー定義関数を使って可読性を高めミスを減らす

最後に、同じような処理を何度も書きたくないのでユーザー定義関数も使っていきます。

### `event_params` を関数で展開してみる

`event_params` の処理を関数にしてみました。コード全体の分量が増えていますし、数値系の型が失われているのでこれだけだと微妙かもしれませんが、メインのクエリ部分でやりたいことは読み取りやすくなったのではないでしょうか。

```sql
CREATE TEMP FUNCTION StringFromEventParams(event_params ANY TYPE, key_name STRING)
RETURNS STRING
AS 
    ((
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = key_name));

CREATE TEMP FUNCTION NumberFromEventParams(event_params ANY TYPE, key_name STRING)
-- RETURNS の型は省略できる
AS 
    ((
    SELECT
      COALESCE(value.int_value, value.float_value, value.double_value) -- COALESCE は配列内の最初の非 null 値を取得するもの。int, float, doubleは共通のスーパータイプに変換可能なのでできる（stringは含められない）
    FROM
      UNNEST(event_params)
    WHERE
      key = key_name));

SELECT
  StringFromEventParams(event_params, 'page_location') as page_location,
  NumberFromEventParams(event_params, 'engagement_time_msec') as engagement_time_msec
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
LIMIT 10
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/e8da6ee4-e228-b54d-4dbd-e66a48b2f185.png)

### URL を関数でカテゴリに分類してみる

`page_location` は様々なクエリがついていてそのままでは扱いにくいことがあります。
ページをカテゴリごとにまとめるのに関数を使うとクエリがスッキリします。

```sql
CREATE TEMP FUNCTION
  StringFromEventParams(event_params ANY TYPE,
    key_name STRING)
  RETURNS STRING AS ((
    SELECT
      value.string_value
    FROM
      UNNEST(event_params)
    WHERE
      key = key_name));
CREATE TEMP FUNCTION
  GetCategoryNameFromPath(path STRING)
  RETURNS STRING AS (
    CASE
      WHEN REGEXP_CONTAINS(path, r'^https://www.googlemerchandisestore.com/$') THEN 'トップ'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/\?*$') THEN 'ショップトップ'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/myaccount.html\?*$') THEN 'マイアカウント'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/register.html\?*$') THEN '登録'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/registersuccess.html\?*$') THEN '登録成功'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/revieworder.html\?*$') THEN 'レビューオーダー'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/store.html\?*$') THEN 'ストア'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/basket.html\?*$') THEN 'バスケット'
      WHEN REGEXP_CONTAINS(path, r'^https://shop.googlemerchandisestore.com/Google\+Redesign/') THEN 'Google Redesign' -- 以下略！
      WHEN path = '' THEN 'なし'
      WHEN path IS NULL THEN 'なし'
    ELSE
    'その他'
  END
    );
SELECT
  GetCategoryNameFromPath(StringFromEventParams(event_params,
      'page_location')) AS page_location_category,
  GetCategoryNameFromPath(StringFromEventParams(event_params,
      'page_referrer')) AS page_referrer_category,
  COUNT(user_pseudo_id) AS pv
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE
  event_name = 'page_view'
GROUP BY
  page_location_category,
  page_referrer_category
ORDER BY
  pv DESC
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/e780f083-ba23-3c33-5e77-f114e58cdb20.png)

ちなみに、関数内の `ELSE path` 部分を以下のように書き換えると、機能追加により新たな `path` が観測されたときなどに、以下のように実行時エラーになってくれますので、重要なページが「その他」として埋もれてしまうのを防ぐのに役立ちます。

```sql
ELSE ERROR(CONCAT('Found unexpected path: ', path))
```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/35965/2cf3d75e-a8ea-2767-5430-fd8cf1240425.png)

`TEMP` を省略することで一度定義したユーザー定義関数を永続的に使えるようプロジェクトに定義してしまうこともできます。
無闇に設定するのは危なそうですが、自分しか使わないプロジェクトなどでは有効活用できそうです。
記事の初めに書いた「ユーザーの閲覧環境をまとめる `CASE` 文」など、あまり頻繁に変更しないであろうセグメント分けなどは永続関数にしてしまってもいいかもしれませんね。

参考: [ユーザー定義の関数  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/reference/standard-sql/user-defined-functions)

注意として、ユーザー定義関数は `SELECT` 句の中でしか使えないため、変数定義的に扱うのは（ `WHERE` 句で使うシチュエーションの方が多そうなので）少し難しそうです。そういった縛りはありますが、うまく使えれば大きな武器になってくれるでしょう。
また、ユーザー定義関数は JavaScript でも書けるようですが、さすがにそこまで使う必要はないかな…と感じています。 JavaScript が得意な方は見てみるといいかもしれません。

## まとめ
よく使う記法や覚えておきたい基本を雑多にまとめてみました。
特におすすめなのは最後のユーザー定義関数です。関数に切り出すことでクエリのネストを減らすことができ、コードの本筋を追いやすくなります。同じような変換を複数のフィールドに対して行う際にも、どれかひとつだけ間違った変換をかけてしまうような凡ミスを防げるでしょう。
あまり入り組んだことをやりすぎると一気に難読クエリになりそうなのでそこは要注意です。
他にも便利な記法があれば教えてください！