ChatGPTにBigQueryの分析用クエリを教えてもらっていたら、面白いクエリが出てきたので簡単に記事に書いてみる。
やりたいこと
ARRAY型のカラムの中で、特定のn個の要素の組み合わせ（順不同）の出現数を調べたい。
例えば、以下のようなデータが与えれ、2個の組み合わせを求めるとき、
WITH sample_data AS (
SELECT
["A", "B", "C"] as field1
UNION ALL
SELECT
["A", "B"] as filed1
UNION ALL
SELECT
["B", "D"] as filed1
)
SELECT
*
FROM sample_data
| field1 |
|---|
| "[A,B,C]" |
| "[A,B]" |
| "[B,D]" |
こんな出力がしたい。
（1行目は、"A", "B"の組み合わせが全体で2回出現していることを意味している。）
| a | b | cnt |
|---|---|---|
| A | B | 2 |
| A | C | 1 |
| B | C | 1 |
| B | D | 1 |
クエリ
自分で考えてもあまりいい方法が思い浮かばなかったので、ChatGPTに聞いてみたところ面白いクエリを教えてくれた。
ChatGPT考案のおもしろクエリ
WITH sample_data AS (
SELECT
["A", "B", "C"] as field1
UNION ALL
SELECT
["A", "B"] as field1
UNION ALL
SELECT
["B", "D"] as field1
)
, combs AS (
SELECT
a,
b
FROM sample_data
, UNNEST(field1) as a
, UNNEST(field1) as b
WHERE
a < b
)
SELECT
a,
b,
COUNT(*) as cnt
FROM combs
GROUP BY
a, b
ORDER BY
cnt DESC
まず結果を確認してみると、以下の通りで期待していた出力と一致していることが分かった。
| a | b | cnt |
|---|---|---|
| A | B | 2 |
| A | C | 1 |
| B | C | 1 |
| B | D | 1 |
クエリはとても短くシンプルで、肝となるのは下の部分。
FROM sample_data
, UNNEST(field1) as a
, UNNEST(field1) as b
WHERE
a < b
field1（n個の要素が入った配列）の要素を2回UNNESTしているため、配列の中で各要素の全部の組み合わせの行を生成している。だが、そのままでは順序のみ違う二つの配列（例：["A", "B"], ["B", "A"]）がそれぞれカウントされてしまうため、a < bという条件を付けることでどちらか片方のみを取得するように設定している。（数値だけでなく文字列も問題なく動作する）
また、UNNESTとWHERE条件の数を増やすことで組み合わせの数を変更することもできる。
例えば、3つにしたいときは以下のようにする。a < b AND b < cを満たす組み合わせは要素が同じものの中では一つしかないため、順不同の組み合わせと同じ意味となる。
FROM sample_data
, UNNEST(field1) as a
, UNNEST(field1) as b
, UNNEST(field1) as c
WHERE
a < b
AND b < c
ただ計算量がどんどん増えていくため、大きいデータを用いるときにはあまり向かないかもしれない。（数百万行のデータで3つの組み合わせを行ったときは数秒で終了したため、ある程度実用性はありそう）