運算式、函式和運算子

本頁面說明 Cloud Spanner SQL 運算式,包括函式與運算子。

函式呼叫規則

除非在函式說明中另有明確指示,否則下列規則適用於所有函式:

  • 針對接受數字類型的函式,如有一個運算元是浮點運算元,另一個運算元是其他數字類型,這兩個運算元都會在評估函式之前轉換為 FLOAT64。
  • 如果運算元是 NULL,結果會是 NULL,但 IS 運算子例外。

  • 在未指定時區的情況下,注重時區分別的函式 (如函式說明中的指示) 會採用預設時區 America/Los_Angeles。

轉換規則

「轉換」包括但不限於類型轉換與強制轉換。

  • 類型轉換需要手動轉換,使用的是 CAST() 函式。
  • 強制轉換是自動轉換,Cloud Spanner SQL 會在以下情況中自動執行。
  • 此外,第三組轉換函式具有專屬的函式名稱,例如 UNIX_DATE()

下表大致列出 Cloud Spanner SQL 資料類型的所有 CAST 與強制轉換的可能情況。「強制轉換為」適用於特定資料類型 (例如資料欄) 的所有「運算式」

來源類型 轉換為 強制轉換為
INT64 BOOL
INT64
FLOAT64
STRING
FLOAT64
FLOAT64 INT64
FLOAT64
STRING
 
BOOL BOOL
INT64
STRING
 
STRING BOOL
INT64
FLOAT64
STRING
BYTES
DATE
TIMESTAMP
 
BYTES BYTES
STRING
 
DATE DATE
STRING
TIMESTAMP
 
TIMESTAMP DATE
STRING
TIMESTAMP
 
ARRAY ARRAY  
STRUCT STRUCT  

類型轉換

語法:

CAST(expr AS typename)

類型轉換語法可在查詢中用來指示運算式的結果類型應轉換為其他某一類型。

範例:

CAST(x=1 AS STRING)

如果 x 是 1,這會產生 "true",其他非 NULL 值則會產生 "false",如果 x 是 NULL 則會產生 NULL

在未成功從原始值對應至目標網域的支援類型之間轉換會產生執行階段錯誤。例如,將 BYTES 轉換為 STRING 時,若位元組順序不是有效的 UTF-8,會產生執行階段錯誤。

轉換下列類型的運算式 x 時,適用這些規則:

轉換 x 類型時的規則
INT64 FLOAT64 傳回接近但可能不完全相同的 FLOAT64 值。
INT64 BOOL 如果 x0 就傳回 FALSE,否則傳回 TRUE
FLOAT64 INT64 傳回最接近的 INT64 值。
例如 1.5 或 -0.5 之類的中間情況會向遠離 0 的方向四捨五入。
FLOAT64 STRING 傳回近似字串表示。
BOOL INT64 如果 xTRUE 就傳回 1,否則傳回 0
BOOL STRING 如果 xTRUE 就傳回 "true",否則傳回 "false"
STRING FLOAT64 x 傳回為 FLOAT64 值,將它解讀為具有與有效 FLOAT64 常值相同的格式。
也支援從 "inf""+inf""-inf""nan" 轉換類型。
轉換區分大小寫。
STRING BOOL 如果 x"true" 就傳回 TRUE,如果 x 是 "false" 就傳回 FALSE
x 的其他所有值無效,會擲回錯誤而非將類型轉換為 BOOL。
將類型轉換為 BOOL 時,STRING 區分大小寫。
STRING BYTES STRING 使用 UTF-8 編碼將類型轉換為 BYTES。例如,將 STRING「©」的類型轉換為 BYTES 時,會變成含有十六進位值 C2 與 A9 的 2 位元組順序。
BYTES STRING 傳回解讀為 UTF-8 STRING 的 x
以 BYTES 常值 b'\xc2\xa9' 為例,將類型轉換為 STRING 時,會解讀為 UTF-8 並變成 unicode 字元「©」。
如果 x 不是有效的 UTF-8,就會發生錯誤。
ARRAY ARRAY 必須是完全相同的 ARRAY 類型。
STRUCT STRUCT 如果符合以下條件就允許:
  1. 這兩個 STRUCT 具有相同的欄位數。
  2. 原始 STRUCT 欄位類型可手動轉換為對應目標 STRUCT 欄位類型 (如欄位順序定義,而非如欄位名稱定義)。

將十六進位字串類型轉換為整數

如果您打算使用十六進位字串 (0x123),可以將這些字串的類型轉換為整數:

