数値データ型¶

精度¶

Snowflakeは、倍精度（64ビット） IEEE 754浮動小数点数を使用します。

精度は約15桁です。たとえば、整数の場合、範囲は-9007199254740991から+9007199254740991（-2⁵³ + 1から+2⁵³ - 1）です。浮動小数点値の範囲は、およそ10:sup:-308 から10:sup:+308 です。(Snowflakeは、約10 ^-324 と 10^-308 の間のより極端な値を、より低い精度で表すことができます。)詳細については、 倍精度数に関するウィキペディアの記事 をご参照ください。

Snowflakeは固定小数点データ型 NUMBER をサポートしているため、指数の範囲は狭くなりますが、精度が向上します。

特別な価値¶

Snowflakeは、 FLOAT に対して次の特別な値をサポートしています。

• 'NaN' （非数）

• 'inf' （無限大）

• '-inf' （負の無限大）

記号 'NaN'、 'inf'、および '-inf' は一重引用符で囲む必要があり、大文字と小文字は区別されません。

'NaN' の比較セマンティクスは、次の点で IEEE 754標準と異なります。

丸め誤差¶

浮動小数点操作には、最下位桁に小さな丸めエラーが生じることがあります。丸めエラーは、三角関数、統計関数、地理空間関数を含む、あらゆるタイプの浮動小数点処理で発生する可能性があります。

丸めエラーに関する注意事項は以下のとおりです。

• エラーは、クエリが実行されるたびに変わる可能性があります。

• オペランドの精度やスケールが異なると、エラーが大きくなる可能性があります。

• 特に集計関数（例: SUM や AVG）が大量の行を処理する場合は、エラーが蓄積する可能性があります。集約する前に固定小数点データ型にキャストすると、これらのエラーを削減または排除できます。

• 丸めエラーは、 SQL を操作する場合だけではなく、Snowflake内で実行される他のコード（例: Java、 JavaScript、またはPython）を操作する場合にも発生する可能性があります（例: UDFs および ストアドプロシージャ）。

• 2つの浮動小数点数を比較する場合、Snowflakeは、正確な等式ではなく、近似的な等式を比較することをお勧めします。

It might be possible to avoid these types of approximation errors by using the exact DECFLOAT data type.

Use cases for the DECFLOAT data type¶

Use the DECFLOAT data type when you need exact decimal results and a wide, variable scale in the same column.

The DECFLOAT data type is appropriate for the following general use cases:

• You are ingesting data, and the scale of incoming numeric values is unknown or highly variable.

• You require exact numeric values; for example, ledgers, taxes, or compliance.

• You are migrating from systems that rely on the IEEE 754-decimal representation or 128-bit decimals. These migrations might be blocked by the precision or range limitations of other Snowflake data types.

• You want to avoid Number out of representable range errors when you sum, multiply, or divide high-precision numeric values.

For example, you can use the DECFLOAT data type for the following specific use cases:

• You are ingesting heterogeneously-scaled data from Oracle DECIMAL or DB2 DECFLOAT columns.

• You are performing financial modeling involving computations with scales of results that are hard to predict.

• You are running scientific measurements that swing from nano units to astronomical units.

You can continue to use the NUMBER data type for fixed-scale numeric columns or the FLOAT data type for high-throughput analytics where imprecise results are acceptable.

Usage notes for the DECFLOAT data type¶

• If an operation produces a result with more than 38 digits, the DECFLOAT value is rounded to 38-digit precision, with the least-significant digits rounded off according to the current rounding mode. Snowflake uses the half up rounding mode for DECFLOAT values.

• When specifying a DECFLOAT value or casting to a DECFLOAT value, avoid using numeric literals in SQL. If you do this, the values are interpreted as NUMBER or FLOAT values before being cast to a DECFLOAT value, which can result in range errors or loss of exactness. Use either string literals --- such as SELECT '<value>'::DECFLOAT --- or the DECFLOAT literal --- such as SELECT DECFLOAT '<value>'.

• When operations mix DECFLOAT values and values of other numeric types, coercion prefers the DECFLOAT values. For example, when adding a value of NUMBER type and DECFLOAT type, the result is a DECFLOAT value.

