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

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

# はじめに

今回がBigQuery50本ノック最後の記事になります。

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

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

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

実践編では、実業務の中で他チームに依頼されるような内容だったり、自分でデータを見たいときに書きそうなクエリだったりを題材にしたいと思います。

一部こじつけのような問題や、「それはBigQuery上ではやらんだろ！」という問題もある気がしますが、パズルみたいなものだと思ってお付き合いいただければと思います。

# 実践編

## 問題

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

使うテーブルは初級編・中級編で使ったものと同じです。

`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.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|
|...|...|...|

`aiuto-public.knock2022_simple_ec.items` 商品テーブル

|id<br>商品id<br>INT64|name<br>商品名<br>STRING|price<br>価格<br>INT64|stock<br>在庫数<br>INT64|
|:----|:----|:----|:----|
|1|ベーコン|299|12|
|...|...|...|...|

`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|
|...|...|...|

### 41. [集計] 日別のアクセス状況を可視化する

`整形済みアクセスログ` テーブルの全ての行を集計して、次の列を出力してください。
並び順は `アクセス日` の昇順にしてください。

|列名|意味|計算方法|
|:----|:----|:----|
|`date_jst`|アクセス日(日本時間)||
|`pv_count`|PV数|全体の行数|
|`signed_in_pv_count`|会員PV数| `会員ID` 列が `NULL` でない行数|
|`signed_in_pv_rate`|会員PVの割合| `会員ID` 列が `NULL` でない行数 / 全体の行数|
|`uu_count`|会員UU| `会員ID` 列の重複を除いた行数|

|date_jst|pv_count|signed_in_pv_count|signed_in_pv_rate|uu_count|
|:----|:----|:----|:----|:----|
|2022-10-01|448|403|0.899...|145|
|...|...|...|...|...|

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

### 42. [正規表現] 第一階層別のアクセス状況を可視化する

(41)の回答も参考に、 `整形済みアクセスログ` テーブルの全ての行を集計して、次の列を出力してください。
並び順は `first_dir` の昇順にしてください。

|列名|意味|計算方法|
|:----|:----|:----|
|`first_dir`|第一階層|`パス` の最初から2個目の `/` の直前まで（2個目の `/` がなければ最初から最後まで）を抜き出す<br>例： `/items/10001` なら `/items` を出力|
|`pv_count`|PV数|全体の行数|
|`signed_in_pv_count`|会員PV数| `会員ID` 列が `NULL` でない行数|
|`signed_in_pv_rate`|会員PVの割合| `会員ID` 列が `NULL` でない行数 / 全体の行数|
|`uu_count`|ユニーク会員数| `会員ID` 列の重複を除いた行数|

出力イメージ

|first_dir|pv_count|signed_in_pv_count|signed_in_pv_rate|uu_count|
|:----|:----|:----|:----|:----|
|/|486|238|0.489...|188|
|/cart|10321|10321|1.0|378|
|...|...|...|...|...|

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

### 43. [集計] 日別の注文状況を可視化する

`注文` テーブルの全ての行を集計して、次の列を出力してください。
並び順は注文日の昇順にしてください。

|列名|意味|計算方法|
|:----|:----|:----|
|`ordered_on_jst`|注文日(日本時間)| `注文日時` 列を日本時間の日付として解釈する|
|`orders_count`|注文数|全体の行数|
|`uu_count`|ユニーク会員数| `会員ID` 列の重複を除いた行数|
|`sum_total`|注文額合計|`合計額` 列の合計|

出力イメージ

|ordered_on_jst|orders_count|uu_count|sum_total|
|:----|:----|:----|:----|
|2022-10-04|56|45|218604|
|...|...|...|...|...|

### 44. [集計] 本登録完了までにかかる日数を調べる

`会員` テーブルの全ての行を集計して、次の列を出力してください。
並び順は経過日数の昇順にしてください。

