標準 SQL 資料類型

BigQuery 支援簡易的資料類型 (例如整數),以及較為複雜的類型 (例如 ARRAY 和 STRUCT)。本頁面概要說明各種資料類型,包括允許的值。如需資料類型實字和建構函式的相關資訊,請參閱 詞法結構和語法

資料類型屬性

儲存和查詢資料時,記住以下資料類型屬性會很有幫助:

屬性 說明 適用對象
可以為空值 NULL 是一個有效值。 所有資料類型,除了以下幾個例外狀況:
  • ARRAY 不能為 NULL
  • NULL ARRAY 元素不能保存到資料表中。
可排序 可在 ORDER BY 子句中使用。 所有資料類型,除了以下幾個例外狀況:
  • ARRAY
  • STRUCT
  • GEOGRAPHY
可分組 通常可以出現在
GROUP BYDISTINCTPARTITION BY 後面的運算式中。
不過,PARTITION BY 運算式不能包含
FLOATDOUBLE 這兩種浮點類型。
所有資料類型,除了以下幾個例外狀況:
  • ARRAY
  • STRUCT
  • GEOGRAPHY
可比較 相同類型的值可以互相比較。 所有資料類型,除了以下幾個例外狀況: 不支援 ARRAY 比較。

支援依欄位順序,對 STRUCT 進行逐欄位的比較,確認彼此是否相等。欄位名稱會遭到忽略。不支援「小於」和「大於」比較。

不支援 GEOGRAPHY 比較。如要比較 GEOGRAPHY 值,請使用 ST_Equals

支援比較的所有類型均可用於 JOIN 條件。如需彙整條件的說明,請參閱 JOIN 類型一節。


數值類型

數字類型包括整數類型、浮點類型和 NUMERIC 資料類型。

整數類型

整數是沒有小數的數值。

名稱 儲存空間大小 範圍
INT64 8 個位元組 -9,223,372,036,854,775,808 至 9,223,372,036,854,775,807

NUMERIC 類型

NUMERIC 資料類型屬於精確數值,總共有 38 位數,包含小數點後 9 位數。總位數代表是該數字包含的數字數量。小數位數是小數點後數字的數量。

這項類型可準確表達分數的小數位數,適合用於財務計算。

名稱 儲存空間大小 說明 範圍
NUMERIC 16 個位元組 十進位值,總位數 38 位數及小數位數 9 位數。 -99999999999999999999999999999.999999999 至 99999999999999999999999999999.999999999

浮點類型

浮點數值是具有小數的近似數值。

名稱 儲存空間大小 說明
FLOAT64 8 個位元組 雙精確度 (近似) 小數值。

浮點語意

使用浮點數時,需要考慮特殊的非數值: NaN+/-inf

算術運算子可為產生有限輸出的所有有限輸入值和至少一個輸入為非有限的所有作業提供標準的 IEEE-754 行為。

如果輸入是有限的但輸出將是非有限的,函式會呼叫而且運算子會傳回溢位錯誤。如果輸入包含非有限值,則輸出可以是非有限的。一般而言,函式不會引入 NaN+/-inf。不過,像 IEEE_DIVIDE 這樣的特定函式可以在有限輸入上傳回非有限值。所有這類情況都會在 數學函式中明確指出。

數學函式範例
左邊的字詞 運算子 右邊的字詞 傳回
任何值 + NaN NaN
1.0 + +inf +inf
1.0 + -inf -inf
-inf + +inf NaN
最大的 FLOAT64 + 最大的 FLOAT64 溢位錯誤
最小的 FLOAT64 / 2.0 0.0
1.0 / 0.0 「除以零」錯誤

比較運算子可為浮點輸入提供標準的 IEEE-754 行為。

比較運算子範例
左邊的字詞 運算子 右邊的字詞 傳回
NaN = 任何值 FALSE
NaN < 任何值 FALSE
任何值 < NaN FALSE
-0.0 = 0.0 TRUE
-0.0 < 0.0 FALSE

浮點數值會按此順序排序,從最小值到最大值:

  1. NULL
  2. NaN — 排序時,會將所有 NaN 值視為相等。
  3. -inf
  4. 負數
  5. 0 或 -0 — 排序時,會將所有零值視為相等。
  6. 正數
  7. +inf

特殊的浮點數值會以這種方式分組,包括由 GROUP BY 子句完成的分組和由 DISTINCT 關鍵字完成的分組:

  • NULL
  • NaN — 分組時,會將所有 NaN 值視為相等。
  • -inf
  • 0 或 -0 — 分組時,會將所有零值視為相等。
  • +inf

