【Treasure Data】集合演算子

こんにちは！noteをお読みいただきありがとうございます。
smkt事業部note運用チームです。

Treasure Data では、主にSQLを用いてクエリを記述していくことができます。
SQLには様々な演算子が存在しますが、
今回はTreasure Dataを実際に運用していく中で経験した実例なども交えて、
SQLの「集合演算子」についてご紹介します。

「集合演算子」は2つの集合（問合せ結果）の間で集合演算を行う演算子で
SQLを学習していく上で基礎的な知識です。
よく混同されやすい「JOIN演算子」との違いなども併せて紹介しておりますので、
「Treasure Dataを使ってみたいが、SQLの記述力に不安がある」初心者の方などのお役に立てればと思います。

概要

• Treasure Data では Hive および Presto という
  SQL-Like な記述ができる分散SQLエンジンを採用しています

• 今回はSQLで抽出した2つ以上のSELECTの結果を、1つに結合して表示できる
  「集合演算子」の使用方法をサンプルを交えて解説します

参考: SQLとは？ - SQLとは - Japanese (日本のコンテンツ) Product Documentation- Reference - Treasure Data Product Documentation

1.UNION

• 抽出した複数のSELECT結果を一つにまとめて表示する和集合の演算子です
  以下の図を見ていただくと、イメージがつきやすいと思います

• 結合するSELECT結果で重複するレコードは削除され、
  ユニークな行のみが表示されます

• 結合するSELECT句の中身は、
  取得するカラム数・データ型が同じで互換性がある必要があります

構文

SELECT * FROM テーブル名
UNION
SELECT * FROM テーブル名
;

使用例

• サンプルとして、以下の2つのテーブルを用意します

sample1

sample2

SELECT 
  name
  , birthplace 
FROM sample1
UNION
SELECT 
  name
  , birthplace 
FROM sample2
;

結果

• 使用例のクエリは以下のデータを返します

  • sample1とsample2のテーブルでは、
    (name:Tanaka birthplace:Nagoya)がどちらのテーブルにも
    存在しているので、重複となり1行にまとめられます

2.UNION ALL

• UNIONと同じく、抽出した複数のSELECT結果を
  一つにまとめて表示しますが、
  結合するSELECTの結果で重複したレコードも
  結果として表示されます

• 結合するSELECT句の中身は、
  取得するカラム数・データ型が同じで互換性がある必要があります

構文

SELECT * FROM テーブル名
UNION　ALL
SELECT * FROM テーブル名
;

使用例

• 今回もUNIONの使用例の際に用意した
  sample1 と sample2 のテーブルを使用します

SELECT 
  name
  , birthplace 
FROM sample1
UNION ALL
SELECT 
  name
  , birthplace 
FROM sample2
;

結果

• 使用例のクエリは以下のデータを返します

  • どちらのテーブルにも存在している
    (name:Tanaka birthplace:Nagoya)は重複していますが、
    削除されずに2レコード分表示されます

3.EXCEPT

• あるSELECT結果には含まれるが、別のSELECT結果には含まれない
  レコードを結果として表示する差集合の演算子です
  以下の図を見ていただくと、イメージがつきやすいと思います

• あるSELECT結果と別のSELECT結果をカラムの値で比較し、
  各カラムの値がすべて一致するレコードは抽出しません

• 結合するSELECT句の中身は、
  取得するカラム数・データ型が同じで互換性がある必要があります

構文

SELECT * FROM テーブル名
EXCEPT
SELECT * FROM テーブル名
;

使用例

• 今回もUNIONの使用例の際に用意した
  sample1 と sample2 のテーブルを使用します

SELECT 
  name
  , birthplace 
FROM sample1
EXCEPT
SELECT 
  name
  , birthplace 
FROM sample2
;

結果

• 使用例のクエリは以下のデータを返します

  • どちらのテーブルにも存在している
    (name:Tanaka birthplace:Nagoya)が除かれた
    sample1の3レコード分が表示されます

4.INTERSECT

• 2つのSELECT結果に共通するレコードを結果として表示する
  積集合の演算子です
  以下の図を見ていただくと、イメージがつきやすいと思います

• あるSELECT結果と別のSELECT結果をカラムの値で比較し、
  　各カラムの値がすべて一致するレコードを抽出します

• 結合するSELECT句の中身は、
  取得するカラム数・データ型が同じで互換性がある必要があります

構文

SELECT * FROM テーブル名
INTERSECT
SELECT * FROM テーブル名
;

使用例

• 今回もUNIONの使用例の際に用意した
  sample1 と sample2 のテーブルを使用します

SELECT 
  name
  , birthplace 
FROM sample1
INTERSECT
SELECT 
  name
  , birthplace 
FROM sample2
;

結果

• 使用例のクエリは以下のデータを返します

  • どちらのテーブルにも存在している
    (name:Tanaka birthplace:Nagoya) 1レコード分が表示されます