SELECT '0x123' as hex_value, CAST('0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | 291        |
+-----------+------------+

SELECT '0x123' as hex_value, CAST('-0x123' as INT64) as hex_to_int;

+-----------+------------+
| hex_value | hex_to_int |
+-----------+------------+
| 0x123     | -291       |
+-----------+------------+

轉換資料類型

Cloud Spanner SQL 支援將日期類型轉換為字串,或從字串轉換,如下:

CAST(date_expression AS STRING)
CAST(string_expression AS DATE)

從日期類型轉換為字串與時區無關,且格式為 YYYY-MM-DD。將類型從字串轉換為日期時,字串必須符合支援的日期常值格式,這與時區無關。如果字串運算式無效或顯示的日期超出支援的最小/最大範圍,就會產生錯誤。

轉換時間戳記類型

Cloud Spanner SQL 支援將時間戳記類型轉換為字串,或從字串轉換,如下:

CAST(timestamp_expression AS STRING)
CAST(string_expression AS TIMESTAMP)

將時間戳記類型轉換為字串時,會使用預設時區 America/Los_Angeles 解讀時間戳記。產生的亞秒位數取決於亞秒部分的結尾零數:CAST 函式將會截斷零、三或六位數。

將類型從字串轉換為時間戳記時,string_expression 必須符合支援的時間戳記常值格式,否則會發生執行階段錯誤。string_expression 本身可能包含 time_zone,詳情請請參閱時區。如果 string_expression 中有時區,會使用那個時區進行轉換,否則會使用預設時區 America/Los_Angeles。如果字串少於六位數,會將自動加寬。

如果 string_expression 無效、亞秒有六位數以上 (也就是精確度大於毫秒),或顯示的時間超出支援的時間戳記範圍,就會產生錯誤。

在日期與時間戳記類型之間轉換

Cloud Spanner SQL 支援在日期與時間戳記類型之間轉換,如下:

CAST(date_expression AS TIMESTAMP)
CAST(timestamp_expression AS DATE)

將類型從日期轉換為時間戳記會將 date_expression 解讀為預設時區 America/Los_Angeles 的午夜 (一天的開始)。將類型從時間戳記轉換為日期會有效截斷從預設時區開始的時間戳記。

強制轉換

如需比對函式簽名,Cloud Spanner SQL 會將運算式的結果類型強制轉換為其他類型。例如,若將 func() 函式定義為使用 INT64 類型的單一引數,並且將運算式作為具有 FLOAT64 結果類型的引數使用,就會在計算 func() 之前將運算式的結果強制轉換為 INT64 類型。

其他轉換函式

Cloud Spanner SQL 提供下列其他轉換函式:

Aggregate 函式

「Aggregate 函式」是對一組值執行計算的函式。COUNT、MIN 與 MAX 都是 aggregate 函式的範例。

SELECT COUNT(*) as total_count, COUNT(fruit) as non_null_count,
       MIN(fruit) as min, MAX(fruit) as max
FROM UNNEST([NULL, "apple", "pear", "orange"]) as fruit;
+-------------+----------------+-------+------+
| total_count | non_null_count | min   | max  |
+-------------+----------------+-------+------+
| 4           | 3              | apple | pear |
+-------------+----------------+-------+------+

以下各節說明 Cloud Spanner SQL 支援的 aggregate 函式。

ANY_VALUE

ANY_VALUE(expression)

說明

如果輸入資料列數為零,就會從輸入傳回任何值或傳回 NULL。如果 expression 評估所有資料列後所得的值為 NULL,就會傳回 NULL,否則會傳回非 NULL 的值。傳回的值非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。

支援的引數類型

不限

傳回的資料類型

符合輸入資料類型。

範例

SELECT ANY_VALUE(fruit) as any_value
FROM UNNEST(["apple", "banana", "pear"]) as fruit;

+-----------+
| any_value |
+-----------+
| apple     |
+-----------+

ARRAY_AGG

ARRAY_AGG([DISTINCT] expression)

說明

傳回 expression 值的 ARRAY。

支援的引數類型

除 ARRAY 以外的所有資料類型。

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

輸出元素順序

輸出中的的元素順序非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。

傳回的資料類型

ARRAY

如果輸入資料列數為零,這個函式會傳回 NULL

範例

SELECT ARRAY_AGG(x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+-------------------------+
| array_agg               |
+-------------------------+
| [2, 1, -2, 3, -2, 1, 2] |
+-------------------------+

SELECT ARRAY_AGG(DISTINCT x) AS array_agg
FROM UNNEST([2, 1, -2, 3, -2, 1, 2]) AS x;

+---------------+
| array_agg     |
+---------------+
| [2, 1, -2, 3] |
+---------------+

AVG

AVG([DISTINCT] expression)

說明

傳回非 NULL 輸入值的平均數,如果輸入中包含 NaN 就會傳回 NaN

支援的引數類型

任何數字輸入類型,例如 INT64。請注意,對於浮點輸入類型而言,傳回結果非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

傳回的資料類型

  • FLOAT64

範例

SELECT AVG(x) as avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) as x;

+-----+
| avg |
+-----+
| 3   |
+-----+

SELECT AVG(DISTINCT x) AS avg
FROM UNNEST([0, 2, NULL, 4, 4, 5]) AS x;

+------+
| avg  |
+------+
| 2.75 |
+------+

BIT_AND

BIT_AND([DISTINCT] expression)

說明

expression 執行位元 AND 運算並傳回結果。

支援的引數類型

  • INT64

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

傳回的資料類型

INT64

範例

SELECT BIT_AND(x) as bit_and FROM UNNEST([0xF001, 0x00A1]) as x;

+---------+
| bit_and |
+---------+
| 1       |
+---------+

BIT_OR

BIT_OR([DISTINCT] expression)

說明

expression 執行位元 OR 運算並傳回結果。

支援的引數類型

  • INT64

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

傳回的資料類型

INT64

範例

SELECT BIT_OR(x) as bit_or FROM UNNEST([0xF001, 0x00A1]) as x;

+--------+
| bit_or |
+--------+
| 61601  |
+--------+

BIT_XOR

BIT_XOR([DISTINCT] expression)

說明

expression 執行位元 XOR 運算並傳回結果。

支援的引數類型

  • INT64

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

傳回的資料類型

INT64

範例

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

SELECT BIT_XOR(x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;
+---------+
| bit_xor |
+---------+
| 5678    |
+---------+

SELECT BIT_XOR(DISTINCT x) AS bit_xor FROM UNNEST([1234, 5678, 1234]) AS x;

+---------+
| bit_xor |
+---------+
| 4860    |
+---------+

COUNT

1.

COUNT(*)

2.

COUNT([DISTINCT] expression)

說明

  1. 傳回輸入中的資料列數。
  2. 傳回 expression 評估為 NULL 以外任何值的資料列數。

支援的引數類型

expression 可以是任何資料類型。

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

傳回資料類型

INT64

範例

SELECT
  COUNT(*) AS count_star,
  COUNT(x) AS count_x,
  COUNT(DISTINCT x) AS count_dist_x
FROM UNNEST([1, 4, NULL, 4, 5]) AS x;

+------------+---------+--------------+
| count_star | count_x | count_dist_x |
+------------+---------+--------------+
| 5          | 4       | 3            |
+------------+---------+--------------+

COUNTIF

COUNTIF([DISTINCT] expression)

說明

針對 expression 傳回 TRUE 值的計數。 如果輸入資料列數為零或 expression 針對所有資料列評估為 FALSE,就會傳回 0

支援的引數類型

BOOL

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

傳回資料類型

INT64

範例

SELECT COUNTIF(x<0) AS num_negative, COUNTIF(x>0) AS num_positive
FROM UNNEST([5, -2, 3, 6, -10, NULL, -7, 4, 0]) AS x;

+--------------+--------------+
| num_negative | num_positive |
+--------------+--------------+
| 3            | 4            |
+--------------+--------------+

LOGICAL_AND

LOGICAL_AND(expression)

說明

傳回所有非 NULL 運算式的邏輯 AND。如果輸入資料列數為零或 expression 針對所有資料列評估為 NULL,就會傳回 NULL

支援的引數類型

BOOL

傳回資料類型

BOOL

範例

SELECT LOGICAL_AND(x) as logical_and FROM UNNEST([true, false, true]) as x;

+-------------+
| logical_and |
+-------------+
| false       |
+-------------+

LOGICAL_OR

LOGICAL_OR(expression)

說明

傳回所有非 NULL 運算式的邏輯 OR。如果輸入資料列數為零或 expression 針對所有資料列評估為 NULL,就會傳回 NULL

支援的引數類型

BOOL

傳回資料類型

BOOL

範例

SELECT LOGICAL_OR(x) as logical_or FROM UNNEST([true, false, true]) as x;

+------------+
| logical_or |
+------------+
| true       |
+------------+

MAX

MAX(expression)

說明

傳回非 NULL 運算式的最大值。如果輸入資料列數為零或 expression 針對所有資料列評估為 NULL,就會傳回 NULL。 如果輸入中包含 NaN,就會傳回 NaN

支援的引數類型

除下列項目外的任何資料: ARRAY STRUCT

傳回資料類型

與用於輸入值的資料類型相同。

範例

SELECT MAX(x) AS max
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+-----+
| max |
+-----+
| 55  |
+-----+

MIN

MIN(expression)

說明

傳回非 NULL 運算式的最小值。如果輸入資料列數為零或 expression 針對所有資料列評估為 NULL,就會傳回 NULL。 如果輸入中包含 NaN,就會傳回 NaN

支援的引數類型

除下列項目外的任何資料: ARRAY STRUCT

傳回資料類型

與用於輸入值的資料類型相同。

範例

SELECT MIN(x) AS min
FROM UNNEST([8, NULL, 37, 4, NULL, 55]) AS x;

+-----+
| min |
+-----+
| 4   |
+-----+

STRING_AGG

STRING_AGG([DISTINCT] expression [, delimiter])

說明

傳回由串連非空值取得的值 (STRING 或 BYTES)。

如果指定 delimiter,串連值會由那個分隔符號分隔;否則,會使用逗號作為分隔符號。

支援的引數類型

STRING BYTES

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

輸出元素順序

輸出中的的元素順序非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。

傳回資料類型

STRING BYTES

範例

SELECT STRING_AGG(fruit) AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------+
| string_agg             |
+------------------------+
| apple,pear,banana,pear |
+------------------------+

SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+------------------------------+
| string_agg                   |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+

SELECT STRING_AGG(DISTINCT fruit, " & ") AS string_agg
FROM UNNEST(["apple", NULL, "pear", "banana", "pear"]) AS fruit;

+-----------------------+
| string_agg            |
+-----------------------+
| apple & banana & pear |
+-----------------------+

SUM

SUM([DISTINCT] expression)

說明

傳回非空值的總和。

如果運算式是浮點值,總和非絕對,這表示您可能會在每次使用這個函式時得到不同的結果。

支援的引數類型

任何支援的數字資料類型。

選用子句

DISTINCTexpression 的每個不同值都只會匯總一次到結果中。

傳回資料類型

  • 如果輸入是整數,就會傳回 INT64。

  • 如果輸入是浮點值,就會傳回 FLOAT64。

如果輸入中只包含 NULL,就會傳回 NULL

如果輸入中包含 Inf,就會傳回 Inf

如果輸入中包含 -Inf,就會傳回 -Inf

如果輸入中包含 NaN,就會傳回 NaN

如果輸入中包含 Inf-Inf 的組合,就會傳回 NaN

範例

SELECT SUM(x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 25  |
+-----+

SELECT SUM(DISTINCT x) AS sum
FROM UNNEST([1, 2, 3, 4, 5, 4, 3, 2, 1]) AS x;

+-----+
| sum |
+-----+
| 15  |
+-----+

Mathematical 函式

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

  • 如果任何輸入參數是 NULL,就會傳回 NULL
  • 如果任何引數是 NaN,就會傳回 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 = +/-inf,就會傳回 NaN

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

IEEE_DIVIDE 的特殊案例

下表列出 IEEE_DIVIDE 的特殊案例。

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

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

LEAST

LEAST(X1,...,XN)

說明

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

DIV

DIV(X, Y)

說明

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

MOD

MOD(X, Y)

說明

Modulo 函式:傳回 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,則 TRUNC 的作用類似 ROUND(X, N),但一律無條件捨去且絕不會溢位。

CEIL

CEIL(X)

說明

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

CEILING

CEILING(X)

說明

CEIL(X) 的同義詞

FLOOR

FLOOR(X)

說明

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

Rounding 函式行為範例

Cloud Spanner SQL 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 的餘弦。此函式永不失敗。

COSH

COSH(X)

說明

計算 X 的雙曲餘弦。如果發生溢位,就會產生錯誤。

ACOS

ACOS(X)

說明

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

ACOSH

ACOSH(X)

說明

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

SIN

SIN(X)

說明

計算 X 的正弦。此函式永不失敗。

SINH

SINH(X)

說明

計算 X 的雙曲正弦。如果發生溢位,就會產生錯誤。

ASIN

ASIN(X)

說明

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

ASINH

ASINH(X)

說明

計算 X 的反雙曲正弦。此函式不會失敗。

TAN

TAN(X)

說明

計算 X 的正切。如果發生溢位,就會產生錯誤。

TANH

TANH(X)

說明

計算 X 的雙曲正切。此函式不會失敗。

ATAN

ATAN(X)

說明

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

ATANH

ATANH(X)

說明

計算 X 的反雙曲正切。如果 X 的絕對值大於或等於 1,就會產生錯誤。

ATAN2

ATAN2(Y, X)

說明

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

ATAN2() 的特殊案例

Y 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

Hash 函式

FARM_FINGERPRINT

FARM_FINGERPRINT(value)

說明

使用來自開放原始碼 FarmHash 程式庫Fingerprint64 函式,計算 STRING 或 BYTES 輸入的指紋。特定輸入的這個函式之輸出將永不變更。

傳回類型

INT64

範例

SELECT
  *,
  FARM_FINGERPRINT(CONCAT(CAST(x AS STRING), y, CAST(z AS STRING)))
    AS row_fingerprint
FROM (
  SELECT 1 AS x, "foo" AS y, true AS z UNION ALL
  SELECT 2 AS x, "apple" AS y, false AS z UNION ALL
  SELECT 3 AS x, "" AS y, true AS z
);

+---+-------+-------+----------------------+
| x | y     | z     | row_fingerprint      |
+---+-------+-------+----------------------+
| 1 | foo   | true  | -1541654101129638711 |
| 2 | apple | false | 2794438866806483259  |
| 3 |       | true  | -4880158226897771312 |
+---+-------+-------+----------------------+

SHA1

SHA1(input)

說明

使用 SHA-1 演算法計算輸入的雜湊。輸入可以是 STRING 或 BYTES。字串版本會將輸入當成位元組的陣列處理。

這個函式會傳回 20 個位元組。

傳回類型

BYTES

範例

SELECT SHA1("Hello World") as sha1;

+-----------------------------------------------------------+
| sha1                                                      |
+-----------------------------------------------------------+
| \nMU\xa8\xd7x\xe5\x02/\xabp\x19w\xc5\xd8@\xbb\xc4\x86\xd0 |
+-----------------------------------------------------------+

SHA256

SHA256(input)

說明

使用 SHA-256 演算法計算輸入的雜湊。輸入可以是 STRING 或 BYTES。字串版本會將輸入當成位元組的陣列處理。

這個函式會傳回 32 個位元組。

傳回類型

BYTES

範例

SELECT SHA256("Hello World") as sha256;

SHA512

SHA512(input)

說明

使用 SHA-512 演算法計算輸入的雜湊。輸入可以是 STRING 或 BYTES。字串版本會將輸入當成位元組的陣列處理。

這個函式會傳回 64 個位元組。

傳回類型

BYTES

範例

SELECT SHA512("Hello World") as sha512;

String 函式

這些 string 函式會處理兩種不同的值:STRING 與 BYTES 資料類型。STRING 值必須是格式正確的 UTF-8。

傳回正值的函式 (例如 STRPOS) 會將這些位置編碼為 INT64。1 值會參照第一個字元 (或位元組),2 會參照第二個,依此類推。0 值表示無效索引。處理 STRING 類型時,傳回的位置會參照字元位置。

所有字串比較都是逐一位元組完成的,無論 Unicode 正式相等性為何。

BYTE_LENGTH

BYTE_LENGTH(value)

說明

傳回的位元組長度,無論值的類型是 STRING 還是 BYTES。

傳回類型

INT64

範例

Table example:

+----------------+--------+
| characters     | bytes  |
+----------------+--------+
| абвгд          | абвгд  |
+----------------+--------+

SELECT
  characters,
  BYTE_LENGTH(characters) AS string_example,
  bytes,
  BYTE_LENGTH(bytes) AS bytes_example
FROM example;

+------------+----------------+-------+---------------+
| characters | string_example | bytes | bytes_example |
+------------+----------------+-------+---------------+
| абвгд      | 10             | абвгд | 10            |
+------------+----------------+-------+---------------+

CHAR_LENGTH

CHAR_LENGTH(value)

說明

傳回 STRING 的字元長度。

傳回類型

INT64

範例

Table example:

+----------------+
| characters     |
+----------------+
| абвгд          |
+----------------+

SELECT
  characters,
  CHAR_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CHARACTER_LENGTH

CHARACTER_LENGTH(value)

說明

CHAR_LENGTH 的同義詞。

傳回類型

INT64

範例

Table example:

+----------------+
| characters     |
+----------------+
| абвгд          |
+----------------+

SELECT
  characters,
  CHARACTER_LENGTH(characters) AS char_length_example
FROM example;

+------------+---------------------+
| characters | char_length_example |
+------------+---------------------+
| абвгд      |                   5 |
+------------+---------------------+

CODE_POINTS_TO_BYTES

CODE_POINTS_TO_BYTES(ascii_values)

說明

取用延伸 ASCII 碼點的陣列 (INT64 的 ARRAY) 並傳回 BYTES。

若要從 BYTES 轉換為碼點的陣列,請參閱 TO_CODE_POINTS

傳回類型

BYTES

範例

以下是使用 CODE_POINTS_TO_BYTES 的基本範例。

SELECT CODE_POINTS_TO_BYTES([65, 98, 67, 100]) AS bytes;

+-------+
| bytes |
+-------+
| AbCd  |
+-------+

以下範例使用迴轉 13 位 (ROT13) 演算法來編碼字串。

SELECT CODE_POINTS_TO_BYTES(ARRAY(
  (SELECT
      CASE
        WHEN chr BETWEEN b'a' AND b'z'
          THEN TO_CODE_POINTS(b'a')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'a')[offset(0)],26)
        WHEN chr BETWEEN b'A' AND b'Z'
          THEN TO_CODE_POINTS(b'A')[offset(0)] +
            MOD(code + 13 - TO_CODE_POINTS(b'A')[offset(0)],26)
        ELSE code
      END
   FROM
     (SELECT code, CODE_POINTS_TO_BYTES(ARRAY[code]) AS chr
      FROM UNNEST(TO_CODE_POINTS(input)) AS code WITH OFFSET
      ORDER BY OFFSET)
   ))) AS encoded_string
FROM UNNEST(ARRAY['Test String!']) AS input;

+----------------+
| encoded_string |
+----------------+
| Grfg Fgevat!   |
+----------------+

CODE_POINTS_TO_STRING

CODE_POINTS_TO_STRING(value)

說明

取用 Unicode 碼點的陣列 (INT64 的 ARRAY) 並傳回 STRING。

若要從字串轉換為碼點的陣列,請參閱 TO_CODE_POINTS

傳回類型

STRING

範例

以下是使用 CODE_POINTS_TO_STRING 的基本範例。

SELECT CODE_POINTS_TO_STRING([65, 255, 513, 1024]) AS string;

+--------+
| string |
+--------+
| AÿȁЀ   |
+--------+

以下範例會計算一組字詞中的字母頻率。

SELECT
  CODE_POINTS_TO_STRING([code_point]) AS letter,
  COUNT(*) AS letter_count
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word,
     UNNEST(TO_CODE_POINTS(word)) AS code_point
GROUP BY 1
ORDER BY 2 DESC;

+--------+--------------+
| letter | letter_count |
+--------+--------------+
| a      | 5            |
| f      | 3            |
| r      | 2            |
| b      | 2            |
| l      | 2            |
| o      | 2            |
| g      | 1            |
| z      | 1            |
| e      | 1            |
| m      | 1            |
| i      | 1            |
+--------+--------------+

CONCAT

CONCAT(value1[, ...])

說明

將一或多個串連成單一結果。

傳回類型

STRING 或 BYTES

範例

Table Employees:

+-------------+-----------+
| first_name  | last_name |
+-------------+-----------+
| John        | Doe       |
| Jane        | Smith     |
| Joe         | Jackson   |
+-------------+-----------+

SELECT
  CONCAT(first_name, " ", last_name)
  AS full_name
FROM Employees;

+---------------------+
| full_name           |
+---------------------+
| John Doe            |
| Jane Smith          |
| Joe Jackson         |
+---------------------+

ENDS_WITH

ENDS_WITH(value1, value2)

說明

取用兩個。如果第二個值是第一個值的後置字串,就換傳回 TRUE。

傳回類型

BOOL

範例

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  ENDS_WITH(item, "e") as example
FROM items;

+---------+
| example |
+---------+
|    True |
|   False |
|    True |
+---------+

FORMAT

Cloud Spanner SQL 支援格式化字串的 FORMAT() 函式。這個函式與 C printf 函式類似。它會從包含零或更多格式指定碼的格式字串中產生 STRING,以及符合格式指定碼的其他引數變數長度清單。範例如下:

說明 陳述式 結果
簡單整數 format("%d", 10) 10
左側有填補空格的整數 format("|%10d|", 11) |           11|
左側有填補零的整數 format("+%010d+", 12) +0000000012+
STRING format("-%s-", 'abcd efg') -abcd efg-
FLOAT64 format("%f %E", 1.1, 2.2) 1.100000 2.200000E+00
DATE format("%t", date "2015-09-01") 2015-09-01
TIMESTAMP format("%t", timestamp "2015-09-01 12:34:56 America/Los_Angeles") 2015‑09‑01 19:34:56+00

FORMAT() 函式不為所有類型與值提供完全可自訂的格式化,也不提供對語言代碼敏感的格式化。

如果某一類型需要自訂格式化,您必須先使用類型特定格式函式 (例如 FORMAT_DATE() 或 FORMAT_TIMESTAMP()) 來對它進行格式化。例如:

FORMAT("date: %s!", FORMAT_DATE("%B %d, %Y", date '2015-01-02'));

傳回

date: January 02, 2015!

語法

FORMAT() 語法會取用格式字串與引數變數長度清單並產生 STRING 結果:

FORMAT(<format_string>, ...)

<format_string> 運算式可以取得零個或更多的格式指定碼。 每個格式指定碼都由 % 符號引入,且必須對應至一或多個剩餘引數。大多數情況下,除非當 * 指定碼存在時,這都是一對一的對應。例如,%.*i 對應至兩個引數—長度引數與正負號整數引數。如果與格式指定碼相關的引數數目與引數數目不同,就會發生錯誤。

支援的格式指定碼

FORMAT() 函式格式指定碼遵循這個原型:

%[flags][width][.precision]specifier

下表中列出支援的格式指定碼。 從 printf() 擴充的項目會以斜體列出。

指定碼 說明 範例 類型
di 十進位整數 392 INT64

o 八進位 610
INT64*
x 十六進位整數 7fa
INT64*
X 十六進位整數 (大寫) 7FA
INT64*
f 十進位浮點,小寫 392.65
inf
NaN

FLOAT64
F 十進位浮點,大寫 392.65
inf
NAN

FLOAT64
e 科學記數法 (尾數/指數),小寫 3.9265e+2
inf
NaN

FLOAT64
E 科學記數法 (尾數/指數),大寫 3.9265E+2
inf
NAN

FLOAT64
g 使用最短表示法,%e 或 %f 392.65
FLOAT64
G 使用最短表示法,%E 或 %F 392.65
FLOAT64
s 字元字串 範例 STRING
t 傳回表示值的可列印字串。通常看起來類似將引數類型轉換為 STRING。請參閱以下的 %t 部分 範例
2014‑01‑01
<any>
T 產生的字串是有效的 Cloud Spanner SQL 常數,它的類型與值的類型類似 (可能更寬,也可能是字串)。 請參閱以下的 %T 部分 'sample'
b'bytes sample'
1234
2.3
date '2014‑01‑01'
<any>
% 「%%」會產生單一的「%」 %

*如果使用負值,則 o、x 與 X 指定碼會產生錯誤。

格式指定碼可以選擇性地包含在指定碼原型上方列出的子指定碼。

這些子指定碼必須遵守下列指定。

標記
標記 說明
- 在指定欄位寬度內靠左對齊;預設為靠右對齊 (請參閱寬度子指定碼)
+ 強制在結果前面加上加號或減號 (+-),即使是正數也加上。預設只在負數前面加上 -
<space> 如果沒有正負號可以寫上,就會在值前面插入一個空格
# 與 o、x 或 X 指定碼搭配使用。分別為零以外的值在前面加上 0、0x 或 0X
0 當指定填補時,為數字靠左填補零 (0) 而非空格 (請參閱寬度子指定碼)
'

使用適當的分組字元格式化整數。例如:

  • FORMAT("%'d", 12345678) 會傳回 12,345,678
  • FORMAT("%'x", 12345678) 會傳回 bc:614e
  • FORMAT("%'o", 55555) 會傳回 15,4403
  • 這個標記只與十進位、十六進位與八進位值相關。

標記可以任何順序指定。重複的標記不算是錯誤。當標記對某些元素類型而言無關時,就會忽略它們。

寬度
寬度 說明
<number> 要列印的字元數量下限。如果要列印的值短於這個數目,會為結果填補空格。即使結果較大,也不會截斷值
* 不會在格式字串中指定寬度,但會做為前置於必須格式化之引數前面的其他整數值引數
精確度
精確度 說明
.<number> 針對整數指定碼 (d、i、o、u、x、X):精確度指定了要編寫的位數下限。如果要編寫的值短於這個數目,就會為結果填補尾隨零。即使結果較長,也不會截斷值。精確度為 0 表示不會為值 0 編寫任何字元。對於 a、A、e、E、f 與 F 指定碼:這是要列印在小數點之後的位數 (預設是 6)
.* 不會在格式字串中指定精確度,但會做為前置於必須格式化之引數前面的其他整數值引數

%t 與 %T 行為

會為所有類型定義 %t%T 格式指定碼。寬度、精確度與標記的行為就像用於 %s 時一樣:width 是最小寬度,STRING 將會填補至那個大小,precision 是要顯示內容的最大寬度,STRING 將會在填補至寬度之前截斷至那個大小。

%t 一定是值的可閱讀形式。

%T 一律是類似類型的有效 SQL 常值,例如較寬的數字類型。 該常值將不會包含類型轉換或類型名稱,非有限浮點值的特殊案例除外:

STRING 的格式化情形如下:

類型 %t %T
任何類型的 NULL NULL NULL
INT64
123 123
FLOAT64 123.0 (一律帶有 .0)
123e+10
inf
-inf
NaN


123.0 (一律帶有 .0)
123e+10
CAST("inf" 作為 <type>)
CAST("-inf" 作為 <type>)
CAST("nan" 作為 <type>)
STRING 未加引號的字串值 加引號的字串常值
BYTES 未加引號的逸出位元組
例如 abc\x01\x02
加引號的位元組常值
例如 b"abc\x01\x02"
DATE 2011-02-03 DATE "2011-02-03"
TIMESTAMP 2011-02-03 04:05:06+00 TIMESTAMP "2011-02-03 04:05:06+00"
ARRAY [值, 值, ...]
其中值的格式為 %t
[值, 值, ...]
其中值的格式為 %T

錯誤狀況

如果格式指定碼無效,或與相關引數類型不相容,或提供了錯誤數字或引數,就會產生錯誤。例如,以下 <format_string> 運算式無效:

FORMAT('%s', 1)
FORMAT('%')

NULL 引數處理

NULL 格式字串會產生 NULL 輸出 STRING。在此情況下會忽略其他任何引數。

如果 NULL 引數存在,函式一般會產生 NULL 值。例如,FORMAT('%i', <NULL expression>) 會產生 NULL STRING 作為輸出。

但是,也有一些例外:如果格式指定碼是 %t 或 %T (這兩者都會產生與 CAST 和常值語意有效相符的 STRING),NULL 值會在結果 STRING 中產生「NULL」(不含引號)。例如,以下函式:

FORMAT('00-%t-00', <NULL expression>);

傳回

00-NULL-00

其他語意規則

FLOAT64 值可以是 +/-infNaN。當引數具有這裡的其中一個值時,在適當情況下,格式指定碼 %f%F%e%E%g%G%t 的結果是 inf-infnan (大寫也相同)。這與 Cloud Spanner SQL 將這些值的類型轉換為 STRING 的方式一致。對於 %T,Cloud Spanner SQL 會傳回 FLOAT64 值加上引號的字串,但它們沒有非字串常值表示。

FROM_BASE64

FROM_BASE64(string_expr)

說明

將採 Base64 編碼的輸入 string_expr 轉換成 BYTES 格式。若要將 BYTES 轉換成採 Base64 編碼的 STRING,請使用 TO_BASE64

傳回類型

BYTES

範例

SELECT FROM_BASE64('3q2+7w==') AS byte_data;

+------------------+
| byte_data        |
+------------------+
| \xde\xad\xbe\xef |
+------------------+

FROM_HEX

FROM_HEX(string)

說明

將採十六進位編碼的 STRING 轉換成 BYTES 格式。如果輸入 STRING 含有 (0..9, A..F, a..f) 範圍以外的字元,則會傳回錯誤。小寫字元沒有影響。若要將 BYTES 轉換成採十六進位編碼的 STRING,請使用 TO_HEX

傳回類型

BYTES

範例

SELECT hex_str, FROM_HEX(hex_str) AS bytes_str
FROM UNNEST(['00010203aaeeefff',
             '0AF',
             '666f6f626172']) as hex_str;

+------------------+----------------------------------+
| hex_str          | bytes_str                        |
+------------------+----------------------------------+
| 0AF              | \x00\xaf                         |
| 00010203aaeeefff | \x00\x01\x02\x03\xaa\xee\xef\xff |
| 666f6f626172     | foobar                           |
+------------------+----------------------------------+

LENGTH

LENGTH(value)

說明

傳回的長度。傳回值若是 STRING 引數就會以字元形式表示,若是 BYTES 引數就會以位元組形式表示。

傳回類型

INT64

範例

Table example:

+----------------+
| characters     |
+----------------+
| абвгд          |
+----------------+

SELECT
  characters,
  LENGTH(characters) AS string_example,
  LENGTH(CAST(characters AS BYTES)) AS bytes_example
FROM example;

+------------+----------------+---------------+
| characters | string_example | bytes_example |
+------------+----------------+---------------+
| абвгд      |              5 |            10 |
+------------+----------------+---------------+

LPAD

LPAD(original_value, return_length[, pattern])

說明

傳回的是由開頭帶有 patternoriginal_value 所構成。return_length 是指定傳回值之長度的 INT64。如果 original_value 是 BYTES,return_length 就是位元組數。如果 original_value 是 STRING,return_length 就是字元數。

pattern 的預設值是空格。

original_valuepattern 必須是相同的資料類型。

如果 return_length 小於或等於 original_value 長度,這個函式會傳回 original_value 值,截斷至 return_length 的值。例如,LPAD("hello world", 7); 會傳回 "hello w"

如果 original_valuereturn_lengthpattern 是 NULL,這個函式會傳回 NULL。

如果是以下情形,這個函式會傳回錯誤:

  • return_length 是負數
  • pattern 是空白

傳回類型

STRING 或 BYTES

範例

SELECT t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);
t len LPAD
abc 5 "  abc"
abc 2 "ab"
例子 4 "  例子"
SELECT t, len, pattern, FORMAT("%T", LPAD(t, len, pattern)) AS LPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);
t len 模式 LPAD
abc 8 def "defdeabc"
abc 5 - "--abc"
例子 5 中文 "中文中例子"
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", LPAD(t, len)) AS LPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);
t len LPAD
b"abc" 5 b"  abc"
b"abc" 2 b"ab"
b"\xab\xcd\xef" 4 b" \xab\xcd\xef"
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", LPAD(t, len, pattern)) AS LPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);
t len 模式 LPAD
b"abc" 8 b"def" b"defdeabc"
b"abc" 5 b"-" b"--abc"
b"\xab\xcd\xef" 5 b"\x00" b"\x00\x00\xab\xcd\xef"

