SHOEISHA iD

※旧SEメンバーシップ会員の方は、同じ登録情報(メールアドレス&パスワード)でログインいただけます

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

分析関数の衝撃

PostgreSQLの分析関数の衝撃2
(Lag関数と累計と移動累計)

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

  • X ポスト
  • このエントリーをはてなブックマークに追加

ダウンロード 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のイメージ
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のイメージ
SQLのイメージ

次のページ
4. 累計を求める

この記事は参考になりましたか?

  • X ポスト
  • このエントリーをはてなブックマークに追加
分析関数の衝撃連載記事一覧

もっと読む

この記事の著者

山岸 賢治(ヤマギシ ケンジ)

趣味が競技プログラミングなWebエンジニアで、OracleSQLパズルの運営者。AtCoderの最高レーティングは1204(水色)。

※プロフィールは、執筆時点、または直近の記事の寄稿時点での内容です

この記事は参考になりましたか?

この記事をシェア

  • X ポスト
  • このエントリーをはてなブックマークに追加
CodeZine(コードジン)
https://codezine.jp/article/detail/2693 2009/08/11 14:00

おすすめ

アクセスランキング

アクセスランキング

イベント

CodeZine編集部では、現場で活躍するデベロッパーをスターにするためのカンファレンス「Developers Summit」や、エンジニアの生きざまをブーストするためのイベント「Developers Boost」など、さまざまなカンファレンスを企画・運営しています。

新規会員登録無料のご案内

  • ・全ての過去記事が閲覧できます
  • ・会員限定メルマガを受信できます

メールバックナンバー

アクセスランキング

アクセスランキング