同じテーブル内の別の行から値を計算する
最後に、今度は同じテーブル内の異なる行の情報をもとに計算した結果を更新するケース を考えます。サンプルテーブルStcoksは株価の取引情報を記録するもので、銘柄ごとに取引を行った日の株価が記録されています(表8 ) 。ここから各銘柄についてtrend列を計算して、空っぽのテーブルStocks2にデータをINSERTするのが今回の課題です。
表8 サンプルテーブル(trend列を計算してINSERT)
Stocks:株価テーブル
brand(銘柄) sale_date(取引日) price(終値)
A鉄鋼 2008-07-01 1000
A鉄鋼 2008-07-04 1200
A鉄鋼 2008-08-12 800
B商社 2008-06-04 3000
B商社 2008-09-11 3000
C電気 2008-07-01 9000
D産業 2008-06-04 5000
D産業 2008-06-05 5000
D産業 2008-06-06 4800
D産業 2008-12-01 5100
Stocks2:更新先の株価テーブル(からっぽ!)
brand(銘柄) sale_date(取引日) price(終値) trend(トレンド)
trendは前回の終値と今回の終値を比較して、上昇したなら「↑」 、下降したなら「↓」 、横ばいなら「→」という3つの値をとります(表9 ) 。当然、それぞれの銘柄の最初の取引日の行については計算できないので、NULLのままになります。
表9 Stocks2:更新後
brand(銘柄) sale_date(取引日) price(終値) trend(トレンド)
A鉄鋼 2008-07-01 1000
A鉄鋼 2008-07-04 1200 ↑
A鉄鋼 2008-08-12 800 ↓
B商社 2008-06-04 3000
B商社 2008-09-11 3000 →
C電気 2008-07-01 9000
D産業 2008-06-04 5000
D産業 2008-06-05 5000 →
D産業 2008-06-06 4800 ↓
D産業 2008-12-01 5100 ↑
今度は既存の行に対する更新ではなく、新規に行を追加するので、INSERT SELECT構文を使うことはすぐにわかります。あとはtrend列の計算方法ですが、行間比較とくれば……そう、使う道具は相関サブクエリです(リスト9 ) 。
リスト9 trend列を計算してINSERTする更新SQL
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における行の比較」を参照。リスト比較は、より一般的には行全体を操作単位とする行比較としてとらえられます。