| トラックバックURL: | http://jemi.s5.xrea.com/mt/mt-tb.cgi/450 |
今日は仕事先で誉められますた。使えるじゃ〜ん！とｗ
で、データベース、SQLの話です。
結構前まではおれはE.F.コッド博士が好きじゃありませんでした。
でも、最近は好きです。RDBMSは面白くてやりがいのある分野です。
ということで、今日はSQL初心者に陥りやすい罠を回避する小手先の業を書くことにします。
RDBMSは非常に数学的な概念で成り立っています。
その概念とは「集合」です。
リレーションや、整合性規約はそのおまけみたいなもので、気をつけていれば、
これらは無くても特に問題ありません。
で、SQL初心者の人が陥りやすい罠があるので、それを伝授しましょー。
おれもこのことに気が付くのにだいぶ時間がかかりました。
あまりRDBMSの本は読んだことないのですが、
少なくとも、おれの知っている入門書には書かれていない事です。
上級者にとっては基本的なことだと思うんですが
SQL一文で高度な検索を行おうとするな
これです。
それともうひとつ。
SQLで書けることは、なるべくSQLで書け
SQLは非手続き型で使えない、とか初心者の人は思いがちです。
こんなことしかできねーの？
なんておれもOracle触りたての頃に思ったもんです。
「俺狂」と書いてオラクルと読むんだ、とかバカにしてましたよ。
でも、違います。
SQLは至極単純な制御構造を内部に包括した、
非手続き型言語だというのが正確な説明だと思うんですよ。
分かりやすく言えば、
SQLは導き出した結果セットを上から順に出力する
という制御構造をデフォルトで持っているんです。これを応用しない手は無い。
ここで、肝になるのが一時テーブル。
テンポラリテーブルとも言いますが、どちらでも好きなほうで。
もちろん、普通のテーブルでも構いません。
PHPなどの、Web系言語ではコネクションプールでも無い限り、
1リクエスト毎に接続が切られるので、テンポラリテーブルは無意味です。
では、テクニック。
結果セットは一旦、テーブルに保持しろ
以上。
後はこの結果セットをどういじるかです。ここで「集合」の概念が活きてくるんです。
と言っても簡単です。
集合の和と差だけ。
それから、これも基本的ですごく重要なことなんですが、
全ての四則演算は和と差だけで表現できるということです。
これは一般的なZ80アセンブラのテクニックで気付いたことでした。懐かしい。
しかし、その話は割愛。ADDとかシフト命令を使うのがポイントです。
まー、そういった理論を踏まえてると非常に分かりやすいのです。
〜を含まない条件で検索、なんていうと
select * from A,B where A.index<>B.index
なんてやるじゃないですか。これに、もうひとつかふたつ、条件がついて御覧なさい。
SQLはいっぱい結果セットを持ってきてくれますから。
で、SQLつかえねえ。。。
とか思っちゃうの。これは大きな間違い。
で、結果セットを手続き型言語（一般的なCとかVBとかね）で取得して、
結果セットが空になるまでループさせて処理すればいいんだ！基本だよなー
って初心者は思う。おれも思って、そうやった。
これは大きな間違いです。
さっきも言ったことを思い出してください。
SQLは導き出した結果セットを上から順に出力する
空になるまで回す機能はSQLがデフォルトで持っている機能なんです。
これを無視してはイケナイ。そして、極めつけは「集合」。
ある条件を含まない結果を導くには？
簡単です。全件の結果と、ある条件を含む結果があれば、その差を取ればいいだけです。
create temporary table temp1 as select index from A
いろいろ節約の為に全件の主キーだけを引っ張ろう。
create temporary table temp2 as select A.index from A,B where A.field=B.field
今度は条件付き。これも主キーのみね。
で、
delete temp1 from temp2 where temp1.index=temp2.index
select A.* from A,temp1 where temp1.index=A.index
こうすればいい。もうひとつ条件を追加したいなら、お好きにどうぞ。もういくつでも。
下手にCやVBなんかのプログラムで条件をガリガリ書くよりも、早くて正確な結果がでるよ。
上記のSQLの文法、検証してないから、ちょっと間違ってるかも。
ちなみにOracleやMySQL、PostgreSQLで有効なはず。
SQLServerの場合はcreateをselect 〜 intoに書き換えて。
ちなみに、deleteの反対は、insertです。
ビット演算で言うところのANDとORの関係です。
deleteはAND、insertはORね。
あ、ということは、和と差じゃなくて（論理）積と（論理）和？あれ？ まあ、いいや（＾＾；
insert使うと、同じ結果が複数存在して正確じゃないじゃん・・・とか、
素人くせえこと言うなよな。distinctやgroup byを使えば問題ナシだから。
要するに全件の主キーを保持して、
様々な条件を単純なものに分解し、そこからひとつひとつ、引いたり足したりするのです。
これは簡単に高度な検索行う、
チェックボックスや範囲入力、自由入力、リスト選択が要求される
素人用のアプリケーションやWebアプリケーションで非常に役に立ちます。
客に検索が遅い遅いといじめられて、丸1週間かかってやっと気付いたテクニック（＾＾；
どうしても select * from A where 〜でwhere句に何かどんどん足せばいいって
イメージがSQLにはありますが、
それでは望んだ結果セットは導き出せないことが多いです。
そして、それで正常に検索できたとしても非常に遅くて使い物にならない事がほとんど。
この集合の和と差のテクニックは、処理速度も速く、
時には当社比10倍ものパフォーマンスを稼ぎ出します。
で、今日、これを披露したら、ちょっと尊敬された。
おれは自力でたどり着いたけど、いい本見れば載ってると思うんです。
多分、使い古されてメジャーな方法です。
ていうか、SQLで目的の検索を導き出す方法ってコレなんですよ。
で、最近になって、アプリケーションと連動するようになり、
SQLがなんか勘違いされて使われるようになってきた。
そんな感じだと思う。
それに入門書には、統計関係のクエリばかり載ってるのもよくない。
この手のテクニックが忘れられがちだと思います。
おれもはじめ、非常によくない例を多く見てきちゃったから
複雑怪奇なwhere句を書くことがしばしばでした。
でも、これに気付いて以来、高級言語を使って、
データを処理するのがバカらしくなりました。
Transact-SQLやpl/SQLの手続き型SQLを使うのもいいですが、
その必要性もあまり感じないです。
ちょっと混乱するけど、慣れればとにかく応用が利きます。
データのマッチングや、複合条件検索にこれを使ってくれ。
取り出して表示するのはDB側で出力された、最後の結果だけ。
この方法は、検索でのバグの出る確率が非常に少なくなるのが良い。
だって、結果はSQLのプロンプトですぐに確認できるんだもん。
一時テーブルは使いようです。もちろん、普通のテーブルでも構わないよ。
メモリ代わりにためとくだけですので。
20〜30個くらいの条件を同時に設定しても涼しい顔して検索します。
普通のテーブルの場合には一意な名前を付けて、処理後にdropしとこう。
テーブルの作成と削除を繰り返すって、負担をかけそうで
なんか心配とか思うかもしれないけど
そんなんでオシャカになるようなDBはDBじゃないから使うな（ぉぃ
高級言語のループで、データひとつひとつにクエリを発行するほうが
よっぽど負担かけてることに気づけ。（ほんとかなー（＾＾；
すでに気付いてる人には、分かりきってることなんだけどねーヽ( ･∀･)ノ