【SQL講座1】スカラ値ファンクションとサブクエリ
以前、『「SQLが会話のペースでできるようになる！」わたしの勉強法』というコラムを書きました。エクセルで処理ができるならSQLになるというものです。
それについて、ハンズオンのセミナーをやることになりました。第1回目は5月15日に大阪です。詳しくはこちら。ご興味のある方はinfo@g1sys.co.jpまで。
このコラムをご覧の方はある程度ご理解されている方が多いと思うので、簡単な部分はすっ飛ばして、コアな部分のSQL講座を書いていこうと思います。というか、最初から一貫して説明しないのは、セミナーにして儲けたいなと思っているからです。
どうせ儲かんないのですけれど（苦笑）。
というわけで、最初は「スカラ値ファンクションとサブクエリ」についてです。
（個人的にはスカラファンクションって呼んでたのですが……スカラってそれだけで「値」という意味にならないのかな？）
わたしは、この業界に入る前に、町工場の社員としてAccess 2.0で基幹システムを作っていました。
スカラ値ファンクションは、そのころのAccessにすらある機能で、最近でこそ、ある程度は使われるようになっているようですが、それでも認知度は低い。
認知されないのは、スカラ値ファンクション用の仕様書のフォーマットがないことがハードルになっているのではないかと思います。非常に簡単で効果が高いのに、ボトムアップでは採用されにくく、上の人間は機能を探している暇もなく、という理由で埋もれていきがちです。
本来なら、スキーマを分けるなどして、社内共通と、プロジェクト共通と、プログラム用と、用意すべきなんですが、実にもったいないです。
そんなスカラ値ファンクション（ストアドプロシージャ）ですが、PL/SQLとT-SQLは文法が違いすぎます。2種類書くのは大変なのでAccessで説明します（笑）。
飛躍しすぎですが、DOS版のLotus 1-2-3でもできたぐらいですし、2007はまだ使ってないのでそちらの互換性は分かりませんが、AccessでできるということはExcelでも使える。
最終的に、ExcelでSQLを説明しようとしているので、Accessで説明する方が簡単なのです。
ともかく、Access（お持ちでない人は、Excel）の標準モジュールに以下のソースを貼り付けてください。見やすく（書きやすく）するために全角ブランクを使っているので適当に変換してください。Access（2000、2003など）では、2バイト文字を含むの関数名を受け付けてくれないので、アルファベットの関数名に変更してください。
Option Explicit Public Enum e法人格 Private Type t法人格 Private arr法人格() As t法人格 Public Function fn法人格(ex As String, mode As e法人格) 'VBAでは配列が初期化されたか分からないようなので… ErrH: Private Sub set法人格リスト() '本来はDBなどから取得する。 |
これを利用するには、
■Excel（法人格をなくして表示する）
= fn法人格(A1, 5)
■Access（法人格をなくして出力）
SELECT fn法人格(Col1, 5)
FROM Table1
■Access（法人格をなくした名称から前方一致で検索）
SELECT *
FROM Table1
WHERE fn法人格(Col1, 5) LIKE 入力値 &'*' -- || '%'(Oracle)
のような使い方です（もちろん、検索に使ったらインデックスは効きませんよ）。
VB（VBA）を一度でも使ったことがあれば、ただのPublic関数だと分かるでしょう。
そうなんです。AccessやExcelでは、標準モジュールに書いたPublic関数をSQLやワークシートで使えます（知らない人は本当に知らないのですけど……）。
ソースを読めば、法人格を揃えるための関数だと分かりますね。社内共通関数として（もちろん、あらゆる法人格を加えて）作っておいても良いと思うのですけれど、SQLの社内共通関数ってほとんど聞かない。もったいない。
それは置いておいて、IF関数がネストしまくるとExcelの式も訳が分からなくなりますから、普段使っているExcelでも、関数化してすっきりさせるべきなのです。ただのPublic関数ですから、コードの途中にメッセージボックスを仕込むと、どのタイミングで関数が評価（再計算）されるかよく分かります。
そして、Excelでオリジナルの関数を使うことに慣れたら、SQLは上下の行の比較を苦手としますので、「同じ行で引数が取れるか」という基準で考えるようになるでしょう。
つまり、Excelを想像してSQLを書くときは、
- Excelで関数を1つ書いて、下までコピーできる（関数・引数が同じ）
- 引数は同じ行、または、リテラルである
が満たせたら、SQLでは、スカラ値ファンクションか、SELECT句に置くサブクエリで表現できることになります。
いずれも、パフォーマンスを考えるとFROM句で処理する方が良い場合もあります（RDMBSによっては、スカラ値ファンクションはオーバヘッドが大きいときもあります）。
ところで、上の法人格をそろえるサンプルで、UI側、SQL側のどちらで処理すべきかは、イロイロと議論があるところと思います。
わたしも悩みます。UI側、SQL側の両方にロジックを分散させるのはいやだし、バッチを考えるとSQL側の方にメリットがあり、入力時に揃えたいという要望があった場合は、UIの要件になる可能性もあります。UIの要件で、いちいちサーバに問い合わせるかどうかは、プロジェクトの考え方でしょう（Accessなら、一箇所で済みますけどね）。
ところが、現状ではSQL側は議論の余地すらないのが現実だと思います。できない人は最初から選択肢に加えることができないので、議論にならないのです。しかも、ファンクションを使うことを知らないのは「デキる」と言われている人に多いから本当に説得しにくい。困ったものです。
とにかく、工数を下げるにも、可読性を上げるにも非常に便利なスカラ値ファンクションをどんどん使うようにしましょう。日付関連などは社内共通として用意しても良いのではないでしょうか。
また、他にスカラ値ファンクションの利用法があります。Oracleに慣れている人がSQL Serverに移るときに等価の関数を作るという利用もありかと思います。
余談ですが、TO_CHAR関数とCONVERT関数とか、勘弁してほしい。
ベンダの皆様、乗換えのために、お互いに等価の関数を用意して、「互換性のために残すけれど標準はこっちなのでそろえましょうね」と標準化に向けてもっと努力をしてもらえませんかね。
独自性を出して抱え込もうなんてナンセンスも甚だしい。
さらに、（ストアド）ファンクションの引数にBoolean型が使えないという大問題があります。Accessからの乗換えの人のために、IIF関数を作ろうとして見事に嵌った（苦笑）。そもそも、SQLにBoolean型がないからなのでしょうけれど、拡張言語なのですから杓子定規なことを言ってないで、それぐらい対応したらどうでしょう。PostgreSQLはできたような……。
◇ ◇ ◇ ◇
今後も、「SQLを書くときのコツ」を不定期に書いていこうと思います。
次回は、スカラ値ファンクションやSELECT句に置くサブクエリのパフォーマンスについて書きたいと思います。
コメント
matsin
セミナーに参加したかったのですが、平日はスケジュール調整できませんでした・・。またの開催を楽しみにしておきます。