SQL緊急救命室

第3回ループ依存症~手続き型の呪縛を打ち破れ!

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

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

何軒もの病院をたらいまわしにされた、手の施しようのない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のようになる。

図1Sales、Sales2テーブル
図1 Sales、Sales2テーブル
図2 登録後の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の動作
図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 レコード集合の対応をマッピング
図5 レコード集合の対応をマッピング

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

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

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

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

S0⊂S1⊂S2⊂S3

この包含関係を図示すると、図6のような同心円的な再帰集合が描けるわ。

図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は手続き型のレイヤに挟まれている
図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テーブル
図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 テーブルはファイルではない
図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.ミック相関サブクエリで行と行を比較する
相関サブクエリによる行間比較の解説はこちらをどうぞ。

おすすめ記事

記事・ニュース一覧