ここはとある街の総合病院。

ここには通常の診療科のほかに、一風変わった診療科が存在する。

何軒もの病院をたらいまわしにされた、手の施しようのないSQLや、今すぐに改善が必要なSQLが担ぎ込まれる救命室である。

それがSQL緊急救命室、略してSER(SQL Emergency Room⁠)⁠。

そう、ここは国内でも唯一のプログラミング専門外来である。

ループという病

SQLを学ぶ上で最も高いハードルとなるのが、順序と手続きではなく、集合と論理の観点から考えることだ。

―J.Celko『Joe Celko's SQL Programming Style』
(⁠Morgan Kaufmann、2005年)p.184

ループによる表現

(PM3:00 手術室。先ほどかつぎ込まれたばかりの患者を前にして、何やら揉めている)

ワイリー、そこをどけ!

いーえ、どきません。僕は、どうしても今回の患者に治療が必要だとは思えないのです。先生は、健康な患者に、功名心のあまり必要のない治療をしようとしていませんか? 先生は切らなきゃ気がすまないんでしょう!

若僧が、きいた風な口を! いいからどけっ(ドンッ)

ああっ!(へなへなと崩れ落ちる)

ワイリー、喧嘩している時間はないわ。さっさと始めるわよ。

SQLでループを置き換えるには

患者のコードはリスト1ですね。

CREATE OR REPLACE PROCEDURE PROC_INSERT_VAR
IS
 /* 1カーソル宣言 */
 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
 /* 直前のレコードが同じ会社のレコードの場合 */
 /* 直前のレコードと売り上げを比較(23)*/
 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;
 /* 4登録先テーブルにデータを登録 */
 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;

問題のパターンとしては、行間比較[1] とコントロールブレーク処理[2] ね。患者のコードからアルゴリズムを抽出すると、こうなるわ。

• 1 Salesテーブルから全件レコードを取得する。このとき「企業,年」の昇順にソートしておく
• 2 1レコードずつループを行い、同じ会社のレコードであるか比較する
• 3 同じ会社のレコードであれば、その売り上げと直近の売り上げを比較し、比較の結果に応じて「変化」列の値を設定する
• 4 Sales2テーブルへINSERTする
• 5 2〜4の処理をレコードがなくなるまで続ける

素朴な疑問なんですけど、このコードには何か問題があるんですか? すごくわかりやすい、当たり前の解じゃないですか。やっぱり治療が必要だとは思えないなあ。

確かに、手続き型言語としてはオーソドックスな解だ。だがそれを宣言型の世界に持ち込むと、ループ依存症になる。ループというのは呪いみたいなものだ。我々の思考を常に規定し、縛ろうとしてくる。

その呪いから脱するにはどうすればいいんですか?

「レコード」ではなく、「⁠レコードの集合」という観点から考えること。SQLで行間比較を行う手段は何?

えっと......ウィンドウ関数「参考資料1、2⁠]⁠、それが使えない実装では相関サブクエリ[参考資料3]......でしたっけ。

そうね。ループの部分を丸ごとそれで置き換えることが可能よ。あとは、IF文はSQLでは常にCASE式に変換できることは、前回も言ったとおり。ウィンドウ関数を使った解はこうなるわ(リスト2⁠)⁠。

