數值的計算 - AWS Clean Rooms

本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。

數值的計算

在中 AWS Clean Rooms,計算是指二進制數學運算:加法,減法,乘法和除法。本節說明這些運算預期的傳回類型,以及使用 DECIMAL 資料類型時,用來決定精確度與小數位數的特定公式。

在查詢處理作業期間計算數值時,可能會遇到無法進行計算的情況,而且查詢會傳回數值溢位錯誤。您也可能會遇到計算值的小數位數改變或出乎意料的情況。針對某些運算,您可以使用明確轉換 (類型提升) 或 AWS Clean Rooms 設定參數,來解決這些問題。

關於使用 SQL 函式進行類似計算的結果,詳細資訊請參閱 中的 SQL 函數 AWS Clean Rooms

計算的傳回類型

鑑於中支援的一組數值資料類型 AWS Clean Rooms,下表顯示加法、減法、乘法和除法運算的預期傳回類型。表格左側的第一欄代表計算中的第一個運算元,最上面的列代表第二個運算元。

木属 整數 大整特 DECIMAL FLOAT4 FLOAT8
木属 SMALLINT INTEGER BIGINT DECIMAL FLOAT8 FLOAT8
整數 INTEGER INTEGER BIGINT DECIMAL FLOAT8 FLOAT8
大整特 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 結果時,用來計算結果精確度和小數位數的規則。在這個表格中,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)

下列的計算是一般規則,適用於針對 DECIMAL 數值的運算 (使用 UNION、INTERSECT 和 EXCEPT 等集合運算子,或 COALESCE 和 DECODE 等函式),計算出結果的精確度和小數位數:

Scale = max(s1,s2) Precision = min(max(p1-s1,p2-s2)+scale,19)

例如,包含一個 DECIMAL(7,2) 資料欄的 DEC1 資料表,會與包含一個 DECIMAL(15,3) 資料欄的 DEC2 資料表聯結,以產生 DEC3 資料表。DEC3 的結構描述顯示,其資料欄會變成 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 個 (含) 以下有效位數 (精確度) 的 DECIMAL 資料,會儲存為 64 位元整數。具有 19 個 (含) 以上有效位數 (精確度) 的 DECIMAL 資料,會儲存為 128 位元整數。所有 DECIMAL 數值的最高精確度為 38、最大小數位數為 37。當數值超出這些限值時,會出現溢位錯誤,這會同時發生於中間的和最終的結果集:

  • 當特定資料值不符合轉換函式所要求的精確度或指定的小數位數時,明確轉換作業會產生執行時期的溢位錯誤。例如,您不能從銷售表中的「價格支付」列中轉換所有值(十進制(8,2)列)並返回十進制(7,3)結果:

    select pricepaid::decimal(7,3) from sales; ERROR: Numeric data overflow (result precision)

    之所以發生這個錯誤,是因為 PRICEPAYED 資料行中有些較大的值無法轉換。

  • 在乘法運算所產生的結果中,其小數位數是每個運算元小數位數的總和。例如,如果兩個運算元都有 4 個小數位數,結果的小數位數是 8 個,使得小數點的左邊只有 10 個位數。因此,在將兩個都擁有大量小數位數的大數值相乘時,就會相當容易產生溢位狀況。

INTEGER 和 DECIMAL 類型的數值計算

當計算中的其中一個運算元具有 INTEGER 資料類型,而另一個運算元為 DECIMAL 時,INTEGER 運算元會隱含轉換為 DECIMAL。

  • 小麥林被轉換為十進制(5,0)

  • 整數轉換為十進制(10,0)

  • 大 INT 被轉換為十進制(19,0)

例如,如果將 DECIMAL(8,2) 資料欄 SALES.COMMISSION 乘以 SMALLINT 資料欄 SALES.QTYSOLD,此計算式會進行如下的轉換:

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