概要
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 |
やった方法(下記イメージ)
- それぞれの親・子テーブルを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 |
- 子テーブルの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 |
- 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 |
- 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 |
- このテーブルを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