|列名|意味|計算方法|
|:----|:----|:----|
|`days_count`|仮登録完了～本登録完了までの経過日数| `本登録完了日時` と `仮登録完了日時` をそれぞれ日本時間の日付として解釈したときの日数の差<br>時間に関わらず、当日なら0、翌日なら1、のように計算する|
|`users_count`|会員数|その日数で登録完了した会員数|

出力イメージ

|days_count|users_count|
|:----|:----|
|0|336|
|...|...|

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

### 45. [サブクエリ] 〇〇を買った人はこんな商品を買っています

`注文` テーブルと `注文明細行` テーブルを結合して、 `商品ID` が `10001` の商品を注文した会員が過去・未来問わず注文したことのある商品を、注文数量の合計が多い順に並び替えてください。

`注文明細行` テーブルと `注文` テーブルを結合したものを、 `orde_statements_with_order` などの名前をつけて `WITH` 句で定義しておくと便利かもしれません。

出力イメージ

|item_id|sum_quantity|
|:----|:----|
|10001|4955|
|10009|4622|
|10005|4539|
|...|...|

### 46. [PIVOT] 注文日・注文額セグメントごとの集計

`注文` テーブルについて、縦方向を `注文日時` を日本時間の日付として解釈した値、横方向を `合計額` 列の値に応じて次のように分類した値（「注文額セグメント」）として、注文日・注文額セグメントごとの注文件数をクロス集計表の形で出力してください。
なお、各行の並び順は注文日の昇順にしてください。


|条件|列名|
|:----|:----|
|0円～999円| `range_0_999` |
|1000円～9999円| `range_1000_9999` |
|10000円～| `range_10000_or_more` |

出力イメージ

|ordered_on_jst|range_0_999|range_1000_9999|range_10000_or_more|
|:----|:----|:----|:----|
|2022-10-01|0|18|0|
|...|...|...|...|
|2022-10-07|12|48|2|
|...|...|...|...|

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

### 47. [ウィンドウ関数] 日別の本会員登録数と累積会員数

`会員` テーブルの本登録完了した会員（ `本登録完了日時` が `NULL` でない行）について、次の列を出力してください。
並び順は本登録完了日の昇順にしてください。

|列名|意味|計算方法|
|:----|:----|:----|
|`confirmed_on_jst`|本登録完了日(日本時間)| `本登録完了日時` を日本時間の日付として解釈する|
|`users_count`|本会員登録数|行数|
|`accumulated_users_count`|累積本会員数|本登録完了日順に並べたときの、最初の行から現在の行まで( `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` )の合計|

出力イメージ

|confirmed_on_jst|users_count|accumulated_users_count|
|:----|:----|:----|
|2022-10-01|17|17|
|2022-10-02|23|40|
|2022-10-03|17|57|
|...|...|...|

[ヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls?hl=ja#compute_a_cumulative_sum)
[ヒント※人様のQiita記事](https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5#rows)

### 48. [ウィンドウ関数] 商品別注文額ランキング

`商品` テーブルと `注文明細行` テーブルを使って、 `商品ID` 別の注文額合計 `sum_line_total` を計算した上で、 注文額合計が高い順に順位をつけてください。
順位の列は `rank_by_sum_desc` という名前をつけて、結果は順位の高い順に並び替えてください。

なお、同率の場合は1位、1位、3位のように同率の次の順位が飛ぶものとします。

|item_id|sum_line_total|rank_by_sum_desc|
|:----|:----|:----|
|10004|1419353|1|
|...|...|...|
|10010|450450|10

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

### 49. [ウィンドウ関数] 流入ページの調査

その閲覧者が最初に閲覧した（つまりアクセス日時が1番目の）ページを「流入ページ」と呼ぶものとします。
`整形済みアクセスログ` テーブルの2022年10月10日分の `会員ID` が `NULL` でない行について、さらに「流入ページ」に絞り込んだ上で（つまり各 `会員ID` についてアクセス日時が1番目のアクセスに絞り込んだ上で）、 `パス` ごとの行数 `pv_count` を集計してください。
並び順は `pv_count` の降順にしてください。

|path|pv_count|
|:----|:----|
|/signup|20|
|/items/10010|19|
|/items/10007|18|
|...|...|

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

### 50. [サブクエリ or 結合 or ウィンドウ関数] 2日連続で商品を注文した注文数・ユーザ数

`注文` テーブルに注文日(日本時間)の列を加えた上で、各注文について、2日連続の注文（つまり、同じ `会員ID` の注文内で注文日順に並び替えたときに、一つ前の注文がその注文の前日であるような注文）の数 `two_days_in_row_count` と、重複を除いた `会員ID` の数 `two_days_in_row_uu` を出力してください。

|two_days_in_row_count|two_days_in_row_uu|
|:----|:----|
|949|298|

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

[サブクエリを使う場合のヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries?hl=ja#exists_subquery_concepts)
[サブクエリを使う場合のヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/subqueries?hl=ja#correlated_subquery_concepts)

[ウィンドウ関数を使う場合のヒント](https://cloud.google.com/bigquery/docs/reference/standard-sql/navigation_functions?hl=ja#lag)

## 解答例

### 41. [集計] 日別のアクセス状況を可視化する

```SQL
SELECT
  date_jst,
  COUNT(*) pv_count,
  COUNT(user_id) signed_in_pv_count,
  COUNT(user_id) / COUNT(*) signed_in_pv_rate,
  COUNT(DISTINCT user_id) uu_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_*`
GROUP BY
  date_jst
ORDER BY
  date_jst
```

### 42. [正規表現] 第一階層別のアクセス状況を可視化する

```SQL
SELECT
  REGEXP_EXTRACT(CONCAT(path, '/'), "^(/.*?)/") first_dir,
  COUNT(*) pv_count,
  COUNT(user_id) signed_in_pv_count,
  COUNT(user_id) /COUNT(*) signed_in_ov_rate,
  COUNT(DISTINCT user_id) uu_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_*`
GROUP BY
  first_dir
ORDER BY
  first_dir
```

### 43. [集計] 日別の注文状況を可視化する

```SQL
SELECT
  DATE(ordered_at, "Asia/Tokyo") ordered_on_jst,
  COUNT(*) orders_count,
  COUNT(DISTINCT user_id) uu_count,
  SUM(total) sum_total
FROM
  `aiuto-public.knock2022_simple_ec.orders`
GROUP BY
  ordered_on_jst
```

### 44. [集計] 本登録完了までにかかる日数を調べる

ポイント： `DATE` 型に変換してから比較するか、 `TIMESTAMP_TRUNC` 関数と `TIMESTAMP_DIFF` 関数を組み合わせることもできます

```SQL
SELECT
  DATE_DIFF(DATE(confirmed_at, "Asia/Tokyo"), DATE(registered_at, "Asia/Tokyo"), DAY) days_count,
  COUNT(1) users_count
FROM
  `aiuto-public.knock2022_simple_ec.users`
WHERE
  confirmed_at IS NOT NULL
GROUP BY
  days_count
ORDER BY
  days_count
```

### 45. [サブクエリ] 〇〇を買った人はこんな商品を買っています

```SQL
WITH
  orde_statements_with_order AS (
  SELECT
    *
  FROM
    `aiuto-public.knock2022_simple_ec.order_statements` order_statements
  JOIN
    `aiuto-public.knock2022_simple_ec.orders` orders
  ON
    orders.id = order_statements.order_id)
SELECT
  item_id,
  SUM(quantity) sum_quantity
FROM
  orde_statements_with_order
WHERE
  user_id IN (
  SELECT
    user_id
  FROM
    orde_statements_with_order
  WHERE
    item_id = 10001 )
GROUP BY
  item_id
ORDER BY
  sum_quantity DESC
```

相関サブクエリを使った書き方もできます

```SQL
WITH
  orde_statements_with_order AS (
  SELECT
    *
  FROM
    `aiuto-public.knock2022_simple_ec.order_statements` order_statements
  JOIN
    `aiuto-public.knock2022_simple_ec.orders` orders
  ON
    orders.id = order_statements.order_id)
SELECT
  item_id,
  SUM(quantity) sum_quantity
FROM
  orde_statements_with_order
WHERE
  EXISTS (
  SELECT
    1
  FROM
    orde_statements_with_order statements_inner
  WHERE
    orde_statements_with_order.user_id = statements_inner.user_id
    AND statements_inner.item_id = 10001 )
GROUP BY
  item_id
ORDER BY
  sum_quantity DESC
```


### 46. [PIVOT] 注文日・注文額ごとの集計

ポイント：`CASE` 式の結果を列名にしておくと、 `PIVOT` 演算子を使うときに列名を省略できます（ただし日本語の列名は現時点では使えないのでアルファベット表記です・・・）

```SQL
SELECT
  *
FROM (
  SELECT
    DATE(ordered_at, "Asia/Tokyo") ordered_on_jst,
    CASE
      WHEN total BETWEEN 0 AND 999 THEN 'range_0_999'
      WHEN total BETWEEN 1000
    AND 9999 THEN 'range_1000_9999'
    ELSE
    'range_10000_or_more'
  END
    range_name
  FROM
    `aiuto-public.knock2022_simple_ec.orders`) PIVOT ( COUNT(*) FOR range_name IN ('range_0_999',
      'range_1000_9999',
      'range_10000_or_more') )
ORDER BY
  ordered_on_jst
```


### 47. [ウィンドウ関数] 日別の本会員登録数と累積会員数

ポイント：`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` はデフォルト値なので省略してもOKです

```SQL
WITH
  confirmed_count_by_date AS (
  SELECT
    confirmed_on_jst,
    COUNT(1) users_count,
  FROM (
    SELECT
      DATE(confirmed_at, "Asia/Tokyo") confirmed_on_jst
    FROM
      `aiuto-public.knock2022_simple_ec.users`
    WHERE
      confirmed_at IS NOT NULL )
  GROUP BY
    confirmed_on_jst)
SELECT
  confirmed_on_jst,
  users_count,
  SUM(users_count) OVER(ORDER BY confirmed_on_jst RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) accumulated_users_count
FROM
  confirmed_count_by_date
ORDER BY
  confirmed_on_jst
```

### 48. [ウィンドウ関数] 商品別注文額ランキング

```SQL
WITH
  aggregated AS (
  SELECT
    item_id,
    SUM(quantity * price) sum_line_total
  FROM
    `aiuto-public.knock2022_simple_ec.order_statements` order_statements
  JOIN
    `aiuto-public.knock2022_simple_ec.items` items
  ON
    order_statements.item_id = items.id
  GROUP BY
    item_id)
SELECT
  item_id,
  sum_line_total,
  RANK() OVER (ORDER BY sum_line_total DESC) rank_by_sum_desc
FROM
  aggregated
ORDER BY
  rank_by_sum_desc
```


### 49. [ウィンドウ関数] 流入ページの調査

ポイント：ウィンドウ関数の結果を使って絞り込みたいときはサブクエリが便利です。

```SQL
SELECT
  path,
  COUNT(*) pv_count
FROM (
  SELECT
    path,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time) pv_index_by_user
  FROM
    `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`
  WHERE
    user_id IS NOT NULL )
WHERE
  pv_index_by_user = 1
GROUP BY
  path
ORDER BY
  pv_count DESC
```

### 50. [サブクエリ or 結合 or ウィンドウ関数] 2日連続で商品を注文した注文数・ユーザ数

相関サブクエリを使う場合

```SQL
WITH
  orders_with_date_jst AS (
  SELECT
    *,
    DATE(ordered_at, "Asia/Tokyo") ordered_on_jst
  FROM
    `aiuto-public.knock2022_simple_ec.orders` )
SELECT
  COUNT(1) two_days_in_row_count, 
  COUNT(DISTINCT user_id) two_days_in_row_uu
FROM
  orders_with_date_jst base_orders
WHERE
  EXISTS (
  SELECT
    1
  FROM
    orders_with_date_jst previous_day_orders
  WHERE
    previous_day_orders.user_id = base_orders.user_id
    AND previous_day_orders.ordered_on_jst = DATE_SUB(base_orders.ordered_on_jst,INTERVAL 1 DAY) )
```

結合を使う場合

ポイント：先に `会員ID` ごと・日付ごとに重複を除いておきます

```SQL
WITH
  distinct_ordered_on_by_user_id AS (
  SELECT
    user_id,
    DATE(ordered_at, "Asia/Tokyo") ordered_on_jst,
    DATE_ADD(DATE(ordered_at, "Asia/Tokyo"), INTERVAL 1 DAY) ordered_on_jst_next_day,
    COUNT(1) orders_count
  FROM
    `aiuto-public.knock2022_simple_ec.orders`
  GROUP BY
    user_id,
    ordered_on_jst,
    ordered_on_jst_next_day )
SELECT
  SUM(orders.orders_count) two_days_in_row_count,
  COUNT(DISTINCT orders.user_id) two_days_in_row_uu
FROM
  distinct_ordered_on_by_user_id orders
JOIN
  distinct_ordered_on_by_user_id orders_previous_day
ON
  orders.user_id = orders_previous_day.user_id
  AND orders.ordered_on_jst = orders_previous_day.ordered_on_jst_next_day
```

ウィンドウ関数を使う場合

```SQL
WITH
  distinct_ordered_on_by_user_id AS (
  SELECT
    user_id,
    DATE(ordered_at, "Asia/Tokyo") ordered_on_jst,
    COUNT(1) orders_count
  FROM
    `aiuto-public.knock2022_simple_ec.orders`
  GROUP BY
    user_id,
    ordered_on_jst ),
  with_previous_ordered_on AS (
  SELECT
    user_id,
    ordered_on_jst,
    orders_count,
    LAG(ordered_on_jst) OVER (PARTITION BY user_id ORDER BY ordered_on_jst) AS previous_ordered_on_jst
  FROM
    distinct_ordered_on_by_user_id)
SELECT
  SUM(orders_count) two_days_in_row_count,
  COUNT(DISTINCT user_id )two_days_in_row_uu
FROM
  with_previous_ordered_on
WHERE
  DATE_DIFF(ordered_on_jst, previous_ordered_on_jst, DAY) = 1
```

ちなみに実行統計を見ると次のようになっていました

|クエリ|消費されたスロット時間|シャッフルされたデータ量|
|:----|:----|:----|
|結合|1秒|118.71 KB|
|相関サブクエリ|2秒|59.69 KB|
|ウィンドウ関数|5秒|77.25 KB|

# 最後に

以上で50本ノックは終了となります。お疲れさまでした。

今回は、SQLを触ってみたいという人がSQLでどんなことができるのかイメージできることを目指して問題を作ってみました。
入れるか迷って入れなかった要素としては、例えば配列を生成する関数、配列・構造体に対するクエリ、UDF、ビューやテーブルの作成、実行時パラメータ、レガシーSQLといった部分がありますが、それはまた今後に上級編？か何かとして追加で触れられたらと思っています。

これだけシンプルなテーブル構成でも50問作れたわけですから（終盤はネタ切れになりつつ何とか捻りだした感もありますが。。）、実務の中では文字通り無数のクエリが書けるんだよなと改めて思いました。この記事が今後データ分析に携わりたい方にとって少しでも役に立てば幸いです。

明日は同じチームの先輩で、いつも絶妙なバランス感覚でプロダクト開発・チーム開発を推進してくださっている @H-Asakawa が執筆を担当します。お楽しみに！