LoginSignup
5
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@KAZAMAI_NaruTo(NaruTo KAZAMAI)

按分の話と SQL

Last updated at Posted at 2019-09-14

「按分(案分)」とは

辞書の説明
基準となる数量に比例して物を分けること。

弊社でこれに基づいて担当者が按分処理を実装しかけるという事案が発生した。
担当者談：「按分すると誤差で合計が合わなくなることがあるよね」

簿記に関連することや金額を扱う分野・ビジネスで「按分」と言った場合は少し異なる意味で用いる。

「按分(案分)」とは(実際のビジネス上での用法)

簿記会計・その他ビジネス上での意味
基準となる数量の比率に準じて整数値で(あるいは特定の単位で)分けること。
必ず分けた値の合計が元の値と一致しなくてはならない

「分けた値の合計が元の値と一致する」ことを保証することが、全体での一貫性を保つために極めて重要である。

按分の例

1014 を 4:6:7 で按分する
(1014 * 4 ) / (4 + 6 + 7) = 238.5882352...
(1014 * 6 ) / (4 + 6 + 7) = 357.8823529...
(1014 * 7 ) / (4 + 6 + 7) = 417.5882357...

4 6 7 合計
239 358 417 1014 ○
238 358 418 1014 ○
239 358 417 1014 ○
240 357 417 1014 △
238 359 417 1014 △
238 357 419 1014 △
0 0 1014 1014 ▲
四捨五入 239 358 418 1015 ×
切り捨て 238 357 417 1012 ×
小数 238.588 357.882 417.529 1013.999 ×

○ 按分として妥当
△ 許容されることがある
▲ 場合によっては許容される場合があるかもしれない
× だめ

妥当な按分に関する考察

ある数 S を n 個に按分する場合、そのすべてで按分した値と対応する比例分配した値との差を 1 より小さくすることができる。

$ n\in\mathbb{N}$ とし、
実数の組 $p_1,\ldots,p_n\in\mathbb{R}$ が
任意の $ i\in\{ i | 1\le i \le n, i\in\mathbb{N} \}$ に対して が $0\le p_i \le 1$
かつ $\sum_{i=1}^{n}p_i = 1$　を満たすとする。

このとき、任意の整数 $S \in\mathbb{N}$ に対して
ある自然数の組 $ s_1,\ldots s_n\in\mathbb{N}$が存在し、
$\sum_{i=1}^{n}s_i = S $ かつ
任意の $ i\in \{i|1\le i \le n, i\in\mathbb{N} \} $ に対して $ |S\times p_i\ - s_i|\lt 1 $を 満たす。

これは次のように拡張される

$ n\in\mathbb{N}$ とし、
$p_1,\ldots,p_n\in\mathbb{R}$ が $\sum_{i=1}^{n}p_i = 1$　を満たすとする。

このとき、任意の整数 $S \in\mathbb{Z}$ に対してある整数の組 $ s_1,\ldots s_n\in\mathbb{Z}$が存在し、
$\sum_{i=1}^{n}s_i = S $
かつ任意の $ i\in \{i|1\le i \le n, i\in\mathbb{N} \} $ に対して $ |S\times p_i\ - s_i|\lt 1 $を 満たす。

証明は省略するが次のようにして求められる。

  1. 各$i$ に対して $b_i$ = floor(S * $p_i$) を求める。1
  1. S * $p_i$ ＞ $b_i$ を満たす $i$ の個数を k とする。
    　このとき 0≦k≦n であり、$b_i$ = S * $p_i$ となる $i$ の数は n-k個
  2. r = S - sum($b_i$) を求める。このとき、0 ≦r < k となる。
  3. S * $p_i$ ＞ $b_i$ のうち、r個に対して $s_i$ = $b_i$ +1 、その他を $s_i$ = $b_i$ とすれば
    $s_i$ はすべて整数であり、abs(S * $p_i$ - $s_i$) < 1 で sum($s_i$) = S が成立する。

