🎉

BigQueryのArrayを理解する。

2020/10/14に公開

Google Cloud

BigQuery

tech

BigQueryのArrayを理解する。

目的

BigQuery を利用していると、Billing や informationSchema などで Array が利用されており、Array を自由に使いこなせるとさらに楽しめるのではないかと思っています。

ただ、Array は正直よくわからないので、理解するための軌跡を記載することにしました。

参考資料

BQ公式ドキュメント（Array）

Arrayとは

公式ドキュメントを引用します。

名前	説明
ARRAY	ARRAY 型ではないゼロ以上の要素の順序付きリスト。

制限

NULL 要素を含む ARRAY はクエリ内で使用できますが、
そのような ARRAY がクエリ結果にあると、BigQuery でエラーが発生します。
クエリ内では NULL と空の ARRAY は 2 つの別個の値ですが、
クエリ結果で BigQuery は NULL ARRAY を空の ARRAY に変換します。

使ってみる

SELECT [1, 2, 3] as numbers;

レコードを見ると、1 レコードしか出力していないことがわかります。

ARRAYの型宣言

公式ドキュメントを引用します。

型の宣言	説明
ARRAY<INT64>	64 ビット整数のシンプルな ARRAY。
ARRAY<STRUCT<INT64, INT64>>	STRUCT からなる ARRAY。各 STRUCT には 64 ビット整数が 2 つ含まれます。
ARRAY<ARRAY<INT64>>	サポート対象外
ARRAY<STRUCT<ARRAY<INT64>>>	64 ビット整数の ARRAY からなる ARRAY。ARRAY には直接他の ARRAY を含めることができないため、2 つの ARRAY の間に STRUCT が挿入されています。

ここでのポイントは、ARRAY に直接 ARRAY を保持することはできないということです。
それをするためには STRUCT を利用しましょう。

ARRAYの作成

[]を利用した作成

SELECT
  ARRAY<FLOAT64>[1, 2, 3] as floats,
  [1, 2, 3] as numbers;

型を指定できることを確認しましょう。
同じ値を使っても型を指定することで、結果が変わっていることがわかります。

生成した結果をARRAYで作成

GENERATE_ARRAYを利用する。

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

SELECT
  -- 11 - 33のすべての奇数の整数で構成された配列を生成
  GENERATE_ARRAY(11, 33, 2) AS odds,
  -- 負のステップ値を指定して、降順の値で構成された配列を生成
  GENERATE_ARRAY(21, 14, -1) AS countdown;

日付の配列の生成

GENERATE_DATE_ARRAYを利用する。

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

INT64_expr は INT64 にする必要がある

date_part は、DAY、WEEK、MONTH、QUARTER または YEAR にする必要がある

SELECT
  GENERATE_DATE_ARRAY('2017-11-21', '2017-12-31', INTERVAL 1 WEEK) AS date_array;

次にアクセスです！（作ったらアクセスしないと）

こちらも公式を参考にしています。

こちらがアクセスするためのベースとなる一時テーブルとなります。



WITH sequences AS


  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers


   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers


   UNION ALL SELECT [5, 10] AS some_numbers)

こんな感じでデータが入っています。

+---------------------+

| some_numbers |

+---------------------+

|[0, 1, 1, 2, 3, 5]|

|[2, 4, 8, 16, 32]|

|[5, 10] |

+---------------------+

OFFSET と ORDINAL

SELECT 
  some_numbers,
  -- OFFSETは、0から初めて2番目の値を返します。
  some_numbers[OFFSET(1)] AS offset_1,
  -- ORDINAL、1から初めて1番目の値を返します。
  some_numbers[ORDINAL(1)] AS ordinal_1
FROM 
  sequences;

■説明
OFFSET と ORDINALの詳細説明は公式を参照してください。

下記を理解すれば問題なさそうです。

位置によって ARRAY 要素にアクセスし、その要素を返します。
OFFSET は番号付けがゼロから始まることを意味します。
ORDINAL は番号付けが 1 から始まることを意味します。

ARRAY_LENGTH

SELECT
  some_numbers,
  ARRAY_LENGTH(some_numbers) AS len
FROM
  sequences;

■説明
ARRAY_LENGTHの詳細説明は公式を参照してください。

下記を理解すれば問題なさそうです。

配列のサイズを返します。
空の配列の場合は 0 を返します。array_expression が NULL である場合、NULL を返します。

配列のフラット化

ここを使いこなせればと、いつも思っています。
なので、今回はしっかり理解したいと考えました。

SELECT
  *
