CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃(中編)

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2007/07/19 14:00

ダウンロード ソースコード (4.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;

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

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

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

  • LINEで送る
  • このエントリーをはてなブックマークに追加

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

All contents copyright © 2005-2021 Shoeisha Co., Ltd. All rights reserved. ver.1.5