数值计算 - AWS Clean Rooms
计算的返回类型计算的 DECIMAL 结果的精度和小数位数有关除法运算的说明溢出条件INTEGER 和 DECIMAL 类型的数值计算

本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

数值计算

在中 AWS Clean Rooms，计算是指二进制数学运算：加法、减法、乘法和除法。此部分介绍这些运算的预期返回类型，以及在涉及 DECIMAL 数据类型时应用于确定精度和小数位数的特定公式。

当在查询处理期间计算数值时，您可能会遇到无法计算和查询返回数字溢出错误的情况。您还可能会遇到计算值的小数位数发生变化或出乎意料的情况。对于一些运算，您可使用显式强制转换（类型提升）或 AWS Clean Rooms 配置参数来解决这些问题。

有关类似使用 SQL 函数的计算的结果的信息，请参阅中的 SQL 函数 AWS Clean Rooms。

计算的返回类型

给定中支持的一组数值数据类型 AWS Clean Rooms，下表显示了加法、减法、乘法和除法运算的预期返回类型。表左侧第一列表示计算中的第一个操作数，顶部行表示第二个操作数。

SMALLINT INTEGER BIGINT DECIMAL FLOAT4 FLOAT8
SMALLINT SMALLINT INTEGER BIGINT DECIMAL FLOAT8 FLOAT8
INTEGER INTEGER INTEGER BIGINT DECIMAL FLOAT8 FLOAT8
BIGINT BIGINT BIGINT BIGINT 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)

精度 = 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) 列。

select * from dec1 union select * from dec2;

在上例中，采用的公式如下所示：

Precision = min(max(7-2,15-3) + max(2,3), 19)
= 12 + 3 = 15

Scale = max(2,3) = 3

Result = DECIMAL(15,3)

有关除法运算的说明

对于除法运算， divide-by-zero 条件会返回错误。

在计算精度和小数位数之后，将应用小数位数最多为 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 位。因此，在具有有效小数位数的两个大数相乘时，遇到溢出的情况相对容易一些。

INTEGER 和 DECIMAL 类型的数值计算

如果计算中的一个操作数具有 INTEGER 数据类型且另一个操作数为 DECIMAL，则 INTEGER 操作数将隐式强制转换为 DECIMAL：

  • SMALLINT 将强制转换为 DECIMAL(5,0)

  • INTEGER 将强制转换为 DECIMAL(10,0)

  • BIGINT 将强制转换为 DECIMAL(19,0)

例如，如果将 SALES.COMMISSION（DECIMAL(8,2) 列）和 SALES.QTYSOLD（SMALLINT 列）相乘，则此计算将强制转换为：

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