LOWER

LOWER(value)

說明

對於 STRING 引數,會以小寫傳回帶有所有字母字元的原始字串。 在小寫與大寫之間對應會根據不考慮語言特定對應的 Unicode 字元資料庫完成。

對於 BYTES 引數,會將引數當成 ASCII 文字處理,所有大於 127 的位元組都會保持不變。

傳回類型

STRING 或 BYTES

範例

Table items:

+----------------+
| item           |
+----------------+
| FOO            |
| BAR            |
| BAZ            |
+----------------+

SELECT
  LOWER(item) AS example
FROM items;

+---------+
| example |
+---------+
| foo     |
| bar     |
| baz     |
+---------+

LTRIM

LTRIM(value1[, value2])

說明

TRIM 相同,但只移除前置字元。

傳回類型

STRING 或 BYTES

範例

Table items:

+----------------+
| item           |
+----------------+
|    apple       |
|    banana      |
|    orange      |
+----------------+

SELECT
  CONCAT("#", LTRIM(item), "#") as example
FROM items;

+-------------+
| example     |
+-------------+
| #apple   #  |
| #banana   # |
| #orange   # |
+-------------+

Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

SELECT
  LTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| apple***  |
| banana*** |
| orange*** |
+-----------+

Table items:

+----------------+
| item           |
+----------------+
| xxxapplexxx    |
| yyybananayyy   |
| zzzorangezzz   |
| xyzpearzyz     |
+----------------+

SELECT
  LTRIM(item, "xyz") as example
FROM items;

+-----------+
| example   |
+-----------+
| applexxx  |
| bananayyy |
| orangezzz |
| pearxyz   |
+-----------+

REGEXP_CONTAINS

REGEXP_CONTAINS(value, regex)

說明

如果 value 與規則運算式 regex 部分相符,就會傳回 TRUE。

如果 regex 引數無效,函式會傳回錯誤。

您可以使用 ^ (文字的開頭) 與 $ (文字的結尾) 搜尋完全相符的情況。基於規則運算式運算子的優先順序,為 ^$ 之間的所有文字加上圓括號是不錯的做法。

注意:Cloud Spanner SQL 使用 re2 程式庫,因此支援規則運算式;如要瞭解規則運算式語法,請參閱相關說明文件。

傳回類型

BOOL

範例

SELECT
  email,
  REGEXP_CONTAINS(email, r"@[a-zA-Z0-9-]+\.[a-zA-Z0-9-.]+") AS is_valid
