小分けにしたグループ内での行間比較
これまで使っていたLoadSampleテーブルは、
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通りが考えられます。これに対応するよう、
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回のクエリをそのまま適用するだけでは、
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;
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
そう、
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句を追加します
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;
さて、
machine cur_date latest_1 latest_2 latest_3 ------ -------- -------- -------- -------- PC1 08-02-01 PC1 08-02-02 08-02-01 PC1 08-02-05 08-02-02 08-02-01 PC1 08-02-07 08-02-05 08-02-02 08-02-01 PC1 08-02-08 08-02-07 08-02-05 08-02-02 PC1 08-02-12 08-02-08 08-02-07 08-02-05 PC2 08-02-01 PC2 08-02-02 08-02-01 PC2 08-02-05 08-02-02 08-02-01 PC2 08-02-07 08-02-05 08-02-02 08-02-01 PC3 08-02-01 PC3 08-02-02 08-02-01
もともと2月12日に計測を行っていないPC2やPC3には、
こうした元のテーブルを重複しない部分集合
SELECT machine,
sample_date AS cur_date,
MIN(sample_date)
OVER (PARTITION BY machine ORDER BY sample_date ASC
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS latest_1,
MIN(sample_date)
OVER (PARTITION BY machine ORDER BY sample_date ASC
ROWS BETWEEN 2 PRECEDING AND 2 PRECEDING) AS latest_2,
MIN(sample_date)
OVER (PARTITION BY machine ORDER BY sample_date ASC
ROWS BETWEEN 3 PRECEDING AND 3 PRECEDING) AS latest_3
FROM LoadSample2
GROUP BY machine, sample_date;
SELECT LS0.machine AS machine,
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 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.machine, LS0.sample_date;
OLAP版も自己結合版も、
OLAP版のクエリでは、
まとめ
本章ではSQLを利用した行間比較の方法を、
ここまで読んでいただいた方には、
それでは、
- 一般的なテーブル設計では、
時系列的なデータは普通、 行持ちの形式を取る
したがって、時間をまたいだ比較を行うことは、 行をまたいだ比較 (=行間比較) を必要とする - SQLで行間比較を行う方法には次の2つがある
- 1つは手続き型の
「順序」 の概念を前面に押し出したOLAP関数。簡潔に記述できるが、 まだ実装に依存する - もう1つは、
伝統的な集合指向に基づく自己結合。実装非依存だが、 パフォーマンスに注意が必要
- 1つは手続き型の
行間比較についてさらに学びたい方は、
- 『SQLパズル 第2版』
(J.セルコ 著、 翔泳社、 2007) - 「過去の直近の日付」
という形で最大下界を求めるのが 「パズル30 買い物の平均サイクル」、 DELETE文とUPDATE文で行間比較を利用する問題が 「パズル2 欠勤」 と 「パズル38 記録の更新」。そして相関サブクエリで行同士を比較する高度な応用問題が 「パズル70 株価の動向」。いずれもSQLにおける行間比較をマスターするのに格好の練習問題です。 - 『達人に学ぶ SQL徹底指南書』(ミック、翔泳社、2008)
- 「1-6 相関サブクエリで行と行を比較する」
では、 主に相関サブクエリと自己結合を使った行間比較を取り上げました。移動平均や移動累計のような 「集合をずらして重ね合う」 考え方などは、 本章の発展版としておもしろいでしょう。