標準 SQL 中的 Mathematical 函式

所有 mathematical 函式都具有下列行為:

  • 如果任何輸入參數是 NULL,就會傳回 。
  • 如果任何引數是 NaN,就會傳回 。

ABS

ABS(X)

說明

計算絕對值。如果引數是整數,且輸出值無法以相同類型表示,就會傳回錯誤;只有沒有正表示的最大負輸入值會發生這種情形。如果是 +/-inf 引數,會傳回 +inf

SIGN

SIGN(X)

說明

分別針對負、零與正引數傳回 -1、0 或 +1。若為浮點引數,此函式不會區別正負零。如果是 NaN 引數,會傳回 NaN

IS_INF

IS_INF(X)

說明

如果值是正數或負無限大,則傳回 TRUE。若為 NULL 輸入,則傳回 NULL

IS_NAN

IS_NAN(X)

說明

如果值是 NaN 值,則傳回 TRUE。若為 NULL 輸入,則傳回 NULL

IEEE_DIVIDE

IEEE_DIVIDE(X, Y)

說明

將 X 除以 Y;此函式必會傳回值。傳回 FLOAT64。與除法運算子 (/) 不同,這個函式不會產生除以零或溢位錯誤。

特殊案例:

  • 如果結果溢位,就會傳回 +/-inf
  • 如果 Y=0 且 X=0,就會傳回 NaN
  • 如果 Y=0 且 X!= 0,就會傳回 +/-inf
  • 如果 X = +/-inf且 Y = ,就會傳回 NaN

下表將進一步說明 IEEE_DIVIDE 的行為。

IEEE_DIVIDE 的特殊案例

下表列出 IEEE_DIVIDE 的特殊案例。

分子資料類型 (X) 分母資料類型 (Y) 結果值
除了 0 以外的任何值 0 +/-inf
0 0 NaN
0 NaN NaN
NaN 0 NaN
+/-inf +/-inf NaN

RAND

RAND()

說明

在 [0, 1) 的範圍中產生 FLOAT64 類型的虛擬亂數值,包含 0 但排除 1。

SQRT

SQRT(X)

說明

計算 X 的平方根。如果 X 小於 0,就會產生錯誤。如果 X 是 +inf,就會傳回 +inf

POW

POW(X, Y)

說明

傳回 X 值的 Y 次方。如果結果欠位且無法表示,函式就會傳回零值。如果出現下列其中一種情況,就會傳回錯誤:

  • X 是小於 0 的有限值,且 Y 是非整數
  • X 是 0,且 Y 是小於 0 的有限值

下表將進一步說明 POW() 的行為。

POWER

POWER(X, Y)

說明

POW() 的同義詞。

POW(X, Y)POWER(X, Y) 的特殊案例

以下是 POW(X, Y)POWER(X, Y) 的特殊案例。

X Y POW(X, Y) 或 POWER(X, Y)
1.0 包括 NaN 在內的任何值 1.0
包括 NaN 在內的任何值 0 1.0
-1.0 +/-inf 1.0
ABS(X) < 1 -inf +inf
ABS(X) > 1 -inf 0
ABS(X) < 1 +inf 0
ABS(X) > 1 +inf +inf
-inf Y < 0 0
-inf Y > 0 如果 Y 是奇數整數就是 -inf,否則就是 +inf
+inf Y < 0 0
+inf Y > 0 +inf

EXP

EXP(X)

說明

計算 X 的 e 次方,也稱為自然指數函式。如果結果欠位,此函式就會傳回零。如果結果溢位,就會產生錯誤。如果 X 是 +/-inf,此函式會傳回 +inf 或 0。

LN

LN(X)

說明

計算 X 的自然對數。如果 X 小於或等於零,就會產生錯誤。如果 X 是 +inf,此函式會傳回 +inf

LOG

LOG(X [, Y])

說明

如果只有 X,則 LOGLN 的同義詞。如果還有 Y,LOG 就會計算以 Y 為底數的 X 對數。如有下列情況,則會產生錯誤:

  • X 小於或等於零
  • Y 是 1.0
  • Y 小於或等於零。

下表將進一步說明 LOG(X, Y) 的行為。

LOG(X, Y) 的特殊案例

X Y LOG(X, Y)
-inf 任何值 NaN
任何值 +inf NaN
+inf 0.0 Y < 1.0 -inf
+inf Y > 1.0 +inf

LOG10

LOG10(X)

說明

LOG 類似,但會計算以 10 為底數的對數。

GREATEST

GREATEST(X1,...,XN)

說明

如果任何輸入是 NULL,就會傳回 。否則,如果任何輸入是 NaN,則會傳回 NaN。不然就會根據 < 比較來傳回 X1,...,XN 之間的最大值。