FROM
  (SELECT
    ["foo@example.com", "bar@example.org", "www.example.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+-----------------+----------+
| email           | is_valid |
+-----------------+----------+
| foo@example.com | true     |
| bar@example.org | true     |
| www.example.net | false    |
+-----------------+----------+

# Performs a full match, using ^ and $. Due to regular expression operator
# precedence, it is good practice to use parentheses around everything between ^
# and $.
SELECT
  email,
  REGEXP_CONTAINS(email, r"^([\w.+-]+@foo\.com|[\w.+-]+@bar\.org)$")
    AS valid_email_address,
  REGEXP_CONTAINS(email, r"^[\w.+-]+@foo\.com|[\w.+-]+@bar\.org$")
    AS without_parentheses
FROM
  (SELECT
    ["a@foo.com", "a@foo.computer", "b@bar.org", "!b@bar.org", "c@buz.net"]
    AS addresses),
  UNNEST(addresses) AS email;

+----------------+---------------------+---------------------+
| email          | valid_email_address | without_parentheses |
+----------------+---------------------+---------------------+
| a@foo.com      | true                | true                |
| a@foo.computer | false               | true                |
| b@bar.org      | true                | true                |
| !b@bar.org     | false               | true                |
| c@buz.net      | false               | false               |
+----------------+---------------------+---------------------+

REGEXP_EXTRACT

REGEXP_EXTRACT(value, regex)

說明

傳回 value 中與規則運算式 regex 相符的第一個子字串。如果沒有相符項目,就傳回 NULL

如果規則運算式中包含擷取群組,函式會傳回與那個擷取群組相符的子字串。如果運算式不包含擷取群組,函式會傳回完整相符子字串。

在以下情況下會傳回錯誤:

  • 規則運算式無效
  • 規則運算式有一個以上的擷取群組

注意:Cloud Spanner SQL 使用 re2 程式庫,因此支援規則運算式;如要瞭解規則運算式語法,請參閱相關說明文件。

傳回類型

STRING 或 BYTES

範例

Table email_addresses:

+-------------------------+
| email                   |
+-------------------------+
| foo@example.com         |
| bar@example.com         |
| baz@example.com         |
+-------------------------+

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+")
  AS user_name
FROM email_addresses;

+-----------+
| user_name |
+-----------+
| foo       |
| bar       |
| baz       |
+-----------+

Table email_addresses:

+-------------------------+
| email                   |
+-------------------------+
| foo@example.com         |
| bar@example.com         |
| baz@example.com         |
+-------------------------+

SELECT
  REGEXP_EXTRACT(email, r"^[a-zA-Z0-9_.+-]+@[a-zA-Z0-9-]+\.([a-zA-Z0-9-.]+$)")
  AS top_level_domain
FROM email_addresses;

+------------------+
| top_level_domain |
+------------------+
| com              |
| org              |
| net              |
+------------------+

REGEXP_EXTRACT_ALL

REGEXP_EXTRACT_ALL(value, regex)

說明

傳回與規則運算式 regex 相符的 value 之所有子字串的陣列。

REGEXP_EXTRACT_ALL 函式只會傳回非重疊相符項目。例如,使用這個函式來從 banana 中擷取 ana 只會傳回一個子字串,而不是兩個字子串。

注意:Cloud Spanner SQL 使用 re2 程式庫,因此支援規則運算式;如要瞭解規則運算式語法,請參閱相關說明文件。

傳回類型

STRING 或 BYTES 的陣列

範例

Table code_markdown:

+------------------------------------+
| code                               |
+------------------------------------+
| Try `function(x)` or `function(y)` |
+------------------------------------+

SELECT
  REGEXP_EXTRACT_ALL(code, "`(.+?)`") AS example
FROM code_markdown;

+----------------------------+
| example                    |
+----------------------------+
| [function(x), function(y)] |
+----------------------------+

REGEXP_REPLACE

REGEXP_REPLACE(value, regex, replacement)

說明

傳回與規則運算式 regex 相符之 value 的所有子字串都用 replacement 取代的 STRING。

您可以在 replacement 引數內使用反斜線逸出數字 (\1 到 \9) 來在 regex 模式中插入與對應放入括號內群組相符的文字。請使用 \0 來參照完整相符文字。

注意:若要在規則運算式中加入反斜線,您必須先將它逸出。 例如,SELECT REGEXP_REPLACE("abc", "b(.)", "X\\1"); 會傳回 aXc

REGEXP_REPLACE 函式只會取代非重疊相符項目。例如,取代 banana 內的 ana 只會產生一個取代,而不是兩個。

如果 regex 引數不是有效的規則運算式,這個函式會傳回錯誤。

注意:Cloud Spanner SQL 使用 re2 程式庫,因此支援規則運算式;如要瞭解規則運算式語法,請參閱相關說明文件。

傳回類型

STRING 或 BYTES

範例

Table markdown:

+-------------------------+
| heading                 |
+-------------------------+
| # Heading               |
| # Another heading       |
+-------------------------+

SELECT
  REGEXP_REPLACE(heading, r"^# ([a-zA-Z0-9\s]+$)", "<h1>\\1</h1>")
  AS html
FROM markdown;

+--------------------------+
| html                     |
+--------------------------+
| <h1>Heading</h1>         |
| <h1>Another heading</h1> |
+--------------------------+

REPLACE

REPLACE(original_value, from_value, to_value)

說明

在 original_value 中以 to_value 取代 from_value 的所有發生次數。 如果 from_value 是空白,就不會進行取代。

傳回類型

STRING 或 BYTES

範例

+--------------------+
| dessert            |
+--------------------+
| apple pie          |
| blackberry pie     |
| cherry pie         |
+--------------------+

SELECT
  REPLACE (dessert, "pie", "cobbler") as example
FROM desserts;

+--------------------+
| example            |
+--------------------+
| apple cobbler      |
| blackberry cobbler |
| cherry cobbler     |
+--------------------+

REPEAT

REPEAT(original_value, repetitions)

說明

傳回的是由重複的 original_value 所構成。 repetitions 參數指定了重複 original_value 的次數。如果 original_valuerepetitions 是 NULL,就會傳回 NULL。

如果 repetitions 值是負數,這個函式會傳回錯誤。

傳回類型

STRING 或 BYTES

範例

SELECT t, n, REPEAT(t, n) AS REPEAT FROM UNNEST([
  STRUCT('abc' AS t, 3 AS n),
  ('例子', 2),
  ('abc', null),
  (null, 3)
]);
t n 重複
abc 3 abcabcabc
例子 2 例子例子
abc NULL NULL
NULL 3 NULL

REVERSE

REVERSE(value)

說明

傳回輸入 STRING 或 BYTES 的對調。

傳回類型

STRING 或 BYTES

範例

SELECT
  sample_string,
  REVERSE(sample_string) AS reverse_string,
  sample_bytes,
  REVERSE(sample_bytes) AS reverse_bytes
FROM (
  SELECT "foo" AS sample_string, b"bar" AS sample_bytes UNION ALL
  SELECT "абвгд" AS sample_string, b"123" AS sample_bytes) as example;

+---------------+----------------+--------------+---------------+
| sample_string | reverse_string | sample_bytes | reverse_bytes |
+---------------+----------------+--------------+---------------+
| foo           | oof            | bar          | rab           |
| абвгд         | дгвба          | 123          | 321           |
+---------------+----------------+--------------+---------------+

RPAD

RPAD(original_value, return_length[, pattern])

說明

傳回的是由結尾帶有 patternoriginal_value 所構成。return_length 是指定傳回值長度的 INT64。如果 original_value 是 BYTES,return_length 就是位元組數。如果 original_value 是 STRING,return_length 就是字元數。

pattern 的預設值是空格。

original_valuepattern 必須是相同的資料類型。

如果 return_length 小於或等於 original_value 長度,這個函式會傳回 original_value 值,截斷至 return_length 的值。例如,RPAD("hello world", 7); 會傳回 "hello w"

如果 original_valuereturn_lengthpattern 是 NULL,這個函式會傳回 NULL。

如果是以下情形,這個函式會傳回錯誤:

  • return_length 是負數
  • pattern 是空白

傳回類型

STRING 或 BYTES

範例

SELECT t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 5 AS len),
  ('abc', 2),
  ('例子', 4)
]);
t len RPAD
abc 5 "abc  "
abc 2 "ab"
例子 4 "例子  "
SELECT t, len, pattern, FORMAT("%T", RPAD(t, len, pattern)) AS RPAD FROM UNNEST([
  STRUCT('abc' AS t, 8 AS len, 'def' AS pattern),
  ('abc', 5, '-'),
  ('例子', 5, '中文')
]);
t len 模式 RPAD
abc 8 def "abcdefde"
abc 5 - "abc--"
例子 5 中文 "例子中文中"
SELECT FORMAT("%T", t) AS t, len, FORMAT("%T", RPAD(t, len)) AS RPAD FROM UNNEST([
  STRUCT(b'abc' AS t, 5 AS len),
  (b'abc', 2),
  (b'\xab\xcd\xef', 4)
]);
t len RPAD
b"abc" 5 b"abc  "
b"abc" 2 b"ab"
b"\xab\xcd\xef" 4 b"\xab\xcd\xef "
SELECT
  FORMAT("%T", t) AS t,
  len,
  FORMAT("%T", pattern) AS pattern,
  FORMAT("%T", RPAD(t, len, pattern)) AS RPAD
FROM UNNEST([
  STRUCT(b'abc' AS t, 8 AS len, b'def' AS pattern),
  (b'abc', 5, b'-'),
  (b'\xab\xcd\xef', 5, b'\x00')
]);
t len 模式 RPAD
b"abc" 8 b"def" b"abcdefde"
b"abc" 5 b"-" b"abc--"
b"\xab\xcd\xef" 5 b"\x00" b"\xab\xcd\xef\x00\x00"

RTRIM

RTRIM(value1[, value2])

說明

TRIM 相同,但只移除尾隨字元。

傳回類型

STRING 或 BYTES

範例

Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

SELECT
  RTRIM(item, "*") as example
FROM items;

+-----------+
| example   |
+-----------+
| ***apple  |
| ***banana |
| ***orange |
+-----------+

Table items:

+----------------+
| item           |
+----------------+
| applexxx       |
| bananayyy      |
| orangezzz      |
| pearxyz        |
+----------------+

SELECT
  RTRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

SAFE_CONVERT_BYTES_TO_STRING

SAFE_CONVERT_BYTES_TO_STRING(value)

說明

將位元組的順序轉換為字串。任何無效的 UTF-8 字元都會取代為 Unicode 取代字元 U+FFFD

傳回類型

STRING

範例

以下陳述式會傳回 Unicode 取代字元 �。

SELECT SAFE_CONVERT_BYTES_TO_STRING(b'\xc2') as safe_convert;

SPLIT

SPLIT(value[, delimiter])

說明

使用 delimiter 引數拆分 value

對於 STRING,預設分隔符號是逗號 ,

對於 BYTES,您必須指定分隔符號。

拆分空白分隔符號會針對 STRING 值產生 UTF-8 字元的陣列,針對 BYTES 值產生 BYTES 的陣列。

拆分空白 STRING 會傳回帶有單一空白 STRING 的 ARRAY。

傳回類型

STRUNG 類型的 ARRAY 或 BYTES 類型的 ARRAY

範例

SELECT SPLIT(letter_group, " ") as example
FROM (
  SELECT "a b c d" as letter_group
  UNION ALL SELECT "e f g h" as letter_group
  UNION ALL SELECT "i j k l" as letter_group) AS letters;

+----------------------+
| example              |
+----------------------+
| [a, b, c, d]         |
| [e, f, g, h]         |
| [i, j, k, l]         |
+----------------------+

STARTS_WITH

STARTS_WITH(value1, value2)

說明

取用兩個。如果第二個值是第一個值的前置字元,就會傳回 TRUE。

傳回類型

BOOL

範例

SELECT
  STARTS_WITH(item, "b") as example
FROM (
  SELECT "foo" as item
  UNION ALL SELECT "bar" as item
  UNION ALL SELECT "baz" as item) AS items;

+---------+
| example |
+---------+
|   False |
|    True |
|    True |
+---------+

STRPOS

STRPOS(string, substring)

說明

傳回在 string 內第一次出現 substring 的以 1 為基礎的索引。如果找不到 substring,就會傳回 0

傳回類型

INT64

範例

Table email_addresses:

+-------------------------+
| email_address           |
+-------------------------+
| foo@example.com         |
| foobar@example.com      |
| foobarbaz@example.com   |
| quxexample.com          |
+-------------------------+

SELECT
  STRPOS(email_address, "@") AS example
FROM email_addresses;

+---------+
| example |
+---------+
|       4 |
|       7 |
|      10 |
|       0 |
+---------+

SUBSTR

SUBSTR(value, position[, length])

說明

傳回提供之的子字串。position 引數是指定子字串開始位置的整數,位置 = 1 指示第一個字元或位元組。length 引數是 STRING 引數的數量上限,或 BYTES 引數的位元組上限。

如果 position 是負數,函式會從 value 的結尾開始計數,-1 指示最後一個字元。

如果 position 是 STRING 左端以外的位置 (position = 0 或 position < -LENGTH(value)),函式會從位置 = 1 開始。如果 length 超過 value 的長度,就會傳回少於 length 的字元。

如果 length 小於 0,函式會傳回錯誤。

傳回類型

STRING 或 BYTES

範例

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  SUBSTR(item, 2) as example
FROM items;

+---------+
| example |
+---------+
| pple    |
| anana   |
| range   |
+---------+

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  SUBSTR(item, 2, 2) as example
FROM items;

+---------+
| example |
+---------+
| pp      |
| an      |
| ra      |
+---------+

Table items:

+----------------+
| item           |
+----------------+
| apple          |
| banana         |
| orange         |
+----------------+

SELECT
  SUBSTR(item, -2) as example
FROM items;

+---------+
| example |
+---------+
| le      |
| na      |
| ge      |
+---------+

TO_BASE64

TO_BASE64(bytes_expr)

說明

將 BYTES 的順序轉換成採 base64 編碼的 STRING。若要將採 Base64 編碼的 STRING 轉換成 BYTES,請使用 FROM_BASE64

傳回類型

STRING

範例

SELECT TO_BASE64(b'\xde\xad\xbe\xef') AS base64_string;

+---------------+
| base64_string |
+---------------+
| 3q2+7w==      |
+---------------+

TO_CODE_POINTS

TO_CODE_POINTS(value)

說明

取用並傳回 INT64 的陣列。

  • 如果 value 是 STRING,則傳回陣列中的每個元素都代表一個碼點。每個碼點都在 [0, 0xD7FF] 與 [0xE000, 0x10FFFF] 的範圍之內。
  • 如果 value 是 BYTES,陣列中的每個元素都是 [0, 255] 範圍中的延伸 ASCII 字元值。

如要將碼點陣列轉換成 STRING 或 BYTES,請參閱 CODE_POINTS_TO_STRINGCODE_POINTS_TO_BYTES

傳回類型

INT64 的 ARRAY

範例

以下範例在字詞的陣列中取得每個元素的碼點。

SELECT word, TO_CODE_POINTS(word) AS code_points
FROM UNNEST(['foo', 'bar', 'baz', 'giraffe', 'llama']) AS word;

+---------+------------------------------------+
| word    | code_points                        |
+---------+------------------------------------+
| foo     | [102, 111, 111]                    |
| bar     | [98, 97, 114]                      |
| baz     | [98, 97, 122]                      |
| giraffe | [103, 105, 114, 97, 102, 102, 101] |
| llama   | [108, 108, 97, 109, 97]            |
+---------+------------------------------------+

以下範例將 BYTES 的整數表示轉換為它們的對應 ASCII 字元值。

SELECT word, TO_CODE_POINTS(word) AS bytes_value_as_integer
FROM UNNEST([b'\x00\x01\x10\xff', b'\x66\x6f\x6f']) AS word;

+------------------+------------------------+
| word             | bytes_value_as_integer |
+------------------+------------------------+
| \x00\x01\x10\xff | [0, 1, 16, 255]        |
| foo              | [102, 111, 111]        |
+------------------+------------------------+

以下範例展示了 BYTES 結果與 STRING 結果之間的差異。

SELECT TO_CODE_POINTS(b'Ā') AS b_result, TO_CODE_POINTS('Ā') AS s_result;

+------------+----------+
| b_result   | s_result |
+------------+----------+
| [196, 128] | [256]    |
+------------+----------+

請注意,字元 Ā 由兩位元組的 Unicode 順序表示。因此,BYTES 版本的 TO_CODE_POINTS 傳回了含有兩個元素的陣列,而 STRING 版本傳回了含有單一元素的陣列。

TO_HEX

TO_HEX(bytes)

說明

將 BYTES 的順序轉換成十六進位 STRING。將 STRING 中的每個位元組轉換成 (0..9, a..f) 範圍中的兩個十六進位字元。若要將採十六進位編碼的 STRING 轉換成 BYTES,請使用 FROM_HEX

傳回類型

STRING

範例

SELECT byte_str, TO_HEX(byte_str) AS hex_str
FROM UNNEST([b'\x00\x01\x02\x03\xAA\xEE\xEF\xFF',
             b'foobar']) AS byte_str;

+----------------------------------+------------------+
| byte_string                      | hex_string       |
+----------------------------------+------------------+
| foobar                           | 666f6f626172     |
| \x00\x01\x02\x03\xaa\xee\xef\xff | 00010203aaeeefff |
+----------------------------------+------------------+

TRIM

TRIM(value1[, value2])

說明

移除符合 value2 的所有前置與尾隨字元。若未指定 value2,會移除所有前置與尾隨空格字元 (如 Unicode 標準的定義)。如果第一個引數的類型是 BYTES,就需要第二個引數。

如果 value2 中包含一個以上的字元或位元組,函式會移除 value2 中包含的所有前置或尾隨字元或位元組。

傳回類型

STRING 或 BYTES

範例

Table items:

