标准 SQL 中的运算符

运算符由特殊字符或关键字表示;它们不使用函数调用语法。一个运算符可操作任意数量的数据输入(也称为操作数),并返回结果。

常见惯例:

  • 除非另有指定,否则只要有一个操作数是 NULL,所有运算符都会返回 NULL
  • 如果计算结果溢出,所有运算符都将抛出错误。
  • 对于所有浮点运算,仅当其中一个操作数是 +/-infNaN 时才可能返回 +/-infNaN。在其他情况下,系统会返回错误。

下表按从高到低的优先顺序(即在语句中计算的顺序)列出所有 BigQuery 运算符。

优先顺序 运算符 输入数据类型 名称 运算符元数
1 . STRUCT
成员字段访问运算符 二元
  [ ] ARRAY 数组位置。必须与 OFFSET 或 ORDINAL 一起使用 - 请参阅 ARRAY 函数 二元
2 - 所有数字类型 一元取反 一元
  ~ 整数或 BYTES 按位非 一元
3 * 所有数字类型 二元
  / 所有数字类型 二元
  || STRING、BYTES 或 ARRAY<T> 串联运算符 二元
4 + 所有数字类型 二元
  - 所有数字类型 二元
5 << 整数或 BYTES 按位左移 二元
  >> 整数或 BYTES 按位右移 二元
6 & 整数或 BYTES 按位与 二元
7 ^ 整数或 BYTES 按位异或 二元
8 | 整数或 BYTES 按位或 二元
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 逻辑非 一元
11 AND BOOL 逻辑与 二元
12 OR BOOL 逻辑或 二元

优先级相同的运算符采用左结合方式。这表示这些运算符会从左开始向右组合在一起。例如,以下表达式:

x AND y AND z

会被解释为

( ( x AND y ) AND z )

以下表达式:

x * y / z

会被解释为:

( ( x * y ) / z )

所有比较运算符的优先级相同,但比较运算符不具有结合性。因此,需要使用括号来避免歧义。例如:

(x < y) IS FALSE

元素访问运算符

运算符 语法 输入数据类型 结果数据类型 说明
. expression.fieldname1... STRUCT
fieldname1 中存储的类型 T 点运算符。可用于访问嵌套字段,例如 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

注意:除以 0 的运算会返回一个错误。如需返回不同的结果,请考虑使用 IEEE_DIVIDE 或 SAFE_DIVIDE 函数。

加法和乘法的结果类型:

 INT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

减法的结果类型:

 INT64NUMERICFLOAT64
INT64INT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

除法的结果类型:

 INT64NUMERICFLOAT64
INT64FLOAT64NUMERICFLOAT64
NUMERICNUMERICNUMERICFLOAT64
FLOAT64FLOAT64FLOAT64FLOAT64

一元取反的结果类型:

输入数据类型 结果数据类型
INT64 INT64
NUMERIC NUMERIC
FLOAT64 FLOAT64

按位运算符

所有按位运算符均返回与第一个操作符相同的类型和长度。

名称 语法 输入数据类型 说明
按位非 ~ X 整数或 BYTES 对每一位进行逻辑非运算,生成给定二进制值的补码。
按位或 X | Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑兼或运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
按位异或 X ^ Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑排斥或运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
按位与 X & Y X:整数或 BYTES
Y:类型与 X 相同
采用长度相等的两位模式,并对每一对相应位执行逻辑与运算。如果 X 和 Y 是不同长度的 BYTES,则此运算符会抛出错误。
向左移位 X << Y X:整数或 BYTES
Y:INT64
将第一个操作数 X 向左移位。如果第二个操作数 Y 大于或等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符返回 0,或者返回 b'\x00' 字节序列。如果 Y 是负数,则此运算符会抛出错误。
向右移位 X >> Y X:整数或 BYTES
Y:INT64
将第一个操作数 X 向右移位。此运算符不会对带符号的类型执行符号位扩展(也就是说,它会在左侧的空位上填入 0)。如果第二个操作数 Y 大于或等于第一个操作数 X 的位长度(例如,如果 X 的类型是 INT64,则其位长度是 64),则此运算符返回 0,或者返回 b'\x00' 字节序列。如果 Y 是负数,则此运算符会抛出错误。

