Go to Qiita Advent Calendar 2024 Top
LoginSignup
20
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

LITALICOAdvent Calendar 2022

Day 4
@ti_aiutoin株式会社LITALICO

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

Last updated at Posted at 2022-12-03

この記事は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

実践編へ続く

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

20
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
20
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?