BigQueryでカンマ区切り文字列項目の中の値が重複してたら抽出しない方法

はじめに

BigQueryの項目で区切り文字のある値が入った項目を
SPLITで配列にする方法は有名ですね。こんな感じの。

WITH id_table AS (
   SELECT '111,112,113' AS ids
)
SELECT
    SPLIT(ids, ',') AS id
FROM
    id_table

この記事はこれの小さな応用編です。

やりたいこと

以下のようにidsの中で値ダブりがあった場合のレコードは除外して出力したい

record1:
  name: 'aaaa'
  ids : '111,112,113'
record2:
  name: 'bbbb'
  ids : '111,112,111,113'

# record1だけを出力

record1:
  name: 'aaaa'
  ids : '111,112,113'

方法

WITH dummy_table AS (
    SELECT
      'aaaa' AS name
      ,'111,112,113' AS ids
    UNION ALL
    SELECT
      'bbbb' AS name
      ,'111,112,111,113' AS ids
), ids_table AS (
    SELECT
        SPLIT(ids, ',') AS id_array
        ,*
    FROM
        dummy_table
)
SELECT
  * except(id_array)
FROM
  ids_table
WHERE
    ARRAY_LENGTH(ARRAY( SELECT DISTINCT id FROM UNNEST(id_array) AS id))
    = 
    ARRAY_LENGTH(ARRAY( SELECT id FROM UNNEST(id_array) AS id));

1. SPLITで,区切りの項目を配列にします
2. UNNESTで縦持ちにします
3. 比較する片方はDISTINCTをかけて、重複行を排除します ※ここがポイント
4. 両辺を配列(ARRAY)として、配列の長さ(ARRAY_LENGTH)を出します
5. 重複の値がない場合は、両辺値が一致してTRUEになりますが、重複する値が存在する場合はFALSEとなり除外されます

プログラミング言語で配列長さ比較して云々はしたことあったのですが、
BigQueryでそれっぽいことして除外できないか模索したら、この形に辿り着きました。

ちなみに

WHERE句の部分、これでも同じことができます。

WHERE
   ( SELECT COUNT(DISTINCT id) FROM UNNEST(id_array) AS id)
    = 
   ( SELECT COUNT(id) FROM UNNEST(id_array) AS id);

こっちの方がクエリ書き慣れてる方はわかりやすいかもしれないですね。

おわり

良いお年を。