Blog

ブログ

どうも、PostgreSQL推進委員会のアフロ・ナミヘイです。

今回はOR条件とCASE式を組み合わせたレコード集約のテクニックをご紹介します。

 

あるテーブルに同一体系のコード値を持つカラムが2つあり、その2つのコード値をマスタと結合して名称変換したい、とします。
例えば、以下のようなケースです。

 

【社員情報】

社員名	現住所地域コード	出身地域コード
貴ノ岩 義司	J001	M800
貴公俊 剛	J001	J002

 

【地域マスタ】

地域コード	地域名称
J001	東京都
J002	栃木県
M800	ウランバートル

 

モンゴル・ウランバートル出身で東京都在住の貴ノ岩くんと、栃木県出身で東京都在住の貴公俊くんが社員情報テーブルで管理されているとします。

現住所と出身地域はコードで持っており、対応する地域名称は地域マスタで保持しています。

どちらのテーブルも実際にはもっと多くのレコードを持っているはずですが、ここで使用するレコードだけを抜粋していると思ってください。

このテーブルから以下のように、社員ごとに現住所と出身地域を地域名称で出力したいとします。

 

【出力：社員の現住所と出身地域】

社員名	現住所地域	出身地域
貴ノ岩 義司	東京都	ウランバートル
貴公俊 剛	東京都	栃木県

さて、このような出力を得るには、現住所地域コードと出身地域コードに対応する地域名称をそれぞれ地域マスタから取ってこなければなりません。

ということで作ってしまいがちなのがこんなSQLではないでしょうか。

 

リスト1　現住所地域と出身地域を別々にselectして結合

SELECT  社員_現住所地域.社員名,  社員_現住所地域.地域名称 AS 現住所地域,  社員_出身地域.地域名称 AS 出身地域 FROM  (SELECT   A.社員名,   B.地域名称  FROM   社員情報 A  INNER JOIN 地域マスタ B  ON A.現住所地域コード = B.地域コード ) 社員_現住所地域 INNER JOIN  (SELECT   A.社員名,   B.地域名称  FROM   社員情報 A  INNER JOIN 地域マスタ B  ON A.出身地域コード = B.地域コード ) 社員_出身地域 ON 社員_現住所地域.社員名 = 社員_出身地域.社員名

 

この出力を得るうえで厄介なのは、社員情報の1行に対して地域マスタは2行取ったうえで最終的に1行にしないといけない点です。

これを解決するのに、「まず現住所地域と出身地域を別々に取る→結合して1行の結果にまとめる」という手続きは確かにわかりやすいです。同時に、この手続に沿ったこのようなSQLは確かに考えやすいと思います。

 

しかし、このような手続きを踏まなくとも複数のレコードを集約するのはSQLの得意とするところです。SQLを使うからにはその本領を存分に発揮してもらいましょう。

この問題をGROUP BYで集約しCASE式を使って解決するとこうなります。

 

リスト2　CASE式で現住所地域コードと出身地域コードどちらが一致したかを判別

SELECT  A.社員名,  MAX(CASE WHEN A.現住所地域コード = B.地域コード THEN B.地域名称 ELSE NULL END) AS 現住所地域,  MAX(CASE WHEN A.出身地域コード   = B.地域コード THEN B.地域名称 ELSE NULL END) AS 出身地域 FROM  社員情報 A INNER JOIN 地域マスタ B ON  A.現住所地域コード = B.地域コード  OR A.出身地域コード   = B.地域コード GROUP BY   A.社員名

 

CASE式を使ってOR条件のうちどちらに該当したのかを判定し、該当した場合はその地域名称を保持します。

該当しなかった場合はNULLにしておくことで、MAX関数により該当した地域名称が残ります。

リスト1のSQLと異なり、社員情報、地域マスタのスキャンは1回で済み副問い合わせも使う必要がありません。

実行結果は以下のようになります。

 

リスト2　実行結果

|  社員名        | 現住所地域   |  出身地域          | |貴ノ岩 義司   |東京都          |ウランバートル   | |貴公俊 剛      |東京都          |栃木県　　         |

いかがでしたでしょうか？
前回記事では取得したレコードの特定カラムの値をCASE式で判別しましたが、OR条件のどちらに一致したかまでもわかるということに感動すら覚えないでしょうか？

 

それではまた次回もよろしくお願いします。

 

 

※文中の実行環境はPostgreSQL9.6を使用しています。