ここはとある街の総合病院。
ここには通常の診療科のほかに、一風変わった診療科が存在する。
何軒もの病院をたらいまわしにされた、手の施しようのないSQLや、今すぐに改善が必要なSQLが担ぎ込まれる救命室である。
それがSQL緊急救命室、略してSER(SQL Emergency Room) 。
そう、ここは国内でも唯一のプログラミング専門外来である。
ロバート 救命室部長。腕の立つエンジニアだが、口が悪く性格はもっと悪い四十オヤジ。
SQLを学ぶ上で最も高いハードルとなるのが、順序と手続きではなく、集合と論理の観点から考えることだ。
―J.Celko『Joe Celko's SQL Programming Style』
( Morgan Kaufmann、2005年)p.184
ループによる表現
(PM3:00 手術室。先ほどかつぎ込まれたばかりの患者を前にして、何やら揉めている)
ワイリー、そこをどけ!
いーえ、どきません。僕は、どうしても今回の患者に治療が必要だとは思えないのです。先生は、健康な患者に、功名心のあまり必要のない治療をしようとしていませんか? 先生は切らなきゃ気がすまないんでしょう!
若僧が、きいた風な口を! いいからどけっ(ドンッ)
ああっ!(へなへなと崩れ落ちる)
ワイリー、喧嘩している時間はないわ。さっさと始めるわよ。
カルテ: 図1 のような2つのテーブルがあるとする。Salesテーブルは企業ごとの会計年ごとの売り上げを記録している。ただし年は連続しているとは限らない。このデータから、同じ企業についてある年とその直近の年の売り上げの変化を調べたい。
その結果を、var列を追加したSales2に登録する。var列の値は次のルールによって決められる。
より古い年のデータが存在しない場合:NULL
直近の年のデータより売り上げが伸びた場合:+
直近の年のデータより売り上げが減った場合:-
直近の年のデータより売り上げと同じ場合:=
登録後のSales2テーブルは図2 のようになる。
図1 Sales、Sales2テーブル
図2 登録後のSales2テーブル
SQLでループを置き換えるには
患者のコードはリスト1 ですね。
リスト1 患者のコード(Oracle PL/SQL)
CREATE OR REPLACE PROCEDURE PROC_INSERT_VAR
IS
/* ①カーソル宣言 */
CURSOR c_sales IS
SELECT company, year, sale
FROM Sales
ORDER BY company, year;
/* レコードタイプ宣言 */
rec_sales c_sales%ROWTYPE;
/* カウンタ */
i_pre_sale INTEGER := 0;
c_company CHAR(1) := '*';
c_var CHAR(1) := '*';
BEGIN
OPEN c_sales;
LOOP
/* レコードをフェッチして変数に代入 */
fetch c_sales into rec_sales;
/* レコードがなくなったらループ終了 */
exit when c_sales%notfound;
IF (c_company = rec_sales.company) THEN
/* 直前のレコードが同じ会社のレコードの場合 */
/* 直前のレコードと売り上げを比較(②③)*/
IF (i_pre_sale < rec_sales.sale) THEN
c_var := '+';
ELSIF (i_pre_sale > rec_sales.sale) THEN
c_var := '-';
ELSE
c_var := '=';
END IF;
ELSE
c_var := NULL;
END IF;
/* ④登録先テーブルにデータを登録 */
INSERT INTO Sales2 (company, year, sale, var) VALUES (rec_sales.company, rec_sales.year, rec_sales.sale, c_var);
c_company := rec_sales.company;
i_pre_sale := rec_sales.sale;
END LOOP;
CLOSE c_sales;
commit;
END;
※ OracleのPL/SQL のコードを提示しましたが、便宜的なサンプルとして使っているだけなので、他のDBMSのプロシージャ言語およびJavaのようなホスト言語に適宜読み替えてください。
問題のパターンとしては、行間比較[1] とコントロールブレーク処理[2] ね。患者のコードからアルゴリズムを抽出すると、こうなるわ。
① Salesテーブルから全件レコードを取得する。このとき「企業,年」の昇順にソートしておく
② 1レコードずつループを行い、同じ会社のレコードであるか比較する
③ 同じ会社のレコードであれば、その売り上げと直近の売り上げを比較し、比較の結果に応じて「変化」列の値を設定する
④ Sales2テーブルへINSERTする
⑤ ②~④の処理をレコードがなくなるまで続ける
素朴な疑問なんですけど、このコードには何か問題があるんですか? すごくわかりやすい、当たり前の解じゃないですか。やっぱり治療が必要だとは思えないなあ。
確かに、手続き型言語としてはオーソドックスな解だ。だがそれを宣言型の世界に持ち込むと、ループ依存症になる。ループというのは呪い みたいなものだ。我々の思考を常に規定し、縛ろうとしてくる。
その呪いから脱するにはどうすればいいんですか?
「レコード」ではなく、「 レコードの集合」という観点から考えること。SQLで行間比較を行う手段は何?
えっと……ウィンドウ関数「参考資料1、2 ] 、それが使えない実装では相関サブクエリ[参考資料3 ]……でしたっけ。
そうね。ループの部分を丸ごとそれで置き換えることが可能よ。あとは、IF文はSQLでは常にCASE式に変換できることは、前回 も言ったとおり。ウィンドウ関数を使った解はこうなるわ(リスト2 ) 。
リスト2 ウィンドウ関数を使った解
INSERT INTO Sales2
SELECT company,
year,
sale,
CASE WHEN MAX(company)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) = company
THEN CASE SIGN(sale - MAX(sale)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) )
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END
ELSE NULL END AS var
FROM Sales;
ウィンドウ関数でループを置き換える
リスト2の2つのCASE式が、それぞれ「① 同じ会社であるかの比較」「 ③ 直近の売り上げとの比較」に対応しています。これは手続き型言語で書いた場合と同様です。
この解で重要な技術は、ウィンドウ関数においてROWS BETWEEN
オプションを使っていることです(②④ ) 。これはさかのぼる対象範囲のレコードを、直前の1行 に制限しています。ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING
は、「 カレントレコードの1行前から1行前の範囲 」という意味なので、結局、直前の1行だけを含みます(図3 ) 。
図3 ROWS BETWEENの動作
つまり、2つのウィンドウ関数は、それぞれ「1行前の会社名」と「1行前の売り上げ」を戻り値とします。
リスト3 、図4 のように、結果を表示させてみればはっきりします。
リスト3 ウィンドウ関数で「1行前の会社名」と「1行前の売り上げ」を取得
SELECT company,
year,
sale,
MAX(company)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS pre_company,
MAX(sale)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) AS pre_sale
FROM Sales;
図4 リスト3の実行結果
company year sale pre_company pre_sale
------- ---- ----- ----------- --------
A 2002 50
A 2003 52 A 50
A 2004 55 A 52
A 2007 55 A 55
B 2001 27 A 55
B 2005 28 B 27
B 2006 28 B 28
B 2009 30 B 28
C 2001 40 B 30
C 2005 39 C 40
C 2006 38 C 39
C 2010 35 C 38
もし、比較対象のレコードを「1行前」ではなく「2行前」にしたいならば、ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING
と、さかのぼるレンジを変えてやることで簡単に対応できます。この柔軟さは、次に見る相関サブクエリの解にはない利点です。
またこの解では、SIGN関数を使っているのもワンポイントです(リスト2③ ) 。これは、数値型を引数に取り、符号がマイナスなら-1を、プラスなら1を、0の場合は0を返す関数で、直近の年との売り上げの変化を知るために利用しています。CASE式の条件部分に、何度もウィンドウ関数を記述しないためのちょっとした小技です(SQLには変数がない ため、こうした小技が必要になります) 。
ROWS BETWEENオプションを利用できる環境
ただし、このアプローチを利用するときには注意点があります。このコードが実行できるのは、現在のところOracleおよびDB2のみです。MySQLはもともとウィンドウ関数を未実装のため使えないのは自明ですが、PostgreSQLとSQL Serverの場合ウィンドウ関数は一応実装しているのですが、ROWS BETWEEN
オプションを使うことができません。
ですがこれは標準SQLの機能なので、時間が経てばいずれ多くの実装に普及していくことが期待されます。ただ、まだ比較的新しい機能のため、使える実装が限られることも事実です。
ウィンドウ関数が使えない場合のために、次に相関サブクエリによるアプローチを見てみましょう。
相関サブクエリでループを置き換える
うーん、難しい。難しいですよこれは。
自慢気に言うな。では、相関サブクエリの解はどうなる?
「直前の1行」をスカラサブクエリで表現すれば、ウィンドウ関数で作っているvar列を同じように作れるはずってことですよね。よし……これでどうでしょう(リスト4 ) 。
リスト4 相関サブクエリを使ったワイリーの回答
INSERT INTO Sales2
SELECT company,
year,
sale,
CASE SIGN(sale - (SELECT sale
FROM Sales S2
WHERE S1.company = S2.company
AND S2.year =
(SELECT MAX(year)
FROM Sales S3
WHERE S1.company = S3.company
AND S1.year > S3.year )))
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END AS var
FROM Sales S1;
うむ、いいだろう。相関サブクエリではS1.company = S2.company
という条件によって「同じ会社ならば 」という条件を実現できる。こちらのほうが、典型的なSQLの集合指向の考え方だな。
相関サブクエリの中で非等値結合を使うことによってカレントレコードを起点とした集合を作るのは、ウィンドウ関数が導入される前のSQLでは定石の技術でした。
今、相関サブクエリの中では、S2テーブルからある条件でレコードを選択し、その中から最大の年MAX(sale)
を選択しています。これがすなわち「直近の年の売り上げ」になります。その条件の中心となるのが、S1.year > S3.year
という不等式です。カレントレコードはS1.year
のほうですから、「 それより小さい(=昔の)年」という意味になります。
S1.year
と、S1.year > S3.year
の条件に合致するレコード集合の対応をマッピングすると図5 のようになります(太字の年は、集合の中の最大値を示します) 。
図5 レコード集合の対応をマッピング
このように、「 ある値を基準にそれより小さい値の集合」を集合論で「下界 」( lower bound )と呼びます。基準値となる年が進むに従って、下界の要素数は1つずつ増えていく様子がわかります。
相関サブクエリの正体は入れ子集合
へえ~、おもしろいなあ。S1はS0を含み、S2はS1を含み……という風にどんどん入れ子状 に集合が大きくなっていくんですね。
そうね。そういう見方をすれば、この相関サブクエリが作る下界は再帰的集合 でもあるわね。あなたが今言ったように、S0~S3には次のような包含関係が成立するから。
S0⊂S1⊂S2⊂S3
この包含関係を図示すると、図6 のような同心円的な再帰集合が描けるわ。
図6 非等値結合は同心円的な入れ子集合を作る
奇妙なサンドイッチ
非等値結合は、数学的にはいろいろ興味深い操作ではあるな。では、ウィンドウ関数、相関サブクエリを使った2つの解について実行計画も見ておくとしよう(図7、8 ) 。
図7 リスト2:ウィンドウ関数による解の実行計画(Oracle)
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 108 | 2 (0)| 00:00:01 |
| 1 | WINDOW BUFFER | | 12 | 108 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| SALES | 12 | 108 | 2 (0)| 00:00:01 |
| 3 | I NDEX FULL SCAN | SYS_C004248 | 12 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
図8 リスト4:相関サブクエリによる解の実行計画(Oracle)
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 348 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 29 | | |
| 2 | TABLE ACCESS BY INDEX ROWID | SALES | 1 | 29 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C004280 | 1 | | 1 (0)| 00:00:01 |
| 4 | SORT AGGREGATE | | 1 | 16 | | |
| 5 | FIRST ROW | | 1 | 16 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN (MIN/MAX)| SYS_C004280 | 1 | 16 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | SALES | 12 | 348 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("S2"."COMPANY"=:B1 AND "S2"."YEAR"= (SELECT "YEAR" FROM "SALES"
"S3" WHERE "S3"."YEAR"<:B2 AND "S3"."COMPANY"=:B3))
6 - access("S3"."COMPANY"=:B1 AND "S3"."YEAR"<:B2)
あれ? ウィンドウ関数を使ったほうのSQL、実行計画でウィンドウ関数の処理(WINDOW BUFFER)が1回しか現れませんね。2つ使っているから、てっきり2回現れると思ったのですが。
それはウィンドウ関数のソートキーがどちらも(company、year)の昇順だからよ。ソート順が同じなら、1回で十分でしょ。逆に、もしソートキーが違えば、ウィンドウ関数の数だけソート処理が必要になるわ。
そっか。ウィンドウ関数は内部的にはソート処理をしていたんだった。うーん、なんか変な気分。
変?
いや、僕らは今、ループがムダだという理由でループを含まないSQLに書き換えたじゃないですか。でも、結局DBMSは内部でSQLをもう一度ループに変換している んですよね。DBMS内部では、結局処理は手続き型言語で実行されるじゃないですか。結合のアルゴリズムであるNested Loopsなんかも、まさにループだし。SQLってその意味では、図9 みたいに、手続き型言語に挟まれたサンドイッチみたいだ、と思って。
図9 SQLは手続き型のレイヤに挟まれている
ループからの脱出
うまいことを言うようになったな。お前の指摘はSQLの微妙な立場をうまく言い当てている。もともと、SQLは「手続き」を隠蔽(いんぺい)するために考え出された言語だった。RDBとSQLの生みの親であるCoddはこう言っている。
“関係操作では、関係全体をまとめて操作の対象とする。目的は繰返し(ループ)をなくすことである 。いやしくも末端利用者の生産性を考えようというのであれば、この条件を欠くことはできないし、応用プログラマの生産性向上に有益であることも明らかである。”
(強調は筆者)
―E.F.Codd「関係データベース:生産性向上のための実用的基盤」
『 ACMチューリング賞講演集』( 赤摂也 翻訳、共立出版、1989年)p.455
Codd博士、はっきり言い切っていますね。「 ループをなくす」って。なんか「自民党をぶっこわす」みたいでかっくいー。
アメリカにそんな政党あった? Coddには、データベースを広いユーザに提供するには、一部プログラマしか使えない言語ではなく、誰でも扱える言語が必要だ、という思いがあったのね。SQLの構文を自然言語(英語)に似せたのも、そういう理由からでしょうね。
その意味で、SQLそのものが手続き型言語を隠蔽 する一種のフレームワークというか、ラッパーの役割を果たしているわけだ。ところが皮肉なことに、SQLの上位でさらに手続き型言語を使って、SQLを隠蔽しようとするコードが後を絶たん。フレームワークを使うと、問答無用でこういうループに置き換えられることも多い。
先生はフレームワーク否定派ですか?
別に否定も肯定もせんよ。一長一短さ。フレームワークにも処理の隠蔽による開発効率改善や共通化による保守性向上など、メリットはある。ただ、何事にもトレードオフはある というだけだ。
あれ、意外にリベラルですね。僕はまたてっきり「スクラッチ以外の選択肢などありえん!」と言うかと思っていました。
お前、ワシをただの偏屈オヤジだと思っているんじゃないだろうな?
ぎくっ!
と、ところで、この患者は別テーブルへINSERTする処理でしたけど、これが同じテーブルへのUPDATEだったら、どんな解になっていたんでしょう。
……では図10 のようなテーブルSales3を想定しよう。今度はvar列を同じテーブル内の列として持っており、これをさっきと同じルールで更新する。この問題をループで解くときは、患者のコードのINSERTの部分をUPDATEに書き換えるだけだ。では、SQLで解く場合はどうする?
図10 Sales3テーブル
相関サブクエリによる更新
うーんと、相関サブクエリのほうはこう(リスト5 )……あれ、エラーになった。「 単一行副問い合わせにより2つ以上の行が戻されます」って。
当たり前よ。これじゃSalesテーブルの1行にSales3テーブルから選択した全行を対応させようとしているのだから。ちゃんと一対一に条件を絞りなさい。
リスト5 相関サブクエリによる解(間違い)
UPDATE Sales3
SET var =(SELECT CASE SIGN(sale - (SELECT MAX(sale)
FROM Sales3 S2
WHERE S1.company = S2.company
AND S1.year > S2.year ))
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END AS var
FROM Sales3 S1);
そっか。SET句で相関サブクエリを使えば……できた!(リスト6 )
リスト6 相関サブクエリを使った更新
UPDATE Sales3
SET var =(SELECT CASE SIGN(sale - (SELECT MAX(sale)
FROM Sales3 S2
WHERE S1.company = S2.company
AND S2.year =
(SELECT MAX(year)
FROM Sales3 S3
WHERE S1.company = S3.company
AND S1.year > S3.year )))
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END AS var
FROM Sales3 S1
WHERE Sales3.company = S1.company
AND Sales3.year = S1.year);
正解ね。
ウィンドウ関数による更新
じゃあ、ウィンドウ関数のほうはどうなる?
こんなんでどうでしょう(リスト7 ) 。単純にSET句でウィンドウ関数の部分だけ代入してみたんですけど。
リスト7 ウィンドウ関数を使った更新(DB2でのみOK)
UPDATE Sales3
SET var = CASE WHEN MAX(company)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) = company
THEN CASE SIGN(sale - MAX(sale)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) )
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END
ELSE NULL END ;
基本的にはこれでOKよ。留保つきなのは、このコードが通るのはDB2だけだから。OracleはSET句でのウィンドウ関数の使用を認めていないので、これだとエラーになるわ。Oracleの場合、一段サブクエリを間にかませる必要があるの(リスト8 ) 。
リスト8 Oracleの場合
UPDATE Sales3
SET var = (SELECT var
FROM (SELECT company,
year,
CASE WHEN MAX(company)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) = company
THEN CASE SIGN(sale - MAX(sale)
OVER (ORDER BY company, year
ROWS BETWEEN 1 PRECEDING
AND 1 PRECEDING) )
WHEN 0 THEN '='
WHEN 1 THEN '+'
WHEN -1 THEN '-'
ELSE NULL END
ELSE NULL END AS var
FROM Sales3) TMP
WHERE Sales3.company = TMP.company
AND Sales3.year = TMP.year);
明らかに外側のサブクエリは冗長ですね。
ま、これに関しちゃOracleにさらなる改善を期待するしかないな。
もし、Salesテーブルがファイルで、データがCSV形式で格納されていたとします。その場合、今回考えたような処理を実現するには、ループはほとんど唯一の選択肢です。しかし、RDBにおけるテーブルはファイルではありません(図11 ) 。それはむしろ、雑多なコインが入った財布 みたいなもので、財布の中ではコインは順序づけられて格納されていません。テーブルのレコードには順序がないためです。したがって、SQLでは「1レコードずつ順次アクセスする」という発想がありません。常に、複数のレコードをまとめて処理しようとします。
図11 テーブルはファイルではない
しかし、SQLのこのような考え方は、手続き型言語のスキームで育ったエンジニアには特異に感じられます。したがって、SQLよりも手続き型言語の側に処理を寄せようとする発想は、自然なものです。そしてこの考えは、一概に悪いものではありません。ロバートが言うように、どちらにも一長一短あるため、よく考える必要があります。それぞれのメリット・デメリットを比較してみましょう。
手続き型言語的な書き方~(ループ)を積極的に使う場合
まず、手続き型言語に業務ロジックを寄せて、SQLをライトにする方法のメリットは、手続き型言語の考え方さえ知っていれば、誰でもコーディングできることです。開発メンバーにSQLに詳しい人間がいなくても、アプリケーションの品質を担保できます。
一方、この方法のデメリットは、性能が出ないことです。レイヤを重ねるわけですから、多くのオーバーヘッドが出てしまうことは避けられませんし、SQLを単純化すると、チューニングの手段が非常に限られます。たとえば、今回の例のように単純なINSERT文というのは、それ自体をチューニングする選択肢はほとんどありません[3] 。
つまり、ループ処理が遅延するケースというのは、1回あたりの処理時間は短い処理が積もり積もって遅くなる「チリツモ」型がほとんどであり、これを高速化するにはアプリケーションのロジックに踏み込まなければならないのです。
また、アプリケーション層で多くのループを行う場合、実装にもよりますが、アプリケーションサーバのリソースを多く消費することになるでしょう。一般的に、アプリケーションサーバはデータベースサーバよりも貧弱なリソースしか持っていないことが多いため、「 弱い環(わ) 」に高い負荷をかけてしまうことになりがちで、これも欠点の一つです[4] 。
SQLのみで記述する場合
一方、SQLのみで処理を実現する場合は、その長所短所は裏返しです。
SQLで記述することのメリットはパフォーマンスにあります。フレームワークを使う場合よりもレイヤが1つ少ない分、オーバーヘッドが減りますし、SQLはチューニングポテンシャルの高い言語で、性能改善の選択肢が豊富です。DBMSの進歩は日進月歩で、せっかくベンダーが日夜取り組んでいるSQL高速化の恩恵をあえて受けないというのも、損な話です。
デメリットは、複雑な処理をSQL単体で実現しようとすると、そのSQLはたいへん長大で読みにくいものになります。SQLは手続き型言語と違ってモジュール分割はできません 。たとえば32段の分岐を持ったSQLというのは、読むに堪えない代物になるでしょう。
また、SQLというのはコーディングする人間のレベル差がかなり顕著に出る言語です。SQLに業務ロジックを組み込むと、品質水準にバラつきが生じるリスクを負うことになります。残念なことに、SQLはCoodが期待したほど誰もが話せる言語にはなりませんでした。
トレードオフを考える
このように、業務ロジックをアプリケーションとSQLのどちらにどのように配分するか、という問題には常にトレードオフが存在しており、最適解はそれぞれの開発プロジェクトの事情によって変わってきます。冒頭でワイリーが体を張って(?) 、治療を止めようとしていましたが、それが正解になるケースもあるのです。
ただ、一つ言えることは、武器は多いほうがよい ということです。どちらか一方しか選択できない場合、それがダメだった場合に進退窮まります。状況に応じて選択肢を切り替えられる引き出しの多さとアタマの柔軟さが、優れたエンジニアに求められる資質ではないでしょうか。
【参考資料】
1.ミック『SQL ゼロからはじめるデータベース操作』(翔泳社、2010年)
「第8章 SQLで高度な処理を行なう」でウィンドウ関数の考え方について、詳しく解説しています。「 ROWS BETWEEN」オプションの使い方についても触れています。
2.ミック WEB+DBVol.55 連載「SQLアタマアカデミー」最終回「OLAP関数で強力な統計処理を実現!」
ウィンドウ関数の基礎的な使い方についてはこの回を参照してください。なお「OLAP関数」とは、ウィンドウ関数の(やや古い)別名です。gihyo.jp でも公開しております。
3.ミック「相関サブクエリで行と行を比較する 」
相関サブクエリによる行間比較の解説はこちらをどうぞ。