小分けにしたグループ内での行間比較
これまで使っていたLoadSampleテーブルは,
表2 LoadSample2テーブル
machine | sample_ | 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通りが考えられます。これに対応するよう,
図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 第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版
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句で指定したキーによって一意になる集合を作ります。ここでは,
なお,
実装非依存の自己結合バージョンも同様の考え方でいけます。やはりGROUP BY句を追加します
リスト13 自己結合版
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;