BigQueryでテーブルをARRAYで結合したい

## 概要

BigQueryはクエリ言語にもかかわらず、ARRAYやSTRUCTURE、FORやWHILEも使用することができる。

今回は、2つのデータがそれぞれARRAYをもっている場合。

親テーブルのARRAYのいずれかの値を持つ子テーブルの数とそのある列の合計値を抽出したかったのでその整理。

##　やりたいこと

親テーブル

| fruit_id | tags |
| -- | -- |
| 123 | orange, mandarin |
| 456 | orange, grapefruit |  
| 789 | grape | 

子テーブル

| vote_id | tagst| people |
| -- | -- | -- |
| 987 | orange, mandarin, grapefruit | 3 | 
| 654 | grape | 4 |
| 321 | orange | 1 |

出力したい結果

| fruit_id | tags | vote_count |　people_count | 
| -- | -- | -- | -- |
| 123 | orange, mandarin | 2 | 4 | 
| 456 | orange, grapefruit | 2 | 4 |
| 789 | grape | 1 | 4 | 

親テーブルのtagsも子テーブルのtagsも複数の値を持っている。

親テーブルの各fruit_idについて、いずれかのtagを持つ子テーブルのvote_idを求めたい。

複数のtagをつけてfruit_idとvote_idを指定できることが重複のカウントを引き起こしてしまって、難点だった。

## 使用する関数

`UNNEST`: ARRAYをテーブルにする関数。

これを

| row| num_array |
| -- | -- |
| 1 | [1,2,3] |

こう変換できるイメージ。

| row | num |
| -- | -- |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |


[公式: 配列内の要素をテーブル内の行に変換する](https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#flattening_arrays)

## やった方法(下記イメージ)

1. それぞれの親・子テーブルをUNNESTでテーブルに直す。 

親テーブル

| fruit_id | fruit_tag | 
| -- | -- | 
| 123 | orange|
| 123 | mandarin | 
| 456 | orange |
| 456 | grapefruit | 
| 789 | grape |

子テーブル

| vote_id | vote_tag | people |
| -- | -- | -- |
| 987 | orange | 3 |
| 987 | mandarin| 3 |
| 987 | grapefruit | 4 |
| 654 | grape | 4 |
| 321 | orange | 1 |

2. 子テーブルのtagに一致するfruit_idをすべて結合する。

| vote_id | vote_tag | fruit_id | fruit_tag
| -- | -- | -- | -- | -- |
| 987 | orange　　| 123 | orange |
| 987 | orange | 456 | orange |
| 987 | mandarin| 123 | mandarin |
| 987 | grapefruit | 456 | grapefruit |
| 654 | grape |789 | grape |
| 321 | orange | 123| orange |
| 321 | orange | 456 | orange |

3. vote_idとfruit_idを見て重複を削除する

| vote_id | vote_tag | fruit_id | fruit_tag
| -- | -- | -- | -- | -- |
| 987 | orange　　| 123 | orange |
| 987 | orange | 456 | orange |
| 987 | grapefruit | 456 | grapefruit |
| 654 | grape |789 | grape |
| 321 | orange | 123| orange |
| 321 | orange | 456 | orange |

4. vote_idの変数を再結合で呼び出す

| vote_id | vote_tag | fruit_id | fruit_tag | people |
| -- | -- | -- | -- | -- | -- |
| 987 | orange　　| 123 | orange | 3 |
| 987 | orange | 456 | orange | 3 |
| 654 | grape | 789 | grape | 4 |
| 321 | orange | 123| orange | 1 |
| 321 | orange | 456 | orange | 1 |

5. このテーブルをfruit_tagでグルーピングして集計する

| fruit_id | vote_count | people_count |
| -- | -- | -- | -- |
| 123 | 2 | 4 |
| 456 | 2 | 4 |
| 654 | 1 | 4 |

## 実装

```sql
WITH votes AS (
    SELECT 
        987 as vote_id, 
        [ 'orange', 'mandarin', 'grapefruit'] as tags,
        3 as people
    UNION ALL SELECT 
        654, 
        ['grape'],
        4
    UNION ALL SELECT 
        321, 
        ['orange'],
        1
    ),
fruits AS (
    SELECT 
        123 as fruit_id,
        ['orange', 'mandarin'] as tags
    UNION ALL SELECT 
        456,
        ['orange', 'grapefruit']
    UNION ALL SELECT 
        789,
        ['grape']
)

SELECT
    fruit_id,
    COUNT(1) as vote_count,
    SUM(people) as people_count
FROM(
    -- (3) 重複を削除する
    SELECT DISTINCT 
        fruit_id,
        vote_id,
        people
    FROM 
        (
        -- votes と fruitsのtagsをテーブルにしてtagで結合
        SELECT 
            vote_id as base_vote_id,
            vote_tag,
            fruit_id,
            fruit_tag
        FROM 
            votes
        CROSS JOIN UNNEST(votes.tags) AS vote_tag -- (1)

        INNER JOIN(
            SELECT
                fruit_id,
                fruit_tag
            FROM
                fruits
            CROSS JOIN UNNEST(fruits.tags) AS fruit_tag -- (1)
        )ON vote_tag = fruit_tag
        )

    -- (4) vote_idからpoepleを結合する
    INNER JOIN(
        SELECT
            vote_id,
            people
        FROM
            votes
    )ON base_vote_id = vote_id
)
GROUP BY fruit_id
```