はじめに

データベースを利用する目的の1つとして、過去の情報を時系列順に、文字通り「データの集積」として保持する用途があります。そうした情報は、たとえばシステムのパフォーマンスログや会社の財務状況、あるいは集団の人口推移だったりするでしょう。多くの業務では、こうした過去のデータへ遡って現在と比較する、あるいは過去の2点や期間同士を比較して将来の指針を策定する手がかりとすることが重要になります。いわゆる「データウェアハウス」と総称される用途です。

リレーショナルデータベースとSQLにおいては、そうした時系列的なデータの多くは、「⁠時間」を表す列（時刻、日付、年度などなど）をキーとして、異なる時刻のデータは異なる行として保持するのが一般的なテーブル設計です。これを「列持ち」の形で保持することは、例外的なケースに属します[1]⁠。

そうすると当然、ある2点の時刻におけるデータを比較するためには行間比較が必要となります。そのために必要な記述方法の基礎を学ぶことが本章の目的です。手続き型言語でファイルを扱う場合には、こういう行間比較のためには「ループ」による処理が基本となりますが、SQLには文単位のループは一切現れません。代わりにSQLは、独自の原理に基づいた方法を用います。

具体的には、標準SQLの新しい機能であるOLAP関数を利用する方法と、従来の自己結合（相関サブクエリ）を使う方法の2通りを紹介します。この両者を学ぶことには、SQLに存在する手続き型的な側面と集合指向的な側面を一挙に学ぶことができるというメリットがあります。

まあ、そうしたことは、本章を読み進む過程で追々わかってくるでしょう。今は、頭の片隅にメモ程度に覚えておいていただければ結構です。それでは、早速始めましょう。

まずは基本

直近を求める

まずは基本的な時系列分析から始めましょう。時系列にデータを比較する場合、基本となるのは、時系列に従って、1行ずつ過去へ遡る、または未来へ進むSQLです。サンプルに、表1のようなサーバの時間ごとの負荷量を記録したテーブルを使います。サンプリングは思いついたときに不定期に行われるため、不連続で間隔もランダムな日付が格納されています。

まずは、各行について過去の直近の行を求めてみましょう。OLAP関数を使える実装ならば、リスト1のような簡潔な書き方で実現できます（実行結果は図1：注2⁠）⁠。

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

cur_date   latest
-------- --------
08-02-01
08-02-02 08-02-01
08-02-05 08-02-02
08-02-07 08-02-05
08-02-08 08-02-07
08-02-12 08-02-08

2月1日より前のデータはこのテーブルには登録されていないので、2月1日の行については直前の日付はNULLです。2月2日以降についてはそれぞれ直前の日付が存在するので、これがlatest列に入ることになります。このクエリのポイントは、「⁠BETWEEN 1 PRECEDING AND 1 PRECEDING」によって、OLAP関数が動く範囲をあくまでsample_dateでソートした場合の直前の1行に限定していることです。普通、「⁠BETWEEN」というのは、複数行の範囲を指定するために使う場合が多いのですが、ここはあえて範囲を1行に限定するために利用しています。

これを実装非依存のクエリにするには、OLAP関数の部分を相関サブクエリに書き換えます（リスト2⁠）⁠。

SELECT LS0.sample_date AS cur_date,
         (SELECT MAX(sample_date)
            FROM LoadSample LS1
          WHERE LS1.sample_date < LS0.sample_date) AS latest
  FROM LoadSample LS0;

これも結果はリスト1と同じです。ポイントはWHERE句の「LS1.sample_date < LS0.sample_date」という非等値結合です。この意味は、カレント行（LS0）のsample_dateより小さい日付の集合（LS1）から、最大の日付を選択する、ということです。このクエリの意味がわかりづらいなら、相関サブクエリを自己結合に変えたものを見るとわかりやすくなるでしょう（リスト3⁠）⁠。

SELECT LS0.sample_date AS cur_date,
       MAX(LS1.sample_date) AS latest
  FROM LoadSample LS0
           LEFT OUTER JOIN LoadSample LS1
             ON LS1.sample_date < LS0.sample_date
 GROUP BY LS0.sample_date;

