숫자 값 계산 - Amazon Redshift
계산 반환 형식계산된 DECIMAL 결과의 정밀도와 소수점 자릿수나누기 연산에 대한 주의 사항오버플로우 조건INTEGER 및 DECIMAL 형식을 사용한 숫자 계산

숫자 값 계산

여기에서 계산이란 더하기, 빼기, 곱하기, 나누기 등 수학적인 이진 연산을 말합니다. 이번 단원에서는 이러한 연산에 따라 예상되는 반환 형식을 비롯해 DECIMAL 데이터 형식이 포함되어 있을 경우 정밀도와 소수점 자릿수의 계산 공식에 대해서 설명합니다.

쿼리 처리 시 숫자 값을 계산할 때는 계산이 불가능하거나 쿼리가 숫자 오버플로우 오류를 반환하는 상황이 발생할 수 있습니다. 그 밖에도 계산된 값의 소수점 자릿수가 바뀌거나 예상과 다를 수도 있습니다. 일부 연산에서는 명시적 캐스팅(형식 승격) 또는 Amazon Redshift 구성 파라미터를 사용하여 이러한 문제를 피할 수 있습니다.

SQL 함수를 사용하는 비슷한 계산 결과에 대한 자세한 내용은 집계 함수 섹션을 참조하세요.

계산 반환 형식

다음 표는 Amazon Redshift에서 지원되는 숫자 데이터 형식을 고려하여 더하기, 빼기, 곱하기 및 나누기 연산에 따라 예상되는 반환 형식을 나타낸 것입니다. 표 왼쪽에서 첫 번째 열은 계산에 포함되는 첫 번째 피연산자이고, 맨 위의 행은 두 번째 피연산자를 의미합니다.

INT2 INT4 INT8 DECIMAL FLOAT4 FLOAT8
INT2 INT2 INT4 INT8 DECIMAL FLOAT8 FLOAT8
INT4 INT4 INT4 INT8 DECIMAL FLOAT8 FLOAT8
INT8 INT8 INT8 INT8 DECIMAL FLOAT8 FLOAT8
DECIMAL DECIMAL DECIMAL DECIMAL DECIMAL FLOAT8 FLOAT8
FLOAT4 FLOAT8 FLOAT8 FLOAT8 FLOAT8 FLOAT4 FLOAT8
FLOAT8 FLOAT8 FLOAT8 FLOAT8 FLOAT8 FLOAT8 FLOAT8

계산된 DECIMAL 결과의 정밀도와 소수점 자릿수

다음 표는 수학 연산이 DECIMAL 결과를 반환할 때 정밀도와 소수점 자릿수를 계산하는 규칙을 요약한 것입니다. 이 표에서 p1과 s1은 계산 시 첫 번째 피연산자의 정밀도 및 소수점 자릿수를, 그리고 p2와 s2는 두 번째 피연산자의 정밀도 및 소수점 자릿수를 의미합니다. 이 계산과 상관없이 최대 결과 정밀도는 38이고, 최대 결과 소수점 자릿수도 38입니다.

Operation 결과 정밀도 및 소수점 자릿수
+ 또는 - Scale = max(s1,s2)

precision = max(p1-s1,p2-s2)+1+scale

* Scale = s1+s2

precision = p1+p2+1

/ Scale = max(4,s1+p2-s2+1)

precision = p1-s1+ s2+scale

예를 들어 SALES 테이블의 PRICEPAID 열과 COMMISSION 열이 모두 DECIMAL(8,2) 열이라고 가정하겠습니다. 이때 PRICEPAID를 COMMISSION으로(혹은 그 반대로) 나누면 다음과 같은 공식이 적용됩니다.

Precision = 8-2 + 2 + max(4,2+8-2+1)
= 6 + 2 + 9 = 17

Scale = max(4,2+8-2+1) = 9

Result = DECIMAL(17,9)

다음 계산은 UNION, INTERSECT, EXCEPT 같은 집합 연산자나 COALESCE, DECODE 같은 함수를 사용해 DECIMAL 값에 대한 연산 결과 정밀도와 소수점 자릿수를 계산하기 위한 일반 규칙입니다.

Scale = max(s1,s2)
Precision = min(max(p1-s1,p2-s2)+scale,19)

예를 들어 DECIMAL(7,2) 열 1개가 포함된 DEC1 테이블이 DECIMAL(15,3) 열 1개가 포함된 DEC2 테이블과 조인되어 DEC3 테이블을 생성한다고 가정할 때, DEC3의 스키마를 보면 NUMERIC(15,3) 열이 되는 것을 알 수 있습니다.