FROM
  UNNEST(['foo', 'bar', 'baz', 'qux', 'corge', 'garply', 'waldo', 'fred']) AS element
WITH OFFSET AS OFFSET
ORDER BY OFFSET;

■説明

UNNEST は ARRAY を取得し、ARRAY に含まれる各要素を 1 行にしたテーブルを返します。
WITH OFFSET 句を使用して各要素のオフセットを含む追加の列を取得できます。
ORDER BY 句でそれぞれのオフセットを使用して行を並べ替えることができます。
デフォルトでは UNNEST は順序を無視するため、順序を保証する場合は利用しましょう。

CROSS JOIN

WITH sequences AS
  (SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers)

SELECT
  id,
  flattened_numbers
FROM
  sequences
CROSS JOIN UNNEST(sequences.some_numbers) AS flattened_numbers;

-- UNNESTを省略して、CROSS JOINを,で表しています。
-- 個人的には上記の方が、可読性的にはいいかなと思っています。
SELECT
  id,
  flattened_numbers
FROM
  sequences,
  sequences.some_numbers AS flattened_numbers;

■説明
まずは公式の記載を引用します。

各行の他の列の値を維持したまま ARRAY の列全体をフラット化するには、
CROSS JOIN を使用して、ARRAY 列を含むテーブルとその ARRAY 列の UNNEST 出力を結合します。

上記クエリでは下記のようなことが起きています。

UNNEST で指定している ARRAY の列から各要素を 1 行ごとに分けて返します
（1）で返した値と sequences を CROSS JOIN を行います

上記のクエリに some_numbers を SELECT に追加してみました。
これをすることで、個人的には CROSS JOIN をしているイメージがつきます。
sequences のテーブルは全ての値を返していることがわかるためです。

ネストされた配列のクエリ

WITH races AS (
  SELECT "800M" AS race,
    [STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
     STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
     STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
     STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
     STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
     STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
     STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
     STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)]
       AS participants)

SELECT
  race,
  participant
FROM
  races r
CROSS JOIN UNNEST(r.participants) as participant;

出力例です。
＊公式ドキュメントから引用しています。

race	participant
800M	{Rudisha, [23.4, 26.3, 26.4, 26.1]}
800M	{Makhloufi, [24.5, 25.4, 26.6, 26.1]}
800M	{Murphy, [23.9, 26, 27, 26]}
800M	{Bosse, [23.6, 26.2, 26.5, 27.1]}
800M	{Rotich, [24.7, 25.6, 26.9, 26.4]}
800M	{Lewandowski, [25, 25.7, 26.3, 27.2]}
800M	{Kipketer, [23.2, 26.1, 27.3, 29.4]}
800M	{Berian, [23.7, 26.1, 27, 29.3]}

お題
800m 競走で最速の参加者を返してください。

考え方

まずは ARRAY を UNNSET して、利用できるようにする
次に、1 のカラムにも ARRAY があるので UNNSET して値を利用できるようにする
全ての値を操作できるので、コメントアウトしているロジックを記載する

WITH races AS (
  SELECT "800M" AS race,
  [
    STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)
  ]
  AS participants
)

SELECT
  race,
  (
    -- 各参加者ごとに分けた上で、
    -- レース記録の合計値を昇順にした上で、
    -- 一番最初の名前を取得する。
    SELECT
      name
    FROM
      UNNEST(participants)
    ORDER BY (
      -- 書く参加者ごとの記録を合計する。
      SELECT
        SUM(duration)
      FROM
        UNNEST(splits) AS duration
    ) ASC
    LIMIT 1
  ) AS fastest_racer
FROM races;

STRUCT内のARRAY型フィールドのクエリ

ネストされた繰り返しフィールドの情報も取得できます。
たとえば、次のステートメントでは 800m 競走で最速ラップの参加者が返されます。
＊公式ドキュメント

お題
800m 競走で最速ラップの参加者を返してください。

WITH races AS (
 SELECT "800M" AS race,
  [
    STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits)
  ] AS participants)

-- CROSS JOINをこちらは省略しています。
SELECT
  race,
  (
    SELECT
      name
    FROM
      UNNEST(participants),
      UNNEST(splits) AS duration
    ORDER BY duration ASC
    LIMIT 1
  ) AS runner_with_fastest_lap
FROM
  races;

-- CROSS JOINを明記しています。（上記と結果は変わりません。）
SELECT
  race,
  (
    SELECT
      name
    FROM
      UNNEST(participants)
      CROSS JOIN UNNEST(splits) AS duration
    ORDER BY duration ASC
    LIMIT 1
  ) AS runner_with_fastest_lap
