社内SEの徒然なる日記

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

■ 差分計算

今回は、按分した結果に差額が出た場合の処理についてです(初回はこちら:SQL(Oracle)で按分計算(差額も考慮)する方法の確認(前編))。

合計金額を比率に応じて按分するわけですが、按分した結果の合計と、按分の元となった合計金額に差額が出ることがあります。

仮に、3件のデータの値が同じだったとします。そこから比率を算出すると、0.33333333・・・・・・となります。さて、合計金額が1,000、按分金額を小数点以下四捨五入すると、こうなります。

番号 比率      合計金額   按分金額(小数点以下四捨五入)
1 0.333 * 1,000 = 333
2 0.333 * 1,000 = 333
3 0.333 * 1,000 = 333


1件あたりの按分金額が333円。これを合計すると、999円。合計金額が1,000円なので、1円足りません。通常、この手の按分処理の場合は、この1円の差額をどこかにしわ寄せします。

普通のプログラムだと簡単ですが、集合論をベースにしたSQLだと、こう言った処理には工夫が必要です。


サンプルでは、集計するグループの中で金額が最も大きいレコードに差額を加算することにしました。なお、金額が最も大きいレコードが複数ある場合は、支店コードが小さい方のレコードをしわ寄せ先にします。

分析関数「ROW_NUMBER」は、指定したグループ内の、指定した並びで連番を設定する関数です。これを使って、集計単位内で連番を振ってやります。
       ,ROW_NUMBER ()
OVER (PARTITION BY
A.管理番号
,A.期日
ORDER BY
A.金額 DESC
,A.支店 ASC
) AS 連番


PARTITION BY で、連番を採番する集計単位を指定します。今回は、管理番号、期日を集計単位として連番を振ります。さらに、ORDER BY句で金額の降順、支店の昇順に並ぶようにします。

サンプルデータだと、こういう結果になります。
管理番号  支店  期日    金額  連番
10000 10 2/10 10,000 2
10000 20 2/10 90,000 1
20000 10 5/15 50,000 1


これで、連番が1であるという条件を指定すれば、グループ内の1レコードを判断することができます。

■ しわ寄せ先の決定

ここまでくれば、もう少しです。ここまでの実行結果を、内部Zテーブルとします。

さて、連番が1となっているデータには、内部Bテーブルの按分合計と、比率を掛けた按分金額の合計の差額を加算するだけです。

連番1のデータの指定は、CASE句を使えば簡単ですね。問題は分析関数の「SUM」でしょうか。
       ,Z.按分金額
+
CASE WHEN Z.連番 = 1
THEN
Z.按分合計 - SUM (Z.按分金額)
OVER (PARTITION BY
Z.管理番号
,Z.期日
)
ELSE 0
END
AS 最終按分金額


SUM関数も、指定した集計単位で合計を取得することが可能です。まず、SUM (Z.按分金額) で按分金額の合計を計算するように指定しています。

さらに、PARTITION BY Z.管理番号, Z.期日とすることで、管理番号、期日を集計単位とした按分金額の合計を計算しています。最初のサンプルだと、999円を作っている部分です。

差額は、按分合計から集計した按分金額を減算して作ります。この差額作成処理を、連番が1のデータに対してのみ実行させます。

■ 後書き

比率で合計金額を按分するっていう要件は結構あるかと思います。今回のように、分析関数を駆使すればSQLだけでも作れますが、今後の仕様変更を考えると、プログラム側で対応した方がコストは低く済む気がします。

今回のサンプルの要件だと、Aテーブルに按分金額を設定するという要件ですが、これにBテーブルの各レコードに按分先の支店を更新するという要望が発生すると、すべてが崩れ去ります。

仮に、金額の小さい順に、支店に手数料を振り分ける。金額だけでなく、件数も振り分けられるようにする。という要件だと、こうなります。
管理番号  支店  期日    金額   手数料  手数料設定件数
10000 10 2/10 10,000 400 2
10000 20 2/10 90,000 600 2
20000 10 5/15 50,000 0 0


Bテーブル(PrimaryKey:管理番号、期日、連番)
管理番号  期日  連番  手数料  支店
10000 2/10 1 400 20
10000 2/10 2 200 20
10000 2/10 3 100 10
10000 2/10 4 300 10


合計金額を按分するんじゃなくて、各明細(Bテーブル)の1件ずつAテーブルに振り分ける、しかも、可能な限り金額と件数が分かれるようにっていうのが問題です。

仮にSQLで実装できたとしても、それはパズルのような難解なものになる筈です。そのくらいなら、プログラム側でループ処理を作った方が保守性が高まります。

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

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

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

PageTop

コメント


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