正の数と負の数の扱いを分けて次のようにする方法もある。

  1. 各$i$ に対して $b_i$ = floor(abs(S * $p_i$)) * sign(S * $p_i$) を求める。12
  1. S * $p_i$ ＞ $b_i$ (＞ 0) を満たす $i$ の個数を k(>=0) とする。
    S * $p_i$ ＜ $b_i$ (＜ 0) を満たす $i$ の個数を l(>=0) とする。
    　このとき 0≦k,l≦n であり、$b_i$ = S * $p_i$ となる $i$ の数は n-k-l個
  2. r = S - sum($b_i$) を求める。このとき (-1) * l < r < k となる。
  3. r > 0 のときは S * $p_i$ ＞ $b_i$（＞0) のうち、r個に対して $s_i$ = $b_i$ +1 、その他を $s_i$ = $b_i$ とすれば
    $s_i$ はすべて整数であり、abs(S * $p_i$ - $s_i$) < 1 で sum($s_i$) = S が成立する。
    r < 0 のときは S * $p_i$ ＜ $b_i$ (<０) のうち、(-1) * r個に対して $s_i$ = $b_i$ - 1 、その他を $s_i$ = $b_i$ とすれば
    $s_i$ はすべて整数であり、abs(S * $p_i$ - $s_i$) < 1 で sum($s_i$) = S が成立する。
    r=0 のときは $s_i$ = $b_i$ とすれば abs(S * $p_i$ - $s_i$) < 1 で sum($s_i$) = S が成立する。

SQLでの按分の例1 (SQL Server 2017)

次のような加重を持つテーブルを用意する。

if object_id('weight') IS NOT NULL BEGIN
	DROP TABLE weight;
END
SELECT
	*
	INTO WEIGHT
FROM
	(VALUES(1,4),(2,6),(3,7))
	AS W(id,weight);

SELECT * FROM weight;

そして按分するべき値を変数として用意する。

DECLARE @amount int = 1014

この値の荷重に応じた按分値は例えば次のような SQL で求められる。

SELECT
    id, weight
    , RAW_DIVISION_VALUE
    , PROVISIONAL_DIVISION_VALUE_1
            + IIF(ROW_NUMBER() OVER (ORDER BY DIVIDABLE_1, id) <= TOTAL_DIFF_1,1,0)
        AS PROPORTIONAL_DIVISION_VALUE_1_1
    , PROVISIONAL_DIVISION_VALUE_1
            + IIF(ROW_NUMBER() OVER (ORDER BY DIVIDABLE_1, DIFF1 DESC, id) <= TOTAL_DIFF_1,1,0)
        AS PROPORTIONAL_DIVISION_VALUE_1_2
    , PROVISIONAL_DIVISION_VALUE_1
            + IIF(ROW_NUMBER() OVER (ORDER BY DIVIDABLE_1, NEWID(), id) <= TOTAL_DIFF_1,1,0)
        AS PROPORTIONAL_DIVISION_VALUE_1_3
FROM
    (
        SELECT
            id, weight
            , RAW_DIVISION_VALUE
            , PROVISIONAL_DIVISION_VALUE_1
            , IIF(PROVISIONAL_DIVISION_VALUE_1 * DENOMINATOR = NUMERATOR1, 1, 0) AS DIVIDABLE_1
            , NUMERATOR1 - PROVISIONAL_DIVISION_VALUE_1 * DENOMINATOR AS DIFF1
            , @amount - SUM(PROVISIONAL_DIVISION_VALUE_1) OVER () AS TOTAL_DIFF_1
        FROM
            (
                SELECT
                    id, weight
                    , NUMERATOR1, DENOMINATOR
                    , FLOOR(NUMERATOR1 / DENOMINATOR) AS PROVISIONAL_DIVISION_VALUE_1
                    , NUMERATOR1 / DENOMINATOR AS RAW_DIVISION_VALUE
                FROM
                    (
                        SELECT
                            id
                            , weight
                            , CAST(@amount AS DECIMAL) * weight AS NUMERATOR1
                            , SUM(weight) OVER () AS DENOMINATOR
                        FROM
                            weight
                    ) T1
            ) T2
    ) T3
ORDER BY ID
;

結果：

| ID | WEIGHT | RAW_DIVISION_VALUE | PROPORTIONAL_DIVISION_VALUE_1 | PROPORTIONAL_DIVISION_VALUE_2 | PROPORTIONAL_DIVISION_VALUE_3 |
|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|:-:|
| 1 | 4 | 238.588 | 239 | 239 | 239 |
| 2 | 6 | 357.882 | 358 | 358 | 358 |
| 3 | 7 | 417.529 | 417 | 417 | 417 |