+----------------+
| item           |
+----------------+
|    apple       |
|    banana      |
|    orange      |
+----------------+

SELECT
  CONCAT("#", TRIM(item), "#") as example
FROM items;

+----------+
| example  |
+----------+
| #apple#  |
| #banana# |
| #orange# |
+----------+

Table items:

+----------------+
| item           |
+----------------+
| ***apple***    |
| ***banana***   |
| ***orange***   |
+----------------+

SELECT
  TRIM(item, "*") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
+---------+

Table items:

+----------------+
| item           |
+----------------+
| xxxapplexxx    |
| yyybananayyy   |
| zzzorangezzz   |
| xyzpearxyz     |
+----------------+

SELECT
  TRIM(item, "xyz") as example
FROM items;

+---------+
| example |
+---------+
| apple   |
| banana  |
| orange  |
| pear    |
+---------+

UPPER

UPPER(value)

說明

對於 STRING 引數,會傳回所有字母字元都是大寫的原始字串。 在小寫與大寫之間對應會根據不考慮語言特定對應的 Unicode 字元資料庫完成。

對於 BYTES 引數,會將引數當成 ASCII 文字處理,所有大於 127 的位元組都會保持不變。

傳回類型

STRING 或 BYTES

範例

Table items:

+----------------+
| item           |
+----------------+
| foo            |
| bar            |
| baz            |
+----------------+

SELECT
  UPPER(item) AS example
FROM items;

+---------+
| example |
+---------+
| FOO     |
| BAR     |
| BAZ     |
+---------+

JSON 函式

Cloud Spanner SQL 支援可協助您擷取以 JSON 格式字串儲存之資料的函式,也支援可協助您將資料轉換成 JSON 格式字串的函式。

JSON_QUERY 或 JSON_VALUE

JSON_QUERY(json_string_expr, json_path_string_literal),以 STRING 傳回 JSON 值。

JSON_VALUE(json_string_expr, json_path_string_literal),以 STRING 傳回純量 JSON 值。

說明

json_string_expr 參數必須是 JSON 格式字串。例如:

{"class" : {"students" : [{"name" : "Jane"}]}}

json_path_string_literal 參數可列出您要從 JSON 格式字串中取得的一或多個值。您可使用 JSONPath 格式建構這個參數。作為這個格式的一部分,這個參數必須以 $ 符號開頭,這會參照 JSON 格式字串的最外層。您可以透過點或周圍的雙引號來識別子值。如果 JSON 物件是陣列,您可以使用方括號來指定陣列索引。

JSONPath 說明
$ 根物件或元素
. 子運算子
[] 下標運算子

如果 json_path_string_literal 參數與 json_string_expr 中的值不符,這兩個函式都會傳回NULL。如果針對 JSON_VALUE 選取的值不是純量 (例如物件或陣列),函式會傳回 NULL

如果 JSONPath 無效,這些函式會產生錯誤。

如果 JSON 金鑰使用無效的 JSONPath 字元,您可以使用雙引號逸出這些字元。例如:

SELECT JSON_VALUE('{"a.b": {"c": "world"}}', '$."a.b".c') as hello;

+-------+
| hello |
+-------+
| world |
+-------+

範例

SELECT JSON_QUERY(json_text, '$') AS json_text_string
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

上述查詢會產生下列結果:

+-----------------------------------------------------------+
| json_text_string                                          |
+-----------------------------------------------------------+
| {"class":{"students":[{"name":"Jane"}]}}                  |
| {"class":{"students":[]}}                                 |
| {"class":{"students":[{"name":"John"},{"name":"Jamie"}]}} |
+-----------------------------------------------------------+
SELECT JSON_QUERY(json_text, '$.class.students[0]') AS first_student
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

上述查詢會產生下列結果:

+-----------------+
| first_student   |
+-----------------+
| {"name":"Jane"} |
| NULL            |
| {"name":"John"} |
+-----------------+
SELECT JSON_QUERY(json_text, '$.class.students[1].name') AS second_student_name
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

上述查詢會產生下列結果:

+-------------------+
| second_student    |
+-------------------+
| NULL              |
| NULL              |
| {"first":"Jamie"} |
+-------------------+
SELECT JSON_QUERY(json_text, '$.class."students"') AS student_names
FROM UNNEST([
  '{"class" : {"students" : [{"name" : "Jane"}]}}',
  '{"class" : {"students" : []}}',
  '{"class" : {"students" : [{"name" : "John"}, {"name": "Jamie"}]}}'
  ]) AS json_text;

上述查詢會產生下列結果:

+------------------------------------+
| student_names                      |
+------------------------------------+
| [{"name":"Jane"}]                  |
| []                                 |
| [{"name":"John"},{"name":"Jamie"}] |
+------------------------------------+
SELECT JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.name') as json_name,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.name') as scalar_name,
  JSON_QUERY('{ "name" : "Jakob", "age" : "6" }', '$.age') as json_age,
  JSON_VALUE('{ "name" : "Jakob", "age" : "6" }', '$.age') as scalar;

上述查詢會產生下列結果:

+-----------+-------------+----------+--------+
| json_name | scalar_name | json_age | scalar |
+-----------+-------------+----------+--------+
| "Jakob"   | Jakob       | "6"      | 6      |
+-----------+-------------+----------+--------+

Array 函式

ARRAY

ARRAY(subquery)

說明

ARRAY 函式會傳回 ARRAY子查詢中的每個資料列都有一個元素。

如果 subquery 產生標準 SQL 資料表,則該資料表只能有一個資料欄。輸出 ARRAY 中的每個元素都是資料表中資料列之單一資料欄的值。

限制

  • 子查詢沒有排序,所以輸出 ARRAY 的元素不會保證保留子查詢來源資料表中的任何順序。但是,如果子查詢包含 ORDER BY 子句,ARRAY 函式將會傳回遵循那個子句的 ARRAY
  • 如果子查詢傳回一個以上的資料欄,ARRAY 函式會傳回錯誤。
  • 如果子查詢傳回 ARRAY 類型資料欄或 ARRAY 類型資料列,則 ARRAY 函式會傳回錯誤:Cloud Spanner SQL 不支援元素類型為 ARRAYARRAY
  • 如果子查詢傳回零個資料列,ARRAY 函式就會傳回空 ARRAY。它永遠不會傳回 NULL ARRAY

傳回類型

ARRAY

範例

SELECT ARRAY
  (SELECT 1 UNION ALL
   SELECT 2 UNION ALL
   SELECT 3) AS new_array;

+-----------+
| new_array |
+-----------+
| [1, 2, 3] |
+-----------+

若要從包含多個資料欄的子查詢中建構 ARRAY,請變更使子查詢使用 SELECT AS STRUCT。現在 ARRAY 函式將會傳回 STRUCTARRAYARRAY 將會針對子查詢中的每個資料列包含一個 STRUCT,而這裡的每一個 STRUCT 都將針對那個資料列中的每個資料欄包含一個欄位。

SELECT
  ARRAY
    (SELECT AS STRUCT 1, 2, 3
     UNION ALL SELECT AS STRUCT 4, 5, 6) AS new_array;

+------------------------+
| new_array              |
+------------------------+
| [{1, 2, 3}, {4, 5, 6}] |
+------------------------+

同樣的,若要從包含一或多個 ARRAY 的子查詢中建構 ARRAY,請將子查詢變更為使用 SELECT AS STRUCT

SELECT ARRAY
  (SELECT AS STRUCT [1, 2, 3] UNION ALL
   SELECT AS STRUCT [4, 5, 6]) AS new_array;

+----------------------------+
| new_array                  |
+----------------------------+
| [{[1, 2, 3]}, {[4, 5, 6]}] |
+----------------------------+

ARRAY_CONCAT

ARRAY_CONCAT(array_expression_1 [, array_expression_n])

說明

將具有相同元素類型的一或多個陣列串連為單一陣列。

傳回類型

ARRAY

範例

SELECT ARRAY_CONCAT([1, 2], [3, 4], [5, 6]) as count_to_six;

+--------------------------------------------------+
| count_to_six                                     |
+--------------------------------------------------+
| [1, 2, 3, 4, 5, 6]                               |
+--------------------------------------------------+

ARRAY_LENGTH

ARRAY_LENGTH(array_expression)

說明

傳回陣列的大小。若是空陣列,就會傳回 0。如果 array_expressionNULL,就會傳回 NULL

傳回類型

INT64

範例

SELECT list, ARRAY_LENGTH(list) AS size
FROM (
  SELECT ["apples", "bananas", NULL, "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie"] as list) AS items
ORDER BY size DESC;

+---------------------------------+------+
| list                            | size |
+---------------------------------+------+
| [apples, bananas, NULL, grapes] | 4    |
| [coffee, tea, milk]             | 3    |
| [cake, pie]                     | 2    |
+---------------------------------+------+

ARRAY_TO_STRING

ARRAY_TO_STRING(array_expression, delimiter[, null_text])

說明

array_expression 中的元素串連傳回為 STRING。array_expression 的值可以是 STRING 或 BYTES 資料類型的陣列。

如果使用 null_text 參數,函式會將陣列中的任何  NULL 值取代為 null_text 的值。

若未使用 null_text 參數,函式會忽略 NULL 值和它的先前分隔符號。

範例

SELECT ARRAY_TO_STRING(list, '--') AS text
FROM (
  SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie", NULL] as list) AS items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie                      |
+--------------------------------+

SELECT ARRAY_TO_STRING(list, '--', 'MISSING') AS text
FROM (
  SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie", NULL] as list) AS items;

+--------------------------------+
| text                           |
+--------------------------------+
| apples--bananas--pears--grapes |
| coffee--tea--milk              |
| cake--pie--MISSING             |
+--------------------------------+

GENERATE_ARRAY

GENERATE_ARRAY(start_expression, end_expression[, step_expression])

說明

傳回值的陣列。start_expressionend_expression 參數可決定陣列的包含開始與結束。

GENERATE_ARRAY 函式接受下列資料類型作為輸入:

  • INT64
  • FLOAT64

step_expression 參數可決定用來產生陣列值的遞增量。這個參數的預設值是 1

若將 step_expression 設定為 0,或如果任何輸入是 NaN,這個函式就會傳回錯誤。

如果任何引數是 NULL,函式將會傳回 NULL 陣列。

傳回資料類型

ARRAY

範例

以下會以預設步階 1 傳回整數的陣列。

SELECT GENERATE_ARRAY(1, 5) AS example_array;

+-----------------+
| example_array   |
+-----------------+
| [1, 2, 3, 4, 5] |
+-----------------+

以下會使用使用者指定的步階大小傳回陣列。

SELECT GENERATE_ARRAY(0, 10, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| [0, 3, 6, 9]  |
+---------------+

以下會使用負值 -3 作為它的步階大小傳回陣列。

SELECT GENERATE_ARRAY(10, 0, -3) AS example_array;

+---------------+
| example_array |
+---------------+
| [10, 7, 4, 1] |
+---------------+

以下會使用 start_expressionend_expression 的相同值傳回陣列。

SELECT GENERATE_ARRAY(4, 4, 10) AS example_array;

+---------------+
| example_array |
+---------------+
| [4]           |
+---------------+

以下會傳回空陣列,因為 start_expression 大於 end_expression,且 step_expression 值為正。

SELECT GENERATE_ARRAY(10, 0, 3) AS example_array;

+---------------+
| example_array |
+---------------+
| []            |
+---------------+

以下會傳回 NULL 陣列,因為 end_expressionNULL

SELECT GENERATE_ARRAY(5, NULL, 1) AS example_array;

+---------------+
| example_array |
+---------------+
| NULL          |
+---------------+

以下會傳回多個陣列。

SELECT GENERATE_ARRAY(start, 5) AS example_array
FROM UNNEST([3, 4, 5]) AS start;

+---------------+
| example_array |
+---------------+
| [3, 4, 5]     |
| [4, 5]        |
| [5]           |
+---------------+

GENERATE_DATE_ARRAY

GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])

說明

傳回日期的陣列。start_dateend_date 參數可決定陣列的包含開始與結束。

GENERATE_DATE_ARRAY 函式會將下列資料類型接受為輸入:

  • start_date 必須是 DATE
  • end_date 必須是 DATE
  • INT64_expr 必須是 INT64
  • date_part 必須是 DAY、WEEK、MONTH、QUARTER 或 YEAR。

INT64_expr 參數可決定用來產生日期的遞增。這個參數的預設值是 1 天。

若將 INT64_expr 設定為 0,這個函式會傳回錯誤。

傳回資料類型

包含 0 或多個 DATE 值的 ARRAY。

範例

以下會以預設步階 1 傳回日期的陣列。

SELECT GENERATE_DATE_ARRAY('2016-10-05', '2016-10-08') AS example;

+--------------------------------------------------+
| example                                          |
+--------------------------------------------------+
| [2016-10-05, 2016-10-06, 2016-10-07, 2016-10-08] |
+--------------------------------------------------+

以下會使用使用者指定的步階大小傳回陣列。

SELECT GENERATE_DATE_ARRAY(
 '2016-10-05', '2016-10-09', INTERVAL 2 DAY) AS example;

+--------------------------------------+
| example                              |
+--------------------------------------+
| [2016-10-05, 2016-10-07, 2016-10-09] |
+--------------------------------------+

以下會使用負值 -3 作為它的步階大小傳回陣列。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL -3 DAY) AS example;

+--------------------------+
| example                  |
+--------------------------+
| [2016-10-05, 2016-10-02] |
+--------------------------+

以下會使用 start_dateend_date 的相同值傳回陣列。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-05', INTERVAL 8 DAY) AS example;

+--------------+
| example      |
+--------------+
| [2016-10-05] |
+--------------+

以下會傳回空陣列,因為 start_date 大於 end_date,且 step 值為正。

SELECT GENERATE_DATE_ARRAY('2016-10-05',
  '2016-10-01', INTERVAL 1 DAY) AS example;

+---------+
| example |
+---------+
| []      |
+---------+

以下會傳回 NULL 陣列,因為它的其中一個輸入是 NULL

SELECT GENERATE_DATE_ARRAY('2016-10-05', NULL) AS example;

+---------+
| example |
+---------+
| NULL    |
+---------+

以下會使用 MONTH 作為 date_part 間隔傳回日期的陣列:

SELECT GENERATE_DATE_ARRAY('2016-01-01',
  '2016-12-31', INTERVAL 2 MONTH) AS example;

