2. 前年と年商が同じ年度を求める
次に前年と年商が同じ年度を求めるSQLです。「相関サブクエリで行と行を比較する」では、以下のSQLが提示されています。
SELECT year,sale FROM Sales S1 WHERE sale = (SELECT sale FROM Sales S2 WHERE S2.year = S1.year - 1) ORDER BY year;
これを分析関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
![](http://cz-cdn.shoeisha.jp/static/images/article/1298/1298_02.gif)
手続き型の言語であれば、
- yearの昇順にソート
- yearの最小値からループ開始
- saleの値を変数に保存
- 前のsaleの値と等しかったら出力
といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。
select year,sale from (select year,sale, Lag(sale) over(order by year) as LagSale from Sales) where sale= LagSale order by year;
Lag
関数で、「year」の昇順にソートした1行前のレコードの値を取得してます。「year」が連続している(歯抜けがない)ので、「year」の昇順にソートした1行前のレコードは、1年前のレコードとなります。
![SQLのイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/1298/1298_03.gif)
後は「sale」と「1年前のsale」が等しいことを、外側のselect
文のwhere
句で条件としてます。
DB2では、Lag
関数が使えないので、下記のSQLとなります。
select year,sale from (select year,sale, max(sale) over(order by year Rows between 1 Preceding and 1 Preceding) as LagSale from Sales) dummy where sale= LagSale order by year;
3. 累積差を求める
次に累積差を求めるSQLです。「相関サブクエリで行と行を比較する」では、以下のSQLが提示されています。
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;
これを分析関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。
![](http://cz-cdn.shoeisha.jp/static/images/article/1298/1298_04.gif)
手続き型の言語であれば、
- yearの昇順にソート
- yearの最小値からループ開始
- 当年のyearとsaleを出力
- 変数に保存した前年のyearとsaleがあれば出力
- yearとsaleの値を変数に保存
といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。
select Lag(year) over(order by year) as pre_year, year as now_year, Lag(sale) over(order by year) as pre_sale, sale as now_sale, sale - Lag(sale) over(order by year) as diff from Sales2 order by now_year;
前問と同様に、Lag
関数で、「year」の昇順にソートした1行前のレコードの値を取得してます。
![SQLのイメージ SQLのイメージ](http://cz-cdn.shoeisha.jp/static/images/article/1298/1298_05.gif)
DB2では、Lag
関数が使えないので、下記のSQLとなります。
select max(year) over(order by year Rows between 1 Preceding and 1 Preceding) as pre_year, year as now_year, max(sale) over(order by year Rows between 1 Preceding and 1 Preceding) as pre_sale, sale as now_sale, sale - max(sale) over(order by year Rows between 1 Preceding and 1 Preceding) as diff from Sales2 order by now_year;