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;
これをwindow関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。
Sales
| year | sale |
| 1990 | 50 |
| 1991 | 51 |
| 1992 | 52 |
| 1993 | 52 |
| 1994 | 50 |
| 1995 | 50 |
| 1996 | 49 |
| 1997 | 55 |
出力結果
| year | sale |
| 1993 | 52 |
| 1995 | 50 |
Lag関数を使って、答えは、下記となります。
window関数で書き換えたSQL
select year,sale
from (select year,sale,Lag(sale) over(order by year) as LagSale
from Sales) a
where sale= LagSale
order by year;
Lag関数で、yearの昇順にソートした1行前のレコードの値を取得してます。yearが連続している（歯抜けがない）ので、yearの昇順にソートした1行前のレコードは、1年前のレコードとなります。
後は、Saleと1年前のSaleが等しいことを、外側のselect文のwhere句で条件としてます。SQLのイメージは下記となります。
SQLのイメージ
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;
これをwindow関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
Sales
| year | sale |
| 1990 | 50 |
| 1992 | 50 |
| 1993 | 52 |
| 1994 | 55 |
| 1997 | 55 |
出力結果
| pre_year | now_year | pre_sale | now_sale | diff |
| null | 1990 | null | 50 | null |
| 1990 | 1992 | 50 | 50 | 0 |
| 1992 | 1993 | 50 | 52 | 2 |
| 1993 | 1994 | 52 | 55 | 3 |
| 1994 | 1997 | 55 | 55 | 0 |
Lag関数を使って、答えは下記となります。
window関数で書き換えた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のイメージ
