SQLアタマ養成講座
第7回 SQL流行間比較（3） 小分けにしたグループ内での行間比較
小分けにしたグループ内での行間比較
これまで使っていたLoadSampleテーブルは，ある特定のマシンに限定した内容になっていました。今度はこれにマシンを管理する列を追加して，一般化したテーブルをサンプルに使いましょう（表2）。
表2 LoadSample2テーブル
| machine （マシン名） | sample_date （計測日） | load （負荷量） |
|---|---|---|
| PC1 | 2008-02-01 | 1024 |
| PC1 | 2008-02-02 | 2366 |
| PC1 | 2008-02-05 | 2366 |
| PC1 | 2008-02-07 | 985 |
| PC1 | 2008-02-08 | 780 |
| PC1 | 2008-02-12 | 1000 |
| PC2 | 2008-02-01 | 999 |
| PC2 | 2008-02-02 | 50 |
| PC2 | 2008-02-05 | 328 |
| PC2 | 2008-02-07 | 913 |
| PC3 | 2008-02-01 | 2000 |
| PC3 | 2008-02-02 | 1000 |
今回は，日付の列だけを見た場合には，テーブル内で一意になっていません。こういうケースでは，日付を時系列に並べる際の基準としても，
- あくまでテーブル全体で日付を並べる
- マシンごとに集合を区切り，その中で日付を並べる
という2通りが考えられます。これに対応するよう，第6回「直近，直近の1つ前，そのまた1つ前…」のクエリを拡張するのが今回の目的です。まず1番目の方針に従って過去の日付を順に求めるなら，結果は第6回の場合と同じく図8のようになるでしょう。
図8 1．の実行結果
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
第6回のクエリをそのまま適用するだけでは，上の結果は得られません。論より証拠，実験してみましょう（リスト11，図9）。
リスト11 第6回のクエリをそのまま実行してみる：うまくいかない
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 LoadSample2;
図9 リスト11の実行結果
cur_date latest_1 latest_2 latest_3 -------- -------- -------- -------- 08-02-01 08-02-01 08-02-01 08-02-01 08-02-01 08-02-01 08-02-02 08-02-01 08-02-01 08-02-01 08-02-02 08-02-02 08-02-01 08-02-01 08-02-02 08-02-02 08-02-02 08-02-01 08-02-05 08-02-02 08-02-02 08-02-02 08-02-05 08-02-05 08-02-02 08-02-02 08-02-07 08-02-05 08-02-05 08-02-02 08-02-07 08-02-07 08-02-05 08-02-05 08-02-08 08-02-07 08-02-07 08-02-05 08-02-12 08-02-08 08-02-07 08-02-07
そう，結果はそのままテーブルの12行が現れてしまいます。OLAP関数そのものに集約機能はないのだから，これは当然と言えば当然の結果です。ということは，裏を返せば，正しい結果を得るには，事前に日付で一意になるような中間テーブルを作っておけばよい，ということになります。したがって，正しいクエリはリスト12のようになります。
リスト12 OLAP版（machine列なし）
SELECT sample_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 LoadSample2
GROUP BY sample_date;
ポイントは最後のGROUP BY句にあります。SQLはGROUP BY句で指定したキーによって一意になる集合を作ります。ここでは，sample_dateを指定しているので，これによって元のテーブルでは重複値の存在したsample_date列を一意にすることができるわけです。
なお，SELECT句のOLAP関数には，wのクエリから手を加える必要はありません。これは，SELECT句がGROUP BY句より後に実行されるからです。GROUP BY句でwと同じ中間テーブルを用意できれば，後の演算には影響が及ばない，という仕掛けです。
実装非依存の自己結合バージョンも同様の考え方でいけます。やはりGROUP BY句を追加します（リスト13）。
リスト13 自己結合版（machine列なし）
SELECT LS0.sample_date AS cur_date,
MAX(LS1.sample_date) AS latest_1,
MAX(LS2.sample_date) AS latest_2,
MAX(LS3.sample_date) AS latest_3
FROM LoadSample2 LS0
LEFT OUTER JOIN LoadSample2 LS1
ON LS1.sample_date < LS0.sample_date
LEFT OUTER JOIN LoadSample2 LS2
ON LS2.sample_date < LS1.sample_date
LEFT OUTER JOIN LoadSample2 LS3
ON LS3.sample_date < LS2.sample_date
GROUP BY LS0.sample_date;
ミック
SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。
著書：『達人に学ぶ SQL徹底指南書』（翔泳社、2008）訳書：J.セルコ『SQLパズル 第2版』（翔泳社、2007）
SQLアタマアカデミー：サポートページ
![[改訂3版]図解でよくわかる ネットワークの重要用語解説](http://image.gihyo.co.jp/assets/images/cover/2009/thumb/TH40_9784774138213.jpg){kind=small}
