BigQueryのStandard SQLを使っていて配列から位置を指定して要素を取り出すことはできるのですが、UNNEST()を使ってそれぞれの要素を取り出した時にある要素が何番目かという情報を一緒に得る方法がわからなかったのでやり方を考えました(もしかしたら簡単に得る方法があるかも?)
余談ですが、最近BigQueryに触っていて意外とSQLでなんでも書けるなぁと思い始めてきました(BigQueryはWITHの再帰ができないのが残念ですが)
追記
添字を得るだけなら簡単に書けました
sucrose.hatenablog.com
CROSS JOINによる方法
配列の長さがわからないのでGENERATE_ARRAY(1, 100)
のように1から十分な長さまでの配列を作って、それとCROSS JOINしてi番目の要素を取り出す関数のSAFE_ORDINAL()
(0-indexedならSAFE_OFFSET()
)で配列から要素を取り出します
#StandardSQL WITH data AS (SELECT SPLIT('a,b,c', ',') AS split) SELECT i , split[SAFE_ORDINAL(i)] AS token FROM data, UNNEST(GENERATE_ARRAY(1, 100)) AS i WHERE NOT split[SAFE_ORDINAL(i)] IS NULL ORDER BY i
同様に2つの配列の値を引いてくればzip的に使うこともできます
#StandardSQL WITH data1 AS (SELECT SPLIT('a,b,c', ',') AS split) , data2 AS (SELECT SPLIT('あ,い,う,え,お', ',') AS split) SELECT i , data1.split[SAFE_ORDINAL(i)] AS token , data2.split[SAFE_ORDINAL(i)] AS token2 FROM data1, data2, UNNEST(GENERATE_ARRAY(1, 100)) AS i WHERE NOT data1.split[SAFE_ORDINAL(i)] IS NULL AND NOT data2.split[SAFE_ORDINAL(i)] IS NULL ORDER BY i
UDF(ユーザー定義関数)による方法
BigQueryのSQLではJavaScriptによるUDFが動かせます
以下のコード例のようにJavaScriptの関数を定義するだけで簡単に使い回しができます
#StandardSQL CREATE TEMPORARY FUNCTION array_with_index(x ARRAY<STRING>) RETURNS ARRAY<STRUCT<index INT64, value STRING>> LANGUAGE js AS """ return x.map( (value, index) => ({value, 'index': index + 1}) ); """; WITH data AS (SELECT SPLIT('a,b,c', ',') AS split) SELECT array_with_index(split) AS result FROM data
配列をzipする場合も同様にUDFを定義して使えばよいです(型名とカラム名を指定しないといけないのであまり汎用的にはならないですが)
#StandardSQL CREATE TEMPORARY FUNCTION array_with_index(x ARRAY<STRING>, y ARRAY<STRING>) RETURNS ARRAY<STRUCT<index INT64, value1 STRING, value2 STRING>> LANGUAGE js AS """ return x.map( (value, index) => ({'value1': value, 'value2': y[index], 'index': index + 1}) ); """; WITH data1 AS (SELECT SPLIT('a,b,c', ',') AS split) , data2 AS (SELECT SPLIT('あ,い,う,え,お', ',') AS split) SELECT array_with_index(data1.split, data2.split) AS result FROM data1, data2