+--------------------------------------------------------------------------+
| example                                                                  |
+--------------------------------------------------------------------------+
| [2016-01-01, 2016-03-01, 2016-05-01, 2016-07-01, 2016-09-01, 2016-11-01] |
+--------------------------------------------------------------------------+

以下使用非常數日期來產生陣列。

WITH StartsAndEnds AS (
  SELECT DATE '2016-01-01' AS date_start, DATE '2016-01-31' AS date_end
  UNION ALL SELECT DATE "2016-04-01", DATE "2016-04-30"
  UNION ALL SELECT DATE "2016-07-01", DATE "2016-07-31"
  UNION ALL SELECT DATE "2016-10-01", DATE "2016-10-31"
)
SELECT GENERATE_DATE_ARRAY(date_start, date_end, INTERVAL 1 WEEK) AS date_range
FROM StartsAndEnds;

+--------------------------------------------------------------+
| date_range                                                   |
+--------------------------------------------------------------+
| [2016-01-01, 2016-01-08, 2016-01-15, 2016-01-22, 2016-01-29] |
| [2016-04-01, 2016-04-08, 2016-04-15, 2016-04-22, 2016-04-29] |
| [2016-07-01, 2016-07-08, 2016-07-15, 2016-07-22, 2016-07-29] |
| [2016-10-01, 2016-10-08, 2016-10-15, 2016-10-22, 2016-10-29] |
+--------------------------------------------------------------+

OFFSET 與 ORDINAL

array_expression[OFFSET(zero_based_offset)]
array_expression[ORDINAL(one_based_offset)]

說明

存取特定位置的 ARRAY 元素,並傳回該元素的值。OFFSET 表示編號從零開始,ORDINAL 表示編號從一開始。

指定陣列可解讀為以 0 為基礎或以 1 為基礎。存取陣列元素時,您必須在陣列位置前面分別加上 OFFSETORDINAL;沒有預設行為。

如果索引超出範圍,OFFSETORDINAL 會產生錯誤。

傳回類型

因 ARRAY 中的元素而不同。

範例

SELECT list, list[OFFSET(1)] as offset_1, list[ORDINAL(1)] as ordinal_1
FROM (
  SELECT ["apples", "bananas", "pears", "grapes"] as list
  UNION ALL SELECT ["coffee", "tea", "milk" ] as list
  UNION ALL SELECT ["cake", "pie"] as list) AS items;

+----------------------------------+-----------+-----------+
| list                             | offset_1  | ordinal_1 |
+----------------------------------+-----------+-----------+
| [apples, bananas, pears, grapes] | bananas   | apples    |
| [coffee, tea, milk]              | tea       | coffee    |
| [cake, pie]                      | pie       | cake      |
+----------------------------------+-----------+-----------+

ARRAY_REVERSE

ARRAY_REVERSE(value)

說明

以元素的對調順序傳回輸入 ARRAY。

傳回類型

ARRAY

範例

SELECT
  arr,
  ARRAY_REVERSE(arr) AS reverse_arr
FROM (
  SELECT [1, 2, 3] AS arr UNION ALL
  SELECT [4, 5] AS arr UNION ALL
  SELECT [] AS arr) AS example;

+-----------+-------------+
| arr       | reverse_arr |
+-----------+-------------+
| [1, 2, 3] | [3, 2, 1]   |
| [4, 5]    | [5, 4]      |
| []        | []          |
+-----------+-------------+

Date 函式

Cloud Spanner SQL 支援下列 DATE 函式。

CURRENT_DATE

CURRENT_DATE([time_zone])

說明

傳回指定或預設時區的目前日期。

這個函式支援選用 time_zone 參數。這個參數是代表要使用之時區的字串。如果沒有指定時區,就會使用預設時區 America/Los_Angeles。請參閱時區定義,瞭解如何指定時區。

如果 time_zone 參數評估為 NULL,這個函式會傳回 NULL

傳回資料類型

DATE

範例

SELECT CURRENT_DATE() as the_date;

+--------------+
| the_date     |
+--------------+
| 2016-12-25   |
+--------------+

EXTRACT

EXTRACT(part FROM date_expression)

說明

傳回對應於指定日期部分的值。part 必須是以下其中一項:

  • DAYOFWEEK:以星期日作為一週的第一天傳回 [1,7] 範圍中的值。
  • DAY
  • DAYOFYEAR
  • WEEK:傳回 [0, 53] 範圍中日期的週數。一週以星期日開始,而一年中第一個星期日之前的日期為第 0 週。

  • ISOWEEK:傳回 date_expressionISO 8601 週數。ISOWEEK 以星期一開始。傳回值位於 [1, 53] 範圍中。每個 ISO 年的第一個 ISOWEEK 從公曆年第一個星期四之前的星期一開始。

  • MONTH
  • QUARTER:傳回 [1,4] 範圍中的值。
  • YEAR
  • ISOYEAR:傳回 ISO 8601 週數年,這是包含 date_expression 所屬之星期四的公曆年。

傳回資料類型

INT64

範例

在以下範例中,EXTRACT 會傳回對應於 DAY 時間部分的值。

SELECT EXTRACT(DAY FROM DATE '2013-12-25') as the_day;

+---------+
| the_day |
+---------+
| 25      |
+---------+

在以下範例中,EXTRACT 會傳回接近年底的日期資料欄中時間部分的對應值。

SELECT
  date,
  EXTRACT(ISOYEAR FROM date) AS isoyear,
  EXTRACT(ISOWEEK FROM date) AS isoweek,
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(WEEK FROM date) AS week
FROM UNNEST(GENERATE_DATE_ARRAY('2015-12-23', '2016-01-09')) AS date
ORDER BY date;
+------------+---------+---------+------+------+
| date       | isoyear | isoweek | year | week |
+------------+---------+---------+------+------+
| 2015-12-23 | 2015    | 52      | 2015 | 51   |
| 2015-12-24 | 2015    | 52      | 2015 | 51   |
| 2015-12-25 | 2015    | 52      | 2015 | 51   |
| 2015-12-26 | 2015    | 52      | 2015 | 51   |
| 2015-12-27 | 2015    | 52      | 2015 | 52   |
| 2015-12-28 | 2015    | 53      | 2015 | 52   |
| 2015-12-29 | 2015    | 53      | 2015 | 52   |
| 2015-12-30 | 2015    | 53      | 2015 | 52   |
| 2015-12-31 | 2015    | 53      | 2015 | 52   |
| 2016-01-01 | 2015    | 53      | 2016 | 0    |
| 2016-01-02 | 2015    | 53      | 2016 | 0    |
| 2016-01-03 | 2015    | 53      | 2016 | 1    |
| 2016-01-04 | 2016    | 1       | 2016 | 1    |
| 2016-01-05 | 2016    | 1       | 2016 | 1    |
| 2016-01-06 | 2016    | 1       | 2016 | 1    |
| 2016-01-07 | 2016    | 1       | 2016 | 1    |
| 2016-01-08 | 2016    | 1       | 2016 | 1    |
| 2016-01-09 | 2016    | 1       | 2016 | 1    |
+------------+---------+---------+------+------+

DATE

1. DATE(year, month, day)
2. DATE(timestamp_expression[, timezone])

說明

  1. 從表示年、月與日的 INT64 值中建構 DATE。
  2. timestamp_expression 轉換為 DATE 資料類型。它支援指定時區的選用參數。如果沒有指定時區,就會使用預設時區 America/Los_Angeles。

傳回資料類型

DATE

範例

SELECT
  DATE(2016, 12, 25) as date_ymd,
  DATE(TIMESTAMP "2016-12-25 05:30:00+07", "America/Los_Angeles") as date_tstz;

+------------+------------+
| date_ymd   | date_tstz  |
+------------+------------+
| 2016-12-25 | 2016-12-24 |
+------------+------------+

DATE_ADD

DATE_ADD(date_expression, INTERVAL INT64_expr date_part)

說明

在 DATE 中加上指定時間間隔。

DATE_ADD 支援下列 date_part 值:

  • DAY
  • WEEK。等於 7 DAY
  • MONTH
  • QUARTER
  • YEAR

當日期位於 (或接近) 一個月的最後一天時,MONTH、QUARTER 與 YEAR 部分需要特殊處理。如果結果月的天數少於原始日期天數,結果日就是新月的最後一天。

傳回資料類型

DATE

範例

