Zenn
🥧

BigQueryでもSnowflakeのARRAY_UNION_AGGのような出力を再現してみる

2024/12/07に公開
BigQuery
Snowflake
tech

はじめに

SnowflakeのARRAY_UNION_AGGを使うと配列データのUNIONが簡単にできて便利ですよね。
例えば下記のような感じです。

/* Snowflake */

WITH 
sample_data AS (
    SELECT 1 AS group_id, [1, 2, 3] AS items 
    UNION ALL
    SELECT 1 AS group_id, [3, 4] AS items 
    UNION ALL
    SELECT 2 AS group_id, [5, 6] AS items 
    UNION ALL
    SELECT 2 AS group_id, [6, 7, 8] AS items
),

union_group_id AS (
    SELECT 
        group_id,
        ARRAY_UNION_AGG(items) AS union_items
    FROM sample_data
    GROUP BY group_id
),

final as (
    SELECT
        group_id,
        union_items
    FROM union_group_id     
)
SELECT * FROM final;

/*
    RESULT
    GROUP_ID,UNION_ITEMS
    1,[1,2,3,4]
    2,[5,6,7,8]
*/

ですが、BigQueryではARRAY_UNION_AGG関数がありません。
なので、今回は似たような処理をBigQueryで作成していきます。

BigQueryバージョン

/* BigQuery */

WITH 
sample_data AS (
    SELECT 1 AS group_id, [1, 2, 3] AS items 
    UNION ALL
    SELECT 1 AS group_id, [3, 4] AS items 
    UNION ALL
    SELECT 2 AS group_id, [5, 6] AS items 
    UNION ALL
    SELECT 2 AS group_id, [6, 7, 8] AS items
),

concat_array_group_id AS (
    SELECT 
        group_id,
        ARRAY_CONCAT_AGG(items) AS concat_items
    FROM sample_data
    GROUP BY group_id
),

union_array AS (
    SELECT 
        group_id,
        ARRAY(
            SELECT DISTINCT unnest_items FROM UNNEST(concat_items) AS unnest_items
        ) AS union_items
    FROM concat_array_group_id
),

final as (
    SELECT
        group_id,
        union_items
    FROM union_array     
)
SELECT * FROM final;

/*
    RESULT
    group_id,union_items
    1,[1,2,3,4]
    2,[5,6,7,8]
*/

各CTEの説明

ダミーデータの準備

WITH 
sample_data AS (
    SELECT 1 AS group_id, [1, 2, 3] AS items 
    UNION ALL
    SELECT 1 AS group_id, [3, 4] AS items 
    UNION ALL
    SELECT 2 AS group_id, [5, 6] AS items 
    UNION ALL
    SELECT 2 AS group_id, [6, 7, 8] AS items
),

Snowflakeだと、UNIONは重複排除が入っています。

なので、BigQueryで実現するなら UNION DISTINCT となるはずなのですが、
ARRAYがUNION DISTINCTに対応していないのでUNION ALLにしています。

BQにおけるARRAY_UNION_AGGと似た出力の実現方法

concat_array_group_id AS (
    SELECT 
        group_id,
        ARRAY_CONCAT_AGG(items) AS concat_items
    FROM sample_data
    GROUP BY group_id
),

union_array AS (
    SELECT 
        group_id,
        ARRAY(
            SELECT DISTINCT unnest_items FROM UNNEST(concat_items) AS unnest_items
        ) AS union_items
    FROM concat_array_group_id
),

ARRAY_UNION_AGGを二段階の処理に分けて処理しています。

  • concat_array_group_idのCTE：ARRAY_CONCAT_AGGで配列をまとめる
  • union_arrayのCTE：ARRAYの要素をUNNESTで1行にして重複削除し、配列に戻している

追記

SnowflakeのARRAY_UNION_AGGで使用されている例では構造体を保持することが可能ですが、
BigQueryではその実装までできませんでした・・・
この記事ではgroup byをした際に配列内で重複する内容を消すことのみ可能です。

さいごに

複数のDWHをみてSQLや関数を比較していると

  • 実装されてなかったり
  • 使い方は同じはずだけど、、出力がちょっと違う・・・

みたいな部分で悩まされますよね〜

あと、意外とBQは配列関連で怒られることが多かったので注意して書かないと理論的には合ってても動かないことがありますね汗
(「ARRAYで二重配列のレコードを持てない」とか「ARRAYに対してUNION DISTINCTができない」とかむつかしい。。。)

何はともあれ、無事にSnowflakeのARRAY_UNION_AGGと同じような実装をBigQueryできました！
試したい方はぜひ動かしてみてください。

以上hamaでした〜

Discussion