#概要
今回はBigQueryの集合演算子についてまとめたいと思います。
1. 集合演算子とは
2. 集合演算子を使う上の注意点
3. データ紹介
4. BigQueryで使える集合演算子
5. 集合演算子を使って、即席のテーブルを作成
※BigQueryのSQL言語設定は、標準です。
#1. 集合演算子とは
早速ですが、集合演算子とはどんなものか一言で説明してみましょう。
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#set-operators
集合演算子は、2 つ以上の入力クエリの結果を 1 つの結果セットに結合します。
例えばSELECT文を2つ書いたとしましょう。
集合演算子を利用することで、2つのSELECT文の結果を結合し出力することができます。
この時、結果がどのように出力されるかは、集合演算子の種類に応じて変わってきます。
#2. 集合演算子を使う上の注意点
集合演算子を利用するにはいくつかのルールがあります。
- 結合する結果の列数は同じでなければならない。
- 結合する結果列のデータ型は同じでないとならない。
- 列名は最初に書いた方の列名になる。
これらに注意しながら、集合演算子を利用する必要があります。
#3. データ紹介
テーブル：item.item_table
| user_id | item | price |
|---|---|---|
| A | みかん | 50 |
| A | りんご | 100 |
| B | りんご | 100 |
| A | もやし | 19 |
| B | りんご | 90 |
スキーマ詳細は以下の通りです。
#4. BigQueryで使える集合演算子
BigQueryでサポートされている集合演算子は以下の通りです。
| 集合演算子 | 戻り値 | イメージ | 重複 |
|---|---|---|---|
| UNION ALL | 結果を結合したもの |  |
あり |
| UNION DISTINCT | 結果を結合したもの |  |
なし |
| INTERSECT DISTINCT | 結果の共通部分のみ |  |
なし |
| EXCEPT DISTINCT | 一つ目のクエリの結果から 両方の結果の共通部分を除いたもの |
 |
なし |
UNION ALL使用例
SELECT
*
FROM
item.item_table
UNION ALL
SELECT
*
FROM
item.item_table;
結果は以下の通りです。
二つのSELECT文の結果が、縦に結合されていますね。
表で見た通り、重複はある状態で結合されています。
UNION DISTINCT使用例
SELECT
*
FROM
item.item_table
UNION DISTINCT
SELECT
*
FROM
item.item_table
ORDER BY
user_id
, item
, price;
結果は以下の通りです。
先程のUNION ALLと比較して、重複が無くなっているのがわかると思います。
名前にDISTINCTが付いてるだけありますね。
INTERSECT DISTINCT使用例
SELECT
*
FROM
item.item_table
WHERE
item = 'りんご'
INTERSECT DISTINCT
SELECT
*
FROM
item.item_table
ORDER BY
user_id
, item
, price;
結果は以下の通りです。
一つ目のSELECT文で、WHERE item ='りんご'としているので、結果もitemがりんごのものだけが集計されました。ここではわかりにくいですが、重複も無くなった状態で結果が出力されています。
EXCEPT DISTINCT使用例
SELECT
*
FROM
item.item_table
EXCEPT DISTINCT
SELECT
*
FROM
item.item_table
WHERE
user_id = 'A'
ORDER BY
user_id
, item
, price;
結果は以下の通りです。
二つ目のSELECT文で、WHERE user_id = 'A'としています。
したがって一つ目のSELECT文の結果から、これらの結果が差し引かれた結果が返っています。
#5. 集合演算子を使って、即席のテーブルを作成
ちょっとしたテーブルを作って、挙動を確認したい場合などがあると思います。その際、わざわざテーブルを作るのは面倒ですよね。そんな時は、UNION ALLを使って、即席テーブルを作ってしまいましょう。例えば、、、
WITH with_1 AS (
SELECT
'A' AS item
UNION ALL
SELECT
'B' AS item
UNION ALL
SELECT
'C' AS item
)
SELECT
*
FROM
with_1;
結果は次の通りです。
WITH文とUNION ALLを利用することで、with_1という即席テーブルを作ることができました。テーブル作成と比較して、修正が簡単にできる点が、特に便利なのかなと思いました。また、無料で実行できるのも魅力的ですね。
#まとめ
以上がBigQueryの集合演算子のまとめとなります。
他のデータベース言語と比較して、独特な名前がついているので、気を付けましょう。
#参考サイト
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#set-operators