目指せ！！SQLの配列マスター

はじめに

最近仕事でBigQueryを触り始めたのですが、配列の処理に悪戦苦闘していました。
この記事では、私がBigQuery上でより上手く配列を扱うために調べたコトや取り組んだコトをまとめました。

なお、この記事ではUDFでJSを使わない方針で記述しています。

BigQueryで配列に関わる命令一覧

https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays?hl=ja
https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators?hl=ja

上記の公式ドキュメントから配列が関係するものを引っ張ってきただけですので、あしからず。

操作系

• ARRAY: サブクエリを配列化
• ARRAY_CONCAT: 配列の結合
• ARRAY_LENGTH: 配列サイズの獲得
• ARRAY_TO_STRING: 配列のSTRING化
• OFFSET: 要素の獲得（0スタート）
• ORDINAL: 要素の獲得（1スタート）
• SAFE_OFFSET: 安全な要素の獲得（1スタート）
• SAFE_ORDINAL: 安全な要素の獲得（1スタート）
• ARRAY_REVERSE: 配列の逆順を獲得

生成系

• GENERATE_ARRAY: 配列の生成
• GENERATE_DATE_ARRAY: 配列の生成（DATE型）
• GENERATE_TIMESTAMP_ARRAY: 配列の生成（TIMESTAMP型）

集計系

• ARRAY_AGG: 配列を返す集計関数
• ARRAY_CONCAT_AGG: 結合した単一の配列を返す集計関数
• APPROX_QUANTILES: 近似境界を返す近似集計関数
• APPROX_TOP_COUNT: 近似トップ要素とその頻度を返す近似集計関数
• APPROX_TOP_SUM: 近似トップ要素とそのweightの合計を返す近似集計関数

文字列系

• SPLIT: 文字列の分解
• TO_CODE_POINTS: コードポイントの取得

構文系

• UNNEST: 配列のフラット化（コレだけ構文。関数ではない）

---

と、上記にほぼ全列挙しましたが、よく使うものは限られています。実際に私が普段良く使うのは以下の6つです。

• ARRAY_LENGTH
• ARRAY_CONCAT
• ARRAY_TO_STRING
• SPLIT
• ARRAY_AGG
• UNNEST

上記は基本的には単純な処理ばかりで、公式ドキュメントでもわかりやすい説明がされています。
しかし、ARRAY_AGGとUNNESTの2つは少し複雑です。基本的な使い方を以下で紹介します。

基本的にテーブルのある要素を配列化したい場合は、ARRAY_AGGを使います（ARRAYはほとんど使いません）。

SELECT key, ARRAY_AGG(value) FROM table GROUP BY key

重複項目の削除やソートしつつ配列化することもできます。

SELECT key, ARRAY_AGG(DISTINCT value ORDER BY key) FROM table GROUP BY key

また、ARRAY_AGGは分析関数として使うこともできます。

SELECT key, ARRAY_AGG(value) OVER(PARTITION BY key) FROM table

UNNESTは配列をフラット化する構文ですが、WITH OFFSETをつけることで配列のインデックス番号を得ることができます（非常によく使います）。

SELECT
 T.*, val, idx
FROM
 table AS T
, UNNEST(T.arr) AS val WITH OFFSET AS idx

配列小ネタ集

ユーザー定義関数など、私がよく使う配列処理をリストアップしておきます。

型指定できる（基本自動で事足ります）

SELECT ARRAY<FLOAT64>[1, 2, 3] as floats;

SPLIT小ネタ

SPLITの区切り文字に空文字を指定すると、１文字ずつに分割された配列が得られる。

SELECT SPLIT('abcdef', '')
-- 配列['a','b','c','d','e','f']が得られる

indexを返す関数

Pythonのindex()のような感じ

CREATE TEMPORARY FUNCTION ARRAY_INDEX(arr ARRAY<INT64>, val INT64)
RETURNS INT64 AS ((
  SELECT MIN(idx) FROM UNNEST(arr) AS elem WITH OFFSET AS idx
  WHERE elem = val
));

配列の末尾の要素を削除する関数

Pythonのpop()のような感じ

CREATE TEMPORARY FUNCTION ARRAY_POP(arr ARRAY<INT64>, val INT64)
RETURNS ARRAY<INT64> AS ((
  SELECT MIN(idx) FROM UNNEST(arr) AS elem WITH OFFSET AS idx
  WHERE ARRAY_LENGTH(arr) - 1 != idx
));

arr1 ∈ arr2を判定

配列間は=などで直接比較ができないので、こういう関数を作っておくと便利です。

-- ある値valが配列arrに入っているかを確認
CREATE TEMPORARY FUNCTION VALUE_INCLUDE(arr ARRAY<STRING>, val STRING)
RETURNS BOOL AS ((
  SELECT LOGICAL_OR(elem = val) FROM UNNEST(arr) as elem
));

-- arr1がarr2の部分集合かを確認
CREATE TEMPORARY FUNCTION ARRAY_INCLIDE(arr1 ARRAY<STRING>, arr2 ARRAY<STRING>)
RETURNS BOOL AS ((
  SELECT LOGICAL_AND(VALUE_INCLUDE(arr2, val)) FROM UNNEST(arr1) AS val
));

文字列をソートして出力

含まれる文字が一致しているかを判定するために使いました。

CREATE TEMPORARY FUNCTION STRING_SORT(str STRING)
RETURNS STRING AS ((
  SELECT ARRAY_TO_STRING(ARRAY_AGG(x), '') FROM(
    SELECT x FROM UNNEST(SPLIT(REPLACE(str, ' ', ''), '')) AS x ORDER BY x
  )
));

おわりに

BigQuery(SQL)はPythonなどのプログラミング言語と比べると、処理を柔軟に書きづらくなっています。
しかし、できることをしっかりと理解して工夫を凝らすことで複雑な配列の操作などの実現できるようになります。
これを機会に皆さんもBigQueryでの配列の操作を極めてみてはいかがでしょうか？

参考

• 標準 SQL 関数と演算子 BigQueryドキュメント
• 標準 SQL での配列の操作 BigQueryドキュメント