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

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

はじめに

自己紹介

株式会社LITALICOでWEBエンジニアをやっています。ti-aiutoと申します。

普段は個人向けのWEBサービスの開発を担当していて、特にモノリスなアプリケーションを持続可能な形に保つこと、フロントエンドの開発しやすい環境を整えることに関心があります。

サービスの開発に加えて、なんだかんだでダッシュボードの構築やデータマート構築のような業務を担当することも多いです。ということで、今回はそんな中で学んだBigQueryの知識を人に伝えるという観点で、50本ノックと題して問題集を作ってみることにしました。

ちなみに今年は業務時間も使ってGoogleの研修プログラムを受講させてもらい、Google Cloud Professional Data Engineerという資格を取ってみたりもしました。今後はデータモデリングやデータウェアハウス構築の方向にも知見を深めていきたいなと思っています。（勝手にデータエンジニア見習いを名乗っている

こんな方におすすめ

基本的には簡単なクエリを書いてデータ分析をしたり、データ分析をするための元データを取得したりしたい方に向けて書いています。
中でも、

• SQLの基本的な知識は本で読んだけど、実際に自分の手でクエリを書いたことはない
• 他のDBMS向けのクエリは書けるけど、BigQuery上で書いたことはない

という方におすすめしたいです。

使い方

普通に問題集として問題を解いてもよし、回答例を眺めるだけでもよし、写経してから後日解き直すでもよし、辞書代わりに使ってもよしです。
問題の後にヒントのリンクも張ってあるのでご活用ください。

構成

全体は初級編・中級編・実践編に分かれています。

初級編は、「SQL初心者が本で読んだことを試してみる」「SQLの本には書いてないけどBigQueryを使う上で特に必要な知識をつける」という観点で作りました。
中級編は、データ型やスキーマを意識するような問題を取り上げたのと、その他知っておくと便利な関数や演算子を使ってみる問題、またデータの簡単な前処理の手順にも触れています。
実践編は、「こんなクエリが書ければ書きたいと思ったクエリの大半は書けるはず」という観点で、独断と偏見の上で作った問題です。

拙い問題かもしれないですが多少は役に立つと思いますので、50問、お付き合いいただければ幸いです。

問題で使うテーブルについて

今回は、基本情報技術者のテキストやSQLの解説書でもよく目にする（気がする）、簡単なECサイトの注文データとアクセスログを題材にしてみました。
なお消費税とか送料とか割引とか色違いとか難しいことは忘れることにします。

BigQuery上では aiuto-public.knock2022_simple_ec のデータセット内に格納してあります。

注文データ

• items 商品
  • id(INT64) 商品ID
  • name(STRING) 商品名
  • price(INT64) 価格
  • stock(INT64) 在庫数
• users 会員
  • id(INT64) 会員ID
  • registered_at(TIMESTAMP) 仮登録完了日時
  • confirmed_at(TIMESTAMP) 本登録完了日時
• orders 注文
  • id(INT64) 注文ID
  • user_id(INT64) 会員ID(外部キー)
  • ordered_at(TIMESTAMP) 注文日時
  • total(INT64) 合計額
• order_statements 注文明細行(一回の注文での各商品の注文個数)
  • order_id(INT64) 注文ID(外部キー)
  • item_id(INT64) 商品ID(外部キー)
  • quantity(INT64) 個数
  • ※一つの注文IDについて、同じ商品IDの行は2行以上存在しない

アクセスログ

• access_log_transform_日付 テーブル
  • time(TIMESTAMP) アクセス日時
  • date_jst(DATE) アクセス日(日本時間)
  • path(STRING) HTTPリクエストのパス
  • method(STRING) HTTPリクエストのメソッド
  • status_code(STRING) HTTPレスポンスのステータスコード
  • request_id(STRING) リクエストに一意のID
  • user_id(INT64) 会員ID
• access_log_raw_日付 テーブル
  • time(INT64) アクセス日時(UNIXTIME)
  • message(STRING) JSON形式でのログ情報

以下のGitHubリポジトリから、元データのCSVと、データの生成に使ったRubyのコードがダウンロードできます。

初級編

問題

ここから使うテーブル

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

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

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

id 商品id	name 商品名	price 価格	stock 在庫数
1	ベーコン	299	12
...	...	...	...

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

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

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

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

例題

商品 テーブルから全ての行を取得して、 商品名 と 価格 の列だけを抜き出してください。

name	price
ベーコン	299
...	...

回答例

SELECT
  name,
  price
