BigQueryでcross join unnest splitを使ってカンマ区切りのデータを分解する処理をまとめてみた

8500 ワード
analytics SQL データサイエンス BigQuery プロダクトマネージャー SQL テキストリンク

この記事は？

あなたがECサイトの分析をしていて、下記のようなテーブルを扱っていたとします。
ユーザが購入した商品がカンマ区切りでitemというカラムに格納されてます。

「各商品を買った人はそれぞれ何人いるかを集計したい」

と言われたときにどうしますか？
この問題を解決するのがCROSS JOIN UNNESTです！

SQL

先に正解となるSQLを共有します。

-- サンプルテーブルを生成
WITH ec_tbl AS(
SELECT "A" AS user_id,'apple,beer,orange'  as item
UNION ALL
SELECT "B" AS user_id, 'apple,beer,tuna' as item
UNION ALL
SELECT "C" AS user_id, 'apple,milk' as item
UNION ALL
SELECT "D" AS user_id, 'apple' as item)

-- CROSS JOIN UNNEST SPLITでカンマ区切りのデータを分解する
, ec_unnest AS(
SELECT
user_id
, unnest_item
FROM
ec_tbl
CROSS JOIN
UNNEST(SPLIT(ec_tbl.item,",")) AS unnest_item
)

-- 分解したデータで商品ごとのユーザ数を集計する
SELECT
unnest_item
, COUNT(user_id) user
FROM
ec_unnest
GROUP BY unnest_item 
ORDER BY user

このSQLを理解するためにはSPLIT、UNNEST、CROSS JOINのそれぞれの挙動を理解する必要があります。

SPLIT関数と配列型

SPLIT関数はカンマなどの区切り文字を含む文字列データを、配列型に変換する処理です。

配列型のデータ見た目は、初見だと少し気持ち悪く感じますが、
ポイントは1行に複数のitemがぶら下がっているところです。これをネスト（入れ子）といいます。
Aさんの買った商品であるapple, beer, orange の3つすべてが1行に格納されているのです。

-- 上記の画像の右の結果が見たい場合
-- 一番上のサンプルテーブルをつかってください。
SELECT
user_id 
, SPLIT(ec_tbl.item,",") item_split
FROM
ec_tbl

参考までに配列の生成の仕方をかんたんに触れます。
下記のように[]を使っても配列が生成できます。同じアウトプットが出てきます。


SELECT "A" AS user_id, ["apple","beer","orange"]  as item
UNION ALL
SELECT "B" AS user_id, ["apple","beer","tuna"] as item
UNION ALL
SELECT "C" AS user_id, ["apple","milk"] as item
UNION ALL
SELECT "D" AS user_id, ["apple"] as item

UNNEST関数

配列を引数にとって、それを複数の行としてフラット化するものです。
ネストを解除するからUNNESTっていうんですね。


微妙な違いですが、左は1行に3つのitemが格納されているのに対して、右は1行に1つのアイテムになってます。


SELECT *
FROM
UNNEST(["apple","beer","orange"]) AS item_unnest

CROSS JOINとUNNESTを組み合わせて使う

CROSS JOINは総当り的にJOINする結合方法です。
UNNESTと組み合わせて使うことで、配列の中のそれぞれの値を分解して縦持ちに変換させることができます。

-- 上記の画像の右の結果が見たい場合
-- 一番上のサンプルテーブルをつかってください。
SELECT
user_id
, unnest_item
FROM
ec_tbl
CROSS JOIN
UNNEST(SPLIT(ec_tbl.item,",")) AS unnest_item

最後このアウトプットをGROUP BYで集計しておしまい！

※参考
「CROSS JOIN(総当り結合)じゃないやん」と思った方、
UNNESTと組み合わせることで相関クロス結合という結合方法になるようです。
詳しくは知りたい方は公式を。

結論

CROSS JOIN UNNESRは、使っているデータに配列や区切り文字を含む文字列がある場合、とても便利な関数です。
公式を呼んでもなかなか理解が難しい場合は、上記のSQLを一つ一つ実行してみると挙動を理解出来ると思います。