SQL 分析関数

SQL 解決


SQLの分析関数はかなり便利なので。

プログラム側でループ回してゴリゴリって所をSQL１発で結果を取って来れるので、
工数の短縮になるし、不要なループをコーディングしなくてすむからバグも減るだろう。

分析関数がなくてもサブクエリゴリゴリ書けば出来るようだ。
いずれにせよ、SQL1発でやれることはSQLでやったほうが、パフォーマンスも品質も上がりそう。

標準SQLとのことなので（SQL:2003の「ウインドウの機能」のことだろうか）、今回試したoracle10g、11g（9i以降で対応らしい)の他にもSQLServer、DB2はサポート済とのこと。


適当な例。areaごとに予算があるとして、支出明細の日付が古い明細から予算を割り当てていきたいとする。

detailテーブル（明細）

areacode paydate pay
1 20090501 5000
1 20090510 2000
1 20090630 4000
1 20090701 1000
2 20090529 3000

areaテーブル

areacode budget（予算）
1 7000
2 2000

欲しい結果はこう。

areacode paydate pay wariate
1 20090501 5000 5000
1 20090510 2000 2000
1 20090630 4000 0
1 20090701 1000 0
2 20090529 3000 2000

まあJOINしてデータ取って来て、アプリケーションプログラム側でループ回してーってとこだけど、以下のSQLなら1発。

    SELECT
          areacode
          ,paydate
          ,pay
          ,CASE WHEN budget - amount_pay_pre > pay
                THEN pay
                ELSE
                    (CASE WHEN 0 < budget - amount_pay_pre
                          THEN budget - amount_pay_pre
                          ELSE 0 END
                    )END
           AS wariate
    FROM
    (
        SELECT
              details.areacode
              ,details.paydate
              ,details.pay
          　　,(SUM(details.pay) OVER(PARTITION BY details.areacode  ORDER BY details.paydate ASC
                                       ROWS BETWEEN UNBOUNDED PRECEDING
                                                AND CURRENT ROW) - details.pay
              ) AS amount_pay_pre -- これが『使用済の予算額』
              ,area.budget
        FROM details
        LEFT OUTER JOIN area ON
             details.areacode = area.areacode
    )


『同一areaごとに集計した明細自身までのpay合計額』が分析関数で簡単に求められることがミソだった。それから明細自身のpayを引けば、その明細に辿り着く直前までに使用済の予算額になるから。

あとはその集計額から予算額を減算し、その結果に従ってCASEで分岐。

・減算結果が0かマイナスなら当然割当は0
・減算結果がpay以下なら減算結果をそのまま表示
・それ以外ならpay全額割り当てできたってことでpayを表示

という感じ。