分析関数の衝撃

PostgreSQLの分析関数の衝撃2
（Lag関数と累計と移動累計）

PostgreSQLの基本的なwindow関数の使用例2

山岸賢治[著]

2009/08/11 14:00

ポスト

ダウンロード SourceCode (2.2 KB)

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のイメージ