5.Tips

5-1.JOINとの違い

• テーブルの結合に用いられるJOIN演算子ですが、
  UNION演算子とよく混同されがちです

• UNION演算子がSELECT結果で得られた2つ以上のテーブルの行を結合するのに対し、
  JOIN演算子は結合条件（key）を指定し、2つ以上のテーブルの列を結合することができます
  UNION演算子：SELECT結果で得られた行の結合
  JOIN演算子：テーブルの列の結合

• JOIN演算子を使用する際には、結合条件となるkeyが必要となり、
  keyに指定したカラムに共通した値が入っているかどうかを一行一行精査し、
  keyのカラムの値が合致した時、異なるテーブルに存在するレコードを繋ぎます
  その点が単純にSELECTの結果となる行を結合するUNION演算子とは大きく異なります

5-2. カッコの有無によるクエリ実行の変化

• 実際の運用で、同じクエリでもカッコの使用により、
  結合結果が全く変わってくることがありました
  カッコのありなしで、SQLの動きがどう変わってくるのかをご紹介します

• ここでもUNIONの使用例の際に用意した
  sample1 と sample2 のテーブルを例として参照し、解説していきます

• 比較するのは、以下2文のクエリです
  違いはカッコの使用のみで、それ以外のクエリは全く同じです

test1

SELECT 
  *
FROM sample1
EXCEPT
SELECT  
  *
FROM sample2  
UNION ALL  
SELECT 
  *
FROM sample2
EXCEPT
SELECT  
  *
FROM sample1
;

test2

(SELECT 
  *
FROM sample1
EXCEPT
SELECT  
  *
FROM sample2  
)  
UNION ALL  
(SELECT 
  *
FROM sample2
EXCEPT
SELECT  
  *
FROM sample1  
)
;

test1は以下のフロー図のように、計算が進んでいきます

① sample1にあって、sample2にないレコードが抽出される

その結果、 (number:4 name:Tanaka birthplace:Nagoya)以外のsample1の
レコードが抽出されます

② ①の結果にsample2の全レコードが足される

③ ②の結果から、sample1の全レコードが引かれる

よってtest1のクエリは、
(number:1 name:Itou birthplace:Tokyo)
(number:2 name:Watanabe birthplace:Fukuoka)
(number:3 name:Yamamoto birthplace:Miyagi)
の3件を返します

次にtest2です

① sample1にあって、sample2にないレコードが抽出される

これはtest1と同じ動きです

② ①の結果にsample2にあって、sample1にないレコードが足される

(number:4 name:Tanaka birthplace:Nagoya)以外のsample2の
レコードが①の結果に足されます

よってtest2のクエリは、
(number:1 name:Satou birthplace:Tokyo)
(number:2 name:Suzuki birthplace:Tokyo)
(number:3 name:Takahashi birthplace:Osaka)
(number:1 name:Itou birthplace:Tokyo)
(number:2 name:Watanabe birthplace:Fukuoka)
(number:3 name:Yamamoto birthplace:Miyagi)
の6件を返します

• この様にカッコにより、計算のまとまりと優先度が指定されるため、
  同じクエリでも結果として返す値が全く異なってきます

• test2は、データ値の比較として有効な方法です
  クエリの結果は、「sample1にしかないレコードとsample2にしかないレコード」となるので
  結果が0レコードであれば、sample1とsample2は同じデータといえます
  ただし、sample1とsample2に重複したレコードがあると上手くいかないので注意が必要です

6. まとめ

今回は、Treasure Data で使用できるSQLの集合演算子をまとめてみました
実運用でも非常によく使用する基礎的な演算子です
今後も初心者目線で別の記事をあげていければと思います
最後までお読みいただきありがとうございました！

参考: 中山清喬　飯田理恵子(2018) . 『スッキリわかるSQL入門　第2版　ドリル 222 問付き！』 . 株式会社インプレス .

●●●

パーソルP&Tでは、CDP導入・活用支援サービスに注力しています。

データ基盤構築（トレジャーデータ・BI） - セールスマーケティングサービス｜パーソルプロセス＆テクノロジー

●●●

✉ サービスに関するお問い合わせ ✉
パーソルプロセス＆テクノロジー SMKT事業部
smkt_markegr_note@persol-pt.co.jp

●●●

またSMKT事業部では　データエンジニアを募集しています。
ご興味をお持ちの方はこちらからご応募ください！

パーソルプロセス＆テクノロジー株式会社 | データエンジニア_＜フレックス・リモート勤務＞大手企業のビッグデータ解析、マーケティング精度向上の企画・施策に参画

▽その他募集職種こちらから▽

デジタルマーケティング - 募集要項 - セールスマーケティング事業部キャリア採用

●●●

マガジン「Treasure Data Tips集」では、現場で役立つTipsをまとめています。是非こちらもご覧ください。

TreasureData Tips集｜パーソルプロセス＆テクノロジー SMKT事業部｜note