【GCP】BigQueryのARRAY_AGG関数

2022年5月15日 2022年5月21日

gcp

BigQuerでデータ整形をするとなったら、SQLだけではなくユーザー定義関数と呼ばれるUDF。
UDFを用いることでデータの変換処理などより細かく行うことができます。
そしてそんなUDFを用いるとき、各ユーザーごとにグループ化してUDFを使おうとするとうまくいかなかったりします。
その時に1つ考える方法として、グループ化することで複数の値を1つにまとめる必要があり、そのまとめ方として配列化することができます。

ここではBigQueryで配列化する際に使用する関数、ARRAY_AGG関数について説明したいと思います。

ARRAY_AGG関数

BigQueryではnestという特殊な値があります。
nestつまり配列の状態の値を作成することができます。

Group byでは以下のようなデータを指定したディメンションの値ごとにまとめるような処理を行います。
よく一緒に使用されるものとしてはSUMやAVGなどの算術演算ですね。
要は、実際にテーブルで見ると以下のようなイメージです。

そうすると、この複数あるレコードを1つにまとめないといけないため、
メジャメントであればSUMで1つの値になどの処理が必要になります。
でも普通の文字列などの値を1つにするにはどうするか？
それはBigQueryの関数であるARRAY_AGG関数を用いて1つの配列にするということが考えられるかと思います。

以下のようなテーブルがあったとします。
ユーザーごとのアクセスページのデータです。

&nbs;
各ユーザーごとにいくつかページにアクセスしているので、これを配列化してみたいと思います。

select
  user_id,
  ARRAY_AGG(access_pagepath) as pagepath
  from `< project名 >.bigquery_test.array_agg_test`
  group by user_id

実行すると以下のようにアクセスページが配列化されて1つにまとまっているのが確認できます。

上ではユーザーごとにアクセスページを配列化してひとまとめにした例です。
この配列をUDFに渡して、処理を行ったりすることもできたりします。
このようにUDFやARRAY_AGG関数などによって、SQLでデータ整形まで容易に行えるのがBigQueryの特徴でもありますね！

BigQueryのUDFと絡めてARRAY_AGG関数を使用する

UDFを用いてARRAY_AGG関数で配列化したデータをUDFを使って、1つの連結文字列に変換して表示するクエリを実行してみます。
UDFの作成方法は以下で取り扱ってます。
ユーザー定義関数

ここでは非推奨ではありますが、javascriptを用いて1つの連結文字列に変換するような処理をしてみたいと思います。

-- UDF関数（配列データを1つの文字列に変換する --
CREATE TEMP FUNCTION concatString(pathList ARRAY)
   RETURNS STRING
   LANGUAGE js AS
   """
     var str = "";
     for(element of pathList){
       str += element;
     }
     return str;
   """;

select
  user_id,
  concatString(ARRAY_AGG(access_pagepath)) as pagepath
  from `yoshida-firebase-test.bigquery_test.array_agg_test`
  group by user_id

これを実行してみると、
以下のようにpagepathカラムには文字列がつながって1つの値となって表示されているのがわかります。