概要

今回は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使用例

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


SELECT
    * 
FROM
  item.item_table 
UNION ALL 
SELECT
    * 
FROM
  item.item_table;

結果は以下の通りです。

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

UNION DISTINCT使用例

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


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

結果は以下の通りです。

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

INTERSECT DISTINCT使用例

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



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使用例

BigQuery：一つ目のクエリの結果から、両方の結果の共通部分を除いたものを返します。

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を使って、即席テーブルを作ってしまいましょう。例えば、、、

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;

結果は次の通りです。

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

まとめ

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

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

概要

1. 集合演算子とは

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

3. データ紹介

4. BigQueryで使える集合演算子

5. 集合演算子を使って、即席のテーブルを作成

まとめ

参考サイト