【要望】
マーケティングのため、１ヶ月間取引のない得意先で、それ以前の半年間に５回以上取引のある得意先の一覧を出力したい。
【外部仕様（基本設計）】
■ 目的
以前に頻繁に取引があり、最近、取引のない得意先の一覧をつくりマーケティングに利用する。
■ 処理概要
ｍヶ月前から当日までに取引データがない得意先のうち、受注日が（ｍ＋ｎ）ヶ月前からｍヶ月前までの間にｃ件以上の取引データがある得意先の情報を一覧表に出力する。
ｍ、ｎ、ｃはユーザから画面で入力を受付け、それぞれ１、６、５を初期表示とする。
くどいですが、あと数回この問題を元に書いてみます。
ＳＱＬという言語仕様は、基本的には記述方法しか定義されていません。
データベースエンジンはそれをどう実行するかは決まっていないのです。
ＳＱＬ（≒外部仕様）を受け取ったデータベースエンジンはそれをどう実行するか（内部仕様）を考えます。
データベースエンジンが内部仕様を作っていくとき、直訳するのではなく、もっと効率の良い記述に変更してから実行します。
いかに効率よくするか、オラクル社やマイクロソフト社は競い合っているのですから、ＳＱＬの記述によっての速度の違いも一定ではありません。
しかし、ある程度の法則がありますので、記述の違いで処理速度が変わるのはなぜか、データベースエンジンの処理手順を見ていきましょう。
さて、解答例３には３つの書き方を作りました。
これは In と Exists という述語を換えているだけです。
In と Exists はどう違うのでしょう。
・基本１
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY の順で実行される。
・基本２
括弧があれば、括弧の内側から実行される。
・基本３
Exists (SELECT … )、NOT Exists (SELECT … ) のときは括弧の内側よりも先に、FROM句を実行し、対応するレコードが Exists (SELECT … ) の中にあるかないかで判定を行う。
他にもありますが、とりあえず、上の問題にかかわる部分をピックアップするとこんな感じです。
では、解答例からデータベースエンジンの処理を見ていきましょう。
解答例３－Ａ
SELECT tm.*
FROM 得意先マスタ tm
WHERE
tm.得意先コード IN
(SELECT jt.得意先コード
FROM 受注データ jt
WHERE jt.受注日 >= ｍ＋ｎ ヶ月前
AND jt.受注日 <= ｍヶ月前
GROUP BY 得意先コード
HAVING COUNT(*) >= ｃ
)
AND NOT EXISTS
(SELECT *
FROM 受注データ jt
WHERE jt.受注日 > ｍヶ月前
AND jt.得意先コード = tm.得意先コード
)
これは次の順序で実行されます。
ｍ＋ｎ ヶ月前受注データの検索し５件以上ある得意先を抽出。…Ａ
Ａに対応する得意先データの抽出。…Ｂ
Ｂに対応する、ｍヶ月前より後の受注データに存在するとき、そのレコードを破棄する。
解答例３－Ｂ
SELECT tm.*
FROM 得意先マスタ tm
WHERE
EXISTS
(SELECT *
FROM 受注データ jt
WHERE jt.受注日 >= ｍ＋ｎ ヶ月前
AND jt.受注日 <= ｍヶ月前
AND jt.得意先コード = tm.得意先コード
GROUP BY 得意先コード
HAVING COUNT(*) >= ｃ
)
AND NOT EXISTS
(SELECT *
FROM 受注データ jt
WHERE jt.受注日 > ｍヶ月前
AND jt.得意先コード = tm.得意先コード
)
これは次の順序で実行されます。
得意先データの抽出。…Ａ
Ａに対応するｍ＋ｎ ヶ月前の受注データを検索し５件以上あるか確認。
（ないとき破棄）
抽出した得意先コードが、ｍヶ月前より後の受注データに存在するとき破棄する。
解答例３－Ｃ
SELECT tm.*
FROM 得意先マスタ tm
WHERE
tm.得意先コード IN
(SELECT jt.得意先コード
FROM 受注データ jt
WHERE jt.受注日 >= ｍ＋ｎ ヶ月前
AND jt.受注日 <= ｍヶ月前
GROUP BY 得意先コード
HAVING COUNT(*) >= ｃ
)
AND tm.得意先コード NOT IN
(SELECT jt.得意先コード
FROM 受注データ jt
WHERE jt.受注日 > ｍヶ月前
)
これは次の順序で実行されます。
ｍ＋ｎ ヶ月前受注データの検索し５件以上ある得意先を抽出。…Ａ
Ａに対応する得意先データの抽出。…Ｂ
ｍヶ月前より後の受注データに存在する得意先コードを抽出する。…Ｃ
ＢからＣと一致するものを破棄していく。
外部仕様を直訳すればＳＱＬになると話をしてきました。
ＳＱＬは魔法じゃないので、最終的にはデータベースエンジンがループを書いてデータを抽出してくるのです。
ということは、データベースエンジンが、内部仕様（詳細設計）を作って、実行してくれるわけです。
だから、ＳＱＬを実行するというのはかなり遅い処理になるため、何回も実行してはいけない。
つまり、ループの中に書いてはいけないのです。
手続き型のプログラミング言語を経験してきたあなたなら、どれが一番効率が良いかは、得意先マスタ、受注データのデータ量によって違ってくることがすぐに分かるはずです。
データベースエンジン自体は、あなたが考えた処理とまったく同じことをするのだということも分かると思います。
それは、手続き型のプログラミング言語を経験してきたから分かるのでしょう。
あなたが優秀な技術者であれば、テーブルのデータ量と対象となるデータ量を予想して、ＡかＢを選ぶはずです。
本当にＳＱＬが分かってくると、最適な処理フローを考えてそのフローをデータベースエンジンが実行してくれるように記述するようになります。
最初と逆のことを書いていますが、カマドで上手にご飯を炊ける人の方がＩＨのキッチンでもおいしい料理を作れるのは当たり前です。
優秀なあなたは、外部仕様（基本設計）を書くとき、内部仕様（詳細設計）を思い浮かべて書くことでしょう。
このとき、ＳＱＬが分かっていないと、思い浮かべた内部仕様（詳細設計）は『営業マンが何度も往復する』構造になっています。
『営業マンが何度も往復する』ためのテーブル構造になってしまいます。
そうなると、後からは取り返しがつきません。
正しくＳＱＬを理解すれば、あなたが、手続き型のプログラミング言語を経験してきたのなら、データベースエンジンの処理を予想してＳＱＬを書くという、より高いレベルで理解し習得できるはずです。
是非、ＳＱＬを習得して、高いレベルで後輩を指導してください。
『処理フローを書け』はよく考えてから言いましょう。
『ＳＱＬの仕様書を書け』は…。
多くのプロジェクトで仕様書のフォーマットから考え直さないといけないと思います。
Ｃｏｂｏｌで使っていた仕様書のフォーマットをそのまま使おうなんて最低です。
もちろん、ＵＭＬで書こうが、解答例１・２を最終形として考えてスタートを切ったプロジェクトは簡単には変わりません。
<< 第２１回目 「SQLについて（安い、且つ 速いシステムを作るには) 解説２」 | | 第２３回目 「SQLについて（安い、且つ 速いシステムを作るには) のまとめ」 >>
コラムの全インデックス
