SQLアタマ養成講座

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

この記事を読むのに必要な時間:およそ 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;

著者プロフィール

ミック

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

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

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

コメント

コメントの記入