前年との比較結果を一覧表示する
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年については前年が存在しないので、「-」を表示しています。これもやはり自己結合で書くことができます。
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を満たす年度の中で最大であること
この条件を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;
さらに、このアプローチによれば「累積差」、すなわち現在の値と直近の値との差分を求めることもできます。
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）。
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」テーブルをマスタにして外部結合すれば、全年度が表側に現れるという仕掛けです。自己結合に外部結合、それに非等値結合まで組み合わせた、豪勢な併せ技です。
この直近と比較するクエリは、歯抜けなしのケースもカバーできるうえ、数値型に限らず、文字型や日付型の列で順序づける場合にも使える汎用性の高さが魅力です。ただし、極値関数はソートを発生させるため、前問の方法に比べてパフォーマンスでは劣るでしょう（極値関数の引数が主キーの場合は、そのインデックスを利用できることもありますが）。
汎用性とコストを比較考量して、使い分けてください。
