標準 SQL 中的運算子

運算子是以特殊字元或關鍵字來表示的;它們不會使用函式呼叫語法。運算子可操作任何數目的資料輸入 (也稱為運算元) 並傳回結果。

常見慣例:

  • 除非另有指定,否則只要其中一個運算元是 NULL,所有運算子都會傳回 。
  • 當運算結果溢位時,所有運算子都會擲回錯誤。
  • 對於所有浮點運算,只要其中一個運算元是 +/-infNaN,就可能只會傳回 和 NaN 的結果。在其他情況下,則會傳回錯誤。

下列表格列出所有 BigQuery 運算子,而排列順序是從最高優先順序排到最低優先順序,也就是這些運算子將會在陳述式中受到評估的順序。

優先順序 運算子 輸入資料類型 名稱 運算子元數
1 . STRUCT
成員欄位存取運算子 二元
  [ ] ARRAY 陣列位置。必須與 OFFSET 或 ORDINAL 搭配使用—請參閱 ARRAY 函式 二元
2 - 所有數值類型 一元減 一元
  ~ 整數或 BYTES 位元 not 一元
3 * 所有數值類型 二元
  / 所有數值類型 二元
4 + 所有數值類型 二元
  - 所有數值類型 二元
5 << 整數或 BYTES 位元左移 二元
  >> 整數或 BYTES 向右移位 二元
6 & 整數或 BYTES 位元 and 二元
7 ^ 整數或 BYTES 位元 xor 二元
8 | 整數或 BYTES 位元 or 二元
9 (比較運算子) = 任何可比較的類型。完整清單請參閱資料類型 等與 二元
  < 任何可比較的類型。完整清單請參閱資料類型 小於 二元
  > 任何可比較的類型。完整清單請參閱資料類型 大於 二元
  <= 任何可比較的類型。完整清單請參閱資料類型 小於或等於 二元
  >= 任何可比較的類型。完整清單請參閱資料類型 大於或等於 二元
  !=, <> 任何可比較的類型。完整清單請參閱資料類型 不等於 二元
  [NOT] LIKE STRING 與位元組 值與指定模式 [不] 相符 二元
  [NOT] BETWEEN 任何可比較類型。清單請參閱「資料類型」。 值 [不] 在指定範圍之內 二元
  [NOT] IN 任何可比較類型。清單請參閱資料類型。 值 [不] 在指定值組中 二元
  IS [NOT] NULL 全部 值 [不是] NULL 一元
  IS [NOT] TRUE BOOL 值 [不是] TRUE。 一元
  IS [NOT] FALSE BOOL 值 [不是] FALSE。 一元
10 NOT BOOL 邏輯 NOT 一元
11 AND BOOL 邏輯 AND 二元
12 OR BOOL 邏輯 OR 二元

優先順序相同的運算子是左結合的。這表示這些運算子會從左開始向右分組在一起。例如,以下運算式:

x AND y AND z

會被解讀為

( ( x AND y ) AND z )

以下運算式:

x * y / z

會被解讀為:

( ( x * y ) / z )

所有比較運算子都具有相同的優先順序,但比較運算子不會相互關聯。為避免混淆,請務必使用括號。例如:

(x < y) IS FALSE

元素存取運算子

運算子 語法 輸入資料類型 結果資料類型 說明
. expression.fieldname1... STRUCT
類型 T 儲存在 fieldname1 中 點運算子。可用於存取巢狀結構欄位,例如 expression.fieldname1.fieldname2...
[ ] array_expression [position_keyword (int_expression ) ] 請參閱 ARRAY 函式。 儲存在 ARRAY 中的類型 T position_keyword 為 OFFSET 或 ORDINAL。如需瞭解使用這個運算子的兩個函式,請參閱 ARRAY 函式

算術運算子

所有算數運算子都接受數字類型 T 的輸入內容,且除非在以下說明中另有指定,否則結果類型都會有類型 T:

名稱 語法
X + Y
X - Y
X * Y
X / Y
一元減 - X

注意:除以零的運算會傳回錯誤。若要傳回其他結果,請考慮 IEEE_DIVIDE 或 SAFE_DIVIDE 函式。

加與乘的結果類型:

 INT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

減的結果類型:

 INT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

除的結果類型:

 INT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

一元減的結果類型:

輸入資料類型 結果資料類型
INT64 INT64
NUMERIC NUMERIC
FLOAT64 FLOAT64

位元運算子

所有位元運算子都會傳回與第一個運算元相同的類型與長度。