布林類型

名稱 說明
BOOL 布林值由 TRUEFALSE (不區分大小寫) 關鍵字表示。

字串類型

名稱 說明
STRING 變數長度字元 (Unicode) 資料。

輸入 STRING 值必須是 UTF-8 編碼,而輸出 STRING 值將是 UTF-8 編碼。像 CESU-8 和修改後的 UTF-8 等替代編碼不被視為有效的 UTF-8。

所有對 STRING 值產生作用的函式和運算子會針對 Unicode 字元而非位元組進行運算。例如,應用於 STRING 輸入的函式 (如 SUBSTRLENGTH) 會計算 Unicode 字元數而非位元組數。比較是針對 Unicode 字元定義的。針對小於和 ORDER BY 的比較會逐字元進行比較,而較低的 unicode 字碼指標會被視為較低的字元。

STRING 上的大多數函式也都是在 BYTES 上定義的。BYTES 版本會針對原始位元組進行運算,而非 Unicode 字元。STRING 和 BYTES 是無法交替使用的獨立類型。任一方向都沒有隱含轉換。STRING 和 BYTES 之間的明確轉換會進行 UTF-8 編碼和解碼。如果位元組不是有效的 UTF-8,則將 BYTES 轉換為 STRING 時會傳回錯誤。

位元組類型

名稱 說明
BYTES 變數長度二進位資料。

STRING 和 BYTES 是無法交替使用的獨立類型。STRING 上的大多數函式也都是在 BYTES 上定義的。BYTES 版本會針對原始位元組進行運算,而非 Unicode 字元。STRING 和 BYTES 之間的轉換會強制使用 UTF-8 將那些位元組編碼。

日期類型

名稱 說明 範圍
DATE 代表邏輯日曆日期。 0001-01-01 至 9999-12-31。

DATE 類型代表邏輯日曆日期,與時區無關。DATE 值不代表特定的 24 小時時間範圍,而是在不同時區解讀時,特定 DATE 值代表不同的 24 小時期間,而且在日光節約時間轉換期間可能代表較短或較長的一天。如要代表絕對時間點,請使用時間戳記。

標準格式

'YYYY-[M]M-[D]D'
  • YYYY:以四位數字表示的年份
  • [M]M:以一或兩位數字表示的月份
  • [D]D:以一或兩位數字表示的日期

日期時間類型

名稱 說明 範圍
DATETIME 代表年、月、日、時、分、秒和亞秒。 0001-01-01 00:00:00 至 9999-12-31 23:59:59.999999。

DATETIME 代表一個時間點。每個 DATETIME 都包含以下內容:

  • month
  • day
  • hour
  • minute
  • 亞秒

時間戳記 不同,DATETIME 物件不是指絕對時間例項,而是指「民用時」 或使用者會在手錶或日曆上看到的時間。

標準格式

YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]]
  • YYYY:以四位數字表示的年份
  • [M]M:以一或兩位數字表示的月份
  • [D]D:以一或兩位數字表示的日期
  • ( |T):空格或 T 分隔符
  • [H]H:以一或兩位數字表示的小時 (有效值為 00 到 23)
  • [M]M:以一或兩位數字表示的分鐘 (有效值為 00 到 59)
  • [S]S:以一或兩位數字表示的秒鐘 (有效值為 00 到 59)
  • [.DDDDDD]:最多六個小數位數 (即高達微秒的精確度)

Geography 類型

名稱 說明
GEOGRAPHY 點、線與多邊形的集合,以點集或地球表面部分區域表示。

GEOGRAPHY 類型採用 OGC 簡單幾何特徵規格 (SFS),是在 WGS84 參考橢圓體上形成「簡單」排列的點、線或多邊形集合。簡單排列是指集合的多個元素不包含 WGS84 表面上任何點的排列方式。

GEOGRAPHY地理位置函式的結果或引數。

時間類型

名稱 說明 範圍
TIME 代表時間,與特定日期無關。 00:00:00 至 23:59:59.999999。

TIME 資料類型代表時間,與特定日期無關。

標準格式

[H]H:[M]M:[S]S[.DDDDDD]
  • [H]H:以一或兩位數字表示的小時 (有效值為 00 到 23)
  • [M]M:以一或兩位數字表示的分鐘 (有效值為 00 到 59)
  • [S]S:以一或兩位數字表示的秒鐘 (有效值為 00 到 59)
  • [.DDDDDD]:最多六個小數位數 (即高達微秒的精確度)