INSERT INTO Sales2
SELECT company,
 year,
 sale,
 CASE WHEN MAX(company) ―1直前のレコードが同じ会社のレコードの場合
 OVER (ORDER BY company, year
 ROWS BETWEEN 1 PRECEDING ―2
 AND 1 PRECEDING) = company
 THEN CASE SIGN(sale - MAX(sale) ―3直前のレコードと売り上げを比較
 OVER (ORDER BY company, year
 ROWS BETWEEN 1 PRECEDING ―4
 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式が、それぞれ「1同じ会社であるかの比較」「⁠3直近の売り上げとの比較」に対応しています。これは手続き型言語で書いた場合と同様です。

この解で重要な技術は、ウィンドウ関数においてROWS BETWEENオプションを使っていることです(24⁠)⁠。これはさかのぼる対象範囲のレコードを、直前の1行に制限しています。ROWS BETWEEN 1 PRECEDING AND 1 PRECEDINGは、「⁠カレントレコードの1行前から1行前の範囲」という意味なので、結局、直前の1行だけを含みます(図3⁠)⁠。

つまり、2つのウィンドウ関数は、それぞれ「1行前の会社名」と「1行前の売り上げ」を戻り値とします。

リスト3、図4のように、結果を表示させてみればはっきりします。

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;

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関数を使っているのもワンポイントです(リスト23⁠)⁠。これは、数値型を引数に取り、符号がマイナスなら-1を、プラスなら1を、0の場合は0を返す関数で、直近の年との売り上げの変化を知るために利用しています。CASE式の条件部分に、何度もウィンドウ関数を記述しないためのちょっとした小技です(SQLには変数がないため、こうした小技が必要になります⁠)⁠。

ROWS BETWEENオプションを利用できる環境

ただし、このアプローチを利用するときには注意点があります。このコードが実行できるのは、現在のところOracleおよびDB2のみです。MySQLはもともとウィンドウ関数を未実装のため使えないのは自明ですが、PostgreSQLとSQL Serverの場合ウィンドウ関数は一応実装しているのですが、ROWS BETWEENオプションを使うことができません。

ですがこれは標準SQLの機能なので、時間が経てばいずれ多くの実装に普及していくことが期待されます。ただ、まだ比較的新しい機能のため、使える実装が限られることも事実です。

ウィンドウ関数が使えない場合のために、次に相関サブクエリによるアプローチを見てみましょう。

相関サブクエリでループを置き換える

うーん、難しい。難しいですよこれは。

自慢気に言うな。では、相関サブクエリの解はどうなる?

「直前の1行」をスカラサブクエリで表現すれば、ウィンドウ関数で作っているvar列を同じように作れるはずってことですよね。よし......これでどうでしょう(リスト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のようになります(太字の年は、集合の中の最大値を示します⁠)⁠。

このように、「⁠ある値を基準にそれより小さい値の集合」を集合論で「下界」(⁠lower bound)と呼びます。基準値となる年が進むに従って、下界の要素数は1つずつ増えていく様子がわかります。

相関サブクエリの正体は入れ子集合

へえ〜、おもしろいなあ。S1はS0を含み、S2はS1を含み......という風にどんどん入れ子状に集合が大きくなっていくんですね。

そうね。そういう見方をすれば、この相関サブクエリが作る下界は再帰的集合でもあるわね。あなたが今言ったように、S0〜S3には次のような包含関係が成立するから。

奇妙なサンドイッチ

非等値結合は、数学的にはいろいろ興味深い操作ではあるな。では、ウィンドウ関数、相関サブクエリを使った2つの解について実行計画も見ておくとしよう(図7、8⁠)⁠。

-------------------------------------------------------------------------------------------
| 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 |
-------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------
| 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みたいに、手続き型言語に挟まれたサンドイッチみたいだ、と思って。

ループからの脱出

うまいことを言うようになったな。お前の指摘は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で解く場合はどうする?

相関サブクエリによる更新

うーんと、相関サブクエリのほうはこう(リスト5)......あれ、エラーになった。「⁠単一行副問い合わせにより2つ以上の行が戻されます」って。

当たり前よ。これじゃSalesテーブルの1行にSales3テーブルから選択した全行を対応させようとしているのだから。ちゃんと一対一に条件を絞りなさい。

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)

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句でウィンドウ関数の部分だけ代入してみたんですけど。

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⁠)⁠。

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レコードずつ順次アクセスする」という発想がありません。常に、複数のレコードをまとめて処理しようとします。

しかし、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.ミック「相関サブクエリで行と行を比較する」

相関サブクエリによる行間比較の解説はこちらをどうぞ。