トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
3-33 銀行型丸め(Banker's Rounding)
SQLパズル
BankRoundTable
Val
-----
-4.51
-4.50
-4.49
-3.51
-3.50
-3.49
-2.51
-2.50
-2.49
-1.51
-1.50
-1.49
-0.51
-0.50
-0.49
4.51
4.50
4.49
3.51
3.50
3.49
2.51
2.50
2.49
1.51
1.50
1.49
0.51
0.50
0.49
Valに、
算術型丸めと、
銀行型丸め(Banker's Rounding)
を行う方法について考察します。
出力結果
Val MathRound BankRound mod(val,2)
----- --------- --------- ----------
-4.51 -5 -5 -0.51
-4.50 -5 -4 -0.50
-4.49 -4 -4 -0.49
-3.51 -4 -4 -1.51
-3.50 -4 -4 -1.50
-3.49 -3 -3 -1.49
-2.51 -3 -3 -0.51
-2.50 -3 -2 -0.50
-2.49 -2 -2 -0.49
-1.51 -2 -2 -1.51
-1.50 -2 -2 -1.50
-1.49 -1 -1 -1.49
-0.51 -1 -1 -0.51
-0.50 -1 0 -0.50
-0.49 0 0 -0.49
4.51 5 5 0.51
4.50 5 4 0.50
4.49 4 4 0.49
3.51 4 4 1.51
3.50 4 4 1.50
3.49 3 3 1.49
2.51 3 3 0.51
2.50 3 2 0.50
2.49 2 2 0.49
1.51 2 2 1.51
1.50 2 2 1.50
1.49 1 1 1.49
0.51 1 1 0.51
0.50 1 0 0.50
0.49 0 0 0.49
データ作成スクリプト
create table BankRoundTable as
select 4.51 as Val from dual
union select 4.50 from dual
union select 4.49 from dual
union select 3.51 from dual
union select 3.50 from dual
union select 3.49 from dual
union select 2.51 from dual
union select 2.50 from dual
union select 2.49 from dual
union select 1.51 from dual
union select 1.50 from dual
union select 1.49 from dual
union select 0.51 from dual
union select 0.50 from dual
union select 0.49 from dual
union select -0.49 from dual
union select -0.50 from dual
union select -0.51 from dual
union select -1.49 from dual
union select -1.50 from dual
union select -1.51 from dual
union select -2.49 from dual
union select -2.50 from dual
union select -2.51 from dual
union select -3.49 from dual
union select -3.50 from dual
union select -3.51 from dual
union select -4.49 from dual
union select -4.50 from dual
union select -4.51 from dual;
SQL
select to_char(Val,'0.00') as Val,
round(Val) as MathRound,
case mod(val,2)
when 0.5 then val-0.5
when -0.5 then val+0.5
else round(Val) end as BankRound,
to_char(mod(val,2),'0.00') as "mod(val,2)"
from BankRoundTable a
order by a.Val desc;
解説