※ PROPORTIONAL_DIVISION_VALUE_3 の結果は一定ではない。

まず

SELECT
    id, weight
    , NUMERATOR1, DENOMINATOR
    , FLOOR(NUMERATOR1 / DENOMINATOR) AS PROVISIONAL_DIVISION_VALUE_1
    , NUMERATOR1 / DENOMINATOR AS RAW_DIVISION_VALUE
FROM
    (
        SELECT
            id
            , weight
            , CAST(@amount AS DECIMAL) * weight AS NUMERATOR1
            , SUM(weight) OVER () AS DENOMINATOR
        FROM
            weight
    ) T1

誤差を抑えるために予め分子(NUMERATOR1) と分母(DENOMINATOR) をそれぞれ求めている。
その際、分子の方は桁あふれの可能性があるので掛ける前に一部を DECIMALにキャストすることにより分子を DECIMAL にしている。
そのうえで 「分子 / 分母」を計算し、その floor の値を求めて仮按分値 PROVISIONAL_DIVISION_VALUE_1 としている。
また、後の比較用に 丸めない「分子 / 分母」も求めて RAW_DIVISION_VALUE としている。
分母にあたる weight の合計にはウィンドウ関数としての SUM を用いている


SELECT
    id, weight
    , RAW_DIVISION_VALUE
    , PROVISIONAL_DIVISION_VALUE_1
    , IIF(PROVISIONAL_DIVISION_VALUE_1 * DENOMINATOR = NUMERATOR1, 1, 0) AS DIVIDABLE_1
    , NUMERATOR1 - PROVISIONAL_DIVISION_VALUE_1 * DENOMINATOR AS DIFF1
    , @amount - SUM(PROVISIONAL_DIVISION_VALUE_1) OVER () AS TOTAL_DIFF_1

DIVIDABLE_1 は「分子 / 分母」が割り切れる場合は 1、割り切れない場合は 0 を返す。
DIFF1 は「分子」と「仮按分値×分母」との差を表す。「分子 / 分母」と「仮按分値」の差の各辺に分母をかけたもの。差の大小の比較のためにだけもちいるので小数演算を避けるためにこうしている。
TOTAL_DIFF_1 は「分子 / 分母」の端数切捨てた値の合計と元の値との差を求めている。ここでもウィンドウ関数としての SUM を用いている.

SELECT
    id, weight
    , RAW_DIVISION_VALUE
    , PROVISIONAL_DIVISION_VALUE_1
            + IIF(ROW_NUMBER() OVER (ORDER BY DIVIDABLE_1, id) <= TOTAL_DIFF_1,1,0)
        AS PROPORTIONAL_DIVISION_VALUE_1_1
    , PROVISIONAL_DIVISION_VALUE_1
            + IIF(ROW_NUMBER() OVER (ORDER BY DIVIDABLE_1, DIFF1 DESC, id) <= TOTAL_DIFF_1,1,0)
        AS PROPORTIONAL_DIVISION_VALUE_1_2
    , PROVISIONAL_DIVISION_VALUE_1
            + IIF(ROW_NUMBER() OVER (ORDER BY DIVIDABLE_1, NEWID(), id) <= TOTAL_DIFF_1,1,0)
        AS PROPORTIONAL_DIVISION_VALUE_1_3

RAW_DIVISION_VALUE は「分子 / 分母」の小数としての値を求めている。
PROPORTIONAL_DIVISION_VALUE_1_1 は端数の割り振りを id順で行ったもの、
PROPORTIONAL_DIVISION_VALUE_1_2 は端数の割り振りを DIFF1 の大きなもの順、
PROPORTIONAL_DIVISION_VALUE_1_3 は端数の割り振りを ランダムにおこなったもの(ゆえに結果は一定ではない)
いずれも場合も割り切れるものには割り振らないようにしている。

SQLでの按分の例2 (SQL Server 2017)

正の数と負の数を分けて考える場合の按分方法に基づくと次のようになる。

