データ分析入門者のためのBigQuery50本ノック(中級編)

:::note info
この記事はLITALICO Advent Calendar 2022のカレンダー2の4日目の記事です。
https://qiita.com/advent-calendar/2022/litalico
:::

# はじめに

今回もBigQuery50本ノック書いていこうと思います。

初級編の記事をご覧になっていない方はそちらもご参照ください。

https://qiita.com/ti_aiuto/items/638595a148e7d16dd70f

中級編では、関数の戻り値のデータ型やテーブル・クエリの結果のスキーマを意識するのがポイントになってくると思います。
また、知っておくと便利なテクニック・関数や、データの前処理が済んでいない場合の簡単な処理方法も紹介していきます。

# 中級編

## 問題

### ここで使うテーブル

`aiuto-public.knock2022_simple_ec.access_log_transform_日付` 整形済みアクセスログテーブル 

|time<br>アクセス日時<br>TIMESTAMP|date_jst<br>アクセス日<br>DATE|path<br>HTTPリクエストのパス<br>STRING|method<br>HTTPリクエストのメソッド<br>STRING|status_code<br>HTTPレスポンスのステータスコード<br>STRING|request_id<br>リクエストに一意なID<br>STRING|user_id<br>会員ID<br>INT64|
|:----|:----|:----|:----|:----|:----|:----|
|2022-11-10 22:56:00 UTC|2022-11-11|/ranking|GET|200|6924bb32-f1cd-4f7a-a585-4446a8f62d82|200004|
|...|...|...|...|...|...|...|

`aiuto-public.knock2022_simple_ec.orders` 注文テーブル

|id<br>注文id<br>INT64|user_id<br>会員ID<br>INT64|ordered_at<br>注文日時<br>TIMESTAMP|total<br>合計額<br>INT64|
|:----|:----|:----|:----|
|5001|200001|2022-10-03 15:58:12 UTC|1984|
|...|...|...|...|

`aiuto-public.knock2022_simple_ec.order_statements` 注文明細行テーブル

|order_id<br>注文id<br>INT64|item_id<br>商品ID<br>INT64|quantity<br>個数<br>INT64|
|:----|:----|:----|
|5002|10001|1|
|...|...|...|

### 24. [日付] 日付から文字列に変換する

`整形済みアクセスログ` テーブルの全ての日付のテーブルについて、 `パス` の列を取得して、さらにアクセス日を日本語表記 `20xx年xx月xx日` にした列 `date_jst_jpn` を追加してください。

なお日本語表記のアクセス日の出力の際は、 `アクセス日時` ( `TIMESTAMP` 型)の列を使うのではなく、 `アクセス日` ( `DATE` 型)の列を使ってみてください。

|path|date_jst_jpn|
|:----|:----|
|...|...|