この自己結合の場合も、考え方は相関サブクエリの場合とまったく同じです。試しに、集約抜きにしてヒラで結果を得てみましょう（リスト4、図2⁠）⁠。

SELECT LS0.sample_date AS cur_date,
       LS1.sample_date AS latest
  FROM LoadSample LS0
           LEFT OUTER JOIN LoadSample LS1
             ON LS1.sample_date < LS0.sample_date;

cur_date   latest
-------- --------
08-02-01             ← S0：2月1日より小さい日付は
                                     1つもないので、0個
08-02-02 08-02-01    ← S1：2月2日より小さい日付は1個

08-02-05 08-02-01    ← S2：2月5日より小さい日付は2個
08-02-05 08-02-02

08-02-07 08-02-01    ← S3：2月7日より小さい日付は3個
08-02-07 08-02-02
08-02-07 08-02-05

08-02-08 08-02-01    ← S4：2月8日より小さい日付は4個
08-02-08 08-02-02
08-02-08 08-02-05
08-02-08 08-02-07

08-02-12 08-02-01    ← S5：2月12日より小さい日付は5個
08-02-12 08-02-02
08-02-12 08-02-05
08-02-12 08-02-07
08-02-12 08-02-08

このように、カレントの日付であるcur_dateに対して、それぞれより小さい日付の集合が得られることが確認できます。あとは、各集合の中からMAX関数で最大の日付（図2中の太字の箇所です）を選択すればOK。こういうある集合の中で、基準値より小さい最大の要素のことを、集合論では最大下界（greatest lower bound）と呼んでいます[3]⁠。過去の直近の日付を求めるとは、つまり最大下界を求めることと同義なのです。

S0～S5の部分集合は、図3からも明らかなように、

という包含関係のある部分集合群です[4]⁠。こういう同心円的な集合は、数学的にはフォン・ノイマンによる再帰集合の構造にそのアイデアの源泉が求められます[5]⁠。

現在の日付の処理量と、直近の日付の処理量も併せて表示したいなら、リスト5のようにcur_load_amtとlatest_load_amtの2列を追加すればよいでしょう（実行結果は図4⁠）⁠。

SELECT LS0.sample_date AS cur_date,
       MAX(LS0.load_amt)   AS cur_load_amt,
       MAX(LS1.sample_date) AS latest,
       (SELECT MAX(load_amt)
          FROM LoadSample
         WHERE sample_date = MAX(LS1.sample_date)) AS latest_load_amt
  FROM LoadSample LS0
           LEFT OUTER JOIN LoadSample LS1
             ON LS1.sample_date < LS0.sample_date
 GROUP BY LS0.sample_date;

cur_date   cur_load_amt  latest      latest_load_amt
---------- ---------     ----------  -----------
2008-02-01     1024             
2008-02-02     2366      2008-02-01         1024
2008-02-05     2366      2008-02-02         2366
2008-02-07      985      2008-02-05         2366
2008-02-08      780      2008-02-07          985
2008-02-12     1000      2008-02-08          780

ただし注意が必要なのは、latest_load_amtを求めるサブクエリ内のWHERE句でMAX関数を使っていることです。LS1.sample_dateは外側のGROUP BY句によって集約されているため、SELECT句では集約関数を適用する形でしか参照することができないのです。したがってこれは標準SQLに則った妥当なSQLなのですが、現在ではPostgreSQLでしか正しく動作しません[6]⁠。

そこで代替案として、リスト6のように最大下界を求めるロジックをWHERE句に移す方法が考えられます。

SELECT LS0.sample_date AS cur_date,
       LS0.load_amt    AS cur_load,
       LS1.sample_date AS latest,
       LS1.load_amt    AS latest_load
  FROM LoadSample LS0
           LEFT OUTER JOIN LoadSample LS1
             ON LS1.sample_date = (SELECT MAX(sample_date)
                                     FROM LoadSample
                                    WHERE sample_date < LS0.sample_date);

これも結果は先ほどと同じになります[7]⁠。