【SQL講座5】OLAP関数（分析関数）他、セミナーについて
5月15日にセミナーをやりました。
ほとんど身内なのですけれど（苦笑）、受講者はCOBOLerが多く、平均年齢は41歳（最高61歳、最低27歳）。
実際にやってみて、SQLはCOBOLerの方が向いていると改めて感じたしだいです。1日でSELECTまでというのはもったいないので、次回からは2～3日で更新系＆ストアドプロシージャまでと考えています。ご興味のある方はこちらまで info@g1sys.co.jp
さて、OLAP関数（分析関数）について。これは、Oracle9i以上、SQL Server2005以上、DB2？？で使えます。
OLAP関数（分析関数）は、単純にはこうです。
★ GROUP BYは集約して最終結果が減るけれど、OLAP関数（分析関数）は件数を減らさずに集計ができる。
後は、SELECT句に書くどんなに複雑な計算式も、戻りは単なる値ですから、数値なら演算処理、文字なら連結処理などが可能になります。
セミナーでは、スプレッドシートで以下のような表を埋めていきます。
上の絵のシートになるまでが、FROM句（WHERE句）の処理。
SELECT * で取得したFROM句までのシートがイメージできれば、同じ行で使える数式と関数を利用して最終結果に近づけていきます。上下の計算が必要なとき、OLAP関数（分析関数）でできる範囲であれば利用可能です。
エクセルで数式や関数を使える人なら、簡単に理解できるはずです。
1枚のシートで表現できれば、SQLでできる処理と言えます。文法よりもできるかどうかが判断できることの方が重要で、できると分かれば文法はググれば出てきます。
プログラムのコツは、ググれば出てくることを無理して覚えようとしないことですから（私が覚えた頃はヘルプ＆リファレンスでしたけどね）あまり文法解説はしませんけれど、こんなことをするセミナーです。
また、実行計画についてもある程度解説します。
たとえば、Oracleで（なければExpress版をインストールしよう）
コメント
ビガー
ビガーです。
こんな初歩的な内容はいつまで続くのですか？
もっと本質的な話を期待します。
ビガーさん、ども。
難しいことを書くには、具体的な業務を説明せねばならず、更に難しいのですね。
書きたいことは（笑い話も含め）山のようにあるのですけれど、守秘義務もあるし書けない。
セミナーでは反応が薄いとき、初歩的過ぎたかと思って飛ばしだすと、まったく追いついてなかったってことで、後戻りすることが何度かありました。
初歩の繰り返しですから、単純なことを一杯書いていこうと思っています。
インドリ
SQLの基礎さえしっかりやれば応用も自然と理解できると思います。
ですから基礎の解説だけでいいと私は思います。
※ インデントは全角ブランクで行っているので実行する前に変換してください。
-- タイプ作成
CREATE OR REPLACE TYPE typNumber AS TABLE OF Number
/
-- テーブル関数作成
CREATE OR REPLACE FUNCTION get_NumberList
(i_From Number
, i_To Number)
RETURN typNumber pipelined IS
i Number;
BEGIN
IF i_From > i_To THEN
RAISE_APPLICATION_ERROR
(-20001
, '開始値が終了値より大きいです');
END IF;
i := i_From;
WHILE i <= i_To LOOP
PIPE ROW(i);
i := i + 1;
END LOOP;
RETURN;
END;
/
-- テストテーブル作成
CREATE TABLE test AS
(SELECT
COLUMN_VALUE AS id
, '名前 ' || COLUMN_VALUE AS nm
FROM TABLE(get_NumberList(1,1000000)))
/
-- 主キーの設定
ALTER TABLE test ADD CONSTRAINT pktest
PRIMARY KEY (id)
ENABLE
/
ここまでで、カラムは2つしかないけれど、100万件のテーブルができます。
その後、以下のSQLの実行計画を見ます。
SELECT * FROM test WHERE id = 1
SELECT * FROM test WHERE id BETWEEN 1 AND 1000
SELECT * FROM test WHERE id BETWEEN :ifrom AND :ito
SELECT * FROM test WHERE id BETWEEN 1 AND 250000
上の3つはインデックスを利用しますが、下の1つはインデックスを利用しませんが、その理由、コストベースとルールベースの違いを体感してもらいます。
この辺の内容は、未経験者よりもCOBOLerの方が圧倒的に理解が早い。
実は、本当に簡単なのですよ。
セミナーは、次からはパナソニックＩＴカレッジで開催できないか交渉中（RDBMSをインストールしなければいけないから）、可能になれば、エクセルもインストールされているし交通の便も良い。
うまくいけば東京でもできるな～。
ぜひ、よろしくお願いいたします。