同じテーブル内の別の行から値を計算する

最後に、今度は同じテーブル内の異なる行の情報をもとに計算した結果を更新するケースを考えます。サンプルテーブルStcoksは株価の取引情報を記録するもので、銘柄ごとに取引を行った日の株価が記録されています（表8⁠）⁠。ここから各銘柄についてtrend列を計算して、空っぽのテーブルStocks2にデータをINSERTするのが今回の課題です。

trendは前回の終値と今回の終値を比較して、上昇したなら「↑⁠」⁠、下降したなら「↓⁠」⁠、横ばいなら「→」という3つの値をとります（表9⁠）⁠。当然、それぞれの銘柄の最初の取引日の行については計算できないので、NULLのままになります。

今度は既存の行に対する更新ではなく、新規に行を追加するので、INSERT SELECT構文を使うことはすぐにわかります。あとはtrend列の計算方法ですが、行間比較とくれば……そう、使う道具は相関サブクエリです（リスト9⁠）⁠。

INSERT INTO Stocks2
SELECT brand, sale_date, price,
       CASE SIGN(price -
                   (SELECT price
                      FROM Stocks S1
                      WHERE brand = Stocks.brand
                       AND sale_date =
                            (SELECT MAX(sale_date)
                               FROM Stocks S2
                               WHERE brand = Stocks.brand
                                AND sale_date < Stocks.sale_date)))
           WHEN -1 THEN '↓'
           WHEN 0 THEN '→'
           WHEN 1 THEN '↑'
       END
  FROM Stocks;

SIGN関数は引数の値の正・負・ゼロに対して、1、-1、0を返す符号を調べる関数で、すべてのDBで使用できます。これによって、「⁠今日の終値 - 直前の取引日の終値」の符号を調べているのです。

INSERTを使用するメリット、デメリット

同じ要領で、Stocksテーブルそのものにtrend列を用意してUPDATEを行うことも可能なのですが、INSERTには2つのメリットがあります。まず1つは、一般的にUPDATEに比べてINSERTのほうがパフォーマンスが良いため、高速な処理が期待できること。そして2つ目は、MySQLのように更新SQLでの自己参照を許していないDBでもINSERT SELECTならば利用可能なことです（参照元と更新先が別テーブルなのがミソです⁠）⁠。

反対に、INSERTを使用する方法の欠点は、同じサイズのデータを保持する、ほとんど同じ構造のテーブルを2つ用意しなければならない分、メモリを一時的にではあれ2倍以上消費することです。これが問題になる場合はStocks2テーブルをビューにするというのも一案です。ただしその場合はStocks2テーブルにアクセスが発生するたびに再計算が行われるので、パフォーマンスが悪化します。このあたりは状況によって最適解が変わってくるので、よく要件と照らし合わせて方針を決めましょう。

おわりに

私たちが日常SQLでデータベースを扱う際に発生する問題の半分は、テーブル設計そのものに問題があるものです。そういうケースでは、本当は思いきってテーブルをきれいな形に整形しておいたほうがその後の処理もずっと簡単になります（⁠「⁠賢いデータ構造と間抜けなコードのほうが、その逆よりずっとまし」とE.S.Raymond[5]も言っています⁠）⁠。シンプルな設計に勝るものはなし、です。

しかし、私たちはともするとついつい悪いデータ構造を放置して、複雑なコーディングで問題を解決するほうへ傾きがちです。また現実問題として「今さらテーブルの構造を変えられない or 変える権限がない」というやるせない現場の事情もあることでしょう。ですから「鉄は熱いうちに打て」の格言どおり、テーブル設計は最初が肝心です。そして、美しく機能的な設計を実現する上では、本稿で紹介した強力な更新機能を利用することが大きな助けになるでしょう。

参考資料

J.セルコ『SQLパズル 第2版』

（翔泳社、2007、ISBN：978-4-7981-1413-2）

UPDATE文での相関サブクエリの応用については、「⁠パズル4 入館証⁠」⁠、「⁠パズル18 ダイレクトメール⁠」⁠、「⁠パズル38 記録の更新」が格好の演習になるでしょう。また、「⁠SELECT文をあれこれひねるよりも、テーブル設計を見直せ」という言葉の重要性を体感できる実例としては、「⁠パズル8 プリンタの割り当て⁠」⁠、「⁠パズル17 人材紹介会社⁠」⁠、「⁠パズル24 10個のうち1つだけ⁠」⁠、「⁠パズル39 保険損失」など。

ミック『達人に学ぶ　SQL徹底指南書』

（翔泳社、2008、ISBN：978-4-7981-1516-0）

更新SQLと組み合わせることで大きな効力を発揮するCASE式と相関サブクエリの基本については、「⁠1-1. CASE式のススメ⁠」⁠、「⁠1-6. 相関サブクエリで行と行を比較する」を参照。また、相関サブクエリにおける自己参照のイメージをつかみたければ、「⁠1-2. 自己結合の使い方」をどうぞ。

J.セルコ『プログラマのためのSQL 第2版』

（ピアソン・エデュケーション、2001、
ISBN：978-4-8947-1480-9）

リスト機能については「10.2 SQL-92における行の比較」を参照。リスト比較は、より一般的には行全体を操作単位とする行比較としてとらえられます。