[ヒント]()
[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ja#format_date)

### 25. [日付] 日付と文字列を比較する

(24)の結果について、さらにアクセス日が2022年10月9日以前の行だけに絞り込んでください。

なおアクセス日の条件は、 `アクセス日時` ( `TIMESTAMP` 型)の列を使うのではなく、 `アクセス日` ( `DATE` 型)の列を使ってみてください。

|path|date_jst_jpn|
|:----|:----|
|...|...|

### 26. [日付] タイムスタンプから日付に変換する

`注文` テーブルからすべての行と列を取得し、さらに `注文日時` を日本時間の日付として解釈した「注文日」の列を `ordered_on_jst` という名前で加えてください。

なお、日付の列は `FORMAT_TIMESTAMP` で `STRING` 型の日付表記にするのではなく、 `DATE` 型で出力されるようにしてください。

|id|user_id|ordered_at|total|ordered_on_jst|
|:----|:----|:----|:----|:----|
|...|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions?hl=ja#date)

### 27. [サブクエリ] クエリ結果に対するクエリ

(26)のクエリ結果自体を取得元として、注文日が2022年10月9日以前の注文に絞り込んでください。

|id|user_id|ordered_at|total|ordered_on_jst|
|:----|:----|:----|:----|:----|
|...|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries?hl=ja#table_subquery_concepts)

### 28. [サブクエリ] WITH句の活用

(27)のクエリのサブクエリ部分に `orders_with_ordered_on` というエイリアスを付けて `WITH` 句を使って書いてみてください。
結果は(27)と同じです。

|id|user_id|ordered_at|total|ordered_on_jst|
|:----|:----|:----|:----|:----|
|...|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#with_clause)

### 29. [集計] SELECT句で追加した列での集計

(26)と同様に `注文` テーブルに注文日の列 `ordered_on_jst` を追加した上で、注文日ごとの全注文の合計額を `sum_total_by_ordered_on` という名前で集計してください。

なお、今回はサブクエリや `WITH` 句を使わないようしてください。

|ordered_on_jst|sum_total_by_ordered_on|
|:----|:----|
|...|...|

### 30. [サブクエリ] クエリ結果に含まれる列を条件として使う

`整形済みアクセスログ` テーブルの2022年10月10日のアクセスログについて、 `パス` が `/items/10001` のページを閲覧した会員が、他にどんなページを閲覧しているのかを`パス` ごとにカウントしてください。
カウントの列は `pv_count` という名前にして、結果は `パス` の昇順にしてください。

考え方：該当会員のアクセス（パスが `/items/10001` かつ `user_id` が `NULL` でない行）の `user_id` を抽出した上で、その結果を絞り込み条件として使う。

|path|pv_count|
|:----|:----|
|/|1|
|/cart|330|
|...|...|
|/items/10003|26|
|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries?hl=ja#in_subquery_concepts)

### 31. [サブクエリ] サブクエリ内で外側のクエリの列を条件にする

(30)と同じ分析を、相関サブクエリ（サブクエリ内の条件で外側のクエリの列を使う）を使って書いてみてください。

考え方： 「特定のパスにアクセスしたことがある会員のアクセス」という条件を、「その行の会員IDについて、その会員IDで特定のパスにアクセスした行が存在する、ような行」と表現します。

|path|pv_count|
|:----|:----|
|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries?hl=ja#exists_subquery_concepts)

### 32. [集計] 特定の条件に合致するものをカウントする

`注文` テーブルについて、次の3つの値を集計してください。

* 全体の注文数 `orders_count`
* `合計` が5000円以上の注文数 `orders_5000yen_or_more_count`
* `合計` が5000円未満の注文数 `orders_under_5000yen_count`

|orders_count|orders_5000yen_or_more_count|orders_under_5000yen_count|
|:----|:----|:----|
|1892|708|1184|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions?hl=ja#count)

### 33. [集計] 特定の条件に合致する場合に特定の列を合計する

`注文` テーブルについて、次の3つの値を集計してください。

* 全体の注文合計額 `orders_sum_total`
* `合計` が5000円以上の注文の合計額 `orders_5000yen_or_more_sum_total`
* `合計` が5000円未満の注文の合計額 `orders_under_5000yen_sum_total`

|orders_sum_total|orders_5000yen_or_more_sum_total|orders_under_5000yen_sum_total|
|:----|:----|:----|
|8004899|4947805|3057094|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate_functions?hl=ja#sum)
[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions?hl=ja#if)

### 34. [ウィンドウ関数] 特定の列を使って、特定の列ごとに順番を振る

`注文` テーブルの各注文について全ての列を取得し、さらにその注文がその `会員ID` での何回目の注文なのかを示す列を `order_index_by_user` という名前で追加してください。
結果は `会員ID` の昇順・ `order_index_by_user` の昇順になるようにしてください。

なお、最も `注文日時` が古い注文を1回目として、各ユーザ内で注文日時の重複は考えないものとします。

|id|user_id|ordered_at|total|order_index_by_user|
|:----|:----|:----|:----|:----|
|5001|200001|2022-10-03 15:58:12 UTC|1984|1|
|5002|200001|2022-10-03 21:26:49 UTC|1806|2|
|...|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions?hl=ja#row_number)

### 35. [ウィンドウ関数] 特定の列を使って順位を振る

`注文明細` テーブルについて、 `商品ID` 別に `個数` の合計を集計した上で、注文個数の合計が多い順に順位をつけて、順位の高い順に並び替えてください。
注文個数合計の列には `sum_quantity_by_item_id` の名前を、順位の列には `rank_by_quantity_desc` の名前をつけてください。

`商品ID` 別の注文個数の集計を先に行い、WITH句で `aggregated_by_item_id` のような名前で定義しておくと便利かもしれません。

なお同率の場合は「1位・1位・3位」のように同率の次の順位の数字は飛ぶようにしてください。

|item_id|sum_quantity_by_item_id|rank_by_quantity_desc|
|:----|:----|:----|
|10001|4955|1|
|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/numbering_functions?hl=ja#rank)

### 36. [条件式] 条件と値の対応を複数書き並べる

`注文` テーブルの全ての行と列を取得した上で、 `注文額` に応じた分類を表す列 `segment_by_total` を追加してください。 

注文額に応じた分類の方法

|注文額|値|
|:----|:----|
|0円以上999円以下|0円～999円|
|1000円以上9999円以下|1000～9999円|
|10000円以上|10000円～|

出力イメージ

|id|user_id|ordered_at|total|segment_by_total|
|:----|:----|:----|:----|:----|
|5001|200001|2022-10-03 15:58:12 UTC|1984|1000～9999円|
|...|...|...|...|...|
|5003|200001|2022-10-04 04:20:01 UTC|895|0円～999円|
|...|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/conditional_expressions?hl=ja#case)

### 37. [PIVOT] 簡単なクロス集計をする

`注文` テーブルについて、(26)を参考に注文日(日本時間)の列を追加した上で、縦軸を注文日(日本時間)、横軸を10001から10003までの `商品ID` として、注文日・ `商品ID` 別の注文数の合計をクロス集計してください。
注文日の列名は `ordered_on_jst` 、 `商品ID` の列は `item_10001` ... `item_10003` とし、結果は注文日の昇順で並び替えてください。

集計に必要な列（ `注文日` ・ `数量` ・ `商品ID` ）だけを出力するクエリを `WITH` 句を使って `order_with_statements` のような名前で定義しておくと便利かもしれません。

|ordered_on_jst|item_10001|item_10002|item_10003|
|:----|:----|:----|:----|
|2022-10-01|47|28|85|
|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#pivot_operator)

（この問題だけ見慣れない機能を使っていて難易度が高いというか仲間外れに見えますが、使えるとJOIN祭りを回避できて地味に便利なので入れてみました。「BigQuery pivot」などで色々ググってみてください。）

### ここから使うテーブル

最後に、サーバから送られた直後の生のログデータに対して前処理を行いたいときや、前処理を待たずにすぐにクエリを書きたいときを想定した問題をご用意しました。

`aiuto-public.knock2022_simple_ec.access_log_raw_日付` 未整形アクセスログテーブル 

|time<br>アクセス日時<br>INT64|message<br>ログ情報<br>STRING|
|:----|:----|
|1668353024|{"path":"/cart","method":"POST","status_code":"302","request_id":"879884aa-7875-4d23-8b07-d2846c7858f4","user_id":"200286"}|
|...|...|

`aiuto-public.knock2022_simple_ec.users` 会員テーブル

|id<br>会員id<br>INT64|registered_at<br>仮登録完了日時<br>TIMESTAMP|confirmed_at<br> 本登録完了日時<br>TIMESTAMP|
|:----|:----|:----|
|200477|2022-10-17 23:09:22 UTC|2022-10-23 23:57:24 UTC|
|...|...|...|


「JSON」や「UNIXTIME」といった用語に馴染みのない方は、適宜次のようなページもご参照ください。

https://cloudapi.kddi-web.com/magazine/json-javascript-object-notation

https://ja.wikipedia.org/wiki/UNIX%E6%99%82%E9%96%93

### 38. [JSON] スカラ値を抜き出す

`未整形アクセスログ` テーブルの2022年10月10日分の全ての行について、 `message` 列のJSON形式の文字列から、 `path` `method` `status_code` `user_id` のキーを、それぞれ列として抽出してください。

|path|method|status_code|user_id|
|:----|:----|:----|:----|
|/items/10004|GET|200|200486|
|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions?hl=ja#json_value)

### 39. [タイムスタンプ] UNIXTIMEをタイムスタンプに変換する

(38)の結果について、さらに `time` 列の値を `TIMESTAMP` 型に変換して、一番左の列として追加してください。

|time|path|method|status_code|user_id|
|:----|:----|:----|:----|:----|
|2022-10-10 14:19:44 UTC|/items/10004|GET|200|200486|
|...|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions?hl=ja#timestamp_seconds)

### 40. [CAST] 文字列から整数に変換する

(39)の結果に、JSONから抽出した列の `user_id` と `会員` テーブルの `会員ID` を突き合わせて左外部結合を行い、 `仮登録完了日時` と `本登録完了日時` の列を結果に追加してください。

なお、 `user_id` の列は　`STRING` 型ではなく `INT64` 型で出力されるようにしてください。

|time|path|method|status_code|user_id<br>INT64|registered_at|confirmed_at|
|:----|:----|:----|:----|:----|:----|:----|
|2022-10-10 14:19:44 UTC|/items/10004|GET|200|200486|2022-10-12 06:38:53 UTC||
|...|...|...|...|...|...|...|
|2022-10-09 23:19:31 UTC|/items|GET|200||||
|...|...|...|...|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions?hl=ja#cast_as_integer)

## 解答例

### 24. [日付] 日付から文字列に変換する

ポイント：日付の場合は、 `DATE` 型になっている時点でタイムゾーンが考慮されているため、文字列に変換するときにはタイムゾーンの指定は必要ありません。

```SQL
SELECT
  path,
  FORMAT_DATE("%Y年%m月%d日", date_jst) date_jst_jpn
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_*`
```

### 25. [日付] 日付と文字列を比較する

ポイント： `DATE` 型の値について条件を書くときは日付の文字列と直接比較するのが便利です。
※`TIMESTAMP` 型の場合でも文字列と直接比較はできますが、時差を考慮したいため `TIMESTAMP(文字列, "Asia/Tokyo")` とするのが便利です。

```SQL
SELECT
  path,
  FORMAT_DATE("%Y年%m月%d日", date_jst) date_jst_jpn
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_*`
WHERE
  date_jst <= "2022-10-09"
```

### 26. [日付] タイムスタンプから日付に変換する

```SQL
SELECT
  *,
  DATE(ordered_at, 'Asia/Tokyo') ordered_on_jst
FROM
  `aiuto-public.knock2022_simple_ec.orders`
```

### 27. [サブクエリ] クエリ結果に対するクエリ

ポイント：(26)のクエリを丸ごと `FROM` に指定すればOKです。

```SQL
SELECT
  *
FROM (
  SELECT
    *,
    DATE(ordered_at, 'Asia/Tokyo') ordered_on_jst
  FROM
    `aiuto-public.knock2022_simple_ec.orders` )
WHERE
  ordered_on_jst <= "2022-10-09"
```

### 28. [サブクエリ] WITH句の活用

ポイント：サブクエリにしていた部分をあたかも元々テーブルやビューが定義されていたかのように書くことができるので、うまく使えば可読性を上げることができます。

```SQL
WITH
  orders_with_ordered_on AS (
  SELECT
    *,
    DATE(ordered_at, 'Asia/Tokyo') ordered_on_jst
  FROM
    `aiuto-public.knock2022_simple_ec.orders`)
SELECT
  *
FROM
  orders_with_ordered_on
WHERE
  ordered_on_jst <= "2022-10-09"
```

### 29. [集計] SELECT句で追加した列での集計

```SQL
SELECT
  DATE(ordered_at, 'Asia/Tokyo') ordered_on_jst,
  SUM(total) sum_total_by_ordered_on
FROM
  `aiuto-public.knock2022_simple_ec.orders`
GROUP BY
  ordered_on_jst
```

### 30. [サブクエリ] クエリ結果に含まれる列を条件として使う


```SQL
SELECT
  path,
  COUNT(1) pv_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`
WHERE
  user_id IN (
  SELECT
    user_id
  FROM
    `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`
  WHERE
    path = "/items/10001"
    AND user_id IS NOT NULL )
GROUP BY
  path
ORDER BY
  path
```

### 31. [サブクエリ] サブクエリ内で外側のクエリの列を条件にする

ポイント： `EXISTS` 演算子の中で選択する列はどれでも問題ないです

```SQL
SELECT
  path,
  COUNT(1) pv_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010` access_log
WHERE
  user_id IS NOT NULL
  AND EXISTS (
  SELECT
    1
  FROM
    `aiuto-public.knock2022_simple_ec.access_log_transform_20221010` access_log_inner
  WHERE
    path = "/items/10001"
    AND access_log_inner.user_id = access_log.user_id)
GROUP BY
  path
ORDER BY
  path
```

### 32. [集計] 特定の条件に合致するものをカウントする

ポイント：条件のあとに `OR NULL` を付ける必要があります。（そうでないと条件に合致しない場合の `FASLE` がカウントされてしまいます）

```SQL
SELECT
  COUNT(*) orders_count,
  COUNT(total >= 5000
    OR NULL) orders_5000yen_or_more_count,
  COUNT(total < 5000
    OR NULL) orders_under_5000yen_count
FROM
  `aiuto-public.knock2022_simple_ec.orders`
```

### 33. [集計] 特定の条件に合致する場合に特定の列を合計する

ポイント：計算から除きたい場合は `NULL` を渡せばOKです

```SQL
SELECT
  SUM(total) orders_sum_total,
  SUM(
  IF
    (total >= 5000, total, NULL)) orders_5000yen_or_more_sum_total,
  SUM(
  IF
    (total < 5000, total, NULL)) orders_under_5000yen_sum_total
FROM
  `aiuto-public.knock2022_simple_ec.orders`
```


### 34. [ウィンドウ関数] 特定の列を使って、特定の列ごとに順番を振る

```SQL
SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY ordered_at) order_index_by_user
FROM
  `aiuto-public.knock2022_simple_ec.orders`
ORDER BY
  user_id,
  order_index_by_user
```


### 35. [ウィンドウ関数] 特定の列を使って順位を振る

```SQL
WITH
  aggregated_by_item_id AS (
  SELECT
    item_id,
    SUM(quantity) sum_quantity_by_item_id
  FROM
    `aiuto-public.knock2022_simple_ec.order_statements`
  GROUP BY
    item_id )
SELECT
  item_id,
  sum_quantity_by_item_id,
  RANK() OVER (ORDER BY sum_quantity_by_item_id DESC) rank_by_quantity_desc
FROM
  aggregated_by_item_id
ORDER BY
  rank_by_quantity_desc
```

### 36. [条件式] 条件と値の対応を複数書き並べる

```SQL
SELECT
  *,
  CASE
    WHEN total BETWEEN 0 AND 999 THEN '0円～999円'
    WHEN total BETWEEN 1000
  AND 9999 THEN '1000～9999円'
  ELSE
  '10000円～'
END
  segment_by_total
FROM
  `aiuto-public.knock2022_simple_ec.orders`
```

（改行位置はBigQueryのフォーマット機能に任せているのですがこれはなんだか美しくないですね。。

### 37. [PIVOT] 簡単なクロス集計をする

ポイント： `PIVOT` 演算子を使う前に、集計に必要な列だけに限定しておきます

```SQL
WITH
  order_with_statements AS (
  SELECT
    DATE(ordered_at, "Asia/Tokyo") ordered_on_jst,
    item_id,
    quantity
  FROM
    `aiuto-public.knock2022_simple_ec.orders` orders
  JOIN
    `aiuto-public.knock2022_simple_ec.order_statements` order_statements
  ON
    orders.id = order_statements.order_id )
SELECT
  *
FROM
  order_with_statements PIVOT ( SUM(quantity) FOR item_id IN(10001 AS item_10001,
      10002 AS item_10002,
      10003 AS item_10003) )
ORDER BY
  ordered_on_jst
```

### 38. [JSON] スカラ値を抜き出す

```SQL
SELECT
  JSON_VALUE(message, "$.path") path,
  JSON_VALUE(message, "$.method") method,
  JSON_VALUE(message, "$.status_code") status_code,
  JSON_VALUE(message, "$.user_id") user_id
FROM
  `aiuto-public.knock2022_simple_ec.access_log_raw_20221010`
```

### 39. [タイムスタンプ] UNIXTIMEをタイムスタンプに変換する

```SQL
SELECT
  TIMESTAMP_SECONDS(time) time,
  JSON_VALUE(message, "$.path") path,
  JSON_VALUE(message, "$.method") method,
  JSON_VALUE(message, "$.status_code") status_code,
  JSON_VALUE(message, "$.user_id") user_id
FROM
  `aiuto-public.knock2022_simple_ec.access_log_raw_20221010`
```

### 40. [CAST] 文字列から整数に変換する

ポイント： `user_id` 列が `STRING` 型のままだと結合できないので、 `INT64` にキャストして型を合わせます。

```SQL
WITH
  formatted AS (
  SELECT
    TIMESTAMP_SECONDS(time) time,
    JSON_VALUE(message, "$.path") path,
    JSON_VALUE(message, "$.method") method,
    JSON_VALUE(message, "$.status_code") status_code,
    CAST(JSON_VALUE(message, "$.user_id") AS INT64 ) user_id
  FROM
    `aiuto-public.knock2022_simple_ec.access_log_raw_20221010`)
SELECT
  formatted.*,
  registered_at,
  confirmed_at
FROM
  formatted
LEFT JOIN
  `aiuto-public.knock2022_simple_ec.users` users
ON
  formatted.user_id = users.id
```


# 実践編へ続く

以上、中級編でした。
次回の実践編では、初級編と中級編で触れた内容をもとに、実業務で依頼されそうな分析を想定したクエリを書いていきます。

https://qiita.com/ti_aiuto/items/fbb1b655b89f41e777fc