先日同様、ある程度複雑な問題のSQLに関して。出典はSQL実践入門。
問題 下記のようなテーブル(テーブル名)で
より古い年のデータが存在しない場合 NULL
直近の年のデータより売り上げが伸びた場合:+
直近の年のデータより売り上げが下がった場合:-
直近の年のデータと売り上げが同じ場合:=
| company | year | sale |
|---|---|---|
| A | 2002 | 52 |
| A | 2004 | 54 |
| A | 2005 | 84 |
| B | 2006 | 15 |
| B | 2001 | 95 |
| B | 2003 | 23 |
| B | 2008 | 15 |
| C | 2001 | 18 |
| C | 2009 | 32 |
| C | 2010 | 12 |
難しいのは前回との差分の取り方でしょう・・・サブクエリでゴリゴリやればいけるかもしれませんがかなり難しいでしょう。
こんな時にウィンドウ関数を使えば比較的楽に計算結果を出すことができます。
手順としては
PARTITION BYを使って会社ごとにグルーピングし、年度で並び替え、をするのですが、下記関数で前列との比較ができます。
|
1 |
MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) |
select列に上記の関数を入れてあげると、去年の売上を出すことができます。
|
1 |
ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING |
の部分ですが、1行前から1行前の範囲という意味です。(要は直前の1行だけを対象に最大値を求めています。)
これを応用し、下記のように書いてあげれば前年度との変化を書くことができます。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT company, year , sale, CASE WHEN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ) > 0 THEN '+' WHEN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ) = 0 THEN '=' WHEN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ) < 0 THEN '+' ELSE NULL END AS var FROM Sales |
CASE式の中がやや複雑ですが、「今の売上－前期の売上」を書いているだけです。
上記のようなものでも構いませんが、さらにSIGNという関数を使い下記のようにスマートに書けます。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT company, year , sale, CASE SIGN ( sale - MAX (sale) OVER( PARTITION BY company ORDER BY year ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING ) ) WHEN 1 THEN '+' WHEN -1 THEN '-' WHEN 0 THEN '=' ELSE NULL END AS var FROM Sales |
いまさらですがCASE式に関して
一つの値に対してパラメーターだけで分岐させる場合は 下記のように書く。
一般的なプログラミングに近いイメージ。
|
1 2 3 4 |
CASE 値 WHEN パラメーター1 THEN WHEN パラメーター2 THEN ELSE NULL END |
ただしWHENの中には式も書ける
http://skill-up-engineering.com/?p=1963
http://skill-up-engineering.com/?p=1999
のような例。
