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;
これをwindow
関数で書き換えてみます。まずは、テーブルのデータと出力結果を考えます。
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
関数を使って、答えは、下記となります。
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のイメージは下記となります。
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;
これをwindow
関数で書き換えてみます。まずは、テーブルのデータと、出力結果を考えます。
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
関数を使って、答えは下記となります。
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のイメージは下記となります。