🔍 ARRAY_AGG関数

|| array_agg()

#standardSQL
select
    user_id
    , ARRAY_AGG(access_pagepath) as pagepath
from
    `{project名}>.{dataset名}.array_agg_test`
group by
    user_id

コレをコウ

　↓

cf.

• ARRAY_AGG - GoogleCloud
• 9.20. 集約関数 - PostgreSQL 9.6.5文書
• Big QueryでWindow関数を用いて、累積和を計算する - エクスチュア株式会社ブログ
• 【GCP】BigQueryのARRAY_AGG関数 - Yosshi Labo.

| 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 1
;

| array_agg struct

#standardSQL
~CTE中略~

#output
    -- Slack通知用テーブル
    , slack_notification as (
        select
            user_email
            , array_agg(
                struct<
                    job_id string
                    , project_id string
                    -- , destination_path string
                >(
                    job_id
                    , project_id
                    -- , destination_path
                )
              ) as execution_jobs
            , any_value(collect_project_id) as collect_project_id
            , any_value(team_path) as team_path
            , any_value(is_belong_to_engineerteam) as is_belong_to_engineerteam
        from
            rawdata
        group by 1
    )

select * from slack_notification 
-- limit 1 --（検証時に追記）
;

cf.

• -

| array_agg in array_agg

#standardSQL
~ CTE中略 ~

#output

    --▼Slack通知： 各所属部署別×（所属チームメンバー別×実行ジョブ情報）一覧
    , slack_notification as (
        select
            team_path_for_each_project as path
            , array_agg((user_email, execution_jobs)) as notice
        from (
            select
                team_path_for_each_project
                , user_email
                , array_agg(struct<
                        project_id string, job_id string, query_type string, url string, total_slot_hour int64
                    >(
                        project_id, job_id, query_type, url, total_slot_hour
                    )
                ) as execution_jobs
            from rawdata
            group by 1, 2
        )
        group by 1
    )

select * from slack_notification
;

/*****
 | |path     |notice._field_1  |notice._field_2.project_id|~|notice._field_2.total_slot_hour|
 |1|Engineer |[email protected]|production                |~|                 83495372400000|
 | |         |                 |dev_ci                    |~|                 72495372300000|
 | |         |[email protected]|prodaction                |~|                  9495372400000|
 |2|Marketing|[email protected]    |analize                   |~|                183495372400000|
 *****/

1. 子サブクエリでarray_aggでグルーピング
2. 親サブクエリでarray_aggでグルーピング（タプル）

cf.

• is it possible to nest an array_agg inside another array_agg - stackoverflow