LITALICOAdvent Calendar 2022

株式会社LITALICO

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

Last updated at 2024-06-14Posted at 2022-12-04

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

はじめに

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

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

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

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

実践編

問題

ここで使うテーブル

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

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

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

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

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

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

ヒント

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

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

列名	意味	計算方法
`first_dir`	第一階層	`パス` の最初から2個目の `/` の直前まで（2個目の `/` がなければ最初から最後まで）を抜き出す例： `/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
...	...	...	...	...

ヒント

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

出力イメージ

days_count	users_count
0	336
...	...

ヒント

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

ヒント

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

ヒント
 ヒント※人様のQiita記事

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

ヒント

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

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

path	pv_count
/signup	20
/items/10010	19
/items/10007	18
...	...

ヒント

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

ヒント
 ヒント
 ヒント

サブクエリを使う場合のヒント
 サブクエリを使う場合のヒント

ウィンドウ関数を使う場合のヒント

解答例

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

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. [正規表現] 第一階層別のアクセス状況を可視化する

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. [集計] 日別の注文状況を可視化する

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 関数を組み合わせることもできます

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. [サブクエリ] 〇〇を買った人はこんな商品を買っています

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

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

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 演算子を使うときに列名を省略できます（ただし日本語の列名は現時点では使えないのでアルファベット表記です・・・）

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

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. [ウィンドウ関数] 商品別注文額ランキング

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. [ウィンドウ関数] 流入ページの調査

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

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日連続で商品を注文した注文数・ユーザ数

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

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 ごと・日付ごとに重複を除いておきます

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

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

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 が執筆を担当します。お楽しみに！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up