社内SEの徒然なる日記

SQL(Oracle)で按分計算(差額も考慮)する方法の確認(中編)

■ 詳細説明

前回(SQL(Oracle)で按分計算(差額も考慮)する方法の確認(前編))書いたSQLを分解しながら、各工程を解説してみます。ってことで、前回と見比べないと何が何やらわかりませんので、画面を並べて見て下さい。

なお、解説は前回の「■ 簡易解説」の項の順番にしていきます。

■ 内部Bテーブル

       ,(  SELECT 
管理番号
,期日
,SUM(B.手数料) AS 按分合計
FROM
B
GROUP BY
管理番号
,期日
) B


按分する元となる合計金額を取得するための内部テーブルです。今回は、管理番号、期日で集計した手数料の金額が欲しいので、集計条件に管理番号と期日を指定しています。

内部Bテーブルの実行結果は、こんな感じです。
管理番号  期日  手数料
10000 2/10 1,000



Oracleの分析関数「RATIO_TO_REPORT」は、合計金額に対する比率を求めてくれる分析関数です。比率計算って意外と面倒なので、こういう機能はありがたいです。
       ,ROUND(
RATIO_TO_REPORT (A.金額)
OVER (PARTITION BY
A.管理番号
,A.期日
)
* NVL(B.按分合計, 0), 0) AS 按分金額


分析関数の特徴は、PARTITION BY で集計単位を区切ることが出来ることです。

サンプルSQLでは管理番号、期日を集計単位として金額を合計。その合計に対する、各レコードの比率を計算してくれます。

SQLでは、こうやって算出した比率に内部Bテーブルの按分合計を掛けて按分金額を計算しています。

ここまでの実行イメージは、こんな感じです(テストデータは前回参照)。
管理番号  支店  期日    金額   比率    按分合計   按分金額
10000 10 2/10 10,000 0.1 * 1,000 = 100
10000 20 2/10 90,000 0.9 * 1,000 = 900
20000 10 5/15 50,000 1 * 0 = 0


■ 後書き

最初に画面を並べてって書きましたが、それってパソコン前提の話ですよね。スマホやタブレットだと、画面上の複数の画面が表示されないので、こういう時は不便に感じます。

まぁ、でもアレか。SQLを組み立てるのにパソコンを使わないってことは無いだろうから、別に気にしなくて良いのかな。

前回:SQL(Oracle)で按分計算(差額も考慮)する方法の確認(前編)
次回:

最新の記事:http://harikofu.blog.fc2.com/
投稿記事の一覧:http://harikofu.web.fc2.com/

--- blog end — スポンサードリンク

PageTop

コメント


管理者にだけ表示を許可する