create table dec3 as select * from dec1 union select * from dec2;

Result 

select "column", type, encoding, distkey, sortkey
from pg_table_def where tablename = 'dec3';

column |     type      | encoding | distkey | sortkey 
-------+---------------+----------+---------+---------
c1     | numeric(15,3) | none     | f       | 0

위 예에서 적용되는 공식은 다음과 같습니다.

Precision = min(max(7-2,15-3) + max(2,3), 19)
= 12 + 3 = 15

Scale = max(2,3) = 3

Result = DECIMAL(15,3)

나누기 연산에 대한 주의 사항

나누기 연산에서 0으로 나누는(divide-by-zero) 조건은 오류를 반환합니다.

정밀도와 소수점 자릿수를 계산한 후에는 소수점 자릿수 한계로 100이 적용됩니다. 계산된 결과 소수점 자릿수가 100보다 크면 나누기 결과가 다음과 같이 조정됩니다.

  • precision = precision - (scale - max_scale)

  • Scale = max_scale

계산된 정밀도가 최대 정밀도(38)보다 크면 정밀도가 38로 줄어들고, 소수점 자릿수는 다음 공식의 결과 값이 됩니다. max((38 + scale - precision), min(4, 100))

오버플로우 조건

모든 수치 계산에서는 오버플로우 여부를 확인합니다. 정밀도가 19 이하인 DECIMAL 데이터는 64비트 정수로 저장됩니다. 정밀도가 19보다 큰 DECIMAL 데이터는 128비트 정수로 저장됩니다. DECIMAL 값은 모두 최대 정밀도가 38이고, 최대 소수점 자릿수가 37입니다. 오버플로우 오류는 값이 이러한 제한을 초과할 때 발생하며, 중간 결과 집합과 최종 결과 집합 모두에 적용됩니다.

  • 특정 데이터 값이 CAST 함수에서 지정하는 정밀도 또는 소수점 자릿수와 맞지 않으면 명시적 변환을 실행해도 오버플로우 오류를 반환합니다. 예를 들어 SALES 테이블의 PRICEPAID 열(DECIMAL(8,2) 열)에 있는 값을 모두 변환할 수는 없기 때문에 다음과 같이 DECIMAL(7,3) 결과를 반환합니다.

    select pricepaid::decimal(7,3) from sales;
ERROR:  Numeric data overflow (result precision)

    이러한 오류가 발생하는 이유는 PRICEPAID 열에서 일부 더 큰 값은 변환할 수 없기 때문입니다.

  • 곱하기 연산은 결과 소수점 자릿수가 각 피연산자의 소수점 자릿수 합인 결과를 산출합니다. 예를 들어 두 피연산자의 소수점 자릿수가 4라고 한다면 결과 소수점 자릿수는 8이 되고 소수점 왼쪽에는 10자리만 남게 됩니다. 따라서 둘 다 유효 소수점 자릿수를 가지고 있는 큰 수 2개를 곱할 경우에는 비교적 오버플로우 조건이 발생하기 쉽습니다.

    다음 예는 오버플로 오류를 유발합니다.

    SELECT CAST(1 AS DECIMAL(38, 20)) * CAST(10 AS DECIMAL(38, 20));
ERROR: 128 bit numeric data overflow (multiplication)

    곱하기 대신 나누기를 사용하여 오버플로 오류를 해결할 수 있습니다. 다음 예를 사용하여 원래 제수로 나눈 1로 나눕니다.

    SELECT CAST(1 AS DECIMAL(38, 20)) / (1 / CAST(10 AS DECIMAL(38, 20)));
+----------+
| ?column? |
+----------+
| 10       |
+----------+

INTEGER 및 DECIMAL 형식을 사용한 숫자 계산

계산 시 피연산자 하나가 INTEGER 데이터 형식이고, 나머지 피연산자가 DECIMAL 데이터 형식인 경우에는 INTEGER 피연산자가 묵시적으로 DECIMAL로 변환됩니다.

  • INT2(SMALLINT)는 DECIMAL(5,0)로 변환됨

  • INT4(INTEGER)는 DECIMAL(10,0)로 변환됨

  • INT8(BIGINT)은 DECIMAL(19,0)로 변환됨

예를 들어 SALES.COMMISSION(DECIMAL(8,2) 열)과 SALES.QTYSOLD(SMALLINT 열)를 곱하면 다음과 같이 변환됩니다.

DECIMAL(8,2) * DECIMAL(5,0)