SQLアタマ養成講座

第7回　SQL流行間比較（3）　小分けにしたグループ内での行間比較

SQL, データベース, 行間比較

この記事を読むのに必要な時間：およそ 4 分

小分けにしたグループ内での行間比較

これまで使っていたLoadSampleテーブルは，ある特定のマシンに限定した内容になっていました。今度はこれにマシンを管理する列を追加して，一般化したテーブルをサンプルに使いましょう（表2⁠）⁠。

表2　LoadSample2テーブル

machine
（マシン名）		sample_date
（計測日）		load
（負荷量）
PC12008-02-011024
PC12008-02-022366
PC12008-02-052366
PC12008-02-07985
PC12008-02-08780
PC12008-02-121000
PC22008-02-01999
PC22008-02-0250
PC22008-02-05328
PC22008-02-07913
PC32008-02-012000
PC32008-02-021000

今回は，日付の列だけを見た場合には，テーブル内で一意になっていません。こういうケースでは，日付を時系列に並べる際の基準としても，

  1. あくまでテーブル全体で日付を並べる
  2. マシンごとに集合を区切り，その中で日付を並べる

という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;

SQL, データベース, 行間比較

著者プロフィール

ミック

SI企業に勤務するDBエンジニア。主にデータウェアハウス業務に従事している。自身のサイト「リレーショナル・データベースの世界」でデータベースとSQLについての技術情報を公開している。『Web+DB Press』で「SQLアタマアカデミー」を連載中。

著書：『達人に学ぶ SQL徹底指南書』（翔泳社、2008）訳書：J.セルコ『SQLパズル 第2版』（翔泳社、2007）

SQLアタマアカデミー：サポートページ