꿈꾸는 개발자, DBA 커뮤니티 구루비

10장 반올림 오류

개발 언어에서는 실수형 데이터를 float 타입으로 선언하곤 한다.
이 때문에 DB를 구성할 때도 실수형 데이터가 저장될 열을 FLOAT 타입으로 선언
하지만, 실제로 데이터를 저장한 다음 평균을 내거나 합계를 내보면 조금씩 차이가 나는 현상 발생

10.1 목표: 정수 대신 소수 사용

정수는 1, 327, -19 와 같은 정수만 저장 가능 하고 소수 값은 표현하지 못한다.
정수보다 정밀한 수치가 필요하다면 다른 데이터 타입이 필요하다. 예를들어, 금액 합계는 $19.95와 같이 보통 소수점 둘째 자리까지의 소수로 표현한다.
따라서 목표는 정수가 아닌 수를 저장하고 산술 연산의 결과가 정확해야 한다는 것이다.

10.2 안티패턴 : FLOAT 데이터 타입 사용

많은 프로그래머들은 프로그램을 작성할 때 사용하던 float 데이터 타입에 익숙하기 때문에 SQL에서 소수 데이터가 필요한 곳에 자연스럽게 FLOAT 데이터 타입을 사용한다
SQL의 FLOAT 데이터 타입은 다른 프로그래밍 언어의 float와 마찬가지로 IEEE754 표준에 따라 실수를 이진 형식으로 부호화 한다.
이 데이터 타입을 효과적으로 사용하려면 이 형식의 부동 소수점 수 특성을 이해할 필요가 있다.

IEEE 754는 컴퓨터에서 부동소수점를 표현하는 가장 널리 쓰이는 표준이다. 지수를 이용해 아주 큰수와 작은 수를 표현할 수 있고 ±0 등의 수와 무한, NaN 등의 기호를 표시하는 법과 이러한 수에 대한 연산을 정의하고 있다. 참고: http://en.wikipedia.org/wiki/IEEE_754-1985 , http://ko.wikipedia.org/wiki/IEEE_754 , http://www.validlab.com/goldberg/paper.pdf

필요에 의한 반올림

십진수로 표현된 모든 수를 이진수로 표현할 수는 없다.

1/3 + 1/3 + 1/3 = 1.000

0.333 + 0.333 + 0.333 = 0.999

IEEE754는 부동 소수점 수를 밑수가 2인 형식으로 표현한다.
이진수로 무한한 정도를 요하는 값과 십진수에서 무한한 정도를 요구하는 수는 다르다.
59.95와 같이 십진수에서 유한한 정도를 가지는 값을 이진수로 표현하려면 무한한 정도가 필요하다.
FLOAT 데이터는 이렇게 할 수 없으므로, 밑수를 2로하는 가장 가까운 값을 사용해 저장하는데, 밑수를 10으로 했을 때 59.950000762939와 같다.
DOUBLE PRECISION과 REAL(mssql) 타입도 근사값을 저장하는 타입이므로 동일하다.

SQL에서 FLOAT 사용

SELECT hourly_rate FROM Accounts WHERE account_id = 123;
 -- 이쿼리는 59.95를 리턴한다. 

 -- 그러나 FLOAT에 실제로 저장되어 있는 값은 이 값과 정확하게 같지 않다. 이 값에 10억을 곱해보면 불일치를 확인 할 수있다. 
SELECT hourly_rate * 100000000 FROM Accounts WHERE account_id = 123;
 -- 59950000000.000이 아니라 59950000762.939를 리턴. 59.95값이 IEEE754의 이진 형식에 따라 유한 정도로 표현 가능한 값으로 반올림 됨.
 -- 이 경우 오차범위는 천만분의 일 수준으로, 많은 계산에서 무시해도 좋을 정도

 -- 그러나 다른 종류의 계산에서는 이 정도로 충분하지 않을 수 있다.
SELECT * FROM Accounts WHERE hourly_rate = 59.95;
 -- 결과는 없다.

 -- ABS()함수를 이용해 절대값을 만들면 찾을수있다.
SELECT * FROM Account WHERE ABS(hourly_rate - 59.95) < 0.000001;

 -- 그러나 정도를 높이면 여전히 결과를 얻는데 실패한다.
SELECT * FROM Accounts WHERE ABS(hourly_rate - 59.95) < 0.0000001;

 -- 많은 값을 집계해 계산할 때도 FLOAT의 부정확한 특성으로 인한 정확성 문제가 발생
SELECT SUM(b.hours * a.hourly_rate) AS project_cost
FROM Accounts As a, Bugs AS b
JOIN Accounts AS a ON (b.assigned_to = a.account_id);

주어진 집합에 대해 합계대신 곱을 구하는 경우 부동소수점 수의 누적으로 인한 충격은 훨씬 심각해짐
1에 정확하게 1.0을 곱하면 결과는 항상 1
1에 0.999를 천번 연속해서 곱하면 결과는 약 0.3677이된다. 곱하는 횟수가 늘어나면 불일치도 커진다.

10.3 안티패턴 인식 방법

FLOAT,REAL,DOUBLE PRECISION 데이터 타입이 사용되는 곳이면 언제든 의심이 간다.
부동소수점 수를 사용하는 대부분의 애플리케이션에서는 IEEE754형식이 제공한는 넓은 범위의 값이 필요하지 않다.

10.4 안티패턴 사용이 합당한 경우

INTEGER나 NUMERIC 타입이 지원하는 것보다 큰 범위의 실수 값을 사용해야 할때는 FLOAT가 좋은 데이터 타입이다. FLOAT 사용이 가장 적절한 예는 과학계산용 애플리케이션이다.
Oracle에서 FLOAT는 정확한 자릿수를 가지는 수치 타입이며 ,BINARY_FLOAT타입이 IEEE754를 사용해 수치를 표현하는 타입이다.

10.5 해법: NUMERIC 데이터 타입 사용

고정 소수점 수에는 FLOAT나 이와 비슷한 타입을 사용하지 말고, NUMERIC 또는 DECIMAL 타입을 사용해야 한다.
이런데이터 타입은 칼럼 정의에서 지정한 정도까지 수치를 정확하게 표현한다. VARCHAR타입에서 길이를 지정하는 것과 비슷한 문법으로, 데이터 타입의 인수로 정도를 지정한다. 정도는 이 칼럼값이 사용할 수 있는 전체 자릿 수다.
데이터 타입의 둘째 인수로 스케일을 지정할 수 있다. 스케일은 소수점 오른쪽의 자릿수다.
NUMERIC과 DECIMAL의 장점은 유리수가 FLOAT타입에서와 같이 반올림 되지않고 저장된다는 것이다. 59.95란 값을 지정하면 , 이 값이 정확하게 59.95로 저장된다고 확신할 수 있다.

decimal : -10^38 +1에서 10^38 -1 사이의 고정 정밀도 및 배율 숫자 데이터.
numeric: decimal과 기능이 같다.

SQL Antipatterns Tip

가능하면 FLOAT를 사용하지 말라

문서에 대하여

최초작성일 : 2011년 12월 10일
이 문서는 오라클클럽 코어 오라클 데이터베이스 스터디 모임에서 작성하였습니다.

10장. 반올림 오류 (by hun1361) [2011.12.08]