LEAST

LEAST(X1,...,XN)

說明

如果任何輸入是 NULL,就會傳回 。如果任何輸入是 NaN,就會傳回 NaN。不然就會根據 > 比較來傳回 X1,...,XN 之間的最小值。

DIV

DIV(X, Y)

說明

傳回整數 X 除以 Y 的結果。除以零會傳回錯誤。除以 -1 可能會溢位。可能結果類型請見下表。

SAFE_DIVIDE

SAFE_DIVIDE(X, Y)

說明

相當於除法運算子 (/),但如果發生錯誤,例如除以零錯誤,就會傳回 NULL

SAFE_MULTIPLY

SAFE_MULTIPLY(X, Y)

說明

相當於乘法運算子 (*),但如果發生溢位,就會傳回 NULL

SAFE_NEGATE

SAFE_NEGATE(X)

說明

相當於一元減運算子 (-),但如果發生溢位,就會傳回 NULL

SAFE_ADD

SAFE_ADD(X, Y)

說明

相當於加法運算子 (+),但如果發生溢位,就會傳回 NULL

SAFE_SUBTRACT

SAFE_SUBTRACT(X, Y)

說明

相當於減法運算子 (-),但如果發生溢位,就會傳回 NULL

MOD

MOD(X, Y)

說明

模數函式:傳回 X 除以 Y 的餘數。傳回值的正負號與 X 相同。如果 Y 是零就會產生錯誤。可能結果類型請見下表。

ROUND

ROUND(X [, N])

說明

如果只有 X,則 ROUND 會將 X 進位或捨去至最接近的整數。如果有 N,則 ROUND 會將 X 進位或捨去至小數點後的第 N 位數。如果 N 是負值,則 ROUND 會將數字進位或捨去至小數點左側的第一位數。原則為四捨五入。如果發生溢位,就會產生錯誤。

TRUNC

TRUNC(X [, N])

說明

如果只有 X,則 TRUNC 會將 X 進位或捨去至最接近的整數,且其絕對值不大於 X 的絕對值。如果還有 N,則 的作用類似 ROUND(X, N),但一律無條件捨去且絕不會溢位。

CEIL

CEIL(X)

說明

傳回比 X 大的最小整數值 (類型為 FLOAT64)。

CEILING

CEILING(X)

說明

CEIL(X) 的同義詞

FLOOR

FLOOR(X)

說明

傳回比 X 小的最大整數值 (類型為 FLOAT64)。

Rounding 函式行為範例

BigQuery rounding 函式的範例行為:

輸入「X」 ROUND(X) TRUNC(X) CEIL(X) FLOOR(X)
2.0 2.0 2.0 2.0 2.0
2.3 2.0 2.0 3.0 2.0
2.8 3.0 2.0 3.0 2.0
2.5 3.0 2.0 3.0 2.0
-2.3 -2.0 -2.0 -2.0 -3.0
-2.8 -3.0 -2.0 -2.0 -3.0
-2.5 -3.0 -2.0 -2.0 -3.0
0 0 0 0 0
+/-inf +/-inf +/-inf +/-inf +/-inf
NaN NaN NaN NaN NaN

COS

COS(X)

說明

計算 X 的餘弦,其中 X 的單位為弧度。此函式必會傳回值。

COSH

COSH(X)

說明

計算 X 的雙曲餘弦,其中 X 的單位為弧度。如果發生溢位,就會產生錯誤。

ACOS

ACOS(X)

說明

計算 X 的反餘弦的主值。傳回值在範圍 [0,π] 中。如果 X 是 [-1, 1] 範圍外的值,就會產生錯誤。

ACOSH

ACOSH(X)

說明

計算 X 的反雙曲餘弦。如果 X 是小於 1 的值,就會產生錯誤。

SIN

SIN(X)

說明

計算 X 的正弦,其中 X 的單位為弧度。此函式必會傳回值。

SINH

SINH(X)

說明

計算 X 的雙曲正弦,其中 X 的單位為弧度。如果發生溢位,就會產生錯誤。

ASIN

ASIN(X)

說明

計算 X 的反正弦主值。傳回值在範圍 [-π/2,π/2] 中。如果 X 是 [-1, 1] 範圍外的值,就會產生錯誤。

ASINH

ASINH(X)

說明

計算 X 的反雙曲正弦。此函式必會傳回值。

TAN

TAN(X)

說明

計算 X 的正切,其中 X 的單位為弧度。如果發生溢位,就會產生錯誤。

TANH

TANH(X)

說明

計算 X 的雙曲正切,其中 X 的單位為弧度。此函式必會傳回值。

ATAN

ATAN(X)

說明

計算 X 反正切的主值。傳回值在 [-π/2,π/2] 範圍中。不會失敗。

