数値に関する計算 - Amazon Redshift
計算の戻り型DECIMAL の計算結果の精度とスケール除算演算に関する注意事項オーバーフロー条件INTEGER 型および DECIMAL 型での数値計算

数値に関する計算

ここで、計算とは加算、減算、乗算、および除算といった 2 進算術演算を示しています。このセクションでは、このような演算の予期される戻り型について、さらに DECIMAL データ型が必要な場合に精度とスケールを決定するのに適用される特定の計算式について説明します。

クエリ処理中に数値の計算が行われる場合には、計算が不可能であるためにクエリが数値オーバーフローエラーを返すといった状況が発生することがあります。さらに、算出される値のスケールが、変化したり予期せぬものであったりする状況が発生することもあります。一部の演算については、明示的なキャスト (型の上位変換) または Amazon Redshift 設定パラメータを使用してこれらの問題を解決できます。

SQL 関数を使用した同様の計算の結果の詳細については、「集計関数」を参照してください。

計算の戻り型

Amazon Redshift で一連の数値データ型がサポートされていると仮定して、次の表に加算、減算、乗算、および除算の予期される戻り型を示します。表の左側の最初の列は計算の 1 番目のオペランドを示し、一番上の行は 2 番目のオペランドを示します。

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 は計算における 1 番目のオペランドの精度とスケールを示し、p2 と s2 は 2 番目のオペランドの精度とスケールを示します (これらの計算に関係なく、結果の最大精度は 38、結果の最大スケールは 38 です)。

オペレーション 結果の精度とスケール
+ または - スケール = max(s1,s2)

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

* スケール = s1+s2

精度 = p1+p2+1

/ スケール = max(4,s1+p2-s2+1)

精度 = p1-s1+ s2+scale

例えば、SALES テーブルの PRICEPAID 列と COMMISSION 列は両方とも DECIMAL(8,2) 列です。PRICEPAID を COMMISSION で除算する場合 (または COMMISSION を PRICEPAID で除算する場合)、計算式は次のように適用されます。

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;

結果 

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)

除算演算に関する注意事項

除算演算の場合、ゼロ除算を行うとエラーが返されます。

精度とスケールが計算されると、スケール制限 100 が適用されます。算出された結果スケールが 100 より大きい場合、除算結果は次のようにスケーリングされます。

  • 精度 = precision - (scale - max_scale)

  • スケール = max_scale

算出された精度が最大精度 (38) より高い場合、精度は 38 に引き下げられ、スケールは max((38 + scale - precision), min(4, 100)) で計算された結果となります。

オーバーフロー条件

すべての数値計算についてオーバーフローが調べられます。精度が 19 以下の DECIMAL データは 64 ビットの整数として格納されます。精度が 19 を上回る DECIMAL データは 128 ビットの整数として格納されます。すべての DECIMAL 値の最大精度は 38 であり、最大スケールは 37 です。値がこれらの制限を超えるとオーバーフローエラーが発生します。このルールは中間結果セットと最終結果セットの両方に適用されます。

  • 特定のデータ値がキャスト関数で指定された所定の精度またはスケールに適合していない場合、明示的なキャストでは実行時オーバーフローエラーが生じます。例えば、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) としてキャストされます。

例えば、DECIMAL(8,2) 列の SALES.COMMISSION と、SMALLINT 列の SALES.QTYSOLD を乗算する場合、この計算は次のようにキャストされます。

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