NUMERIC/DECIMAL の演算結果の有効桁数と小数点以下桁数
神谷 雅紀
SQL Server Escalation Engineer
numeric/decimal データ型は、有効桁数 (精度) と小数点以下桁数 (スケール) が固定ではないため、演算を行う場合には考慮が必要です。
NUMERIC/DECIMAL の演算結果の精度とスケールの決定方法
numeric/decimal の演算を行った場合、その結果の精度とスケールは以下のようになります。
e1, e2 はそれぞれ以下のように定義されているとします。
e1 numeric(p1, s1)
e2 numeric(p2, s2)
演算 |
結果の精度 |
結果のスケール |
| e1 + e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 - e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 * e2 | p1 + p2 + 1 | s1 + s2 |
| e1 / e2 | p1 - s1 + s2 + max(6, s1 + p2 + 1) | max(6, s1 + p2 + 1) |
| e1 UNION e2 | max(s1, s2) + max(p1-s1, p2-s2) | max(s1, s2) |
| e1 % e2 | min(p1-s1, p2 -s2) + max( s1,s2 ) | max(s1, s2) |
※ CASE, INTERSECT, EXCEPT も UNION と同じ方法です。
例えば、
e1 numeric(10,3)
e2 numeric(20,1)
である場合、e1 + e2 の結果の精度とスケールを上の表の計算式に従って計算すると、
精度 = max(3, 1) + max(10-3, 20-1) + 1 = 3 + 19 + 1 = 23
スケール = max(3, 1) = 3
となるため、e1 + e2 の結果のデータ型は
numeric(23,3)
となります。
整数部 7 桁、小数部 3 桁の数値と整数部 19 桁、小数部 1 桁の数値を足した結果を格納するためには、整数部 20 桁と小数部 3 桁が必要であるため、numeric(23, 3) が計算結果のデータ型となるのは、理解しやすいと思います。
これは以下のような SQL_VARIANT_PROPERTY 関数を使った T-SQL バッチで確認することができます。
declare @e1 numeric(10,3)
declare @e2 numeric(20,1)
set @e1=1111111.111
set @e2=2222222222222222222.2
select
@e1+@e2 as R,
sql_variant_property(@e1+@e2, 'Precision') as Precision,
sql_variant_property(@e1+@e2, 'Scale') as Scale
goR Precision Scale
------------------------ ---------- --------
2222222222223333333.311 23 3
もし、この結果を変数に格納しようとしている場合には、その変数は numeric(23, 3) もしくはそれを超える精度とスケールの変数として定義されている必要があります。
これだけであればそれほど複雑ではありませんが、精度の計算結果が最大精度である 38 を超えるようになると若干複雑になります。
最大精度 38 を超える場合
精度が numeric/decimal の最大精度である 38 を超える場合、小数部よりも整数部が優先され、可能な限り整数部で切り捨てが発生しないようにスケールが調整されます。
例えば、
e1 numeric(38,3)
e2 numeric(38,1)
である場合、e1 + e2 の結果は、
精度 = max(3, 1) + max(38-3, 38-1) + 1 = 3 + 37 + 1 = 41
スケール = max(3, 1) = 3
となりますが、41 は最大精度 38 を超えているため、精度は最大精度と等しい 38 になります。一方、スケールは、整数部が可能な限り切り捨てられないようにするために調整されます。
精度の計算上の値が最大精度を超える場合については、スケールが何になるのかを以下のような T-SQL を用いて確認する必要があります。
-- 加算の例
declare @e1 numeric(38,3)
declare @e2 numeric(38,1)
set @e1=1111111.111
set @e2=2222222222222222222.2
select
@e1+@e2 as R,
sql_variant_property(@e1+@e2, 'Precision') as Precision,
sql_variant_property(@e1+@e2, 'Scale') as Scale
go
R Precision Scale
------------------------ ---------- --------
2222222222223333333.3 38 1
関数を使う場合
SUM 関数を numeric/decimal データに対して使う場合の戻り値のデータ型は numeric(38, s) です。
そのため、スケールの異なる列に対して CASE や UNION を行う場合には、結果のスケールを考慮しておく必要があります。
以下の例を見てみます。
create table t1 (c1 numeric(10, 3))
create table t2 (c1 numeric(10, 5))
go
insert into t1 values (1111111.111)
insert into t2 values (11111.11111)
go
t1.c1 の SUM() の結果は t1.c1 が numeric(10, 3) なので numeric(38, 3) になります。
select SUM(c1) as A,
sql_variant_property(SUM(c1), 'Precision') as Precision,
sql_variant_property(SUM(c1), 'Scale') as Scale
from t1
goA Precision Scale
------------ ---------- ------
1111111.111 38 3
t2.c1 の SUM() の結果は t2.c1 が numeric(10, 5) なので numeric(38, 5) になります。
select SUM(c1) as A,
sql_variant_property(SUM(c1), 'Precision') as Precision,
sql_variant_property(SUM(c1), 'Scale') as Scale
from t2
goA Precision Scale
------------ ---------- ------
11111.11111 38 5
t1.c1 と t2.c1 の UNION は、numeric(38, 3) になります。その結果、t1.c2 の小数部は 3 桁となり、4 桁目以降は切り捨てられることになります。
select A,
sql_variant_property(A, 'Precision') as Precision,
sql_variant_property(A, 'Scale') as Scale
from
(select SUM(c1) as A from t1
union
select SUM(c1) as A from t2) T
goA Precision Scale
------------------ ------------ --------
11111.111 38 3
1111111.111 38 3
こういった切り捨てが発生しないように、もしくは、発生してもそれが問題とならないように、予め対策を行っておくことをお勧めします。(対策はシステム要件などにより異なると思いますので、ここでは特に言及しません。)
参考資料
有効桁数、小数点以下桁数、および長さ (Transact-SQL)
https://msdn.microsoft.com/ja-jp/library/ms190476.aspx
SUM (Transact-SQL)