ATANH

ATANH(X)

說明

計算 X 的反雙曲正切。如果 X 在 [-1, 1] 範圍外,就會產生錯誤。

ATAN2

ATAN2(Y, X)

說明

使用兩個引數的正負號計算 Y/X 的反正切主值,藉以確定象限。傳回值在 [-π,π] 範圍中。下表將進一步說明這個函式的行為。

ATAN2() 的特殊案例

X ATAN2(Y, X)
NaN 任何值 NaN
任何值 NaN NaN
0 0 視 X 與 Y 的正負號而定,為 0、π 或 -π
有限值 -inf 視 Y 的正負號而定,為 π 或 -π
有限值 +inf 0
+/-inf 有限值 視 Y 的正負號而定,為 π/2 或 -π/2
+/-inf -inf 視 Y 的正負號而定,為 ¾π 或 -¾π
+/-inf +inf 視 Y 的正負號而定,為 π/4 或 -π/4

trigonometric 與 hyperbolic rounding 函式的特殊案例

X COS(X) COSH(X) ACOS(X) ACOSH(X) SIN(X) SINH(X) ASIN(X) ASINH(X) TAN(X) TANH(X) ATAN(X) ATANH(X)
+/-inf NaN =+inf NaN =+inf NaN =+inf NaN =+inf NaN =+1.0 π/2 NaN
-inf NaN =+inf NaN NaN NaN -inf NaN -inf NaN -1.0 -π/2 NaN
NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

RANGE_BUCKET

RANGE_BUCKET(point, boundaries_array)

說明

RANGE_BUCKET 會掃描已排序陣列,並傳回該點上限 (從 0 開始) 的位置。如果您需要將資料分組,以便建構分區、直方圖、商業定義規則等,這個方法就相當實用。

RANGE_BUCKET 會遵循以下規則:

  • 如果該點存在於陣列中,系統會傳回下一個較大的索引值。

    RANGE_BUCKET(20, [0, 10, 20, 30, 40]) -- 3 is return value
    RANGE_BUCKET(20, [0, 10, 20, 20, 40, 40]) -- 4 is return value
    
  • 若陣列中不存在該點,但落在兩個值之間,就會傳回較大的索引值。

    RANGE_BUCKET(25, [0, 10, 20, 30, 40]) -- 3 is return value
    
  • 如果該點小於陣列中的第一個值,就會傳回 0。

    RANGE_BUCKET(-10, [5, 10, 20, 30, 40]) -- 0 is return value
    
  • 如果該點大於或等於陣列中的最後一個值,就會傳回陣列長度。

    RANGE_BUCKET(80, [0, 10, 20, 30, 40]) -- 5 is return value
    
  • 如果陣列空白,就會傳回 0。

    RANGE_BUCKET(80, []) -- 0 is return value
    
  • 如果該點是 NULLNaN,就會傳回 。

    RANGE_BUCKET(NULL, [0, 10, 20, 30, 40]) -- NULL is return value
    
  • 點和陣列的資料類型必須相容。

    RANGE_BUCKET('a', ['a', 'b', 'c', 'd']) -- 1 is return value
    RANGE_BUCKET(1.2, [1, 1.2, 1.4, 1.6]) -- 2 is return value
    RANGE_BUCKET(1.2, [1, 2, 4, 6]) -- execution failure
    

以下是會發生執行失敗的情況:

  • 陣列中有 NaNNULL 值。

    RANGE_BUCKET(80, [NULL, 10, 20, 30, 40]) -- execution failure
    
  • 陣列未依遞增順序排序。

    RANGE_BUCKET(30, [10, 30, 20, 40, 50]) -- execution failure
    

參數

  • point:一般值。
  • boundaries_array:一般值陣列。

傳回值

INT64

範例

在名為 students 的資料表中,勾選以查看每個 age_group 值區中存在多少筆記錄 (依學生年齡排序):

  • age_group 0 (年齡 < 10)
  • age_group 1 (年齡 >= 10,年齡 < 20)
  • age_group 2 (年齡 >= 20,年齡 < 30)
  • age_group 3 (年齡 >= 30)
WITH students AS
(
  SELECT 9 AS age UNION ALL
  SELECT 20 AS age UNION ALL
  SELECT 25 AS age UNION ALL
  SELECT 31 AS age UNION ALL
  SELECT 32 AS age UNION ALL
  SELECT 33 AS age
)
SELECT RANGE_BUCKET(age, [10, 20, 30]) AS age_group, COUNT(*) AS count
FROM students
GROUP BY 1

+--------------+-------+
| age_group    | count |
+--------------+-------+
| 0            | 1     |
| 2            | 2     |
| 3            | 3     |
+--------------+-------+