相関サブクエリで行と行を比較する

集合指向言語としてのSQL：その3

前年との比較結果を一覧表示する

成長、後退、現状維持を一度に求める　その1：相関サブクエリの利用

SELECT S1.year, S1.sale,
       CASE WHEN sale =
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '→' --横ばい
            WHEN sale >
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '↑' --成長
            WHEN sale <
             (SELECT sale
                FROM Sales S2
               WHERE S2.year = S1.year - 1) THEN '↓' --後退
       ELSE '-' END AS var
  FROM Sales S1
 ORDER BY year;

結果

year    sale    var
------  ------  -----
1990    50      ―
1991    51      ↑
1992    52      ↑
1993    52      →
1994    50      ↓
1995    50      →
1996    49      ↓
1997    55      ↑

　よく音楽CDや映画の週間ランキングで、こういう推移表を見かけますね。見ての通り、相関サブクエリをSELECT句に移して前年と今年の比較をしています。90年については前年が存在しないので、「-」を表示しています。これもやはり自己結合で書くことができます。

成長、後退、現状維持を一度に求める　その2：自己結合の利用（最初の年度は結果に現れない）

SELECT S1.year, S1.sale,
       CASE WHEN S1.sale = S2.sale THEN '→'
            WHEN S1.sale > S2.sale THEN '↑'
            WHEN S1.sale < S2.sale THEN '↑'
       ELSE '-' END AS var
  FROM Sales S1, Sales S2
 WHERE S2.year = S1.year - 1
 ORDER BY year;

　ただしこの方法だと、前年が存在しない90年が除外されて、結果が1行減ります。実際問題として、それで困ることは無いと思いますが、レイアウト上の要求から最初の年度も結果に含めたいというケースもあるでしょう。これについては、次の問題で考えます。

　ところで、この解では時間軸を縦に取りましたが、時間軸を横にとるようなフォーマットで出力することは可能でしょうか。つまり、次のような形式です。

年度	1990	1991	1992	1993	1994	1995	1996	1997
推移	-	↑	↑	→	↓	→	↓	↑

　……できるかできないかと訊かれれば、「できる」というのが答えです。やり方としては、『外部結合の使い方』で紹介した「行列変換（行→列）」の方法を使うことになります。しかし、前回も書きましたが、SQLでフォーマット整形をすることは、基本的に得策ではありません。結果の整形は、可能な限りホスト言語に任せましょう。

時系列に歯抜けがある場合：直近と比較

　前問では、各年度のデータが抜けなく揃っていました。しかし、ずぼらな会社もあったものです。過去のデータが何年分か失われてしまいました。

 

　こうなると、もう「今年 - 1」という条件設定はできません。より一般的に「直近」の行を比較対象にする必要があります。こういうケースでも、92年は90年と、97年は94年と正しく比較できるSQLを考えましょう。

　ある年度から見て「過去の直近の年度」ということは、次の2つの条件を満たす年度ということです。

1. 自分より前の年度であること
2. 条件1を満たす年度の中で最大であること

　この条件をSQLになおすと、次のようになります。

直近の年度と同じ年商の年度を選択する

SELECT year, sale
  FROM Sales2 S1
 WHERE sale =
   (SELECT sale
      FROM Sales2 S2
     WHERE S2.year =
       (SELECT MAX(year)  --条件2：条件1を満たす年度の中で最大
          FROM Sales2 S3
         WHERE S1.year > S3.year))  --条件1：自分より過去である
 ORDER BY year;

結果

year   sale
-----  ------
1992   50
1997   55

　自己結合を使うと、サブクエリのネストを1つ減らせます。

直近の年度と同じ年商の年度を選択する：自己結合と併用

SELECT S2.year AS pre_year,
       S1.year AS now_year
  FROM Sales2 S1, Sales2 S2
 WHERE S1.sale = S2.sale
   AND S2.year = (SELECT MAX(year)
                    FROM Sales2 S3
                   WHERE S1.year > S3.year)
 ORDER BY now_year;

　さらに、このアプローチによれば「累積差」、すなわち現在の値と直近の値との差分を求めることもできます。

累積差を求める　その1：開始時点は結果に含まれない

SELECT S2.year AS pre_year,
       S1.year AS now_year,
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale  AS diff
 FROM Sales2 S1, Sales2 S2
 WHERE S2.year = (SELECT MAX(year)
                    FROM Sales2 S3
                   WHERE S1.year > S3.year)
 ORDER BY now_year;

結果

pre_year    now_year    pre_sale    now_sale    diff
----------  ----------  ----------  ----------  ------
1990        1992        50          50          0      --50 - 50  = 0
1992        1993        50          52          2      --52 - 50  = 2
1993        1994        52          55          3      --55 - 52  = 3
1994        1997        55          55          0      --55 - 55  = 0

　ただし結果を見ての通り、このクエリでは、最初の年度である90年が結果に現れません。これは、90年より前の年がテーブルにないため、内部結合によって除外されたからです。もし90年も結果に含めたいなら、「自己外部結合」を使いましょう（※注1）。

累積差を求める　その2：自己外部結合の利用。開始時点も結果に含まれる

SELECT S2.year AS pre_year,
       S1.year AS now_year,
       S2.sale AS pre_sale,
       S1.sale AS now_sale,
       S1.sale - S2.sale AS diff
 FROM Sales2 S1 LEFT OUTER JOIN Sales2 S2
   ON S2.year = (SELECT MAX(year)
                   FROM Sales2 S3
                  WHERE S1.year > S3.year)
 ORDER BY now_year;

結果

pre_year    now_year    pre_sale    now_sale    diff
----------  ----------  ----------  ----------  -------
            1990                    50                  --90年も現れる
1990        1992        50          50          0
1992        1993        50          52          2
1993        1994        52          55          3
1994        1997        55          55          0

　「Sales2」テーブルをマスタにして外部結合すれば、全年度が表側に現れるという仕掛けです。自己結合に外部結合、それに非等値結合まで組み合わせた、豪勢な併せ技です。

　この直近と比較するクエリは、歯抜けなしのケースもカバーできるうえ、数値型に限らず、文字型や日付型の列で順序づける場合にも使える汎用性の高さが魅力です。ただし、極値関数はソートを発生させるため、前問の方法に比べてパフォーマンスでは劣るでしょう（極値関数の引数が主キーの場合は、そのインデックスを利用できることもありますが）。

　汎用性とコストを比較考量して、使い分けてください。

※注1

　Oracle10gは外部結合での相関サブクエリの使用をサポートしていないため、このクエリは動きません。PostgreSQL8.1およびMySQL5.0では、正しく動作します。