読者です 読者をやめる 読者になる 読者になる

オカエ・ザ・超雑記帳

徒然趣味主義主張

爽やか

知っておくと得するSQL

ハローワールド

今日はSQLのSELECT文について書いていきたいと思います。SQLって何だ?って人は日本人にとって共通言語は日本語、SEにとっての共通言語はSQL(ここでHTMLとか言うやつはSEというかWEBやってる人です。)みたいなものだと思ったりしてそっとタブを閉じてください。

SELECT文なんて大体同じ、だがたまに居る問題児

SQLってそこそこ業務系のプログラムやってればもう大体定型句みたいな感じで「あーはいはいこういう時はこういう奴ね」という感じでさくさくと書ける様になるもんですよね。(・・・僕は物覚えが悪いのか数ヶ月ぶりにINSERT書く時とかは毎度ググってますが)

なぜ定型句になってくのか?ってとこですが、そこら辺の企業の扱ってるデータの関連なんて大体がマスタトランとかヘッダ明細のモデルの組合せで9割以上が表現しきれてしまえるためです。

扱うデータが違えどデータの関連が似た構造になるので、どの企業のデータを扱っても「こういう奴ね」ってなって過去のSQLと同じような実装で対処できる訳ですね。

実際、RDB上手の人たちが受けるデータベーススペシャリストの問題も上述したマスタトラン、ヘッダ明細、たまに連関、位のリレーションの組合せによるデータ構造を例とした問題が殆どです。

なぜそこら辺の企業のデータ構造が大体「こういう奴ね」ってなるモデルに集約されてしまうのか、という話をし始めるとDOAとかデータベース正規化の話をしていかなければいけませんが、次に行きたいので省きます。

今回の記事はシステム開発を行なう上でたまーに居る「こういう奴ね」に該当しない1割弱のデータ構造をどうやってSELECTしていくかっていう話になります。

頻発しないせいかネットでググってもうまく業務要件に該当してるかわからんわけで、と言ってもググっても出てこないだけで脱初級者レベルの専門性ではあるんですが、日々しょうもないレベルの仕事を延々こなすIT土方にとってはアカデミックな専門書を読む時間もまま取れぬということで。

今回のケースですが、一緒に仕事してたおっさんが数週に渡り頭を悩ました結果ゴミのような処理に仕上げてしまいました代物です。知ってれば半日足らずで綺麗に解決出来たのに、知らないと時間と精神だけが削られて行くわけで、やはり無知は罪ですね。

そんな可哀想な人が増えないようここに解決法を書いておく事で一人でも多くの迷えるプログラマーを救いたいという思いです。

ケース1:階層構造型データ(隣接リストモデル)の検索

RDBってのは行列形式にデータを蓄積していく仕組みなんで、階層がいくつも重なる多次元的なデータモデルは扱いづらいってのが定説です、実際RDBで階層構造を扱うぜ!って話だけでクッソ分厚い技術書を1冊かけるらしいのですが、今回は階層構造型のデータの内、最もポピュラーらしい隣接リストモデルを用いたデータ構造の検索方法について述べていきます。正にこいつが知ってれば半日、知らんと数週間の初見殺しマンでした。

1-0:EMP表の説明

ではここからはscott/tigerのEMP表を例としてSQLの解説をしていきます。(注:scott/tigerはオラクルDBのデフォルトで入ってるスキーマ、ちなみにオラクルにはSQLを独自で拡張したCONNECT BY句とか言う超便利な文法がありこれから書く内容と同じことを超簡単なSQLで実装できるのでオラクラーの人はそっちでどうぞ)

EMP表、日本語で言うと社員マスタといったところです。この表が何故隣接リストモデルに値するデータ構造なのか?というところを軽く説明すると

外部キーとしてEMP表のプライマリーキー(上司の従業員番号)をデータに持ってるから

とか書いておけば情報処理の試験では○を貰えるんじゃないでしょうか?データの中身と様式は以下のような感じです。

