コンピュータ■ 差分計算
今回は、按分した結果に差額が出た場合の処理についてです（初回はこちら：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
１件あたりの按分金額が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であるという条件を指定すれば、グループ内の１レコードを判断することができます。
■ しわ寄せ先の決定
ここまでくれば、もう少しです。ここまでの実行結果を、内部Zテーブルとします。
さて、連番が1となっているデータには、内部Bテーブルの按分合計と、比率を掛けた按分金額の合計の差額を加算するだけです。
連番１のデータの指定は、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円を作っている部分です。
差額は、按分合計から集計した按分金額を減算して作ります。この差額作成処理を、連番が１のデータに対してのみ実行させます。
■ 後書き
比率で合計金額を按分するっていう要件は結構あるかと思います。今回のように、分析関数を駆使すれば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テーブル）の１件ずつAテーブルに振り分ける、しかも、可能な限り金額と件数が分かれるようにっていうのが問題です。
仮にSQLで実装できたとしても、それはパズルのような難解なものになる筈です。そのくらいなら、プログラム側でループ処理を作った方が保守性が高まります。
前回：SQL（Oracle）で按分計算（差額も考慮）する方法の確認（中編）
最新の記事：http://harikofu.blog.fc2.com/
投稿記事の一覧：http://harikofu.web.fc2.com/
--- blog end —
スポンサードリンク
0
0
