HOME/🔍 BigQuery/

ARRAY_AGG関数

Article Outline

|| array_agg()

| 基本

#standardSQL
select
    user_id
    , ARRAY_AGG(access_pagepath) as pagepath
from
    `{project名}>.{dataset名}.array_agg_test`
group by
    user_id
user_id access_domain access_pagepath
1 test.co.jp /page1
1 test.co.jp /page2
2 test.co.jp /dir1
3 test.co.jp /page1
3 test.co.jp /dir1

cf.【GCP】BigQueryのARRAY_AGG関数

 

user_id access_pagepath
1 [/page1,page2]
2 [/dir1]
3 [/page1,/dir1]

cf.【GCP】BigQueryのARRAY_AGG関数

e.g

| JS利用

#standardSQL
--UDF関数(配列データを1つの文字列に変換する--
CREATE TEMP FUNCTION concatString(pathList ARRAY<string>)
   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 `firebase-test.bigquery_test.array_agg_test`
group by user_id
;

|| REFERENCE