数值计算 - Amazon Redshift

数值计算

在此上下文中,计算 指二进制数学运算:加、减、乘、除。此部分介绍这些运算的预期返回类型,以及在涉及 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 结果时用于计算生成的精度和小数位数的规则。在此表中,p1s1 分别表示计算中第一个操作数的精度和小数位数,p2s2 分别表示第二个操作数的精度和小数位数。(不管这些计算如何,最大的结果精度为 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(或者反过来),采用的公式如下所示:

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)