【BigQuery】集合演算子(UNION…)まとめ



#概要
今回は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文の結果を結合し出力することができます。
この時、結果がどのように出力されるかは、集合演算子の種類に応じて変わってきます。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/fab5c746-7f56-d52f-95b2-81519b28b4b5.png)



#2. 集合演算子を使う上の注意点

集合演算子を利用するにはいくつかのルールがあります。

1. 結合する結果の列数は同じでなければならない。
2. 結合する結果列のデータ型は同じでないとならない。
3. 列名は最初に書いた方の列名になる。

これらに注意しながら、集合演算子を利用する必要があります。

#3. データ紹介

テーブル：item.item_table

|user_id|item|price|
|----|----|-----|
|A|みかん|50|
|A|りんご|100|
|B|りんご|100|
|A|もやし|19|
|B|りんご|90|


スキーマ詳細は以下の通りです。
![schema詳細.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/cb982b9e-1a9a-bdd3-f164-9825d3b24b3b.png)


#4. BigQueryで使える集合演算子　
BigQueryでサポートされている集合演算子は以下の通りです。

| 集合演算子 |　戻り値 |イメージ|重複 |
|:---------|:---------|:---------|:---------|
| UNION ALL| 結果を結合したもの |![unionall.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/fbdb4e80-81ae-54f9-de4a-d773d9d654f2.png)|**あり**|
| UNION DISTINCT | 結果を結合したもの|![uniondistinct.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/b458865e-f182-7217-0778-59de5f6f8b8d.png)|なし|
| INTERSECT DISTINCT | 結果の共通部分のみ|![intersect.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/487407aa-5ab1-cb65-8454-21f1010c0103.png)|なし|
| EXCEPT DISTINCT | 一つ目のクエリの結果から<br>両方の結果の共通部分を除いたもの |![except.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/588d8fa9-da2b-cf9f-84ff-e0f9bdec1ee9.png)|なし|



**<font color="Green">UNION ALL使用例</font>**

```sql:BigQuery：結果を結合したものを、重複ありで返します。

SELECT
    * 
FROM
  item.item_table 
UNION ALL 
SELECT
    * 
FROM
  item.item_table;
```
結果は以下の通りです。
![unionkekka.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/26777fde-e5d8-fd79-01c2-ba6051260602.png)

二つのSELECT文の結果が、縦に結合されていますね。
表で見た通り、重複はある状態で結合されています。

**<font color="Green">UNION DISTINCT使用例</font>**


```sql:BigQuery：結果を結合したものを、重複なしで返します。

SELECT
    * 
FROM
  item.item_table 
UNION DISTINCT 
SELECT
    * 
FROM
  item.item_table 
ORDER BY
  user_id
  , item
  , price;

```
結果は以下の通りです。
![uniondistinctkekka.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/b1f5d41a-156f-8e70-0cdc-e3a1900fa171.png)


先程のUNION ALLと比較して、重複が無くなっているのがわかると思います。
名前にDISTINCTが付いてるだけありますね。

**<font color="Green">INTERSECT DISTINCT使用例</font>**

```sql:BigQuery：結果の共通部分を、重複なしで返します。


SELECT
    * 
FROM
  item.item_table 
WHERE
  item = 'りんご' 
INTERSECT DISTINCT 
SELECT
    * 
FROM
  item.item_table 
ORDER BY
  user_id
  , item
  , price;
```
結果は以下の通りです。
![intersect distinct.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/2699019e-e561-d802-3f3f-fdd213df0e32.png)

一つ目のSELECT文で、WHERE item ='りんご'としているので、結果もitemがりんごのものだけが集計されました。ここではわかりにくいですが、重複も無くなった状態で結果が出力されています。

**<font color="Green">EXCEPT DISTINCT使用例</font>**

```sql:BigQuery：一つ目のクエリの結果から、両方の結果の共通部分を除いたものを返します。
SELECT
    * 
FROM
  item.item_table 
EXCEPT DISTINCT 
SELECT
    * 
FROM
  item.item_table 
WHERE
  user_id = 'A' 
ORDER BY
  user_id
  , item
  , price;
```
結果は以下の通りです。
![exceptdistinctkekka.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/d843436f-be46-cfcd-2d08-e4a199f0ef31.png)

二つ目のSELECT文で、WHERE user_id = 'A'としています。
したがって一つ目のSELECT文の結果から、これらの結果が差し引かれた結果が返っています。

#5. 集合演算子を使って、即席のテーブルを作成
ちょっとしたテーブルを作って、挙動を確認したい場合などがあると思います。その際、わざわざテーブルを作るのは面倒ですよね。そんな時は、UNION ALLを使って、即席テーブルを作ってしまいましょう。例えば、、、

```sql:BigQuery：即席テーブル作成

WITH with_1 AS ( 
  SELECT
      'A' AS item 
  UNION ALL 
  SELECT
      'B' AS item 
  UNION ALL 
  SELECT
      'C' AS item
) 
SELECT
    * 
FROM
  with_1;
```
結果は次の通りです。
![kasetsu.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/423202/2ed027d6-6a43-b5dd-54e9-452f6e72ccc7.png)


WITH文とUNION ALLを利用することで、with_1という即席テーブルを作ることができました。テーブル作成と比較して、修正が簡単にできる点が、特に便利なのかなと思いました。また、無料で実行できるのも魅力的ですね。


#まとめ
以上がBigQueryの集合演算子のまとめとなります。
他のデータベース言語と比較して、独特な名前がついているので、気を付けましょう。


#参考サイト
https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?hl=ja#set-operators