ハローワールド
今日はSQLのSELECT文について書いていきたいと思います。SQLって何だ？って人は日本人にとって共通言語は日本語、SEにとっての共通言語はSQL(ここでHTMLとか言うやつはSEというかWEBやってる人です。)みたいなものだと思ったりしてそっとタブを閉じてください。
SELECT文なんて大体同じ、だがたまに居る問題児
SQLってそこそこ業務系のプログラムやってればもう大体定型句みたいな感じで「あーはいはいこういう時はこういう奴ね」という感じでさくさくと書ける様になるもんですよね。(・・・僕は物覚えが悪いのか数ヶ月ぶりにINSERT書く時とかは毎度ググってますが)
なぜ定型句になってくのか？ってとこですが、そこら辺の企業の扱ってるデータの関連なんて大体がマスタトランとかヘッダ明細のモデルの組合せで9割以上が表現しきれてしまえるためです。
扱うデータが違えどデータの関連が似た構造になるので、どの企業のデータを扱っても「こういう奴ね」ってなって過去のSQLと同じような実装で対処できる訳ですね。
実際、RDB上手の人たちが受けるデータベーススペシャリストの問題も上述したマスタトラン、ヘッダ明細、たまに連関、位のリレーションの組合せによるデータ構造を例とした問題が殆どです。
・
なぜそこら辺の企業のデータ構造が大体「こういう奴ね」ってなるモデルに集約されてしまうのか、という話をし始めるとDOAとかデータベース正規化の話をしていかなければいけませんが、次に行きたいので省きます。
今回の記事はシステム開発を行なう上でたまーに居る「こういう奴ね」に該当しない1割弱のデータ構造をどうやってSELECTしていくかっていう話になります。
頻発しないせいかネットでググってもうまく業務要件に該当してるかわからんわけで、と言ってもググっても出てこないだけで脱初級者レベルの専門性ではあるんですが、日々しょうもないレベルの仕事を延々こなすIT土方にとってはアカデミックな専門書を読む時間もまま取れぬということで。
今回のケースですが、一緒に仕事してたおっさんが数週に渡り頭を悩ました結果ゴミのような処理に仕上げてしまいました代物です。知ってれば半日足らずで綺麗に解決出来たのに、知らないと時間と精神だけが削られて行くわけで、やはり無知は罪ですね。
そんな可哀想な人が増えないようここに解決法を書いておく事で一人でも多くの迷えるプログラマーを救いたいという思いです。
ケース１：階層構造型データ(隣接リストモデル)の検索
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の絶対パスみたいなもんですね。これをデータとして持っておくと、別パターンの階層構造である経路列挙型モデルの実装にも応用が利くようになるんじゃないでしょうか。
・
終わりに
見出しにケース１とか連番振っておいて終わりかよ！って感じですけど。ケース一個で取れ高十分すぎたので終わり。このブログの記事は僕が終わりと思ったら終わる。そんなブログ。
