第6回　SQL流行間比較（2）　直近、直近の1つ前、そのまた1つ前…

直近、直近の1つ前、そのまた1つ前…

これで1つ前の日付を求めることはできるようになりました。ですが実務では、もう少し比較の範囲を広げて、「⁠直近の前」の日付や「直近の前のそのまた前」の日付、欲を言えば、一般的に「n個前の日付」と比較したい、という要望も生じることでしょう。つまり、行間比較の一般化です。

この要望に応えるため、まずはある日付を起点として、そこから順次過去へ遡った日付を求める方法を考えます。とりあえず3つ前まで求めるとすると、結果は図5のようにピラミッド型（というか階段型というか）になるでしょう。

図5　想定される実行結果

cur_date latest_1 latest_2 latest_3
-------- -------- -------- --------
08-02-01
08-02-02 08-02-01
08-02-05 08-02-02 08-02-01
08-02-07 08-02-05 08-02-02 08-02-01
08-02-08 08-02-07 08-02-05 08-02-02
08-02-12 08-02-08 08-02-07 08-02-05

まずはOLAP関数を使うならば、リスト7のようになります。

リスト7　3つ前の日付まで出力する：OLAP関数の利用

SELECT sample_date AS cur_date,
       MIN(sample_date) 
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
       MIN(sample_date) 
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
       MIN(sample_date) 
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
  FROM LoadSample;

BETWEENによる行の指定先を「1行前⁠」⁠、「⁠2行前⁠」⁠、「⁠3行前」…と変更するだけなので、非常に簡単に済むのがよいところです。後は何行前でも同じやり方で拡張できますし、「⁠行の順序」という手続き型の発想に基づく方法なので、多くのプログラマにとっても理解しやすいでしょう。

一方、OLAP関数を持たない実装でも動作する、より一般的な解法は、集合指向に基づいて考える必要があります。方法は、基本的に先ほどの直近の場合と同じで、時刻をずらした集合を複数用意して自己結合を使います。

答えはリスト8のようになります。

リスト8　3つ前の日付まで出力する：自己結合の利用

SELECT LS0.sample_date AS sample_date,
       MAX(LS1.sample_date) AS latest_1,
       MAX(LS2.sample_date) AS latest_2,
       MAX(LS3.sample_date) AS latest_3
  FROM LoadSample LS0
           LEFT OUTER JOIN LoadSample LS1
             ON LS1.sample_date < LS0.sample_date
             LEFT OUTER JOIN LoadSample LS2
               ON LS2.sample_date < LS1.sample_date
               LEFT OUTER JOIN LoadSample LS3
                 ON LS3.sample_date < LS2.sample_date
 GROUP BY LS0.sample_date;

クエリの中に使いたいだけ集合を追加できるのが集合指向という考え方の柔軟なところです（その分、パフォーマンスを圧迫するのでやり過ぎには注意が必要ですが⁠）⁠。

S0が「今日⁠」⁠、S1が「その1つ前⁠」⁠、S2が「さらにそのもう1つ前」を表します。確かに2月5日の前の日付として、2月2日と2月1日が選択できています。外部結合を使っているのは、繰り返しになりますが、「⁠前の日付」がテーブル上に存在しない日付についても結果から落とさずに一覧表示するためのトリックです。反対に、そういう行を結果に含める必要がないなら、単純な内部結合を使えばOKです。結果がどのように異なるか、自分で上のクエリを書き換えて確かめてみてください。

そうしたら、後は同じ要領で集合Snと結合条件を追加していけば、どれだけ過去にでも遡れます。無論、過去だけでなく未来へも自由に動けます。たとえば、2月5日の前後の直近を求めるなら、リスト9、リスト10のようなクエリで可能です（実行結果は図6⁠）⁠。

リスト9　現在と過去の直近を求める：OLAP版

SELECT MIN(sample_date) 
          OVER (ORDER BY sample_date ASC
                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS past,
       sample_date AS cur_date,
       MAX(sample_date) 
          OVER (ORDER BY sample_date DESC
                ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS future
  FROM LoadSample;

リスト10　現在と過去の直近を求める：自己結合版

SELECT MAX(LS1.sample_date) AS past,
       LS.sample_date AS sample_date,
       MIN(LS2.sample_date) AS future
  FROM LoadSample LS
           LEFT OUTER JOIN LoadSample LS1
             ON LS1.sample_date < LS.sample_date
             LEFT OUTER JOIN LoadSample LS2
               ON LS2.sample_date > LS.sample_date
 GROUP BY LS.sample_date;

図6　リスト9、10の実行結果

past     cur_date future
-------- -------- --------
         08-02-01 08-02-02
08-02-01 08-02-02 08-02-05
08-02-02 08-02-05 08-02-07
08-02-05 08-02-07 08-02-08
08-02-07 08-02-08 08-02-12
08-02-08 08-02-12

OLAP版、自己結合版ともに、「⁠未来の直近」を求める関数をMAXからMINに変えているのがおわかりいただけるでしょう。たとえばカレントの日付が2月5日だとすると、集合論的な見方をすれば、これは集合を上下2つに分割する境界線（bound）を与える役割を果たしているのです（図7⁠）⁠。

2月5日の前後の日付を求めるということは、上位集合の最小元（＝最小上界）と下位集合の最大元（最大下界）を求めることと同義です。もちろん、範囲は上下にいくらでも広げられます。こう考えてみると、順序の概念を使うOLAP関数の手続き型発想と、集合の概念を使う集合指向の考え方も、使う道具立てが異なるだけで、ロジックの構造として帰するところは1つであることがわかるのではないでしょうか。

このように、与えられた集合（テーブル）をカットして新たな部分集合を作り、それを操作することによってさまざまな演算を自在にこなせるようになると、SQLの使い方が一段レベルアップします。この「集合のカット」という技術については、次章でさらに別の観点から詳しく見ることになります。

さて、それでは最後に、応用問題へ進みましょう。