逻辑运算符

BigQuery 支持 ANDORNOT 逻辑运算符。逻辑运算符仅允许 BOOL 或 NULL 输入,并使用三值逻辑来生成结果。结果可以是 TRUEFALSENULL

x y x AND y x OR y
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE NULL NULL TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
FALSE NULL FALSE NULL
NULL TRUE NULL TRUE
NULL FALSE NULL NULL
NULL NULL NULL NULL
x NOT x
TRUE FALSE
FALSE TRUE
NULL NULL

示例

本部分中的示例引用名为 entry_table 的表:

+-------+
| entry |
+-------+
| a     |
| b     |
| c     |
| NULL  |
+-------+
SELECT 'a' FROM entry_table WHERE entry = 'a'

-- a => 'a' = 'a' => TRUE
-- b => 'b' = 'a' => FALSE
-- NULL => NULL = 'a' => NULL

+-------+
| entry |
+-------+
| a     |
+-------+
SELECT entry FROM entry_table WHERE NOT (entry = 'a')

-- a => NOT('a' = 'a') => NOT(TRUE) => FALSE
-- b => NOT('b' = 'a') => NOT(FALSE) => TRUE
-- NULL => NOT(NULL = 'a') => NOT(NULL) => NULL

+-------+
| entry |
+-------+
| b     |
| c     |
+-------+
SELECT entry FROM entry_table WHERE entry IS NULL

-- a => 'a' IS NULL => FALSE
-- b => 'b' IS NULL => FALSE
-- NULL => NULL IS NULL => TRUE

+-------+
| entry |
+-------+
| NULL  |
+-------+

比较运算符

比较运算符始终返回 BOOL。通常情况下,两个操作数的类型必须相同才能进行比较运算。如果操作数的类型不同,并且 BigQuery 可以在没有精度损失的情况下将这些类型的值转换为某种通用类型,那么 BigQuery 通常会将这些类型强制转换为该通用类型以进行比较;BigQuery 通常会将文本强制转换为非文本类型(如果存在)。如需了解可以进行比较的数据类型,请参阅数据类型

STRUCT 仅支持 4 种比较运算符:等于 (=)、不等于(!= 和 <>)及 IN

比较这些数据类型时,您需遵循以下规则:

  • FLOAT64:所有带 NaN 的比较都返回 FALSE(!=<> 除外,它们返回的是 TRUE)。
  • BOOL:FALSE 小于 TRUE。
  • STRING:字符串按代码点逐一进行比较,这意味着只有先对字符串进行标准化处理,才能保证按规范相等的字符串比较为相等。
  • NULL:根据惯例,带 NULL 输入的任何运算均返回 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。
BETWEEN 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
  • 左侧表达式为 NULL 且右侧表达式非空的 IN 始终返回 NULL
  • IN 列表中具有 NULLIN 仅可返回 TRUE 或 NULL,永远不会返回 FALSE
  • NULL IN (NULL) 返回 NULL
  • IN UNNEST(<NULL array>) 返回 FALSE(而非 NULL
  • IN 列表中具有 NULLNOT IN 只能返回 FALSE 或 NULL,永远不会返回 TRUE

IN 可通过 Struct 构造函数语法与多部分键搭配使用。例如:

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

如需详细了解此语法,请参阅“数据类型”主题的结构体类型部分。

IS 运算符

IS 运算符会针对其所测试的条件返回 TRUE 或 FALSE。与数学函数中定义的 IS_INF 和 IS_NAN 函数不同,IS 运算符永远不会返回 NULL(即使是对于 NULL 输入也是如此)。如果存在 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。

串联运算符

串联运算符用于将多个值合并为一个值。

函数语法 输入数据类型 结果数据类型

STRING || STRING [ || ... ]
STRING STRING

BYTES || BYTES [ || ... ]
BYTES STRING

ARRAY<T> || ARRAY<T> [ || ... ]
ARRAY<T> ARRAY<T>