分析関数の衝撃（中編）

CodeZineに掲載されたSQLを分析関数で記述する 2

2. 前年と年商が同じ年度を求める

　次に前年と年商が同じ年度を求めるSQLです。「相関サブクエリで行と行を比較する」では、以下のSQLが提示されています。

前年と年商が同じ年度を求める　その1：相関サブクエリの利用

SELECT year,sale
  FROM Sales S1
 WHERE sale = (SELECT sale
                 FROM Sales S2
                WHERE S2.year = S1.year - 1)
 ORDER BY year;

　これを分析関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。

　手続き型の言語であれば、

1. yearの昇順にソート
2. yearの最小値からループ開始
3. saleの値を変数に保存
4. 前のsaleの値と等しかったら出力

　といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えた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のイメージ

　後は「sale」と「1年前のsale」が等しいことを、外側のselect文のwhere句で条件としてます。

　DB2では、Lag関数が使えないので、下記のSQLとなります。

分析関数で書き換えたSQL（DB2）

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が提示されています。

累積差を求める　その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;

　これを分析関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。

　手続き型の言語であれば、

1. yearの昇順にソート
2. yearの最小値からループ開始
3. 当年のyearとsaleを出力
4. 変数に保存した前年のyearとsaleがあれば出力
5. yearとsaleの値を変数に保存

　といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えた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のイメージ

　DB2では、Lag関数が使えないので、下記のSQLとなります。

分析関数で書き換えたSQL（DB2）

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;