分析関数の衝撃

分析関数の衝撃（中編）

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

山岸賢治[著]

更新日: 2007/09/04
公開日: 2007/07/19

ポスト

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

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

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

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

　といった処理を行うと思いますが、分析関数を使った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のイメージ

　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;

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

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

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

　といった処理を行うと思いますが、分析関数を使った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のイメージ

　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）