按分時に端数を寄せるSQL

仕訳の配賦処理や消費税の按分計算で端数を伝票の特定の行に寄せたい場合がある。しばしば、この手の計算はPL/SQLなどを使って手続き型で実装されることが多いが、ウィンドウ関数を使えば簡単に実装できる。

基本的な考え方はこうだ。

総金額に対する各明細への按分割合を算出し、明細ごとの仮金額を算出する
総金額と明細ごとの仮金額合計の差を算出し端数を計算する
ROW_NUMBER() 関数を使い優先順位を決め、端数を割り当てる

例えば、全社の販売管理費を部署の売上金額で按分する場合を考える*1。

簡単のため、部署別科目別残高試算表テーブルがあると仮定し、全社販売管理費を計算する。

SELECT
    NVL(SUM(BKZ.貸方金額 - BKZ.借方金額), 0) AS 全社販売管理費
FROM
    部署別科目別残高 BKZ
WHERE
    BKZ.年月 = '201001'
    AND BKZ.勘定科目コード = '50' -- 販売管理費

次に部署ごとの売上高割合を求める（ここでは、割合の計算に RATIO_TO_REPORT を使っているが、SUM関数で計算しても良い。ただし、その場合はゼロ除算に注意すること）。

SELECT
    BKZ.部署コード,
    RATIO_TO_REPORT(NVL(SUM(BKZ.借方金額 - BKZ.貸方金額), 0)) AS 配賦割合
FROM
    部署別科目別残高 BKZ
WHERE
    BKZ.年月 = '201001'
    AND BKZ.勘定科目コード = '10' -- 売上高
GROUP BY
    BKZ.部署コード

そして、按分する（端数は売上の一番大きい部署から順に1円ずつ割り当てると仮定）。

WITH
    全社販売管理費 AS (...),
    部署別売上高割合 AS (...)
SELECT
    B.部署コード,
    B.部署別販売管理費仮 + (CASE
        WHEN 優先順位 <= (全社販売管理費 - 全社販売管理費仮) THEN 1
        ELSE 0
    END) AS 部署別販売管理費
FROM
    (
        SELECT
            B.部署コード,
            TRUNC(A.販売管理費 * B.配賦割合) AS 部署別販売管理費仮,
            SUM(TRUNC(A.販売管理費 * B.配賦割合)) OVER () AS 全社販売管理費仮,
            A.販売管理費 AS 全社販売管理費
            ROW_NUMBER() OVER (PARTITION BY B.部署コード ORDER BY B.配賦割合 DESC) AS 優先順位
        FROM
            全社販売管理費 A
            CROSS JOIN 部署別売上高割合 B
    )

*1:なお、これが配賦基準として適切かは別問題。売上金額で按分すると売上の高い部署ほど費用が上乗せされインセンティブを阻害する恐れがある。