SELECT DATE_ADD(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_later;

+--------------------+
| five_days_later    |
+--------------------+
| 2008-12-30         |
+--------------------+

DATE_SUB

DATE_SUB(date_expression, INTERVAL INT64_expr date_part)

說明

從 DATE 中減去指定時間間隔。

DATE_SUB 支援下列 date_part 值:

  • DAY
  • WEEK。等於 7 DAY
  • MONTH
  • QUARTER
  • YEAR

當日期位於 (或接近) 一個月的最後一天時,MONTH、QUARTER 與 YEAR 部分需要特殊處理。如果結果月的天數少於原始日期天數,結果日就是新月的最後一天。

傳回資料類型

DATE

範例

SELECT DATE_SUB(DATE "2008-12-25", INTERVAL 5 DAY) as five_days_ago;

+---------------+
| five_days_ago |
+---------------+
| 2008-12-20    |
+---------------+

DATE_DIFF

DATE_DIFF(date_expression, date_expression, date_part)

說明

傳回兩個 date_expression 之間的 date_part 邊界數。 如果第一個日期發生在第二個日期之前,結果就是非負數。

DATE_DIFF 支援下列 date_part 值:

  • DAY
  • WEEK 這個日期部分從星期日開始。
  • MONTH
  • QUARTER
  • YEAR

傳回資料類型

INT64

範例

SELECT DATE_DIFF(DATE '2010-07-07', DATE '2008-12-25', DAY) as days_diff;

+-----------+
| days_diff |
+-----------+
| 559       |
+-----------+
SELECT
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', DAY) as days_diff,
  DATE_DIFF(DATE '2017-10-15', DATE '2017-10-14', WEEK) as weeks_diff;

+-----------+------------+
| days_diff | weeks_diff |
+-----------+------------+
| 1         | 1          |
+-----------+------------+

以上範例顯示連續兩天的 DATE_DIFF 結果。 日期部分為 WEEKDATE_DIFF 會傳回 1,因為 DATE_DIFF 會在這個日期範圍中計算日期部分邊界數。每個 WEEK 都從星期日開始,因此在 2017-10-14 星期六與 2017-10-15 星期日之間有一個日期部分邊界。

DATE_TRUNC

DATE_TRUNC(date_expression, date_part)

說明

將日期截斷至指定精細程度。

DATE_TRUNC 支援 date_part 的下列值:

  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

傳回資料類型

DATE

範例

SELECT DATE_TRUNC(DATE '2008-12-25', MONTH) as month;

+------------+
| month      |
+------------+
| 2008-12-01 |
+------------+

DATE_FROM_UNIX_DATE

DATE_FROM_UNIX_DATE(INT64_expression)

說明

INT64_expression 解讀為從 1970-01-01 算起的天數。

傳回資料類型

DATE

範例

SELECT DATE_FROM_UNIX_DATE(14238) as date_from_epoch;

+-----------------+
| date_from_epoch |
+-----------------+
| 2008-12-25      |
+-----------------+

FORMAT_DATE

FORMAT_DATE(format_string, date_expr)

說明

根據指定的 format_string 格式化 date_expr

如需這個函式支援的格式元素清單,請參閱 DATE 的支援格式元素

傳回資料類型

STRING

範例

SELECT FORMAT_DATE("%x", DATE "2008-12-25") as US_format;

+------------+
| US_format  |
+------------+
| 12/25/08   |
+------------+

PARSE_DATE

PARSE_DATE(format_string, date_string)

說明

使用 format_string 與日期的字串表示傳回 DATE 物件。

使用 PARSE_DATE 時,請注意下列幾點:

  • 未指定的欄位。 任何未指定的欄位都從 1970-01-01 開始。
  • 區分大小寫的名稱。 例如 MondayFebruary 等的名稱都區分大小寫。
  • 空格字元。 格式字串中的一或多個連續空格字元都符合日期字串中的零或多個連續空格字元。此外,一律允許日期字串中的前置與尾隨空格字元 -- 即使它們不在格式字串中也一樣。
  • 格式優先順序。 當二 (或多) 個格式元素具有重疊資訊 (例如 %F%Y 都影響到年) 時,最後一個格式元素一般會覆寫任何之前的格式元素。

如需這個函式支援的格式元素清單,請參閱 DATE 的支援格式元素

傳回資料類型

DATE

範例

SELECT PARSE_DATE("%x", "12/25/08") as parsed;

+------------+
| parsed     |
+------------+
| 2008-12-25 |
+------------+

UNIX_DATE

UNIX_DATE(date_expression)

說明

傳回從 1970-01-01 算起的天數。

傳回資料類型

INT64

範例

SELECT UNIX_DATE(DATE "2008-12-25") as days_from_epoch;

+-----------------+
| days_from_epoch |
+-----------------+
| 14238           |
+-----------------+

DATE 的支援格式元素

除非另外提及,否則使用格式字串的 DATE 函式都支援下列元素:

格式元素 說明
%A 完整星期幾名稱。
%a 縮寫的星期幾名稱。
%B 完整月份名稱。
%b 或 %h 縮寫的月份名稱。
%C 以十進位數字 (00-99) 表示的世紀 (除以 100 並截斷至整數的年份)。
%D %m/%d/%y 格式的日期。
%d 以十進位數字 (01-31) 表示的每月日期。
%e 以十進位數字 (1-31) 表示的每月日期;單一數字前面會加上空格。
%F %Y-%m-%d 格式的日期。
%G 以十進位數字表示之帶有世紀的 ISO 8601 年份。每一個 ISO 年開始於公曆年第一個週四之前的週一。請注意 %G 和 %Y 可能在公曆年的界線附近產生不同的結果,其中公曆年和 ISO 年可能會有差異。
%g 以十進位數字 (00-99) 表示之不帶有世紀的 ISO 8601 年份。每一個 ISO 年開始於公曆年第一個週四之前的週一。請注意 %g 和 %y 可能在公曆年的界線附近產生不同的結果,其中公曆年和 ISO 年可能會有差異。
%j 以十進位數字 (001-366) 表示的每年日期。
%m 以十進位數字 (01-12) 表示的月份。
%n 換行字元。
%t 定位字元。
%U 以十進位數字 (00-53) 表示的每年週數 (星期日是每週的第一天)。
%u 以十進位數字 (1-7) 表示的星期幾 (星期一是每週的第一天)。
%V 以十進位數字 (01-53) 表示的每年週數 (星期一是每週的第一天)。 如果包含 1 月 1 日的這一週在新的一年中有四天或四天以上,它就是第 1 週;否則它就是上一年的第 53 週,而下一週才是第 1 週。
%W 以十進位數字 (00-53) 表示的每年週數 (星期一是每週的第一天)。
%w 以十進位數字 (0-6) 表示的星期幾 (星期日是每週的第一天)。
%x MM/DD/YY 格式的日期表示。
%Y 以十進位數字表示的帶世紀年份。
%y 以十進位數字 (00-99) 表示的不帶世紀的年份,可選用前置零。可與 %C 混合。若未指定 %C,00-68 年屬於 2000 年這個世紀,69-99 年屬於 1900 年這個世紀。
%E4Y 四個字元的年份 (0001 ... 9999)。請注意,%Y 會產生完整轉譯年份所需之盡可能多的字元數。

Timestamp 函式

Cloud Spanner SQL 支援下列 TIMESTAMP 函式。

注意:如果發生溢位,這些函式會傳回執行階段錯誤;結果值受限於定義的日期與時間戳記值下限/上限。

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP()

說明

括號是選用的。這個函式可在插入的閏秒前後 20 個小時範圍內模糊化閏秒,藉以處理閏秒。 CURRENT_TIMESTAMP() 可產生連續、不模糊、每分鐘為正好 60 秒且不在遇到閏秒時重複值的 TIMESTAMP 值。

支援的輸入類型

不適用

結果資料類型

TIMESTAMP

範例

SELECT CURRENT_TIMESTAMP() as now;

+----------------------------------+
| now                              |
+----------------------------------+
| 2016-05-16 18:12:47.145482639+00 |
+----------------------------------+

EXTRACT

EXTRACT(part FROM timestamp_expression [AT TIME ZONE tz_spec])

說明

從提供的 timestamp_expression 傳回對應於指定 partINT64 值。

允許的 part 值有:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAYOFWEEK
  • DAY
  • DAYOFYEAR
  • WEEK:傳回 [0, 53] 範圍中日期的週數。一週以星期日開始,而一年中第一個星期日之前的日期為第 0 週。

  • ISOWEEK:傳回 datetime_expressionISO 8601 週數。ISOWEEK 以星期一開始。傳回值位於 [1, 53] 範圍中。每個 ISO 年的第一個 ISOWEEK 從公曆年第一個星期四之前的星期一開始。

  • MONTH
  • QUARTER
  • YEAR
  • ISOYEAR:傳回 ISO 8601 週數年,這是包含 date_expression 所屬之星期四的公曆年。
  • DATE

傳回值會截斷順序較低的時段。例如,當擷取秒時,EXTRACT 會截斷毫秒與微秒值。

請參閱時區定義,瞭解如何指定時區。

傳回資料類型

一般為 INT64。如果 partDATE 就會傳回 DATE

在以下範例中,EXTRACT 會傳回時間戳記資料欄中不同時間部分的對應值。

SELECT
  timestamp,
  EXTRACT(ISOYEAR FROM timestamp) AS isoyear,
  EXTRACT(ISOWEEK FROM timestamp) AS isoweek,
  EXTRACT(YEAR FROM timestamp) AS year,
  EXTRACT(WEEK FROM timestamp) AS week
FROM (
    SELECT TIMESTAMP '2005-01-03 12:34:56' AS timestamp UNION ALL
    SELECT TIMESTAMP '2007-12-31' UNION ALL
    SELECT TIMESTAMP '2009-01-01' UNION ALL
    SELECT TIMESTAMP '2009-12-31' UNION ALL
    SELECT TIMESTAMP '2017-01-02' UNION ALL
    SELECT TIMESTAMP '2017-05-26'
  ) AS Timestamps
ORDER BY timestamp;
+------------------------+---------+---------+------+------+
| timestamp              | isoyear | isoweek | year | week |
+------------------------+---------+---------+------+------+
| 2005-01-03 12:34:56+00 | 2005    | 1       | 2005 | 1    |
| 2007-12-31 00:00:00+00 | 2008    | 1       | 2007 | 52   |
| 2009-01-01 00:00:00+00 | 2009    | 1       | 2009 | 0    |
| 2009-12-31 00:00:00+00 | 2009    | 53      | 2009 | 52   |
| 2017-01-02 00:00:00+00 | 2017    | 1       | 2017 | 1    |
| 2017-05-26 00:00:00+00 | 2017    | 21      | 2017 | 21   |
+------------------------+---------+---------+------+------+

字串

STRING(timestamp_expression[, timezone])

說明

timestamp_expression 轉換為 STRING 資料類型。支援指定時區的選用參數。請參閱時區定義,瞭解如何指定時區。

傳回資料類型

STRING

TIMESTAMP

1. TIMESTAMP(string_expression[, timezone])
2. TIMESTAMP(date_expression[, timezone])

說明

  1. 將 STRING 運算式轉換為 TIMESTAMP 資料類型。

  2. 將 DATE 物件轉換為 TIMESTAMP 資料類型。

這個函式支援指定時區的選用參數。如果沒有指定時區,就會使用預設時區 America/Los_Angeles。

傳回資料類型

TIMESTAMP

範例

SELECT
  CAST(TIMESTAMP("2008-12-25 15:30:00", "America/Los_Angeles") AS STRING) AS timestamp_str,
  CAST(TIMESTAMP(DATE "2008-12-25", "America/Los_Angeles") AS STRING) AS timestamp_date;

+------------------------+------------------------+
| timestamp_str          | timestamp_date         |
+------------------------+------------------------+
| 2008-12-25 15:30:00-08 | 2008-12-25 00:00:00-08 |
+------------------------+------------------------+

TIMESTAMP_ADD

TIMESTAMP_ADD(timestamp_expression, INTERVAL int64_expression date_part)

說明

將 date_partint64_expression 單位加入至時間戳記,與任何時區無關。

TIMESTAMP_ADD 支援 date_part 的下列值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等於 60 MINUTE
  • DAY。等於 24 HOUR

傳回資料類型

TIMESTAMP

範例

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_ADD(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS later;

+------------------------+------------------------+
| original               | later                  |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:40:00+00 |
+------------------------+------------------------+

TIMESTAMP_SUB

TIMESTAMP_SUB(timestamp_expression, INTERVAL int64_expression date_part)

說明

從時間戳記中減去 date_partint64_expression 單位,與任何時區無關。

TIMESTAMP_SUB 支援 date_part 的下列值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等於 60 MINUTE
  • DAY。等於 24 HOUR

傳回資料類型

TIMESTAMP

範例

SELECT
  TIMESTAMP "2008-12-25 15:30:00 UTC" as original,
  TIMESTAMP_SUB(TIMESTAMP "2008-12-25 15:30:00 UTC", INTERVAL 10 MINUTE) AS earlier;

+------------------------+------------------------+
| original               | earlier                |
+------------------------+------------------------+
| 2008-12-25 15:30:00+00 | 2008-12-25 15:20:00+00 |
+------------------------+------------------------+

TIMESTAMP_DIFF

TIMESTAMP_DIFF(timestamp_expression, timestamp_expression, date_part)

說明

傳回兩個時間戳記之間的完整指定 date_part 間隔數。第一個 timestamp_expression 代表較晚的日期;如果第一個 timestamp_expression 早於第二個 timestamp_expression,就會輸出負數。如果計算溢位結果類型就會擲回錯誤,例如兩個時間戳記之間的奈秒差異就會使 INT64 值溢位。

TIMESTAMP_DIFF 支援 date_part 的下列值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR。等於 60 MINUTE
  • DAY。等於 24 HOUR

傳回資料類型

INT64

範例

SELECT
  TIMESTAMP "2010-07-07 10:20:00 UTC" as later_timestamp,
  TIMESTAMP "2008-12-25 15:30:00 UTC" as earlier_timestamp,
  TIMESTAMP_DIFF(TIMESTAMP "2010-07-07 10:20:00 UTC",
    TIMESTAMP "2008-12-25 15:30:00 UTC", HOUR) AS hours;

+------------------------+------------------------+-------+
| later_timestamp        | earlier_timestamp      | hours |
+------------------------+------------------------+-------+
| 2010-07-07 10:20:00+00 | 2008-12-25 15:30:00+00 | 13410 |
+------------------------+------------------------+-------+

在下列範例中,由於第一個時間戳記早於第二個時間戳記,因此會輸出負數。

SELECT TIMESTAMP_DIFF(TIMESTAMP "2018-08-14", TIMESTAMP "2018-10-14", DAY);

+---------------+
| negative_diff |
+---------------+
| -61           |
+---------------+

TIMESTAMP_TRUNC

TIMESTAMP_TRUNC(timestamp_expression, date_part[, time_zone])

說明

將時間戳記截斷至 date_part 的精細程度。

TIMESTAMP_TRUNC 支援 date_part 的下列值:

  • NANOSECOND
  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

TIMESTAMP_TRUNC 函式支援選用 time_zone 參數。這個參數適用於下列 date_parts

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR

如果您想要使用預設時區 America/Los_Angeles 以外的時區做為截斷操作的一部分,請使用這個參數。

TIMESTAMP 截斷成 MINUTEHOUR 時,TIMESTAMP_TRUNC 會判斷 TIMESTAMP 在指定 (或預設) 的時區中的民用時間,然後減去由該 TIMESTAMP 得來的分鐘和秒數 (當截斷成 HOUR 時) 或秒數 (當截斷成 MINUTE 時)。雖然這個方式在多數情況下可提供符合直覺的結果,但在日光節約轉換期間,時間未經校正的情況下,此結果不符合直覺。

傳回資料類型

TIMESTAMP

範例

SELECT
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'UTC') as utc,
  TIMESTAMP_TRUNC(TIMESTAMP '2008-12-25 15:30:00', DAY, 'America/Los_Angeles') as la;

+------------------------+------------------------+
| utc                    | la                     |
+------------------------+------------------------+
| 2008-12-25 00:00:00+00 | 2008-12-25 08:00:00+00 |
+------------------------+------------------------+

FORMAT_TIMESTAMP

FORMAT_TIMESTAMP(format_string, timestamp[, time_zone])

說明

根據指定的 format_string 格式化時間戳記。

如需這個函式支援的格式元素清單,請參閱 TIMESTAMP 的支援格式元素

傳回資料類型

STRING

範例

SELECT FORMAT_TIMESTAMP("%c", TIMESTAMP "2008-12-25 15:30:00", "America/Los_Angeles")
  AS formatted;

+--------------------------+
| formatted                |
+--------------------------+
| Thu Dec 25 07:30:00 2008 |
+--------------------------+

PARSE_TIMESTAMP

PARSE_TIMESTAMP(format_string, string[, time_zone])

說明

使用 format_string 與時間戳記的字串表示傳回 TIMESTAMP 物件。

使用 PARSE_TIMESTAMP 時,請注意下列幾點:

  • 未指定的欄位。 任何未指定的欄位都從 1970-01-01 00:00:00.0 開始。這個初始化值使用函式的時區引數指定的時區 (如果有的話)。如果沒有,初始化值會使用預設時區 America/Los_Angeles。例如,若未指定年份,它的預設就是 1970,依此類推。
  • 區分大小寫的名稱。 例如 MondayFebruary 等的名稱都區分大小寫。
  • 空格字元。 格式字串中的一或多個連續空格字元都符合時間戳記字串中的零或多個連續空格字元。此外,一律允許時間戳記字串中的前置與尾隨空格字元 -- 即使它們不在格式字串中也一樣。
  • 格式優先順序。 當二 (或多) 個格式元素具有重疊資訊 (例如 %F%Y 都影響到年) 時,最後一個格式元素一般會覆寫任何之前的格式元素,但有一些例外 (請參閱 %s%C%y 的說明)。

如需這個函式支援的格式元素清單,請參閱 TIMESTAMP 的支援格式元素

傳回資料類型

TIMESTAMP

範例

SELECT PARSE_TIMESTAMP("%c", "Thu Dec 25 07:30:00 2008", "America/Los_Angeles") as parsed;

+-------------------------+
| parsed                  |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_SECONDS

TIMESTAMP_SECONDS(int64_expression)

說明

int64_expression 解讀為從 1970-01-01 00:00:00 UTC 算起的秒數。

傳回資料類型

TIMESTAMP

範例

SELECT TIMESTAMP_SECONDS(1230219000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MILLIS

TIMESTAMP_MILLIS(int64_expression)

說明

int64_expression 解讀為從 1970-01-01 00:00:00 UTC 算起的毫秒數。

傳回資料類型

TIMESTAMP

範例

SELECT TIMESTAMP_MILLIS(1230219000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

TIMESTAMP_MICROS

TIMESTAMP_MICROS(int64_expression)

說明

int64_expression 解讀為從 1970-01-01 00:00:00 UTC 算起的微秒數。

傳回資料類型

TIMESTAMP

範例

SELECT TIMESTAMP_MICROS(1230219000000000) as timestamp;

+-------------------------+
| timestamp               |
+-------------------------+
| 2008-12-25 15:30:00 UTC |
+-------------------------+

UNIX_SECONDS

UNIX_SECONDS(timestamp_expression)

說明

傳回從 1970-01-01 00:00:00 UTC 算起的秒數。會截斷較高層級的精確度。

傳回資料類型

INT64

範例

SELECT UNIX_SECONDS(TIMESTAMP "2008-12-25 15:30:00") as seconds;

+------------+
| seconds    |
+------------+
| 1230219000 |
+------------+

UNIX_MILLIS

UNIX_MILLIS(timestamp_expression)

說明

傳回從 1970-01-01 00:00:00 UTC 算起的毫秒數。會截斷較高層級的精確度。

傳回資料類型

INT64

範例

SELECT UNIX_MILLIS(TIMESTAMP "2008-12-25 15:30:00 UTC") as millis;

+---------------+
| millis        |
+---------------+
| 1230219000000 |
+---------------+

UNIX_MICROS

UNIX_MICROS(timestamp_expression)

說明

傳回從 1970-01-01 00:00:00 UTC 算起的微秒數。會截斷較高層級的精確度。

傳回資料類型

INT64

範例

SELECT UNIX_MICROS(TIMESTAMP "2008-12-25 15:30:00") as micros;

+------------------+
| micros           |
+------------------+
| 1230219000000000 |
+------------------+

TIMESTAMP 的支援格式元素

除非另外提及,否則使用格式字串的 TIMESTAMP 函式都支援下列元素:

格式元素 說明
%A 完整星期幾名稱。
%a 縮寫的星期幾名稱。
%B 完整月份名稱。
%b 或 %h 縮寫的月份名稱。
%C 以十進位數字 (00-99) 表示的世紀 (除以 100 並截斷至整數的年份)。
%c 日期與時間表示。
%D %m/%d/%y 格式的日期。
%d 以十進位數字 (01-31) 表示的每月日期。
%e 以十進位數字 (1-31) 表示的每月日期;單一數字前面會加上空格。
%F %Y-%m-%d 格式的日期。
%G 以十進位數字表示之帶有世紀的 ISO 8601 年份。每一個 ISO 年開始於公曆年第一個週四之前的週一。請注意 %G 和 %Y 可能在公曆年的界線附近產生不同的結果,其中公曆年和 ISO 年可能會有差異。
%g 以十進位數字 (00-99) 表示之不帶有世紀的 ISO 8601 年份。每一個 ISO 年開始於公曆年第一個週四之前的週一。請注意 %g 和 %y 可能在公曆年的界線附近產生不同的結果,其中公曆年和 ISO 年可能會有差異。
%H 以十進位數字 (00-23) 表示的小時 (24 小時制)。
%I 以十進位數字 (01-12) 表示的小時 (12 小時制)。
%j 以十進位數字 (001-366) 表示的每年日期。
%k 以十進位數字 (0-23) 表示的小時 (24 小時制);單一數字前面會加上空格。
%l 以十進位數字 (1-12) 表示的小時 (12 小時制);單一數字前面會加上空格。
%M 以十進位數字 (00-59) 表示的分鐘。
%m 以十進位數字 (01-12) 表示的月份。
%n 換行字元。
%P am 或 pm。
%p AM 或 PM。
%R %H:%M 格式的時間。
%r 使用 AM/PM 標記法的 12 小時制時間。
%S 以十進位數字 (00-60) 表示的秒。
%s 從 1970-01-01 00:00:00 UTC 算起的秒數。一律覆寫其他所有格式元素,無論 %s 出現在字串中的哪個位置。 如果出現多個 %s 元素,以最後一個為優先。
%T %H:%M:%S 格式的時間。
%t 定位字元。
%U 以十進位數字 (00-53) 表示的每年週數 (星期日是每週的第一天)。
%u 以十進位數字 (1-7) 表示的星期幾 (星期一是每週的第一天)。
%V 以十進位數字 (01-53) 表示的每年週數 (星期一是每週的第一天)。 如果包含 1 月 1 日的這一週在新的一年中有四天或四天以上,它就是第 1 週;否則它就是上一年的第 53 週,而下一週才是第 1 週。
%W 以十進位數字 (00-53) 表示的每年週數 (星期一是每週的第一天)。
%w 以十進位數字 (0-6) 表示的星期幾 (星期日是每週的第一天)。
%X HH:MM:SS 格式的時間表示。
%x MM/DD/YY 格式的日期表示。
%Y 以十進位數字表示的帶世紀年份。
%y 以十進位數字 (00-99) 表示的不帶世紀的年份,可選用前置零。可與 %C 混合。若未指定 %C,00-68 年屬於 2000 年這個世紀,69-99 年屬於 1900 年這個世紀。
%Z 時區名稱。
%z 視情況以 +HHMM 或 -HHMM 格式表示之與本初子午線之間的偏移,正值表示格林威治以東的位置。
%% 單一 % 字元。
%Ez RFC 3339 相容數字時區 (+HH:MM 或 -HH:MM)。
%E#S 帶有分數精確度位數的秒。
%E*S 帶有完整分數精確度 (常值「*」) 的秒。
%E4Y 四個字元的年份 (0001 ... 9999)。請注意,%Y 會產生完整轉譯年份所需之盡可能多的字元數。

時區定義

某些 date 與 timestamp 函式允許您覆寫預設時區並指定其他時區。您可以使用下列格式提供時區的 UTC 偏移來指定時區:

(+|-)H[H][:M[M]]

例如:

-08:00

運算子

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

常見慣例:

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

下表從最高優先順序到最低優先順序 (也就是將在陳述式內接受評估的順序) 列出所有 Cloud Spanner SQL 運算子。

優先順序 運算子 輸入資料類型 名稱 運算子引數數目
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

而非:

x < y IS FALSE

元素存取運算子

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

算術運算子

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

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

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

加與減的結果類型:

 INT64FLOAT64
INT64INT64FLOAT64
FLOAT64FLOAT64FLOAT64

減的結果類型:

 INT64FLOAT64
INT64INT64FLOAT64
FLOAT64FLOAT64FLOAT64

除的結果類型:

 INT64FLOAT64
INT64FLOAT64FLOAT64
FLOAT64FLOAT64FLOAT64

一元減的結果類型:

輸入資料類型 結果資料類型
INT64 INT64
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。比較通常需要兩個類型相同的運算元。如果運算元屬於不同類型,且 Cloud Spanner SQL 可以將那些類型的值轉換成一般類型而不會減損精確度,Cloud Spanner SQL 通常會將它們強制轉換成比較常用的類型;Cloud Spanner SQL 通常會將常值強制轉成非常值類型 (如果有的話)。請參閱資料類型,瞭解可比較的資料類型定義。

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。
介於 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 在 IN 清單中帶有 NULL 只能傳回 TRUE 或 NULL,永遠不會傳回 FALSE
  • NULL IN (NULL) 會傳回 NULL
  • IN UNNEST(<NULL array>) 會傳回 FALSE (非 NULL)

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

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

如需這個語法的詳細資訊,請參閱「資料類型」主題的結構類型一節。

IS 運算子

IS 運算子會針對它們測試的情況傳回 TRUE 或 FALSE。與Mathematical 函式中定義的 IS_INF 與 IS_NAN 函式不同,即使是 NULL 輸入,IS 運算子也絕不會傳回 NULL。若「不」存在,會反轉輸出 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。

條件運算式

條件運算式會對運算式的評估順序加上限制。基本上,它們會從左向右進行短路評估,並只評估選擇的輸出值。相對的,對於正常函式的所有輸入都會在呼叫函式之前評估。條件運算式中的短路可以用來處理錯誤或進行調整。

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

CASE expr
  WHEN value THEN result
  [WHEN ...]
  [ELSE else_result]
  END
exprvalue:不限類型 resultelse_result:輸入類型的超級類型。 expr 與每個連續 WHEN 子句的值進行比較並傳回第一個結果,其中的這個比較會傳回 true。不會評估剩餘的 WHEN 子句與 else_result。如果 expr = value 比較傳回 false 或針對所有 WHEN 子句傳回 NULL,就會傳回 else_result (如果有的話);如果沒有,則傳回 NULL,且 exprvalue 運算式必須自動強制轉換為共通的超級類型;這個運算式會比較強制轉換後的值,確認彼此是否相等。resultelse_result 運算式必須可強制轉換為共通的超級類型。

CASE
  WHEN cond1 THEN result
  [WHEN cond2...]
  [ELSE else_result]
  END
cond:BOOL resultelse_result:輸入類型的超級類型。 評估每個連續 WHEN 子句的條件 cond 並傳回條件為 true 的第一個結果;不會評估任何剩餘的 WHEN 子句與 else_result。如果所有條件都是 false 或 NULL,就會傳回 else_result (如果有的話);如果沒有,則傳回 NULLresultelse_result 運算式必須自動強制轉換為共通的超級類型。
COALESCE(expr1, ..., exprN) 不限類型 輸入類型的超級類型 傳回第一個非空值運算式的值。不會評估剩餘運算式。所有輸入運算式都必須自動強制轉換為共通的超級類型。
IF(cond, true_result, else_result) cond:BOOL true_resultelse_result:不限類型。 如果 cond 是 true,就會傳回 true_result,否則會傳回 else_result。如果 cond 是 true 就不會評估 else_result。如果 cond 是 false 或 NULL 就不會評估 true_resulttrue_resultelse_result 必須可強制轉換為共通的超級類型。
IFNULL(expr, null_result) 不限類型 不限類型或輸入類型的超級類型。 如果 exprNULL,就會傳回 null_result。否則,會傳回 expr。如果 expr 不是 NULL,就不會評估 null_resultexprnull_result 必須自動強制轉換為共通的超級類型。COALESCE(expr, null_result) 的同義詞。
NULLIF(expression, expression_to_match) 任何類型 T 或 T 的子類型 任何類型 T 或 T 的子類型 如果 expression = expression_to_match 是 true,就傳回 NULL,否則傳回 expressionexpressionexpression_to_match 必須自動強制轉換為共通的超級類型;這個運算式會比較強制轉換後的值,確認彼此是否相等。

NULLIF 不支援 STRUCT 類型。

運算式子查詢

運算式子查詢 (也就是作為運算式使用的子查詢) 有四種類型。相對於資料欄或資料表,運算式子查詢會傳回 NULL 或單一值,且必須由括號括住。有關子查詢的更完整討論,請參閱子查詢

子查詢的類型 結果資料類型 說明
純量 任何類型 T 運算式內括號中的子查詢 (例如在 SELECT 清單或 WHERE 子句中) 會解讀為純量子查詢。純量子查詢中的 SELECT 清單必須有正好一個欄位。如果子查詢傳回正好一個資料列,那個單一值就是純量子查詢結果。如果子查詢傳回零個資料列,純量子查詢就是 NULL。如果子查詢傳回一個以上的資料列,查詢會失敗並發生執行階段錯誤。當使用 SELECT AS STRUCT 編寫子查詢時,它可能包含多個資料欄,且傳回的值是建構的 STRUCT。選取多個資料欄時若不使用 SELECT AS 會發生錯誤。
ARRAY ARRAY 可使用 SELECT AS STRUCT 來建構結構的陣列,相反地,選取多個資料欄時若不使用 SELECT AS 會發生錯誤。如果子查詢傳回零個資料列,就會傳回空白 ARRAY。 永遠不會傳回 NULL ARRAY。
IN BOOL 在 IN 運算子之後的運算式中發生。子查詢必須產生類型相等性相容於 IN 運算子左側運算式的單一資料欄。如果子查詢傳回零個資料列,就會傳回 FALSE。 x IN () 相當於 x IN (value, value, ...)如要瞭解完整的語意,請參閱比較運算子中的 IN
EXISTS BOOL 如果子查詢產生一或多個資料列,就會傳回 TRUE。如果子查詢產生零個資料列,就會傳回 FALSE。永遠不會傳回 NULL。與其他所有運算式子查詢不同,並沒有有關資料欄清單的規則。可以選取任何數量的資料欄,而且不會影響查詢結果。

範例

以下運算式子查詢的範例假設 t.int_array 的類型是 ARRAY<INT64>

類型 子查詢 結果資料類型 附註
純量 (SELECT COUNT(*) FROM t.int_array) INT64  
(SELECT DISTINCT i FROM t.int_array i) INT64,可能發生執行階段錯誤  
(SELECT i FROM t.int_array i WHERE i=5) INT64,可能發生執行階段錯誤  
(SELECT ARRAY_AGG(i) FROM t.int_array i) ARRAY 使用 ARRAY_AGG aggregation 函式來傳回 ARRAY。
(SELECT 'xxx' a) STRING  
(SELECT 'xxx' a, 123 b) 錯誤 傳回錯誤,因為有一個以上的資料欄
(SELECT AS STRUCT 'xxx' a, 123 b) STRUCT  
(SELECT AS STRUCT 'xxx' a) STRUCT  
ARRAY ARRAY(SELECT COUNT(*) FROM t.int_array) 大小為 1 的 ARRAY  
ARRAY(SELECT x FROM t) ARRAY  
ARRAY(SELECT 5 a, COUNT(*) b FROM t.int_array) 錯誤 傳回錯誤,因為有一個以上的資料欄
ARRAY(SELECT AS STRUCT 5 a, COUNT(*) b FROM t.int_array) ARRAY  
ARRAY(SELECT AS STRUCT i FROM t.int_array i) ARRAY 建立一個欄位 STRUCT 的 ARRAY
ARRAY(SELECT AS STRUCT 1 x, 2, 3 x) ARRAY 傳回具有匿名或重複欄位之 STRUCT 的 ARRAY。
ARRAY(SELECT AS TypeName SUM(x) a, SUM(y) b, SUM(z) c from t) array<TypeName> 選取到已命名類型中。假設 TypeName 是具有欄位 a,b,c 的 STRUCT 類型。
STRUCT (SELECT AS STRUCT 1 x, 2, 3 x) STRUCT 建構具有匿名或重複欄位的 STRUCT。
EXISTS EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
NOT EXISTS(SELECT x,y,z FROM table WHERE y=z) BOOL  
IN x IN (SELECT y FROM table WHERE z) BOOL  
x NOT IN (SELECT y FROM table WHERE z) BOOL  

Debugging 函式

Cloud Spanner SQL 支援下列 debugging 函式。

錯誤

ERROR(error_message)

說明

傳回錯誤。error_message 引數是 STRING

Cloud Spanner SQL 會以與可能產生下列錯誤的任何運算式相同的方式處理 ERROR:評估順序沒有任何特別保證。

傳回資料類型

Cloud Spanner SQL 會在結構定義中推測傳回類型。

範例

在以下範例中,如果資料列的值與兩個已定義值的其中之一不符,查詢會傳回錯誤訊息。

SELECT
  CASE
    WHEN value = 'foo' THEN 'Value is foo.'
    WHEN value = 'bar' THEN 'Value is bar.'
    ELSE ERROR(concat('Found unexpected value: ', value))
  END AS new_value
FROM (
  SELECT 'foo' AS value UNION ALL
  SELECT 'bar' AS value UNION ALL
  SELECT 'baz' AS value);

Found unexpected value: baz

在以下範例中,Cloud Spanner SQL 會在 x > 0 條件前後評估 ERROR 函式,因為 Cloud Spanner SQL 通常不提供 WHERE 子句條件間的順序保證,也不會對 ERROR 函式提供特別的保證。

SELECT *
FROM (SELECT -1 AS x)
WHERE x > 0 AND ERROR('Example error');

在下一個範例中,WHERE 子句會評估 IF 條件,這會確保條件失敗時,Cloud Spanner SQL 只評估 ERROR 函式。

SELECT *
FROM (SELECT -1 AS x)
WHERE IF(x > 0, true, ERROR(FORMAT('Error: x must be positive but is %t', x)));'

Error: x must be positive but is -1
本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁
Cloud Spanner 說明文件