FROM races;

race	runner_with_fastest_lap
800M	Kipketer

WITH races AS (
 SELECT "800M" AS race,
  [
    STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits),
    STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
    STRUCT("David" as name, NULL as splits)
  ] AS participants
)

-- まずは全てのレコードが1行で配列を利用していることを確認しましょう。
SELECT
  *
FROM
  races

-- 次に CROSS JOIN を利用することで、ARRAY のカラムに対してクエリができることを確認します。
-- ただ、splits はまだ配列のままです。
SELECT
  name,
  splits
FROM
  races,
  races.participants ;

-- 下記 2 つの動きは少し違いがあります。
-- LEFT JOIN を利用することで NULL や空を残すことができます。
-- CROSS JOIN を利用すると NULL や空を残しません。
SELECT
  duration AS finish_time
FROM
  races,
  races.participants
  LEFT JOIN participants.splits duration

SELECT
  duration AS finish_time
FROM
  races,
  races.participants,
  participants.splits duration

CROSS JOIN を使用して配列をフラット化すると、
空の配列または NULL 配列を含む行は除外されます。

WITH races AS (
 SELECT "800M" AS race,
  [
    STRUCT("Rudisha" as name, [23.4, 26.3, 26.4, 26.1] as splits),
    STRUCT("Makhloufi" as name, [24.5, 25.4, 26.6, 26.1] as splits),
    STRUCT("Murphy" as name, [23.9, 26.0, 27.0, 26.0] as splits),
    STRUCT("Bosse" as name, [23.6, 26.2, 26.5, 27.1] as splits),
    STRUCT("Rotich" as name, [24.7, 25.6, 26.9, 26.4] as splits),
    STRUCT("Lewandowski" as name, [25.0, 25.7, 26.3, 27.2] as splits),
    STRUCT("Kipketer" as name, [23.2, 26.1, 27.3, 29.4] as splits),
    STRUCT("Berian" as name, [23.7, 26.1, 27.0, 29.3] as splits),
    STRUCT("Nathan" as name, ARRAY<FLOAT64>[] as splits),
    STRUCT("David" as name, NULL as splits)
  ] AS participants
)

SELECT
  name,
  sum(duration) AS finish_time
FROM
  races,
  races.participants
  LEFT JOIN participants.splits duration
GROUP BY
  name;

name	finish_time
Murphy	102.9
Rudisha	102.19999999999999
David	NULL
Rotich	103.6
Makhloufi	102.6
Berian	106.1
Bosse	103.4
Kipketer	106
Nathan	NULL
Lewandowski	104.2

サブクエリからの配列の作成

ARRAY の中身だけを変更して、
ARRAY で最終的に返す場合の対応方法です。

WITH sequences AS (
  SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
  UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
  UNION ALL SELECT [5, 10] AS some_numbers
)

-- UNNESTをして配列の値を処理後に再度ARRAYに変換
SELECT
  some_numbers,
  ARRAY(
    SELECT
      x * 2
    FROM
      UNNEST(some_numbers) AS x
  ) AS doubled
FROM
  sequences;

some_numbers	doubled
[0, 1, 1, 2, 3, 5]	[0, 2, 2, 4, 6, 10]
[2, 4, 8, 16, 32]	[4, 8, 16, 32, 64]
[5, 10]	[10, 20]

配列のフィルタ処理

考え方としては、配列から値を取得と処理を SELECT 句でやる感じです。

WITH sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)

-- 基準値（5）未満の値だけを2倍する
SELECT
  ARRAY(
    SELECT
      x * 2
    FROM
      UNNEST(some_numbers) AS x
    WHERE
      x < 5
  ) AS doubled_less_than_five
FROM
  sequences;

doubled_less_than_five
[0, 2, 2, 4, 6]
[4, 8]
[]

ARRAY 内の重複も簡単に対応できますね。
ちょっとネストが深くなっていますが。

WITH sequences AS (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers)
SELECT
  ARRAY(
    SELECT
      DISTINCT x
    FROM
      UNNEST(some_numbers) AS x
  ) AS unique_numbers
FROM
  sequences;

unique_numbers
[0, 1, 2, 3, 5]

例えば IN を利用することで、
対象の値が含まれている ARRAY のカラムを取得することもできます。
＊正直コードでもよく書きます。

WITH sequences AS
  (SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
   UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
   UNION ALL SELECT [5, 10] AS some_numbers)

SELECT
  ARRAY(
    SELECT
      x
    FROM
      UNNEST(some_numbers) AS x
    WHERE
      2 IN UNNEST(some_numbers)
  ) AS contains_two
