BigQueryで、CROSS JOINを使わずに配列から重複を省く

はじめに

BigQueryで、配列から重複を省く方法です。

シンプルな場合

重複アイテムを持つ配列内の重複を削除します。

# standardSQL
WITH T_SAMPLE AS(
  SELECT 'id1' id, ['item1', 'item2', 'item2'] items
  UNION ALL SELECT 'id2', ['item1', 'item2']
  UNION ALL SELECT 'id3', ['item10', 'item11', 'item10']
)

SELECT 
  id,
  (SELECT ARRAY_AGG(DISTINCT items_list) FROM UNNEST(T_SAMPLE.items) items_list) items
FROM 
  T_SAMPLE

クエリ実行前のテーブル	実行後のテーブル
T_SAMPLE

集約しつつ重複を省く場合

サブクエリでいったん配列を結合してから、同様に集約します。（しか無い？）

# standardSQL
WITH T_SAMPLE AS(
  SELECT 'id1' id, ['item1', 'item2', 'item2'] items
  UNION ALL SELECT 'id1', ['item1', 'item2']
  UNION ALL SELECT 'id3', ['item10', 'item11', 'item10']
)

SELECT 
  id,
  (SELECT ARRAY_AGG(DISTINCT items_list) FROM UNNEST(items) items_list) items
FROM(
  SELECT 
    id,
    ARRAY_CONCAT_AGG(T_SAMPLE.items) items
  FROM 
    T_SAMPLE
  GROUP BY
    id
)

クエリ実行前のテーブル	実行後のテーブル
T_SAMPLE

おわりに

最近は、AWS Athenaも使っていきたいです。