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

やりたいこと

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

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

対象

対象データ

『達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ』に登場する都道府県別人口テーブルを参考にさせていただいた。

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

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

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

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

対象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に移植する。

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つについて、差集合をとる。差集合の使い方については参考リンクを参照した。

-- 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とした。

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という集合を定義する。

  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句で取り出す。

  on with_alias.district = no_alias.district
where
  no_alias.district is null 
;

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

検証結果

district	total

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

検算

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

検算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