FROM
  `aiuto-public.knock2022_simple_ec.items`

1. [SELECT] 行の絞り込み

商品 テーブルから全ての列を取得して、 価格 が100円以下の商品だけに絞り込んでください。

id	name	price	stock
...	...	...	...

ヒント

2. [SELECT] 行の並び替え

商品 テーブルから全ての行と列を取得して、 価格 が高い順に並び替えてください。

id	name	price	stock
...	...	...	...

ヒント

3. [演算子] 「...から...まで」の指定

商品 テーブルから全ての列を取得して、 価格 が100円以上999円以下の商品だけに絞り込んでください。
絞り込みには BETWEEN 演算子を活用してみてください。

id	name	price	stock
...	...	...	...

ヒント

4. [SELECT] 行の重複を除く

注文 テーブルから 会員ID 列を取得して、重複を除いた 会員ID の一覧を出力してください。

user_id
...

ヒント

5. [演算子] NULLを除く

会員 テーブルから全ての列を取得して、 本登録完了日時 が NULL ではない行（つまり、本登録完了している会員）だけに絞り込んでください。

id	registered_at	confirmed_at
...	...	...

ヒント

6. [集計] 列ごとの合計

注文 テーブルを使って、 会員ID ごとに全注文の 合計額 の合計を計算してください。
会員ID ごとの全注文の合計額の列には、 total_sum_by_user という名前をつけてください。

user_id	total_sum_by_user
...	...

ヒント

7. [集計] 集計値を使った絞り込み・並び替え

(6)の結果について、 会員ID ごとの合計額が30000(円)以上のものに限定して、合計額が多い順に並び替えてください。

user_id	total_sum_by_user
200010	55682
...	...

ヒント

8. [結合] 内部結合をしてから全ての列を取得

注文明細行 テーブルの全ての行を取得して、 注文明細行 テーブルの 商品ID 列と 注文 テーブルの 商品ID 列を突き合わせて内部結合を行い、2つのテーブルのすべての列を取得してください。

order_id	item_id	quantity	id	name	price	stock
...	...	...	...	...	...	...

ヒント

9. [結合] テーブル名のエイリアスの活用・一部の列を取得

(8)の結果について、 商品名 ・ 価格 ・ 数量 の列だけを取得してください。
結合条件の指定の際には、テーブル名のエイリアスを活用してみてください。

name	price	quantity
...	...	...

ヒント

10. [SELECT] 列同士の演算

(9)の結果について、 価格x数量 を計算して、商品ごとの注文合計額の列も一緒に出力してください。
商品ごとの注文合計額の列には line_total という名前をつけてください。

name	price	quantity	line_total
...	...	...	...

11. [結合] 複数のテーブルの結合

(9)の結果について、さらに 注文明細行 テーブルの 注文ID 列と 注文 テーブルの 注文ID 列を使って注文テーブルを結合し、 注文日時 ・ 商品名 ・ 価格 ・ 数量 を取得してください。

ordered_at	name	price	quantity
...	...	...	...

12. [集計] 行数をカウントする

注文明細行 テーブルを使って 商品ID ごとの注文明細行の行数（つまり注文回数）を集計し、注文回数が多い順に並び替えてください。
注文回数の列には orders_count_by_item の名前をつけてください。

item_id	orders_count_by_item
10001	869
...	...

ヒント

13. [集計] 特定の列について重複を除いてカウントする

注文明細行 テーブルと 注文 テーブルを結合して、 商品ID ごとの重複を除いた 会員ID の件数（つまり注文した会員数）を集計し、会員数が多い順に並び替えてください。
会員数の列には orders_count_by_item_by_unique_user の名前をつけてください。

item_id	orders_count_by_item_by_unique_user
10007	334
...	...

ヒント

14. [集計] カウントの際のNULLの扱い

会員 テーブルについて、 仮登録完了日時 が NULL になっていない行数（つまり仮登録完了した人数）と、 本登録完了日時 が NULL になっていない行数（つまり本登録完了した人数）を集計してください。
仮登録完了した人数には registered_count 、本登録完了した人数には confirmed_count という名前をつけてください。

registered_count	confirmed_count
500	418

ヒント

ここから使うテーブル

ここからはアクセスログを題材にします。

どちらも日付で分割しているため、テーブル名に取得したい日付を指定する必要があります。
（例：10月22日のログを取得したければ access_log_transform_20221022 とする）

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

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

15. [日付分割] 全ての日付のテーブルをクエリ