SELECT
    id, weight
    , RAW_DIVISION_VALUE
    , PROVISIONAL_DIVISION_VALUE_2
                + IIF(ROW_NUMBER() OVER (
                                ORDER BY
                                    DIVIDABLE_2
                                    , sign(TOTAL_DIFF_2) * sign(PROVISIONAL_DIVISION_VALUE_2) DESC
                                    , id)
                            <= ABS(TOTAL_DIFF_2)
                        , SIGN(TOTAL_DIFF_2), 0)
        AS PROPORTIONAL_DIVISION_VALUE_2_1
    , PROVISIONAL_DIVISION_VALUE_2
                + IIF(ROW_NUMBER() OVER (
                                ORDER BY
                                    DIVIDABLE_2
                                    , sign(TOTAL_DIFF_2) * sign(PROVISIONAL_DIVISION_VALUE_2) DESC
                                    , DIFF2 DESC
                                    , id)
                            <= ABS(TOTAL_DIFF_2)
                        , SIGN(TOTAL_DIFF_2), 0)
        AS PROPORTIONAL_DIVISION_VALUE_2_2
    , PROVISIONAL_DIVISION_VALUE_2
                + IIF(ROW_NUMBER() OVER (
                                ORDER BY
                                    DIVIDABLE_2
                                    , sign(TOTAL_DIFF_2) * sign(PROVISIONAL_DIVISION_VALUE_2) DESC
                                    , NEWID()
                                    , id)
                            <= ABS(TOTAL_DIFF_2)
                        , SIGN(TOTAL_DIFF_2), 0)
        AS PROPORTIONAL_DIVISION_VALUE_2_3
FROM
    (
        SELECT
            id, weight
            , RAW_DIVISION_VALUE
            , PROVISIONAL_DIVISION_VALUE_2
            , IIF(PROVISIONAL_DIVISION_VALUE_2 * DENOMINATOR = NUMERATOR2, 1, 0) AS DIVIDABLE_2
            , ABS(NUMERATOR2 - PROVISIONAL_DIVISION_VALUE_2 * DENOMINATOR) AS DIFF2
            , @amount - SUM(PROVISIONAL_DIVISION_VALUE_2) OVER () AS TOTAL_DIFF_2
        FROM
            (
                SELECT
                    id, weight
                    , NUMERATOR2, DENOMINATOR
                    , NUMERATOR2 / DENOMINATOR AS PROVISIONAL_DIVISION_VALUE_2
                    , CAST(NUMERATOR2 AS DECIMAL) / DENOMINATOR AS RAW_DIVISION_VALUE
                FROM
                    (
                        SELECT
                            id
                            , weight
                            , CAST(@amount AS BIGINT) * weight AS NUMERATOR2
                            , SUM(weight) OVER () AS DENOMINATOR
                        FROM
                            weight
                    ) T1
            ) T2
    ) T3
ORDER BY ID
;

まず

SELECT
    id, weight
    , NUMERATOR2, DENOMINATOR
    , NUMERATOR2 / DENOMINATOR AS PROVISIONAL_DIVISION_VALUE_2
    , CAST(NUMERATOR2 AS DECIMAL) / DENOMINATOR AS RAW_DIVISION_VALUE
FROM
    (
        SELECT
            id
            , weight
            , CAST(@amount AS BIGINT) * weight AS NUMERATOR2
            , SUM(weight) OVER () AS DENOMINATOR
        FROM
            weight
    ) T1

誤差を抑えるために予め分子(NUMERATOR2) と分母(DENOMINATOR) をそれぞれ求めている。
その際、分子の方は桁あふれの可能性があるので掛ける前に一部を BIGINTにキャストすることにより分子を BIGINT にしている。
そのうえで 「分子 / 分母」を計算し、仮按分値 PROVISIONAL_DIVISION_VALUE_2 としている。
BIGINT の割り算は端数切捨て(負の数に対してはゼロ寄せ)で結果は BIGINT となる。
また、後の比較用に 丸めない「分子 / 分母」も求めて RAW_DIVISION_VALUE としている。
この計算では分子を DECIMAL にキャストして小数の結果を返すようにしている。
分母にあたる weight の合計にはウィンドウ関数としての SUM を用いている

SELECT
    id, weight
    , RAW_DIVISION_VALUE
    , PROVISIONAL_DIVISION_VALUE_2
    , IIF(PROVISIONAL_DIVISION_VALUE_2 * DENOMINATOR = NUMERATOR2, 1, 0) AS DIVIDABLE_2
    , ABS(NUMERATOR2 - PROVISIONAL_DIVISION_VALUE_2 * DENOMINATOR) AS DIFF2
    , @amount - SUM(PROVISIONAL_DIVISION_VALUE_2) OVER () AS TOTAL_DIFF_2

