（元）社内SEの徒然なる日記

SQL（Oracle）で按分計算（差額も考慮）する方法の確認（前編）

2016-06-24（Fri）

■　按分計算

あるシステム開発で、支店別の支払金額から按分比率を算出して、その比率に応じて手数料を按分するという要件が出てきました。

こういう計算は、結構面倒な要素が出てくるのでプログラム側で計算した方が良いかもしれませんが、今回はSQL１回で計算してみます。

計算の前提環境です。

１．取引先に対する合計支払金額が期日別に存在する。
２．支払金額は支店別、期日別に確定している。
３．手数料は取引先別期日別の合計金額がある

はい、取引先別期日別に按分するんです。

Aテーブル（PrimaryKey：管理番号、支店、期日）

管理番号  支店  期日    金額
10000     10  2/10  10,000
10000     20  2/10  90,000
20000     10  5/15  50,000

Bテーブル（PrimaryKey：管理番号、期日、連番）

管理番号  期日  連番  手数料
10000    2/10   1    400
10000    2/10   2    200
10000    2/10   3    100
10000    2/10   4    300

管理番号というのは、１支払単位（支払日＆支払先）を表す番号です。

AテーブルとBテーブルの関係は、管理番号と期日で結合すると1:N。なのでBテーブルにデータがある時は、必ずAテーブルにもデータが存在するとします。

■　SQL

まず、結果をどうぞ。

SELECT
        Z.管理番号
       ,Z.支店
       ,Z.期日
       ,Z.按分金額
        +
        CASE WHEN Z.連番 = 1 
               THEN
                  Z.按分合計 - SUM (Z.按分金額)
                           OVER (PARTITION BY
                                    Z.管理番号
                                   ,Z.期日
                                 )
               ELSE 0
         END
         AS 最終按分金額
FROM
(
SELECT
        A.管理番号
       ,A.支店
       ,A.期日
       ,NVL(B.按分合計 ,0) AS 按分合計
       ,ROUND(
              RATIO_TO_REPORT (A.金額) 
                   OVER (PARTITION BY
                            A.管理番号
                           ,A.期日
                         )
              * NVL(B.按分合計, 0), 0) AS 按分金額
       ,ROW_NUMBER ()
                   OVER (PARTITION BY
                            A.管理番号
                           ,A.期日
                         ORDER BY
                            A.金額 DESC
                           ,A.支店 ASC
                         ) AS 連番
  FROM
        A
       ,(  SELECT 
                   管理番号
                  ,期日
                  ,SUM(B.手数料)  AS 按分合計
             FROM 
                  B
           GROUP BY
                   管理番号
                  ,期日
        ) B
 WHERE
        A.管理番号  = B.管理番号(+)
   AND  A.期日     = B.期日(+)
) Z

もちろん、事前に動かして検証したのですが、ブログ用に項目名を変えたり小細工したので、このSQL自身は動かしていません。もしかすると構文エラーとか発生するかもです。

次回は、このSQLの構成要素を一つずつ分解しながら解説してみようと思います。今回は、概要だけ書いときます。

■　簡易解説

まず、Bテーブルを按分単位である管理番号、期日で集計した手数料の合計金額を作ります（按分合計）。これを内部テーブルとします（内部Bテーブル）。そして、内部Bテーブルと、Aテーブルを管理番号、期日で外部結合します。

Oracleの分析関数「RATIO_TO_REPORT」を使用して、Aテーブルの管理番号、期日を集計単位とした合計金額に対する、管理番号、期日、支店の金額の比率を取得します（按分比率）。

取得した按分比率と内部テーブルの按分合計を掛け算して、ROUND関数で小数点以下四捨五入します（按分金額）。

さらに、分析関数「ROW_NUMBER」を使用して、Aテーブルの管理番号、期日を集計単位として、金額の降順、支店の昇順に連番を採番します。

ここまでの結果から、AテーブルのPrimaryKey（管理番号、支店、期日）と、内部Bテーブルの按分合計、取得した按分金額、連番で内部テーブルを作成します（内部Zテーブル）。

内部Bテーブルから取得した按分合計から、管理番号、期日を集計単位として按分金額の合計を減算します（按分差額）。

連番が１のデータに対してのみ、按分金額に按分差額を加算します。連番が１以外のデータは、按分金額をそのまま使います（最終按分金額）。

内部Zテーブルから、AテーブルのPrimaryKey（管理番号、支店、期日）と、取得した最終按分金額を取得します。

■　後書き

実際には、Aテーブルに対して按分した金額を更新するという案件でした。なので、UPDATE文はこうなるのかな？

UPDATE A
   SET 金額 = 金額 -
      (
       SELECT NVL(ZZ.最終按分金額, 0)
         FROM ( 内部Zテーブル ) ZZ
        WHERE ZZ.管理番号 = A.管理番号
          AND ZZ.支店 = A.支店
          AND ZZ.期日 = A.期日
      )