整形済みアクセスログ テーブルについて、全ての日付を対象として、全体の行数をカウントしてください。
カウントの列には records_count の名前をつけてください。

records_count
28826

ヒント

16. [日付分割] 特定の日付のテーブルをクエリ

整形済みアクセスログ テーブルについて、2022年10月22日のテーブルだけを対象として、全体の行数をカウントしてください。
カウントの列には records_count の名前をつけてください。

records_count
805

ヒント

17. [日付分割] 日付を範囲指定してクエリ

整形済みアクセスログ テーブルについて、2022年10月10日から2022年10月16日のテーブルだけを対象として、全体の行数をカウントしてください。
カウントの列には records_count の名前をつけてください。

records_count
10994

ヒント

18. [正規表現] 正規表現を使って絞り込む

整形済みアクセスログ テーブルの2022年10月10日分について、正規表現を使って パス 列が /items/商品ID （ 商品ID は0-9の1回以上の繰り返し）になっている行だけに絞り込み、全体の行数をカウントしてください。
カウントの列には items_access_count の名前をつけてください。

items_access_count
543

ヒント

「正規表現」になじみがない方はスキップしていただいても大丈夫です。
（簡単に言うと、専用の文法を使って「 ^[0-9]{3}-[0-9]{4}$　数字3桁で始まって、その後にハイフンが続いて、さらに数字4桁が続いて終わる」のように文字の並びのパターンを表現できる方法です。）

19. [正規表現] 正規表現を使って値を抽出する

整形済みアクセスログ テーブルの2022年10月10日分について アクセス日時 と パス の列を取得し、(18)と同様に商品ページへのアクセスに絞り込んだ上で、正規表現を使って パス の商品ID部分を抽出した列を加えてください。
商品IDの列の名前は item_id としてください。

time	path	item_id
...	...	...

ヒント

20. [演算子] LIKE検索

整形済みアクセスログテーブルの2022年10月10日分について アクセス日時 と パ スの列を取得し、LIKE検索を使って パス が /items/ から始まっている行に絞り込んでください。

time	path
...	...

ヒント

21. [条件式] 条件により値を切り替える

整形済みアクセスログ テーブルの2022年10月10日分の全ての行について アクセス日時 と パス の列を取得し、さらに login_status という名前でログイン状態を示す列を追加してください。
login_status 列は、その行の 会員ID 列が NULL であれば 未ログイン 、そうでなければ ログイン済み の値になるものとします。

time	path	login_status
...	...	未ログイン
...	...	ログイン済み

ヒント

22. [タイムスタンプ] タイムゾーンを考慮してタイムスタンプを文字列に変換する

整形済みアクセスログ テーブルの2022年10月10日分の全ての行について アクセス日時 と パス の列を取得し、さらに アクセス日時 を日本時間・日本語表記にした列を追加してください。
日本時間・日本語表記のアクセス日時の列は time_jst_jpn の名前をつけて、形式は 20xx年xx月xx日 xx時xx分xx秒 とします。

time	path	time_jst_jpn
...	...	2022年10月10日 03時14分57秒

ヒント
ヒント
ヒント

23. [タイムスタンプ] タイムゾーンを考慮してタイムスタンプと文字列を比較する

整形済みアクセスログ テーブルの2022年10月10日分について アクセス日時 と パス の列を取得し、アクセス日時が2022年10月12日 19時以降のものだけに絞り込み、該当する行数をカウントしてください。
カウントの列には after_1900_records_count の名前をつけてください。

after_1900_records_count
368

ヒント

回答例

1. [SELECT] 行の絞り込み

SELECT
  *
FROM
  `aiuto-public.knock2022_simple_ec.items`
WHERE
  price <= 100

2. [SELECT] 行の並び替え

SELECT
  *
FROM
  `aiuto-public.knock2022_simple_ec.items`
ORDER BY
  price DESC

3. [演算子] 「...から...まで」の指定

SELECT
  *
FROM
  `aiuto-public.knock2022_simple_ec.items`
WHERE
  price BETWEEN 100
  AND 999

4. [SELECT] 行の重複を除く

SELECT
  DISTINCT user_id
FROM
  `aiuto-public.knock2022_simple_ec.orders`

5. [演算子] NULLを除く

SELECT
  *
FROM
  `aiuto-public.knock2022_simple_ec.users`
WHERE
  confirmed_at IS NOT NULL

6. [集計] 列ごとの合計

SELECT
  user_id,
  SUM(total) total_sum_by_user
FROM
  `aiuto-public.knock2022_simple_ec.orders`
