CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃(中編)

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

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

ダウンロード ソースコード (4.2 KB)

目次

4. 累計を求める

 次に累計を求めるSQLです。「相関サブクエリで行と行を比較する」では、以下のSQLが提示されています。

累計を求める:ノイマン型再帰集合の利用
SELECT prc_date, A1.prc_amt,
      (SELECT SUM(prc_amt)
         FROM Accounts A2
        WHERE A1.prc_date >= A2.prc_date ) AS onhand_amt
  FROM Accounts A1
 ORDER BY prc_date;

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

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

  1. prc_dateの昇順にソート
  2. prc_dateの最小値からループ開始
  3. prc_amtを変数に足しこむ
  4. データ出力

 といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えたSQLその1
select prc_date,prc_amt,
sum(prc_amt) over(order by prc_date
                   Rows between unbounded preceding
                    and current row) as onhand_amt
  from Accounts
 order by prc_date;

 分析関数のsum関数でwindowing_clauseという句を使用してます。下記のようにwindowing_clauseを解釈すると分かりやすいと思います。

order by prc_date   --prc_dateの昇順で、
 Rows between       --行の範囲は、
unbounded preceding --小さいほうは、際限なし
  and current row   --大きいほうは、現在の行まで
SQLのイメージ
SQLのイメージ

 windowing_clauseを完全に省略した場合は、デフォルトである、

Range between unbounded preceding and current row

 となります。これは、

 Rows between unbounded preceding and current row

 と同じ意味なので、以下のSQLでもいいです。

分析関数で書き換えたSQLその2
select prc_date,prc_amt,
sum(prc_amt) over(order by prc_date) as onhand_amt
  from Accounts
order by prc_date;

5. 移動累計を求める

 次に移動累計を求めるSQLです。「相関サブクエリで行と行を比較する」では、以下のSQLが提示されています。

移動累計を求める その2:3行に満たない期間は無効扱い
SELECT prc_date, A1.prc_amt,
 (SELECT SUM(prc_amt)
    FROM Accounts A2
   WHERE A1.prc_date >= A2.prc_date
     AND (SELECT COUNT(*)
            FROM Accounts A3
           WHERE A3.prc_date
             BETWEEN A2.prc_date AND A1.prc_date  ) <= 3
   HAVING  COUNT(*) =3) AS mvg_sum  --3行未満は非表示
  FROM  Accounts A1
 ORDER BY prc_date;

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

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

  1. prc_dateの昇順にソート
  2. prc_dateの最小値からループ開始
  3. prc_amtを配列に保存
  4. データ出力

 といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

分析関数で書き換えたSQL
select prc_date,prc_amt,
case when count(*) over(order by prc_date) >= 3
     then sum(prc_amt) over(order by prc_date rows 3-1 Preceding)
     end as mvg_sum
  from Accounts

 補足しますと、検索case式のwhen句で、「prc_date」の昇順にソートして何番目かを求めてます。(下記)

count(*) over(order by prc_date)

 その結果が3番目以降だったら、then句で3行の累計を求めてます。(下記)

sum(prc_amt) over(order by prc_date rows 3-1 Preceding)
SQLのイメージ
SQLのイメージ

 DB2では、Precedingで計算式が使えないので、検索case式のthen句は、下記(1)となります。(2)の方が分かりやすいと思うのですが、仕方ありません。

sum(prc_amt) over(order by prc_date rows   2 Preceding  --(1)
sum(prc_amt) over(order by prc_date rows 3-1 Preceding  --(2)
分析関数で書き換えたSQL(DB2)
select prc_date,prc_amt,
case when count(*) over(order by prc_date) >= 3
     then sum(prc_amt) over(order by prc_date rows 2 Preceding)
     end as mvg_sum
  from Accounts

 なお、(3)のSQLは(4)のSQLと同じ意味です。

sum(prc_amt) over(order by prc_date rows 2 Preceding)  --(3)

sum(prc_amt) over(order by prc_date rows 
                  between 2 Preceding and current row) --(4)

最後に

 分析関数の衝撃(前編)に引き続いて、今回はpartition byRowsを使った分析関数を挙げました。Rowsの仕様は、Oracleの公式マニュアルを読むと理解しやすいと思います。

 分析関数の衝撃(後編)では、もう少し難しいSQLと、partition byRowsに続いてRangeを扱います。

参考資料

  1. CodeZine 『自己結合の使い方
  2. こちらで記載されたSQLを題材とさせていただきました。
  1. CodeZine 『相関サブクエリで行と行を比較する
  2. こちらで記載されたSQLを題材とさせていただきました。
  1. OTN Japan 『Oracle8i データウェアハウス
  2. Oracleの分析関数についての公式資料です。分析関数を使うことによる、可読性とパフォーマンスの向上例が紹介されてます。
  1. OTN Japan 『分析ファンクション
  2. 分析関数のOracleの公式マニュアルです。(日本語)
  1. US-OTN 『Analytic Functions
  2. 分析関数のOracleの公式マニュアルです。(英語)
  1. IBM 『SQL Reference Vol.1
  2. DB2のSQLの公式マニュアルです。
  1. @IT 『SQLクリニック(9) SQL分析関数をさらに深く追求してみよう
  2. 分析関数のwindowing_clauseについて、分かりやすく解説されてます。
  1. OracleSQLパズル 『累計を取得
  2. 本稿で挙げた累計を取得するSQLと別解を置いてます。
  1. OracleSQLパズル 『移動平均を求める
  2. 分析関数を使って移動平均を取得するSQLと別解を置いてます。
  1. OracleSQLパズル 『正順位と逆順位
  2. 本稿で挙げた、DB2でdistinctオプション付きの分析関数のcount関数を、逆ソートを使って代用する考え方を置いてます。
  1. DB2 SQLパズル 『OracleのLead関数とLag関数を模倣
  2. 本稿で挙げた、DB2でOracleのLag関数を模倣するSQLを置いてます。


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

バックナンバー

連載:分析関数の衝撃

もっと読む

著者プロフィール

あなたにオススメ

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