数值数据类型¶

NUMBER¶

数字最多可达 38 位，精度和小数位数可选：

精度:

允许的总位数。

小数位数:

小数点右侧允许的位数。

By default, precision is 38, and scale is 0; that is, NUMBER(38, 0). Precision limits the range of values that can be inserted into or cast to columns of a given type. For example, the value 999 fits into NUMBER(38,0) but not into NUMBER(2,0).

Because precision is the total number of digits allowed, you can't load a value into a NUMBER column if the number of digits to the left of the decimal point exceeds the precision of the column minus its scale. For example, NUMBER(20, 2) allows 18 digits on the left side of the decimal point and two digits on the right side of the decimal point, for a total of 20 digits.

The maximum scale, which is the number of digits to the right of the decimal point, is 37. Numbers that have fewer than 38 significant digits, but whose least significant digit is past the 37th decimal place --- for example, 0.0000000000000000000000000000000000000012 (1.2e-39) --- can't be represented without losing some digits of precision.

Snowflake 还支持 FLOAT 数据类型，该类型允许的值范围更广，但精度较低。

DECIMAL、DEC、NUMERIC¶

与 NUMBER 同义。

INT、INTEGER、BIGINT、SMALLINT、TINYINT、BYTEINT¶

与 NUMBER 同义，但不能指定精度和小数位数（即始终默认为 NUMBER(38, 0)）。因此，对于所有 INTEGER 数据类型，值范围都是从 -99999999999999999999999999999999999999 到 +99999999999999999999999999999999999999（含）的所有整数值。

The various names --- for example, TINYINT, BYTEINT, and so on ---are to simplify porting from other systems and to suggest the expected range of values for a column of the specified type.

精度和小数位数对存储大小的影响¶

Precision --- the total number of digits --- doesn't affect storage. The storage requirements for the same number in columns with different precisions, such as NUMBER(2,0) and NUMBER(38,0), are the same. For each micro-partition, Snowflake determines the minimum and maximum values for a given column and uses that information to determine the storage size for all values for that column in the partition. For example:

• If a column contains only values between -128 and +127, each of the values consumes 1 byte (uncompressed).

• If the largest value in the column is 10000000, each of the values consumes 4 bytes (uncompressed).

However, scale --- the number of digits following the decimal point --- affects storage. For example, the same value stored in a column of type NUMBER(10,5) consumes more space than NUMBER(5,0). Also, processing values with a larger scale might be slightly slower and consume more memory.

为了节省空间，Snowflake 会在将值写入存储之前对其进行压缩。压缩量取决于数据值和其他因素。

表中的定点数据类型示例¶

下面的语句创建了一个表，包含各种定点数据类型的列：

CREATE OR REPLACE TABLE test_fixed(
  num0 NUMBER,
  num10 NUMBER(10,1),
  dec20 DECIMAL(20,2),
  numeric30 NUMERIC(30,3),
  int1 INT,
  int2 INTEGER);

DESC TABLE test_fixed;

+-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name      | type         | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| NUM0      | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| NUM10     | NUMBER(10,1) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| DEC20     | NUMBER(20,2) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| NUMERIC30 | NUMBER(30,3) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| INT1      | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| INT2      | NUMBER(38,0) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+-----------+--------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

FLOAT、FLOAT4、FLOAT8¶

名称 FLOAT、FLOAT4 和 FLOAT8 是为了与其他系统兼容；Snowflake 将这三类数值都视为 64 位浮点数。

DOUBLE、DOUBLE PRECISION、REAL¶

与 FLOAT 同义。

表中的浮点数据类型示例¶

下面的语句创建了一个表，包含各种浮点数据类型的列：

CREATE OR REPLACE TABLE test_float(
  double1 DOUBLE,
  float1 FLOAT,
  dp1 DOUBLE PRECISION,
  real1 REAL);

DESC TABLE test_float;

+---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name    | type  | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| DOUBLE1 | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| FLOAT1  | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| DP1     | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| REAL1   | FLOAT | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+---------+-------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

DECFLOAT¶

十进制浮点数 (DECFLOAT) 数据类型精确存储数字，精度高达 38 位有效数字，并使用以 10 为底的动态指数来表示非常大或非常小的值。指数范围为 -16383 到 16384，允许值大约介于 -10^(16384) 到 10^(16384) 之间。DECFLOAT 数据类型支持变量小数位数，因此小数位数会根据存储的特定值而变化。与 FLOAT 数据类型以近似值表示数值不同，DECFLOAT 数据类型会在指定的精度范围内表示精确值。

DECFLOAT 数据类型不支持以下 FLOAT 数据类型支持的 特殊值：'NaN'`（非数字），:code:'inf'（无穷大），以及 :code:'-inf'`（负无穷大）。