時間戳記類型

名稱 說明 範圍
TIMESTAMP 代表絕對時間點,精確度高達微秒。 0001-01-01 00:00:00 至 9999-12-31 23:59:59.999999 UTC。

時間戳記代表絕對時間點,與任何時區或慣例 (例如日光節約時間) 無關。

TIMESTAMP 提供高達微秒的精確度。

標準格式

YYYY-[M]M-[D]D[( |T)[H]H:[M]M:[S]S[.DDDDDD]][time zone]
  • YYYY:以四位數字表示的年份
  • [M]M:以一或兩位數字表示的月份
  • [D]D:以一或兩位數字表示的日期
  • ( |T):空格或 T 分隔符
  • [H]H:以一或兩位數字表示的小時 (有效值為 00 到 23)
  • [M]M:以一或兩位數字表示的分鐘 (有效值為 00 到 59)
  • [S]S:以一或兩位數字表示的秒鐘 (有效值為 00 到 59)
  • [.DDDDDD]:最多六個小數位數 (即高達微秒的精確度)
  • [time zone]:代表時區的字串。詳情請參閱時區 一節。

剖析時間戳記或設定時間戳記的格式以供顯示時會使用時區。 時間戳記值本身不會儲存特定時區。字串格式的時間戳記可能包含時區。未明確指定時區時,將會使用預設的時區 UTC。

時區

時區由以下兩種標準格式之一的字串表示:

  • 與世界標準時間 (UTC) 之間的偏移,或代表 UTC 的字母 Z
  • tz 資料庫的時區名稱

與世界標準時間 (UTC) 之間的偏移

偏移格式
(+|-)H[H][:M[M]]
Z
示例
-08:00
-8:15
+3:00
+07:30
-7
Z

使用這種格式時,時區和時間戳記的其餘部分之間不得有空格。

2014-09-27 12:30:00.45-8:00
2014-09-27T12:30:00.45Z

時區名稱

時區名稱取自 tz 資料庫。如需不用太詳盡的簡明參考資料,請參閱 Wikipedia 的 tz 資料庫時區清單

格式
continent/[region/]city
範例
America/Los_Angeles
America/Argentina/Buenos_Aires

使用時區名稱時,名稱和時間戳記的其餘部分之間需要空格:

2014-09-27 12:30:00.45 America/Los_Angeles

請注意,並非所有時區名稱均可互換,即使它們剛好在一年的特定期間報告了相同的時間。例如,在日光節約時間期間,America/Los_AngelesUTC-7:00 報告的時間相同,但在日光節約時間以外的期間報告的時間則與 UTC-8:00 相同。

如果未指定時區,將會使用預設的時區值。

閏秒

時間戳記只是與 1970-01-01 00:00:00 UTC 的時差 (假設每分鐘正好 60 秒)。系統不會將閏秒呈現為已儲存時間戳記的一部分。

如果您的輸入包含在秒欄位使用「:60」來代表閏秒的值,則在轉換為時間戳記值時不會保留該閏秒,而是會將該值解讀為下一分鐘的秒欄位中有「:00」的時間戳記。

閏秒不會影響時間戳記的計算。所有時間戳記計算都會使用 Unix 樣式的時間戳記來完成,而這種方式不會反映閏秒。閏秒只能透過測量真實世界時間的函式來觀察。在這些函式中,當有閏秒時,可能會略過或重複時間戳記的秒。

陣列類型

名稱 說明
ARRAY 零或多個任何非 ARRAY 類型元素的排序清單。

ARRAY 是零或多個非 ARRAY 值元素的排序清單。 系統不允許 ARRAY 的 ARRAY。會產生 ARRAY 的 ARRAY 的查詢將會傳回錯誤。相反地,必須使用 SELECT AS STRUCT 結構在 ARRAY 之間插入 STRUCT。

目前,BigQuery 在 NULL 和 ARRAY 方面有以下兩個限制:

  • 如果查詢結果具有包含 NULL 元素的 ARRAY,則 BigQuery 會引發錯誤,即使可以在查詢中使用這類 ARRAY。
  • BigQuery 會在查詢結果中將 NULL ARRAY 轉換為空的 ARRAY,雖然在查詢中 NULL 和空的 ARRAY 是兩個不同的值。

宣告 ARRAY 類型

您可以使用角括號 (<>) 來宣告 ARRAY 類型。ARRAY 的元素可以為任意複雜類型,但 ARRAY 不能直接包含另一個 ARRAY。