FROM
  sequences;

contains_two
[0, 1, 1, 2, 3, 5]
[2, 4, 8, 16, 32]
[]

配列のスキャン

ARRAY の中に対象の値が含まれているかの確認として使います。

SELECT 2 IN UNNEST([0, 1, 1, 2, 3, 5]) AS contains_value;

contains_value
true

IN を使えば ARRAY に含まれている値があるかどうか一発で確認できます。
これってよくコードでも書きますよね。
BQ 側で対応ができるならコードにするよりも意外に楽なのではないかと思いました。

WITH sequences AS
(
  SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS some_numbers
  UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS some_numbers
  UNION ALL SELECT 3 AS id, [5, 10] AS some_numbers
)

SELECT
  id AS matching_rows
FROM
  sequences
WHERE
  2 IN UNNEST(sequences.some_numbers)
ORDER BY matching_rows;

matching_rows
1
2

条件を満たすSTRUCTフィールド値のスキャン

もはや SQL を書いているというよりは、コードを書いている感じですね。

WITH sequences AS
(
  SELECT 1 AS id, [STRUCT(0 AS a, 1 AS b)] AS some_numbers
  UNION ALL SELECT 2 AS id, [STRUCT(2 AS a, 4 AS b)] AS some_numbers
  UNION ALL SELECT 3 AS id, [STRUCT(5 AS a, 3 AS b), STRUCT (7 AS a, 4 AS b)] AS some_numbers
)

SELECT
  id AS matching_rows
FROM
  sequences
WHERE EXISTS (
  SELECT
    1
  FROM
    UNNEST(some_numbers)
  WHERE
    b > 3
);

matching_rows
2
3

配列と集約

ARRAY_AGG()

順序を保証することなく ARRAY に値を集約させる。

WITH fruits AS
(
  SELECT "apple" AS fruit
  UNION ALL SELECT "pear" AS fruit
  UNION ALL SELECT "banana" AS fruit
)

-- 出力カラムを一つのARRAYに集約させます。
SELECT
  ARRAY_AGG(fruit) AS fruit_basket
FROM
  fruits;

fruit_basket
[apple, pear, banana]

ARRAY_AGG 自体は順序を保証するわけではないので、
もしなにかしらの順序を設けたい場合は ORDER BY を利用するとよいでしょう。

WITH fruits AS
(
  SELECT "apple" AS fruit
  UNION ALL SELECT "pear" AS fruit
  UNION ALL SELECT "banana" AS fruit
)

SELECT
  ARRAY_AGG(fruit ORDER BY fruit) AS fruit_basket
FROM
  fruits;

fruit_basket
[apple, banana, pear]

例えば ARRAY の合計値と ARRAY の値を一緒に表示させたい場合などには、
下記のように SUM を使うこともできます。

WITH sequences AS
(
  SELECT [0, 1, 1, 2, 3, 5] AS some_numbers
  UNION ALL SELECT [2, 4, 8, 16, 32] AS some_numbers
  UNION ALL SELECT [5, 10] AS some_numbers
)

SELECT
  some_numbers,
  (
    SELECT
      SUM(x)
    FROM
      UNNEST(s.some_numbers) x
  ) AS sums
FROM
  sequences s;

some_numbers	sums
[0, 1, 1, 2, 3, 5]	12
[2, 4, 8, 16, 32]	62
[5, 10]	15

ARRAY_CONCAT_AGG()

こちらの関数を利用すると、
ARRAY のカラムがレコードをまたいで値を集約できます。

WITH aggregate_example AS
(
  SELECT [1,2] AS numbers
  UNION ALL SELECT [3,4] AS numbers
  UNION ALL SELECT [5, 6] AS numbers
)

SELECT
  ARRAY_CONCAT_AGG(numbers) AS count_to_six_agg
FROM
  aggregate_example;

count_to_six_agg
[1, 2, 3, 4, 5, 6]

配列から文字列への変換

ARRAY_TO_STRING

ARRAY を文字列として連結したい場合には ARRAY_TO_STRING が便利です。

WITH greetings AS (SELECT ["Hello", "World"] AS greeting)

SELECT
  ARRAY_TO_STRING(greeting, " ") AS greetings
FROM
  greetings;

greetings
Hello World

ARRAY_TO_STRING には引数が利用できます。

第一引数：対象の ARRAY
第二引数：関数が出力を生成するために入力項目間に挿入する区切り文字
第三引数： NULL の置き換えを指定（省略すると無視します）

