【BigQuery】ARRAY,UNNESTを丁寧に解説 | CCI Analytics

こんにちは。CCIのアナリティクス担当の寺田です。

BigQueryのデータ型、ARRAY型, STRUCT型は慣れれば便利ですが、最初はどう扱っていいか困りませんか？

特にGA4 <---> BigQueryを連携し、BigQueryにエクスポートされてきたGA4アクセスログを分析しよう！とした時、最初にぶつかる壁ではないでしょうか。(N=1, 自分です)

今回は、

• 1)ARRAY型の紹介
  
  
• 2)UNNEST演算子の使いどころ

の2つをざっくりご紹介したいと思います。

(STRUCT型については近日中に追って書くと思います。。。)

ARRAY型

定義：同じデータ型の複数の値をまとめて格納できるデータ構造

(※ただし値としてARRAYを入れ込むことは出来ない)

BigQuery公式ドキュメント:Array_type

BigQueryで頻出するARRAY構造の例

--ユーザーごとに購入したitem_idの一覧を作成するサンプルクエリ
WITH array_sample AS (
  SELECT 'A1' AS user_id, [101, 102, 103, 104] AS item_ids
  UNION ALL
  SELECT 'A2' AS user_id, [201, 202, 203, 204, 205] AS item_ids
  UNION ALL
  SELECT 'A3' AS user_id, [301, 302] AS item_ids
)

SELECT user_id, item_ids
FROM array_sample
ORDER BY user_id
;

UNNEST演算子の使いどころ

UNNEST演算子は、上記のようなARRAY型のカラム(=item_ids)に対して力を発揮します！

使いどころとしてはARRAY型のカラム使いにくい！普通のテーブルが欲しい！というケースで使います。

今回の例だと「item_idごとにどのくらいの購入UUがいるのか」、このままのテーブル構造だと集計できませんよね😇

UNNEST演算子の特徴

• 引数として「FROM句で指定したソーステーブル」のARRAY型のカラムを取る
• 受け取ったARRAY内にある各値を、一組の行に変換（フラット化）する
• CROSS JOINやLEFT JOINなどのJOIN句と併用される
  
  

BigQueryで頻出するARRAY構造をフラット化するサンプルクエリ

--ユーザーごとに購入したitem_idの一覧を作成するサンプルクエリ
WITH array_sample AS (
  SELECT 'A1' AS user_id, [101, 102, 103, 104] AS item_ids
  UNION ALL
  SELECT 'A2' AS user_id, [201, 202, 203, 204, 205] AS item_ids
  UNION ALL
  SELECT 'A3' AS user_id, [301, 302] AS item_ids
)

--<ARRAY>item_idsカラムを一組の行に変換（フラット化）する
SELECT user_id, item_id
FROM array_sample
CROSS JOIN UNNEST(item_ids) AS item_id
;

上記サンプルクエリで起こっていることを順番に説明します。

1. UNNEST 演算子はFROM句で指定したソーステーブル(=array_sample)のARRAY型のカラム(=item_ids)を引数に取ります

2. 受け取ったARRAY(=item_ids)内にある各値を、一組の行に変換（フラット化）します

    

   
   

    

3. 一組の行に変換（フラット化）したitem_idカラムを、各レコードごとにCROSS JOINします

※正確にはここで行っているCROSS JOINは「相関CROSS JOIN」です

各行の他の列の値を維持したまま ARRAYをバラし、CROSS JOINしてくれます。

各行の他の列の値を維持したまま ARRAYをバラし、CROSS JOINしてくれます。

この表現がわかりにくいので、上の例で翻訳すると、

※「各行」→ユーザーA1さんの行とします

ユーザーA1さんの行に含まれているuser_idカラムの値(=A1)を維持したまま、

ARRAY(=item_ids)をバラしCROSS JOINしてくれます

いずれ

• 相関CROSS JOIN
• 相関LEFT JOIN
• それぞれの挙動の違い

なども書く予定です(いつか......)

BigQuery公式ドキュメント:Correlated join operation

クエリ結果

A1さん、A2さん、A3さん分で11レコード出力されます。

UNNEST演算子でARRAY(=item_ids)を行に変換し、無事フラット化できました！

例えば、item_idごとにどのくらいの購入UUがいるのかを調べるなど、こちらのテーブルのだと集計できそうです！

いかがだったでしょうか。今回は

• 1)ARRAY型の紹介
  
  
• 2)UNNEST演算子の使いどころ

をご紹介しました。

GA4アクセスログをBigQueryで分析する際には避けられない知識かと思います。

また社内の1st PartyデータをBigQueryに取り込むことにより、GA4アクセスログと1st Partyデータを横断する分析も可能になります。

(なにかしらの突合キーが必要になりますが)

• GA4アクセスログと1st Partyデータを横断するためのキーの実装相談
• 事業KPIに即したGA4に取り込むべきイベント設計/GTM設定
• BigQueryやGoogle Cloud製品を用いた各種分析とインサイトの発見
• 社内の1st Partyデータを一元管理できるダッシュボード
• 広告効果検証用ダッシュボード
  

ここまで読んでいただきありがとうございました！😊

📣なお、私も含め、CCI Analyticsを担当するアナリストは現在は「Data Dig（データディグ）」というCookieに依存しないデジタルマーケティング展開の支援も行っています。そのサービスの詳細も「データマーケティング」という形でご案内しており、Data Dig関連サービスとしてどのような資料が存在するか、それらの資料ダウンロードを行えます。

また、デジタルマーケティングに関するコラムなどを展開しています。

Data Dig（データディグ）

📣CCIが提供する統合デジタルマーケティングサービスの全容、また現状のデジタルマーケティング施策のノウハウを認識されたい場合は、ぜひ近日オープンした「KnowHowNow」をご覧ください。

KnowHowNow（ノウハウナウ）はこちらから