名稱 語法 輸入資料類型 說明
位元 not ~ X 整數或 BYTES 對每個位元執行邏輯否定,形成該位元對於特定二進位值的補數。
位元 or X | Y X:整數或 BYTES
Y:與 X 的類型相同
取用兩個長度相同的位元模式,然後針對每組相對應的位元配對執行邏輯包含 OR 運算。如果 X 與 Y 是不同長度的 BYTES,這個運算子會擲回錯誤。
位元 xor X ^ Y X:整數或 BYTES
Y:與 X 的類型相同
取用兩個長度相同的位元模式,然後針對每組相對應的位元配對執行邏輯排除 OR 運算。如果 X 與 Y 是不同長度的 BYTES,這個運算子會擲回錯誤。
位元 and X & Y X:整數或 BYTES
Y:與 X 的類型相同
取用兩個長度相同的位元模式,然後針對每組相對應的位元配對執行邏輯 AND 運算。如果 X 與 Y 是不同長度的 BYTES,這個運算子會擲回錯誤。
左移 X << Y X:整數或 BYTES
Y:INT64
將第一個運算元 X 向左移。如果第二個運算元 Y 的位元長度大於或等於第一個運算元 X 的位元長度,這個運算子就會傳回 0,或是 b'\x00' 的位元組序列 (舉例來說,如果 X 有類型 INT64,這個運算子就會傳回 64)。如果 Y 是負數,這個運算子會擲回錯誤。
右移 X >> Y X:整數或 BYTES
Y:INT64
將第一個運算元 X 向右移。這個運算子不會對帶正負號的類型執行正負號位元擴充 (也就是說,它會在左側的空位元填入 0)。如果第二個運算元 Y 的位元長度大於或等於第一個運算元 X 的位元長度,這個運算子就會傳回 0,或是 b'\x00' 的位元組序列 (舉例來說,如果 X 有類型 INT64,這個運算子就會傳回 64)。如果 Y 是負數,這個運算子會擲回錯誤。

邏輯運算子

所有邏輯運算子都只允許 BOOL 輸入內容。

名稱 語法 說明
邏輯 NOT NOT X 如果輸入內容為 TRUE,就會傳回 FALSE。如果輸入內容為 FALSE,就會傳回 TRUE。如果是其他情況,則會傳回 NULL
邏輯 AND X AND Y 如果至少有一個輸入內容為 FALSE,就會傳回 FALSE。如果 X 與 Y 都是 TRUE,就會傳回 TRUE。如果是其他情況,則傳回 NULL
邏輯 OR X OR Y 如果 X 與 Y 都是 FALSE,就會傳回 FALSE。如果至少有一個輸入內容為 TRUE,就會傳回 TRUE。如果是其他情況,則傳回 NULL

比較運算子

比較運算子一定會傳回 BOOL。比較運算子通常需要兩個類型相同的運算元。如果運算元的類型並不相同,且 BigQuery 可以將這些類型的值轉換成共通類型的值,同時不會降低這些值的精確度,那麼 BigQuery 通常就會針對該比較運算子,強制把這些值轉換成共通的類型;BigQuery 通常會把常值強制轉換成非常值類型 (如果有的話)。如需可比較資料類型的定義,請參閱資料類型

STRUCT 只支援 4 個比較運算子:等於 (=)、不等於 (!= 和 <>) 與 IN。

以下規則只在比較這些資料類型時適用:

  • FLOAT64:只要比較運算子有 NaN,就會傳回 FALSE;但 !=<> 除外,因為此時比較運算子會傳回 TRUE。
  • BOOL:FALSE 小於 TRUE。
  • STRING:當運算子比較字串時,會按字碼逐一比較,這代表以標準方式比較時是相等的字串,必須先經過正規化,才能讓比較運算子傳回這些字串相同的結果。
  • NULL:以下是適用的慣例:只要是有 輸入內容的運算,都會傳回 。
名稱 語法 說明
小於 X < Y 如果 X 小於 Y,就會傳回 TRUE。
小於或等於 X <= Y 如果 X 小於或等於 Y,就會傳回 TRUE。
大於 X > Y 如果 X 大於 Y,就會傳回 TRUE。
大於或等於 X >= Y 如果 X 大於或等於 Y,就會傳回 TRUE。
等於 X = Y 如果 X 等於 Y,就會傳回 TRUE。
不等於 X != Y
X <> Y
如果 X 不等於 Y,就會傳回 TRUE。
介於 X [NOT] BETWEEN Y AND Z 如果 X [不] 在指定範圍之內就傳回 TRUE。「X BETWEEN Y AND Z」的結果相當於「Y <= X AND X <= Z」但在第一個運算子中,X 只會遭到評估一次。
LIKE X [NOT] LIKE Y 檢查第一個運算元 X 中的 STRING,是否與第二個運算元 Y 指定的模式相符。運算式可以包含下列字元:
  • 百分比符號「%」會與任何數量的字元或位元組相符
  • 底線「_」會比對單一字元或位元組
  • 您可以使用兩條反斜線逸出「\」、「_」或「%」,例如 "\\%"。如果您使用原始字串,就只需要一條反斜線,例如 r"\%"