DIVIDABLE_2 は「分子 / 分母」が割り切れる場合は 1、割り切れない場合は 0 を返す。
DIFF2 は「分子」と「仮按分値×分母」との差の絶対値を表す。「分子 / 分母」と「仮按分値」の差の絶対値の各辺に分母をかけたもの。差の大小の比較のためにだけもちいるので小数演算を避けるためにこうしている。
TOTAL_DIFF_2 は「分子 / 分母」の端数切捨てた値の合計と元の値との差を求めている。ここでもウィンドウ関数としての SUM を用いている.

SELECT
    id, weight
    , RAW_DIVISION_VALUE
    , PROVISIONAL_DIVISION_VALUE_2
                + IIF(ROW_NUMBER() OVER (
                                ORDER BY
                                    DIVIDABLE_2
                                    , sign(TOTAL_DIFF_2) * sign(PROVISIONAL_DIVISION_VALUE_2) DESC
                                    , id)
                            <= ABS(TOTAL_DIFF_2)
                        , SIGN(TOTAL_DIFF_2), 0)
        AS PROPORTIONAL_DIVISION_VALUE_2_1
    , PROVISIONAL_DIVISION_VALUE_2
                + IIF(ROW_NUMBER() OVER (
                                ORDER BY
                                    DIVIDABLE_2
                                    , sign(TOTAL_DIFF_2) * sign(PROVISIONAL_DIVISION_VALUE_2) DESC
                                    , DIFF2 DESC
                                    , id)
                            <= ABS(TOTAL_DIFF_2)
                        , SIGN(TOTAL_DIFF_2), 0)
        AS PROPORTIONAL_DIVISION_VALUE_2_2
    , PROVISIONAL_DIVISION_VALUE_2
                + IIF(ROW_NUMBER() OVER (
                                ORDER BY
                                    DIVIDABLE_2
                                    , sign(TOTAL_DIFF_2) * sign(PROVISIONAL_DIVISION_VALUE_2) DESC
                                    , NEWID()
                                    , id)
                            <= ABS(TOTAL_DIFF_2)
                        , SIGN(TOTAL_DIFF_2), 0)
        AS PROPORTIONAL_DIVISION_VALUE_2_3

RAW_DIVISION_VALUE は「分子 / 分母」の小数としての値を求めている。
TOTAL_DIFF_2 が正ならば 1 を、負ならば -1 を
PROVISIONAL_DIVISION_VALUE_2 の符号と TOTAL_DIFF_2 の符号が一致するものに割り振る。
PROPORTIONAL_DIVISION_VALUE_2_1 は端数の割り振りを id順で行ったもの、
PROPORTIONAL_DIVISION_VALUE_2_2 は端数の割り振りを DIFF2 の大きなもの順、
PROPORTIONAL_DIVISION_VALUE_2_3 は端数の割り振りを ランダムにおこなったもの(ゆえに結果は一定ではない)

「按分」の曖昧性

先に示した通り、「按分」という言葉だけではの方法・結果は一意に定まらない。「妥当な按分」に限ったとしても端数処理の具体的な割り振り方法までは言葉に含んでいない。

ゆえに

・再計算する場合
・集計結果での偏り
・符号が違うだけの場合の割り振りの対称性

なども検討したうえで、

・指示する側は端数処理の具体的な方法まで支持するか実装者にゆだねるかを判断する必要がある。
・指示を受ける側もどのような実装でもいいか、あるいは具体的な実装の指示があるかを確認する必要がある。

もちろん、指示する側の場合、相手が冒頭のような誤解をしていないかの確認は必要

まとめ

  • 「按分」という言葉は辞書を鵜呑みにしてはいけない。「分けた結果の合計が元と同じ」ことが最重要
  • 基準となる比率に基づく実数値との差をすべて 1未満になるように「按分」することができる。("妥当な按分")
  • SQL を使うと簡単に按分の計算ができる(おそらく、EXCELよりも)
  • 「按分」という言葉だけでは完全には仕様は定まらないので必要な場合はより詳細に仕様を定めること

  1. floor は負の数に対しても「その数を超えない最大の整数」を返す ↩ ↩2

  2. つまり、ゼロ寄せで端数を丸める ↩

5
7
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
7

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?