格式

ARRAY<T>

範例

類型宣告 意義
ARRAY<INT64> 由 64 位元整數組成的簡易 ARRAY。
ARRAY<STRUCT<INT64, INT64>> 由 STRUCT 組成的 ARRAY,每個 STRUCT 都包含兩個 64 位元整數。
ARRAY<ARRAY<INT64>>
(不支援)
這是一個無效的類型宣告。我們假設您會來此尋找建立多層 ARRAY 的相關資訊,因此將這個宣告列在這裡。ARRAY 不能直接包含 ARRAY。請改為參閱下一個示例。
ARRAY<STRUCT<ARRAY<INT64>>> 由 64 位元整數 ARRAY 組成的 ARRAY。請注意,由於 ARRAY 不得直接包含其他 ARRAY,因此兩個 ARRAY 之間會有一個 STRUCT。

結構類型

名稱 說明
STRUCT 已排序欄位的容器,每個容器都有一個類型 (必要) 和欄位名稱 (選用)。

宣告 STRUCT 類型

您可以使用角括號 (<>) 來宣告 STRUCT 類型。STRUCT 的元素可以為任意複雜類型。

格式

STRUCT<T>

範例

類型宣告 意義
STRUCT<INT64> 具有單一未命名 64 位元整數欄位的簡單 STRUCT。
STRUCT<x STRUCT<y INT64, z INT64>> 包含名為 x 的巢狀 STRUCT 的 STRUCTSTRUCT x 包含 yz 這兩個欄位,兩者均為 64 位元整數。
STRUCT<inner_array ARRAY<INT64>> 包含名為 inner_array 的 ARRAY 的 STRUCT,其中包含 64 位元整數元素。

建構 STRUCT

組合語法

格式
(expr1, expr2 [, ... ])

輸出類型是具有匿名欄位的匿名 STRUCT 類型,其類型與輸入運算式的類型相符。至少必須指定兩個運算式。否則這個語法無法與加上括號的運算式區別。

示例
語法 輸出類型 附註
(x, x+y) STRUCT<?,?> 如果使用欄名稱 (未加引號的字串),STRUCT 欄位資料類型衍生自欄資料類型。xy 都是欄,因此 STRUCT 欄位的資料類型衍生自欄類型和加法運算子的輸出類型。

如果 STRUCT 比較使用多部分鍵的比較運算式,則可搭配此語法使用。例如,在 WHERE 子句中:

WHERE (Key1,Key2) IN ( (12,34), (56,78) )

無類型的結構語法

格式
STRUCT( expr1 [AS field_name] [, ... ])

允許使用重複的欄位名稱。沒有名稱的欄位會被視為匿名欄位,無法依名稱參照。STRUCT 值可以為 NULL,也可以有 NULL 的欄位值。

範例
語法 輸出類型
STRUCT(1,2,3) STRUCT<int64,int64,int64>
STRUCT() STRUCT<>
STRUCT('abc') STRUCT<string>
STRUCT(1, t.str_col) STRUCT<int64, str_col string>
STRUCT(1 AS a, 'abc' AS b) STRUCT<a int64, b string>
STRUCT(str_col AS abc) STRUCT<abc string>

具類型的結構語法

格式
STRUCT<[field_name] field_type, ...>( expr1 [, ... ])

具類型的語法允許建構具有明確 STRUCT 資料類型的 STRUCT。輸出類型剛好是所提供的 field_type。如果兩種類型不相同,則輸入運算式將強制為 field_type,而且如果類型不相容,將會產生錯誤。輸入運算式不允許 AS alias。運算式的數目必須與類型中的欄位數目相符,而且運算式類型必須可強制或明確強制轉換為欄位類型。

示例
語法 輸出類型
STRUCT<int64>(5) STRUCT<int64>
STRUCT<date>("2011-05-05") STRUCT<date>
STRUCT<x int64, y string>(1, t.str_col) STRUCT<x int64, y string>
STRUCT<int64>(int_col) STRUCT<int64>
STRUCT<x int64>(5 AS x) 錯誤 - 具類型的語法不允許使用 AS

STRUCT 的有限比較

您可以使用等號比較運算子直接比較 STRUCT:

  • 等於 (=)
  • 不等於 (!=<>)
  • [NOT] IN

不過請注意,這些直接相等性比較會忽略任何欄位名稱,並按順序比較 STRUCT 配對欄位。如果您要比較 STRUCT 的同名欄位,則可直接比較個別欄位。

本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