本日も引き続きサブクエリです。
前回とちょっと近いですが、下記のような歯抜けのテーブル(sales2)があるとします。
year | sale
——+——
1990 | 50
1992 | 50
1993 | 52
1994 | 55
1997 | 55
これの前回の年(例えば1992なら1990)との売り上げの比較を見てみましょう。
前回より難しいのが前回の年が昨年ではないので、現在の年-1などのような公式は使えません。
そこで対象の年より過去の中で最大の年を求める必要があります。
例えば対象の年が1994年だとしたら1993,1992,1991年の中で最大の年は1993年になります。)このプロセス自体が結構ややこしいです。
まず下記で直近の年を出します。
|
1 2 3 4 5 6 7 |
SELECT s1.year, s1.sale, --これで直近年を出す ( SELECT MAX( s2.year ) FROM sales2 s2 WHERE s2.year < s1.year ) as last_year FROM sales2 s1 |
year | sale | last_year
——+——+———–
1990 | 50 |
1992 | 50 | 1990
1993 | 52 | 1992
1994 | 55 | 1993
1997 | 55 | 1994
このlast year自体を検索に使い、金額を出します。
|
1 2 3 4 5 6 7 |
SELECT s1.year, s1.sale, ( SELECT MAX( s2.year ) FROM sales2 s2 WHERE s2.year < s1.year ) as last_year, ( SELECT s3.sale FROM sales2 s3 WHERE s3.year = ( SELECT MAX( s2.year ) FROM sales2 s2 WHERE s2.year < s1.year ) ) as last_sale FROM sales2 s1 |
year | sale | last_year | last_sale
——+——+———–+———–
1990 | 50 | |
1992 | 50 | 1990 | 50
1993 | 52 | 1992 | 50
1994 | 55 | 1993 | 52
1997 | 55 | 1994 | 55
ちなみに自己結合でやると下記のように書けます。最初の年がでません。
|
1 2 3 4 5 6 7 8 9 |
SELECT s1.year, s1.sale, s3.year as last_year, s3.sale as last_sale FROM sales2 s1 , sales2 s3 WHERE s3.year = ( SELECT MAX( s2.year ) FROM sales2 s2 WHERE s2.year < s1.year ) |
WHERE句を理解するのが少し難しいですが、なぜこれで抽出できるのかは実際にテーブルが作られるのをイメージすればわかりやすいかと思います。
ちなみに下記のように自己外部結合を使うと最初の年もでます。
LEFT JOINで最初のテーブルの情報が失われないためです。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT s1.year, s1.sale, s3.year as last_year, s3.sale as last_sale FROM sales2 s1 LEFT OUTER JOIN sales2 s3 ON s3.year = ( SELECT MAX( s2.year ) FROM sales2 s2 WHERE s2.year < s1.year ) |
これは結構概念的に難しいですね。ONの使い方に注意しておきましょう。
毎度毎度、自己結合は役に立ちますね・・かなり使えると思っているのですが、私は達人に学ぶSQL徹底指南書を読むまでは知りませんでした。
なぜに有益なのに知られていないのでしょうか・・・
