数值计算
在此上下文中,计算 指二进制数学运算:加、减、乘、除。此部分介绍这些运算的预期返回类型,以及在涉及 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)。
运算 | 结果精度和小数位数 |
---|---|
+ 或者 - | 小数位数 = max(s1,s2) 精度 = |
* | 小数位数 = s1+s2 精度 = |
/ | 小数位数 = max(4,s1+p2-s2+1) 精度 = |
例如,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) 列的 DEC1 表将与具有一个 DECIMAL(15,3) 列的 DEC2 表联接以创建 DEC3 表。DEC3 的 schema 表明该列变成了 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 或 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 位。因此,在具有有效小数位数的两个大数相乘时,遇到溢出的情况相对容易一些。
以下代码会导致溢出错误:
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)