BigQueryで配列を扱うときに使える関数を2つまとめる
目次
集約
GROUP BYで配列カラムをまとめたい時やCOUNTで集計するカラムが存在する時など
使用する関数：ARRAY_AGG_CONCAT
例）testテーブルのsync_dateでグループ化して、日毎のUU数を出す
| sync_date | user_id | value |
|---|---|---|
| 2023-09-01 | 1 | [‘a’, ‘b’] |
| 2023-09-01 | 2 | [‘c’, ‘d’] |
| 2023-09-01 | 3 | [‘e’, ‘f] |
↓
| sync_date | uu | value |
|---|---|---|
| 2023-09-01 | 3 | [‘a’, ‘b’, ‘c’, ‘d’, ‘e’, ‘f’] |
SELECT
sync_date
, COUNT(DISTINCT user_id) AS uu
, ARRAY_CONCAT_AGG(value) AS value_arr
FROM
test
GROUP BY
sync_date結合
別々のテーブルにある配列カラムをまとめたい時など
使用する関数：ARRAY_CONCAT
例）テーブルtest1(上部)とテーブルtest2（下部）を同値のsync_dateとnumで結合する
| sync_date | num | value |
|---|---|---|
| 2023-09-01 | 1 | [‘a’, ‘b’] |
| 2023-09-01 | 2 | [‘c’, ‘d’] |
| 2023-09-01 | 3 | [‘e’, ‘f] |
| sync_date | num | value |
|---|---|---|
| 2023-09-01 | 1 | [‘g’, ‘h’] |
| 2023-09-01 | 2 | [‘i’, ‘j’] |
| 2023-09-01 | 3 | [‘k’, ‘l] |
↓
| sync_date | num | value |
|---|---|---|
| 2023-09-01 | 1 | [‘a’, ‘b’, ‘g’, ‘h’] |
| 2023-09-01 | 2 | [‘c’, ‘d’, ‘i’, ‘j’] |
| 2023-09-01 | 3 | [‘e’, ‘f’, ‘k’, ‘l] |
SELECT
test1.sync_date
, test1.num
, ARRAY_CONCAT(IFNULL(test1.value, [“”]), IFNULL(test2.value, [“”])) AS value_arr
FROM
test1
LEFT OUTER JOIN
test2
ON test1.sync_date = test2.sync_date
AND test1.num = test2.num※ARRAY_CONCATは引数にNULLが存在すると、結果もNULLになってしまうのでIFNULLで回避している
以上、参考になれば幸いです。
