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

公式: 配列内の要素をテーブル内の行に変換する

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

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

1. 子テーブルの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 |

1. 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 |

1. 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 |

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

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

実装

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