IN 多個語法 - 請見以下說明 如果右運算元是空白的,就會傳回 FALSE。如果左運算元是 NULL,就會傳回 NULL。如果右運算元包含 NULL,則會傳回 TRUE 或 NULL,永遠不會傳回 FALSE。IN 兩側的引數都是一般運算式。沒有一個運算元必須是常值,然而在右運算元使用常值是最常見的情況。X 只會遭到評估一次。

當您測試數個值是否相等時,如果這些值擁有 STRUCT 資料類型,就可能至少會有一個欄位是 NULL。在這種情況下:

  • 如果所有非 NULL 欄位的值都是相等的,比較運算子就會傳回 NULL。
  • 只要有任何非 NULL 欄位的值是不相等的,比較運算子就會傳回 false。

下表示範當 STRUCT 資料類型的欄位有 NULL 值時,這些資料類型的比較方式。

Struct1 Struct2 Struct1 = Struct2
STRUCT(1, NULL) STRUCT(1, NULL) NULL
STRUCT(1, NULL) STRUCT(2, NULL) FALSE
STRUCT(1,2) STRUCT(1, NULL) NULL

IN 運算子

IN 運算子支援下列語法:

x [NOT] IN (y, z, ... ) # Requires at least one element
x [NOT] IN (<subquery>)
x [NOT] IN UNNEST(<array expression>) # analysis error if the expression
                                      # does not return an ARRAY type.

IN 運算子兩側的引數都是一般運算式。右側的運算式通常會是常值,但這並不是必要條件。

下列項目的語意:

x IN (y, z, ...)

定義為等於:

(x = y) OR (x = z) OR ...

子查詢與陣列表單的定義方式類似。

x NOT IN ...

等於:

NOT(x IN ...)

UNNEST 表單會把陣列掃描當做 FROM 子句中的 UNNEST 來處理:

x [NOT] IN UNNEST(<array expression>)

這個表單通常與 ARRAY 參數搭配使用。例如:

x IN UNNEST(@array_parameter)

注意:表單會把 NULL ARRAY 當做空白 ARRAY 來處理。

如要進一步瞭解如何使用這個語法,請參閱陣列主題。

當您使用 IN 運算子時,適用下列語意:

  • 如果 IN 的右側運算式是空白的,一定會傳回 FALSE
  • 如果 IN 的左側為 NULL 運算式,且右側為非空白運算式時,一定會傳回 NULL
  • IN 的 清單中有 NULL 時,只會傳回 TRUE 或 NULL,永遠不會傳回 FALSE
  • NULL IN (NULL) 會傳回 NULL
  • IN UNNEST(<NULL array>) 會傳回 FALSE (而不是 NULL)
  • NOT ININ 清單中有 NULL 時,只會能傳回 FALSE 或 NULL,永遠不會傳回 TRUE

您可以利用結構建構函式語法,將 IN 與多部分金鑰搭配使用。例如:

(Key1, Key2) IN ( (12,34), (56,78) )
(Key1, Key2) IN ( SELECT (table.a, table.b) FROM table )

如要進一步瞭解這個語法,請參閱「資料類型」主題中的結構類型小節。

IS 運算子

IS 運算子會針對它們測試的情況傳回 TRUE 或 FALSE。但 IS 運算子絕對不會傳回 NULL,就算輸入內容為 NULL 也一樣,這與在數學函式中定義的 IS_INF 和 IS_NAN 函式不同。如果有「NOT」,則輸出的 BOOL 值會反轉。

函式語法 輸入資料類型 結果資料類型 說明

X IS [NOT] NULL
不限值類型 BOOL 如果運算元 X 評估為 NULL,就會傳回 TRUE,否則會傳回 FALSE。

X IS [NOT] TRUE
BOOL BOOL 如果 BOOL 運算元評估為 TRUE,就會傳回 TRUE,否則會傳回 FALSE。

X IS [NOT] FALSE
BOOL BOOL 如果 BOOL 運算元評估為 FALSE,就會傳回 TRUE,否則會傳回 FALSE。