SELECT
  ARRAY_TO_STRING(arr, ".", "N") AS non_empty_string,
  ARRAY_TO_STRING(arr, ".", "") AS empty_string,
  ARRAY_TO_STRING(arr, ".") AS omitted
FROM
  (SELECT ["a", NULL, "b", NULL, "c", NULL] AS arr);

non_empty_string	empty_string	omitted
a.N.b.N.c.N	a..b..c.	a.b.c

配列の結合

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

count_to_six
[1, 2, 3, 4, 5, 6]

配列の圧縮

UNNEST と WITH OFFSET を使用すると ARRAY を圧縮できます。

WITH OFFSET とは、
UNNEST で ARRAY から生成される行ごとに、
「オフセット」値（カウントはゼロから始まります）を含む別の列を返します。
＊Java などの配列の index で値を取得ができるようになります。

WITH combinations AS
(
  SELECT
    ['a', 'b'] AS letters,
    [1, 2, 3] AS numbers
)

SELECT
  ARRAY_AGG(STRUCT(letter, numbers[OFFSET(letters_offset)] AS number)) AS pairs
FROM
  combinations,
  UNNEST(letters) AS letter WITH OFFSET AS letters_offset;

pairs
[{ letter: "a", number: 1 },
{ letter: "b", number: 2 }]

配列の配列の作成

ARRAY の ARRAY は作成することが BigQuery ではできません。
＊2020/10 時点

そのため ARRAY の ARRAY をするには STRUCT を利用する必要があります。

WITH points AS
(
  SELECT [1, 5] as point
  UNION ALL SELECT [2, 8] as point
  UNION ALL SELECT [3, 7] as point
  UNION ALL SELECT [4, 1] as point
  UNION ALL SELECT [5, 7] as point
)

SELECT
  ARRAY(
    SELECT
      STRUCT(point)
    FROM
      points
  ) AS coordinates;

coordinates
[{point: [1,5]},
{point: [2,8]},
{point: [5,7]},
{point: [3,7]},
{point: [4,1]}]

ちょっとした遊び

平仮名の組み合わせを簡単に作りたいなと思い、
下記のような配列を作ってみました。

正直 3 単語の組み合わせの数が 9 万を超えるので、
2 単語の組み合わせでいいかなとは思いましたが、
コードを書くよりも比較的簡単に作ることができました。

よって、コードではなく SQL で書こうとはならないのですが、ものによっては GO や Python などを利用するよりも SQL が非常に楽で安全な場合もあるのだなと感じました。

ちなみに、もっといい書き方があるとは思います。
＊教えて欲しい！　！

WITH sequences AS
  (SELECT [
  'あ','い','う','え','お',
  'か','き','く','け','こ',
  'さ','し','す','せ','そ',
  'た','ち','つ','て','と',
  'な','に','ぬ','ね','の',
  'は','ひ','ふ','へ','ほ',
  'ま','み','む','め','も',
  'や','ゆ','よ',
  'ら','り','る','れ','ろ',
  'わ','を','ん'] AS n)

-- 2単語の組み合わせ
SELECT
 na1 as firstL,
 na2 as lastL,
 concat(na1, na2) as name
FROM
  sequences
CROSS JOIN UNNEST(sequences.n) AS na1
CROSS JOIN UNNEST(sequences.n) AS na2
where
na1 not in ('を','ん')
and na1 != na2

-- 3単語の組み合わせ
SELECT
 na1 as firstL,
 na2 as secondL,
 na3 as lastL,
 concat(na1, na2, na3) as name
FROM
  sequences
CROSS JOIN UNNEST(sequences.n) AS na1
CROSS JOIN UNNEST(sequences.n) AS na2
CROSS JOIN UNNEST(sequences.n) AS na3
where
na1 not in ('を','ん')
and na1 != na2
and na2 != na3

BigQueryのArrayを理解する。

目的

参考資料

Arrayとは

制限

使ってみる

ARRAYの型宣言

ARRAYの作成

[]を利用した作成

生成した結果をARRAYで作成

日付の配列の生成

次にアクセスです！（作ったらアクセスしないと）

OFFSET と ORDINAL

ARRAY_LENGTH

配列のフラット化

CROSS JOIN

ネストされた配列のクエリ

考え方

STRUCT内のARRAY型フィールドのクエリ

サブクエリからの配列の作成

配列のフィルタ処理

配列のスキャン

条件を満たすSTRUCTフィールド値のスキャン

配列と集約

ARRAY_AGG()

ARRAY_CONCAT_AGG()

配列から文字列への変換

ARRAY_TO_STRING

配列の結合

配列の圧縮

配列の配列の作成

ちょっとした遊び

Discussion