SQLで、CASE式で作った集合同士の差分を取る

# やりたいこと
CASE式で独自のコード体系を付けて作成した集合同士の差分をとりたい。

たとえば、MySQLで動いた構文がPostgreSQLでも動くか試したいときなどに使う。

# 対象

## 対象データ
[『達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ』](https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-CodeZine-BOOKS/dp/4798157821)に登場する都道府県別人口テーブルを参考にさせていただいた。

pref_name|population|
---------|----------|
佐賀       |       100|
徳島       |       100|
愛媛       |       150|
東京       |       400|
福岡       |       300|
群馬       |       500|
長崎       |       200|
香川       |       200|
高知       |       200|

こちらを加工して、下記のデータセットを作成する。

district|sum(population)|
--------|---------------|
九州      |            600|
四国      |            650|
その他     |            900|

データセット作成SQLは下記の通り。**CASE式で、既存のデータに新しいラベルを定義し、集合演算と組み合わせて独自のデモグラフィック情報を作り出している**。

## 対象SQL
```sql:
select
  case pref_name
    when '徳島' then '四国'
    when '香川' then '四国'
    when '愛媛' then '四国'
    when '高知' then '四国'
    when '福岡' then '九州'
    when '佐賀' then '九州'
    when '長崎' then '九州'
    else 'その他' end as district, -- district means parts of nation
  sum(population)
from poptbl
group by district
;
```

# 比較したいSQL

検証したわけでは無いのだが、上記のSQLで定義したdistrictというエイリアスと使ってgroup byすると、構文的におかしくなるケースがあるらしい。そこで、selectで使った構文をそのままgroup byに移植する。

```sql:
select
  case pref_name
    when '徳島' then '四国'
    when '香川' then '四国'
    when '愛媛' then '四国'
    when '高知' then '四国'
    when '福岡' then '九州'
    when '佐賀' then '九州'
    when '長崎' then '九州'
    else 'その他' end as district, -- district means parts of nation
  sum(population)
from poptbl
group by
  case pref_name
    when '徳島' then '四国'
    when '香川' then '四国'
    when '愛媛' then '四国'
    when '高知' then '四国'
    when '福岡' then '九州'
    when '佐賀' then '九州'
    when '長崎' then '九州'
    else 'その他' end
;
```

# 比較検証SQL
エイリアスを使ったgroup by、そうでないgroup byの2つについて、差集合をとる。差集合の使い方については参考リンクを参照した。

```sql:
-- Calc diff set using left join and is null
-- no diff
select
  with_alias.district
  , with_alias.total
from (
  select
    case pref_name
      when '徳島' then '四国'
      when '香川' then '四国'
      when '愛媛' then '四国'
      when '高知' then '四国'
      when '福岡' then '九州'
      when '佐賀' then '九州'
      when '長崎' then '九州'
      else 'その他' end as district,
    sum(population) as total
  from poptbl
  group by district ) as with_alias
  left join (
    select
      case pref_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他' end as district,
      sum(population) as total
    from poptbl
    group by
      case pref_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他' end ) as no_alias
  on with_alias.district = no_alias.district
where
  no_alias.district is null
;
```

## 解説
まず、from句直下でエイリアス使用集合を`with_alias`とした。

```sql 
from (
  select
    case pref_name
      when '徳島' then '四国'
      when '香川' then '四国'
      when '愛媛' then '四国'
      when '高知' then '四国'
      when '福岡' then '九州'
      when '佐賀' then '九州'
      when '長崎' then '九州'
      else 'その他' end as district,
    sum(population) as total
  from poptbl
  group by district ) as with_alias
```

次に、上記の集合にleft joinする`no_alias`という集合を定義する。

```sql 
  left join (
    select
      case pref_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他' end as district,
      sum(population) as total
    from poptbl
    group by
      case pref_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他' end ) as no_alias
```

最後に、両方の集合に存在するdistrict列を結合し、`no_alias`側がnullとなる情報のみをwhere句で取り出す。

```sql 
  on with_alias.district = no_alias.district
where
  no_alias.district is null 
;
```
A .. with_alias, B .. no_alias とすると、下図の赤色部分を表示するということだ。
これが差集合という概念であるらしい。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/34636/ad0307e9-5580-673f-8029-c5acac9896c0.png)

# 検証結果

district|total|
--------|-----|

レコードが1件も検出されないので、差分なしということがわかる。

# 検算
そもそも、差分が検出されるクエリなのかを知りたい。
with_aliasに差分レコードを追加して、検出されるか試してみよう。

## 検算SQL
自分は群馬県出身なので、群馬を使う♨。
草津よいとこ薬の出湯
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/34636/fe036275-9c64-5fbc-3fb1-b3dc1c82fb4a.png)


```sql:
-- Calc diff set using left join and is null
-- with diff
select
  with_alias.district
  , with_alias.total
from (
  select
    case pref_name
      when '徳島' then '四国'
      when '香川' then '四国'
      when '愛媛' then '四国'
      when '高知' then '四国'
      when '福岡' then '九州'
      when '佐賀' then '九州'
      when '長崎' then '九州'
      when '群馬' then '北関東' -- this is diff record
      else 'その他' end as district,
    sum(population) as total
  from poptbl
  group by district ) as with_alias
  left join (
    select
      case pref_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他' end as district,
      sum(population) as total
    from poptbl
    group by
      case pref_name
        when '徳島' then '四国'
        when '香川' then '四国'
        when '愛媛' then '四国'
        when '高知' then '四国'
        when '福岡' then '九州'
        when '佐賀' then '九州'
        when '長崎' then '九州'
        else 'その他' end ) as no_alias
  on with_alias.district = no_alias.district
where
  no_alias.district is null 
;
```

## 検算結果

district|total|
--------|-----|
北関東     |  500|

無事、追加した群馬のレコードが差分として出力された。

# おわりに
差集合を使って、SQLの構文が正しいかどうかを検証した。Oracleなら、`minus`演算を使うと簡単に差分が取れる。

さて、今回試したのはWindows版の`MySQL8.0.19`であるが、ほかのRDBではどのような結果になるだろうか。

そもそも、SQLが長くなりすぎた。ビューを作ったほうが良かったのかもしれない。

# 参考リンク
- https://qiita.com/Hiraku/items/71873bf31e503eb1b4e1
- https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

(2020/7/26追記) PostgreSQLには`except`句があるので、こちらでも差集合が取得できる。
https://www.techlive.tokyo/archives/4011