GROUP BY
  user_id

7. [集計] 列ごとの合計を使った絞り込み・並び替え

SELECT
  user_id,
  SUM(total) total_sum_by_user
FROM
  `aiuto-public.knock2022_simple_ec.orders`
GROUP BY
  user_id
HAVING
  total_sum_by_user >= 30000
ORDER BY
  total_sum_by_user DESC

8. [結合] 内部結合をしてから全ての列を取得

SELECT
  *
FROM
  `aiuto-public.knock2022_simple_ec.order_statements`
JOIN
  `aiuto-public.knock2022_simple_ec.items`
ON
  `aiuto-public.knock2022_simple_ec.order_statements`.item_id = `aiuto-public.knock2022_simple_ec.items`.id

9. [結合] テーブル名のエイリアスの活用・一部の列を取得

SELECT
  items.name,
  items.price,
  order_statements.quantity
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

10. [SELECT] 列同士の演算

SELECT
  items.name,
  items.price,
  order_statements.quantity,
  items.price * order_statements.quantity 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

11. [結合] 複数のテーブルの結合

SELECT
  orders.ordered_at,
  items.name,
  items.price,
  order_statements.quantity
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
JOIN
  `aiuto-public.knock2022_simple_ec.orders` orders
ON
  order_statements.order_id = orders.id

12. [集計] 行数をカウントする

SELECT
  item_id,
  COUNT(*) orders_count_by_item
FROM
  `aiuto-public.knock2022_simple_ec.order_statements`
GROUP BY
  item_id
ORDER BY
  orders_count_by_item DESC

13. [集計] 特定の列について重複を除いてカウントする

SELECT
  item_id,
  COUNT(DISTINCT user_id) orders_count_by_item_by_unique_user
FROM
  `aiuto-public.knock2022_simple_ec.order_statements` order_statements
JOIN
  `aiuto-public.knock2022_simple_ec.orders` orders
ON
  order_statements.order_id = orders.id
GROUP BY
  item_id
ORDER BY
  orders_count_by_item_by_unique_user DESC

14. [集計] カウントの際のNULLの扱い

SELECT
  COUNT(registered_at) registered_count,
  COUNT(confirmed_at) confirmed_count
FROM
  `aiuto-public.knock2022_simple_ec.users`

15. [日付分割] 全ての日付のテーブルをクエリ

SELECT
  COUNT(*) records_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_*`

16. [日付分割] 特定の日付のテーブルをクエリ

SELECT
  COUNT(*) records_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221022`

17. [日付分割] 日付を範囲指定してクエリ

SELECT
  COUNT(*) records_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20221010'
  AND '20221016'

18. [正規表現] 正規表現を使って絞り込む

SELECT
  COUNT(*) items_access_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`
WHERE
  REGEXP_CONTAINS(path, "/items/[0-9]+$")

19. [正規表現] 正規表現を使って値を抽出する

SELECT
  time,
  path,
  REGEXP_EXTRACT(path, "/items/([0-9]+)$") item_id
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`
WHERE
  REGEXP_CONTAINS(path, "/items/[0-9]+$")

20. [演算子] LIKE検索

SELECT
  time,
  path
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`
WHERE
  path LIKE "/items/%"

21. [条件式] 条件により値を切り替える

SELECT
  time,
  path,
IF
  (user_id IS NULL, "未ログイン", "ログイン済み") login_status
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`

22. [タイムスタンプ] タイムゾーンを考慮してタイムスタンプを文字列に変換する

SELECT
  time,
  path,
  FORMAT_TIMESTAMP('%Y年%m月%d日 %H時%M分%S秒', time, 'Asia/Tokyo') time_jst_jpn
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`

23. [タイムスタンプ] タイムゾーンを考慮してタイムスタンプと文字列を比較する

ポイント：条件は9時間引いて（UTCのタイムゾーンで） time >= "2022-10-10 10:00:00" と書くこともできますが、可読性がよろしくないため TIMESTAMP(日本時間の表記, "Asia/Tokyo") とするのが便利です。

SELECT
  COUNT(*) after_1900_records_count
FROM
  `aiuto-public.knock2022_simple_ec.access_log_transform_20221010`
WHERE
  time >= TIMESTAMP("2022-10-10 19:00:00", "Asia/Tokyo")

中級編へ続く

明日も私 @ti_aiuto が執筆を担当させていただきます。次回はより幅広く概念を覚えていくのと、頭は少し疲れるけれど便利なテクニックをご紹介していきたいと思います。