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

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

はじめに

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

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

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

中級編

問題

ここで使うテーブル

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

time アクセス日時 TIMESTAMP	date_jst アクセス日 DATE	path HTTPリクエストのパス STRING	method HTTPリクエストのメソッド STRING	status_code HTTPレスポンスのステータスコード STRING	request_id リクエストに一意なID STRING	user_id 会員ID 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 注文id INT64	user_id 会員ID INT64	ordered_at 注文日時 TIMESTAMP	total 合計額 INT64
5001	200001	2022-10-03 15:58:12 UTC	1984
...	...	...	...

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

order_id 注文id INT64	item_id 商品ID INT64	quantity 個数 INT64
5002	10001	1
...	...	...

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

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

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

path	date_jst_jpn
...	...

ヒント
ヒント

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

ヒント

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

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

id	user_id	ordered_at	total	ordered_on_jst
...	...	...	...	...

ヒント

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

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

id	user_id	ordered_at	total	ordered_on_jst
...	...	...	...	...

ヒント

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

ヒント

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

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

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

path	pv_count
...	...

ヒント

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

ヒント

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

ヒント
ヒント

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

ヒント

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

ヒント

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

ヒント

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

ヒント

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

ここから使うテーブル

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

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

time アクセス日時 INT64	message ログ情報 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 会員id INT64	registered_at 仮登録完了日時 TIMESTAMP	confirmed_at 本登録完了日時 TIMESTAMP
200477	2022-10-17 23:09:22 UTC	2022-10-23 23:57:24 UTC
...	...	...

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

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

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

path	method	status_code	user_id
/items/10004	GET	200	200486
...	...	...	...

ヒント

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

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

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

ヒント

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

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

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

time	path	method	status_code	user_id 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
...	...	...	...	...	...	...

ヒント

解答例

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

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

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") とするのが便利です。

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. [日付] タイムスタンプから日付に変換する

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

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

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

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句の活用

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

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句で追加した列での集計

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. [サブクエリ] クエリ結果に含まれる列を条件として使う

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 演算子の中で選択する列はどれでも問題ないです

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 がカウントされてしまいます）

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です

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. [ウィンドウ関数] 特定の列を使って、特定の列ごとに順番を振る

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. [ウィンドウ関数] 特定の列を使って順位を振る

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. [条件式] 条件と値の対応を複数書き並べる

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 演算子を使う前に、集計に必要な列だけに限定しておきます

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] スカラ値を抜き出す

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をタイムスタンプに変換する

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 にキャストして型を合わせます。

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

実践編へ続く

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