第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;

さて、それでは次に、2番目の方法、つまりマシン列ごとに集合を区切って計測日を並べる場合を考えましょう。今度は、求める結果は図10のようになります。

図10　2．の実行結果

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には、2月12日が現れないことに注目してください。ほかにも、PC3には2月5日や2月8日も結果に現れません。これはもとのテーブルの情報からも妥当なことです。

こうした元のテーブルを重複しない部分集合（⁠「⁠類」と呼びます）に分割する方法を、SQLはちゃんと持っています。それが、次章で詳しく取り上げることになるGROUP BYとPARTITION BY句です。答えはリスト14、リスト15のようになります。

リスト14　OLAP版（machine列あり）

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;

リスト15　自己結合版（machine列あり）

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版も自己結合版も、GROUP BYとPARTITION BYのキーとしてmachine列を使っています。これによって、日付探索の範囲を同一マシン内に制限できるわけです。

OLAP版のクエリでは、PARTITION BYとGROUP BYの両方にmachine列を追加する必要があることに注意してください。PARTITION BY句に追加し忘れると、動作はしますが結果が正しくなりません。GROUP BY句に追加し忘れると、構文エラーとなります。それぞれ、なぜ正しく動作しないか、理由を考えてみてください。これがわかれば本章の理解は十分です。

まとめ

本章ではSQLを利用した行間比較の方法を、いろいろなパターンに応じて解説してきました。

ここまで読んでいただいた方には、章の冒頭で述べた「行間比較を学ぶことで、SQLに存在する手続き型的な側面と集合指向的な側面を一挙に学ぶことができる」という言葉の意味も理解してもらえたのではないでしょうか。手続き型の考え方に基礎を置くOLAP関数と、伝統的な集合指向による自己結合。熟練したDBエンジニアには、この両者をともに使いこなし、状況に応じて使い分けることが要請されます。きっちり理解しておいてください。

それでは、本章の要点です。

一般的なテーブル設計では、時系列的なデータは普通、行持ちの形式を取る
したがって、時間をまたいだ比較を行うことは、行をまたいだ比較（＝行間比較）を必要とする
SQLで行間比較を行う方法には次の2つがある
- 1つは手続き型の「順序」の概念を前面に押し出したOLAP関数。簡潔に記述できるが、まだ実装に依存する
- もう1つは、伝統的な集合指向に基づく自己結合。実装非依存だが、パフォーマンスに注意が必要

行間比較についてさらに学びたい方は、以下の参考文献を参照してください。

『SQLパズル　第2版』 （J.セルコ著、翔泳社、2007）: 「過去の直近の日付」という形で最大下界を求めるのが「パズル30　買い物の平均サイクル⁠」⁠、DELETE文とUPDATE文で行間比較を利用する問題が「パズル2　欠勤」と「パズル38　記録の更新⁠」⁠。そして相関サブクエリで行同士を比較する高度な応用問題が「パズル70　株価の動向⁠」⁠。いずれもSQLにおける行間比較をマスターするのに格好の練習問題です。
『達人に学ぶ　SQL徹底指南書』 （ミック、翔泳社、2008）: 「1-6 相関サブクエリで行と行を比較する」では、主に相関サブクエリと自己結合を使った行間比較を取り上げました。移動平均や移動累計のような「集合をずらして重ね合う」考え方などは、本章の発展版としておもしろいでしょう。