• Use of the DECFLOAT type might cause storage consumption to increase.

Drivers and driver versions that support the DECFLOAT data type¶

The following Snowflake drivers and driver versions support the DECFLOAT data type. You might need to update your drivers to the versions that support DECFLOAT:

Unsupported drivers treat DECFLOAT values as TEXT values. For some drivers, a driver parameter must be set to map the DECFLOAT type to a language-native type. For more information, see ドライバー.

Limitations for the DECFLOAT data type¶

The following limitations apply to the DECFLOAT type:

• DECFLOAT values can't be stored in VARIANT, OBJECT, or ARRAY values. To cast a DECFLOAT value to a VARIANT value, you can first cast it to a VARCHAR value and then to a VARIANT value.

• DECFLOAT values aren't supported in the following types of tables:

  • Tables in external formats, such as Iceberg

  • Hybrid tables

• The DECFLOAT data type isn't supported in Snowflake Scripting stored procedures. However, it is supported in Snowflake Scripting user-defined functions (UDFs).

• The DECFLOAT data type isn't supported in stored procedures or UDFs written in a language other than SQL (such as Python or Java).

• The DECFLOAT data type isn't supported in Snowpark.

• Snowsight has limited support for the DECFLOAT data type.

• The following features don't support the DECFLOAT data type:

  • Clustering keys

  • Differential privacy

  • Sensitive data classification

  • 検索最適化サービス

• The NUMBER and FLOAT types might provide better performance than the DECFLOAT type.

Examples for the DECFLOAT data type¶

The following examples use the DECFLOAT data type:

• Show the differences between DECFLOAT and FLOAT

• Use DECFLOAT values with aggregate functions

CREATE OR REPLACE TABLE decfloat_sample (
  id INT,
  decfloat_val DECFLOAT,
  float_val FLOAT);

INSERT INTO decfloat_sample VALUES
  (
    1,
    DECFLOAT '123e7000',
    FLOAT '123e7000'
  ),
  (
    2,
    12345678901234567890123456789::DECFLOAT,
    12345678901234567890123456789::FLOAT
  ),
  (
    3,
    '-4.2e-5432'::DECFLOAT,
    '-4.2e-5432'::FLOAT
  ),
  (
    4,
    '1.00000000000000000000000000000000000014'::DECFLOAT,
    '1.00000000000000000000000000000000000014'::FLOAT
  ),
  (
    5,
    '1.00000000000000000000000000000000000015'::DECFLOAT,
    '1.00000000000000000000000000000000000015'::FLOAT
  );

DESC TABLE decfloat_sample;

+--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name         | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| ID           | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| DECFLOAT_VAL | DECFLOAT(38) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| FLOAT_VAL    | FLOAT        | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+--------------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

SELECT * FROM decfloat_sample;

+----+-----------------------------------------+------------------------+
| ID | DECFLOAT_VAL                            |              FLOAT_VAL |
|----+-----------------------------------------+------------------------|
|  1 | 1.23e7002                               | inf                    |
|  2 | 12345678901234567890123456789           |   1.23456789012346e+28 |
|  3 | -4.2e-5432                              |  -0                    |
|  4 | 1.0000000000000000000000000000000000001 |   1                    |
|  5 | 1.0000000000000000000000000000000000002 |   1                    |
+----+-----------------------------------------+------------------------+

CREATE OR REPLACE TABLE decfloat_agg_sample (decfloat_val DECFLOAT);

INSERT INTO decfloat_agg_sample VALUES
  (DECFLOAT '1e1000'),
  (DECFLOAT '-2.47e999'),
  (DECFLOAT '22e-75');

SELECT SUM(decfloat_val),
       AVG(decfloat_val),
       MAX(decfloat_val),
       MIN(decfloat_val)
  FROM decfloat_agg_sample;

+-------------------+-------------------+-------------------+-------------------+
| SUM(DECFLOAT_VAL) | AVG(DECFLOAT_VAL) | MAX(DECFLOAT_VAL) | MIN(DECFLOAT_VAL) |
|-------------------+-------------------+-------------------+-------------------|
| 7.53e999          | 2.51e999          | 1e1000            | -2.47e999         |
+-------------------+-------------------+-------------------+-------------------+