従業員テーブル(EMP)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
従業員番号(★) 名前 職種 上司の従業員番号(☆) 入社日 給与($) 歩合給($) 部署コード(☆)
7369 SMITH CLERK(事務員) 7902 80-12-17 800   20
7499 ALLEN SALESMAN(営業マン) 7698 81-02-20 1600 300 30
7521 WARD SALESMAN(営業マン) 7698 81-02-22 1250 500 30
7566 JONES MANAGER(管理職) 7839 81-04-02 2975   20
7654 MARTIN SALESMAN(営業マン) 7698 81-09-28 1250 1400 30
7698 BLAKE MANAGER(管理職) 7839 81-05-01 2850   30
7782 CLARK MANAGER(管理職) 7839 81-06-09 2450   10
7839 KING PRESIDENT(社長)   81-11-17 5000   10
7844 TURNER SALESMAN(営業マン) 7698 81-09-08 1500 0 30
7900 JAMES CLERK(事務員) 7698 81-12-03 950   30
7902 FORD ANALYST(アナリスト) 7566 81-12-03 3000   20
7934 MILLER CLERK(事務員) 7782 82-01-23 1300   10

 

http://replication.hatenablog.com/entry/2015/02/26/093000氏から引用しました。問題あれば消します。

こういう持ち方をしておけば常に階層の下位が一つの上位の階層をポイントできる(社長のKING氏は一番偉く上位がないのでnull)ため、階層構造、つまりこの例で行くと社員の序列を表現できてるということです。

今回は以下の要件に当てはまるSQLの実装を書いていきます。

・とある管理職が管理すべき社員を一覧したい(上位から下位への検索)

1-1:SQLの実装と概説

初めに断わっておきますが、DB2環境の業務に使ってるテーブル向けに書いたSQLをEMP表に読み替えて手書きで再作成したSQLです。手元にORACLEの環境もないので、動作確認もしてませんので動かなくても知りません。(ちなみに元のSQLもIBMknowledgecenterの奴を丸パクリ・・・つまり長ったらしく書いてるけど、URL張れば一発で終わってしまう内容である。)

ーーーーーーーーーーーーーーーーーーーーーーーーーーーー

とある管理職の部下にあたる従業員番号を一覧するSQL

WITH REMP (PATH,LVL,MGR,EMPNO) AS
(SELECT EMPNO, 0,ROOT.MGR, ROOT.EMPNO 
  FROM EMP AS ROOT
  WHERE MGR = "とある管理職のEMPNO"
  UNION ALL
  SELECT CONCAT(PARENT.PATH,CHILD.PATH) , 
  PARENT.LVL + 1 ,CHILD.MGR,CHILD.EMPNO 
  FROM REMP AS PARENT, EMP AS CHILD
  WHERE PARENT.EMPNO = CHILD.MGR AND LVL <= 100
 )
SELECT * FROM REMP ORDER BY PATH

ーーーーーーーーーーーーーーーーーーーーーーーーーーーー

説明しやすいように色分けしてみました。結論から書くとSQLで実装された再帰処理ですね。階層構造は再帰的な形状をしてるので再帰処理で検索するのが筋です。

最初の黄色、WITH句でREMPというビューを定義してます。WITH句で先にビューを宣言しとくと後続でそのビューを参照できるって奴ですね。このREMPビューの検索内容が()内の緑と青になりますが、青の検索にWITHで宣言したREMPを使うため再帰的検索となります。

の検索は検索の始点です。とある管理者の直属の部下達が検索されます。

の検索ではREMP表とEMP表を結合させて、部下の部下達を検索します。検索した結果はREMP表に格納されていきそのREMPの内部では部下の部下の部下達を検索し・・・、と末端に辿り着くまで階層構造を検索します。

ここで注意したいのは俺の部下が実は俺の上司だった、つまり釣りバカ日誌的状況です。このようなデータがあると「俺だ、浜崎だ、俺の会社の社長は鈴木だ、呼んでくる。俺だ、鈴木だ、俺の釣りの師匠は浜崎だ、呼んでくる。俺だ、浜崎だ・・・」と再帰処理が無限に循環してしまうのでスタックオーバーフローを起こします。循環を起こさないために茶色を入れて深い階層が発生しないよう100階層までの検索で打ち止めにしてます。

ちなみにPATHというデータはとある管理職からとある従業員に至るまでの上下関係の変遷となります。イメージとしてはPCの絶対パスみたいなもんですね。これをデータとして持っておくと、別パターンの階層構造である経路列挙型モデルの実装にも応用が利くようになるんじゃないでしょうか。

終わりに

見出しにケース1とか連番振っておいて終わりかよ!って感じですけど。ケース一個で取れ高十分すぎたので終わり。このブログの記事は僕が終わりと思ったら終わる。そんなブログ。