遷移至標準 SQL

BigQuery 支援兩種 SQL 方言:標準 SQL舊版 SQL。本文說明這兩種語法之間的差異,包括語法函式語意,並提供一些標準 SQL 的重點特色範例。

舊版與標準 SQL 之比較

先前 BigQuery 是使用稱為 BigQuery SQL 的非標準 SQL 方言來執行查詢作業。隨著 BigQuery 2.0 的推出,BigQuery 也開始支援標準 SQL,而 BigQuery SQL 也改名為舊版 SQL。如果要查詢 BigQuery 中儲存的資料,則標準 SQL 是建議使用的 SQL 方言。

我需要遷移到標準 SQL 嗎?

我們建議但不要求您從舊版 SQL 遷移到標準 SQL。舉例來說,如果您已經使用舊版 SQL 執行了許多查詢,而也想利用標準 SQL 的功能執行新查詢。您可以使用標準 SQL 建立新查詢,它可與使用舊版 SQL 執行的查詢共同運作。

啟用標準 SQL

執行查詢時您可以選擇使用舊版或標準 SQL。關於在 SQL 方言間進行切換的資訊,請參閱切換 SQL 方言一文。

標準 SQL 的優點

標準 SQL 符合 SQL 2011 標準,並具有支援查詢巢狀及重複資料的擴充功能。與舊版 SQL 相比多了幾項優點,包括:

如需這些功能的示範講解,請參閱標準 SQL 的重點特色

類型差異

舊版 SQL 的資料類型在標準 SQL 中都有相對應的資料類型,反之亦然。有些類型在兩種 SQL 中有不同的名稱。下表列出每種舊版 SQL 資料類型與其相對應的標準 SQL 資料類型。

舊版 SQL 標準 SQL 附註
BOOL BOOL
INTEGER INT64
FLOAT FLOAT64
STRING STRING
BYTES BYTES
RECORD STRUCT
REPEATED ARRAY
TIMESTAMP TIMESTAMP 請參閱 TIMESTAMP 差異一節
DATE DATE 舊版 SQL 對 DATE 只提供有限支援
TIME TIME 舊版 SQL 對 TIME 只提供有限支援
DATETIME DATETIME 舊版 SQL 對 DATETIME 只提供有限支援

如要深入瞭解標準 SQL 類型系統,請參閱標準 SQL 資料類型參考。 如要深入瞭解 BigQuery 中的資料類型,請參閱 BigQuery 資料類型參考

TIMESTAMP 差異

與舊版 SQL 相比,標準 SQL 的 TIMESTAMP 有效值範圍更為嚴格。在標準 SQL 中,TIMESTAMP 的有效值範圍是從 0001-01-01 00:00:00.0000009999-12-31 23:59:59.999999。例如,您可以使用標準 SQL 選取最小和最大的 TIMESTAMP 值:

#standardSQL
SELECT
  min_timestamp,
  max_timestamp,
  UNIX_MICROS(min_timestamp) AS min_unix_micros,
  UNIX_MICROS(max_timestamp) AS max_unix_micros
FROM (
  SELECT
    TIMESTAMP '0001-01-01 00:00:00.000000' AS min_timestamp,
    TIMESTAMP '9999-12-31 23:59:59.999999' AS max_timestamp
);

這個查詢會傳回 -62135596800000000 當做 min_unix_micros,以及傳回 253402300799999999 當做 max_unix_micros

如果您選取了含有超出此範圍的時間戳記值的資料欄,您將會收到一則錯誤訊息:

#standardSQL
SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps;

這個查詢會傳回以下錯誤訊息:

Cannot return an invalid timestamp value of -8446744073709551617
microseconds relative to the Unix epoch. The range of valid
timestamp values is [0001-01-1 00:00:00, 9999-12-31 23:59:59.999999]

更正錯誤的一個方法是,定義並使用使用者定義函式來篩選無效的時間戳記:

#standardSQL
CREATE TEMP FUNCTION TimestampIsValid(t TIMESTAMP) AS (
  t >= TIMESTAMP('0001-01-01 00:00:00') AND
  t <= TIMESTAMP('9999-12-31 23:59:59.999999')
);

SELECT timestamp_column_with_invalid_values
FROM MyTableWithInvalidTimestamps
WHERE TimestampIsValid(timestamp_column_with_invalid_values);

另一個更正錯誤的方法,是使用含時間戳記資料欄的 SAFE_CAST 函式。例如:

#standardSQL
SELECT SAFE_CAST(timestamp_column_with_invalid_values AS STRING) AS timestamp_string
FROM MyTableWithInvalidTimestamps;

這個查詢會傳回 NULL,而不是無效時間戳記值的時間戳記字串。

語法差異

逸出保留關鍵字和無效識別碼

在舊版 SQL 中,您使用使用方括號 [] 來逸出含有如空格 或連字號 - 等無效字元的保留關鍵字和識別碼。在標準 SQL 中,您使用倒引號 ` 來逸出這類關鍵字和識別碼。例如:

#standardSQL
SELECT
  word,
  SUM(word_count) AS word_count
FROM
  `bigquery-public-data.samples.shakespeare`
WHERE word IN ('me', 'I', 'you')
GROUP BY word;

舊版 SQL 允許在一些地方保留關鍵字,而標準 SQL 卻不允許。例如,以下使用標準 SQL 的查詢因 Syntax error 而查詢失敗:

#standardSQL
SELECT
  COUNT(*) AS rows
FROM
  `bigquery-public-data.samples.shakespeare`;

如要更正錯誤,您必須使用倒引號來逸出別名 rows

#standardSQL
SELECT
  COUNT(*) AS `rows`
FROM
  `bigquery-public-data.samples.shakespeare`;

如需保留關鍵字和構成有效識別碼之條件的清單,請參閱詞法結構

符合專案資格的資料表名稱

在舊版 SQL 中,若要查詢名稱符合專案資格的資料表,您會使用冒號 : 作為分隔符。例如:

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT 1;

在標準 SQL 中,您會改為使用 .。例如:

#standardSQL
SELECT
  word
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

如果您的專案名稱含有網域,如 example.com:myproject,您可以使用 example.com:myproject 作為專案名稱,包括 :

資料表修飾符與萬用字元函式

標準 SQL 不支援 TABLE_DATE_RANGETABLE_DATE_RANGE_STRICTTABLE_QUERY 函式。

您可以透過對 _TABLE_SUFFIX 虛擬資料欄使用篩選來達到和 TABLE_DATE_RANGETABLE_QUERY 同樣的語意。例如,以下的舊版 SQL 查詢計算的是美國國家海洋暨大氣總署 GSOD (全球每日氣候概要) 資料表中 2010 及 2011 年的資料列數:

#legacySQL
SELECT COUNT(*)
FROM TABLE_QUERY([bigquery-public-data:noaa_gsod],
                 'table_id IN ("gsod2010", "gsod2011")');

使用標準 SQL 寫成的相對應查詢是:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.noaa_gsod.*`
WHERE _TABLE_SUFFIX IN ("gsod2010", "gsod2011");

如需詳細資訊,包括使用 TABLE_DATE_RANGE 的範例,請參閱資料表修飾符與萬用字元函式一節

SELECT 清單中的尾隨逗號

與舊版 SQL 不同,標準 SQL 不允許 FROM 子句之前出現尾隨逗號。例如,以下的查詢就是無效的:

#standardSQL
SELECT
  word,
  corpus,  -- Error due to trailing comma
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

若要更正錯誤,請移除 corpus 之後的逗號:

#standardSQL
SELECT
  word,
  corpus
FROM
  `bigquery-public-data.samples.shakespeare`
LIMIT 1;

用於資料表的逗號運算子

在舊版 SQL 中,逗號運算子 , 應用在資料表時具有非標準的 UNION ALL 意涵。在標準 SQL 中,逗號運算子具有標準的 JOIN 意涵。例如,請思考以下的舊版 SQL 查詢:

#legacySQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y),
  (SELECT 2 AS x, "bar" AS y);

這相當於標準 SQL 查詢:

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2 AS x, "bar" AS y);

另請注意,在標準 SQL 中,UNION ALL 是按照位置而非名稱來聯集資料欄。上述的查詢相當於:

#standardSQL
SELECT
  x,
  y
FROM
  (SELECT 1 AS x, "foo" AS y UNION ALL
   SELECT 2, "bar");

逗號運算子在標準 SQL 中的常見用法是與陣列 JOIN。例如:

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T,
  UNNEST(arr) AS y;

這會傳回含有 arr 元素的跨產品資料表 T。您也可以使用標準 SQL 來表達這個查詢:

#standardSQL
WITH T AS (
  SELECT 0 AS x, [1, 2, 3] AS arr UNION ALL
  SELECT 1, [4, 5])
SELECT
  x,
  y
FROM
  T
JOIN
  UNNEST(arr) AS y;

在這個查詢中,JOIN 具有和逗號運算子 , 相同的意涵,用以分隔 TUNNEST(arr) AS y,如上例所示。

邏輯檢視表

您無法使用標準 SQL 來查詢以舊版 SQL 定義的邏輯檢視表,反之亦然;因為這兩種方言之間存在語法和語意上的差異。您必須使用標準 SQL 建立新的檢視表 (可能命名為其他名稱),用以取代使用舊版 SQL 建立的檢視表。

舉例來說,假設您使用舊版 SQL 將檢視表 V 定義為:

#legacySQL
SELECT *, UTC_USEC_TO_DAY(timestamp_col) AS day
FROM MyTable;

假設您使用舊版 SQL 將檢視表 W 定義為:

#legacySQL
SELECT user, action, day
FROM V;

假設您每天都會執行以下的舊版 SQL 查詢,但您想要遷移為使用標準 SQL:

#legacySQL
SELECT EXACT_COUNT_DISTINCT(user), action, day
FROM W
GROUP BY action, day;

一個可行的遷移路徑是使用不同的名稱建立新的檢視表。執行步驟為:

使用語句內容如下的標準 SQL 建立名為 V2 的檢視表:

#standardSQL
SELECT *, EXTRACT(DAY FROM timestamp_col) AS day
FROM MyTable;

使用語句內容如下的標準 SQL 建立名為 W2 的檢視表:

#standardSQL
SELECT user, action, day
FROM V2;

將您每天執行的查詢更改為使用標準 SQL 並參照到 W2

#standardSQL
SELECT COUNT(DISTINCT user), action, day
FROM W2
GROUP BY action, day;

另一個選項是刪除檢視表 VW,然後使用標準 SQL 重新建立同樣名稱的檢視表。但若使用此選項,您必須將所有參照 VW 的查詢同時遷移為使用標準 SQL。

函式比較

以下列出部分舊版 SQL 函式與其相對應的標準 SQL。

舊版 SQL 標準 SQL 附註
INTEGER(x) SAFE_CAST(x AS INT64)
CAST(x AS INTEGER) SAFE_CAST(x AS INT64)
DATEDIFF(t1, t2) TIMESTAMP_DIFF(t1, t2, DAY)
NOW CURRENT_TIMESTAMP
STRFTIME_UTC_USEC(t, fmt) FORMAT_TIMESTAMP(fmt, t)
UTC_USEC_TO_DAY(t) TIMESTAMP_TRUNC(t, DAY)
REGEXP_MATCH(s, pattern) REGEXP_CONTAINS(s, pattern)
IS_NULL(x) x IS NULL
LEFT(s, len) SUBSTR(s, 0, len)
RIGHT(s, len) SUBSTR(s, -len)
s CONTAINS "foo" STRPOS(s, "foo") > 0s LIKE '%foo%'
x % y MOD(x, y)
NEST(x) ARRAY_AGG(x)
ANY(x) ANY_VALUE(x)
GROUP_CONCAT_UNQUOTED(s, sep) STRING_AGG(s, sep)
SOME(x) IFNULL(LOGICAL_OR(x), false)
EVERY(x) IFNULL(LOGICAL_AND(x), true)
COUNT(DISTINCT x) APPROX_COUNT_DISTINCT(x) 請參閱下方的附註
EXACT_COUNT_DISTINCT(x) COUNT(DISTINCT x) 請參閱下方的附註
QUANTILES(x, buckets + 1) APPROX_QUANTILES(x, buckets)
TOP(x, num), COUNT(*) APPROX_TOP_COUNT(x, num)
NTH(index, arr) WITHIN RECORD arr[SAFE_ORDINAL(index)]
COUNT(arr) WITHIN RECORD ARRAY_LENGTH(arr)
HOST(url) NET.HOST(url) 請參閱下方的差異
TLD(url) NET.PUBLIC_SUFFIX(url) 請參閱下方的差異
DOMAIN(url) NET.REG_DOMAIN(url) 請參閱下方的差異
PARSE_IP(addr_string) NET.IPV4_TO_INT64(NET.IP_FROM_STRING(addr_string))
FORMAT_IP(addr_int64) NET.IP_TO_STRING(NET.IPV4_FROM_INT64(addr_int64 & 0xFFFFFFFF))
PARSE_PACKED_IP(addr_string) NET.IP_FROM_STRING(addr_string)
FORMAT_PACKED_IP(addr_bytes) NET.IP_TO_STRING(addr_bytes)

如要深入瞭解標準 SQL 函式,請參閱函式與運算子主題。

COUNT 函式比較

舊版 SQL 和標準 SQL 都含有 COUNT 函式。但此函式在這兩種 SQL 方言中各有不同的行為。

在舊版 SQL 中,COUNT(DISTINCT x) 會傳回一個近似計數。在標準 SQL 中,它會傳回一個精確計數。如要執行相異值的近似計數查詢,並要提高執行速度及減少需要的資源,則可使用 APPROX_COUNT_DISTINCT

URL 函式比較

舊版 SQL 和標準 SQL 都含有剖析網址的函式。在舊版 SQL 中,這些函式為 HOST(url)TLD(url)DOMAIN(url)。在標準 SQL 中,這些函式為 NET.HOST(url)NET.PUBLIC_SUFFIX(url)NET.REG_DOMAIN(url)

改良舊版 SQL 函式

  • 標準 SQL 網址函式可以剖析「//」開頭的網址。
  • 當輸入值不符合 RFC 3986 標準或並非網址時 (例如「mailto:?to=&subject=&body=」),會套用不同的規則來剖析輸入值。尤其,標準 SQL 網址函式還可以剖析不含「//」的非標準輸入值,如「www.google.com」。為獲得最佳結果,建議您確認輸入值為網址並符合 RFC 3986 標準。
  • NET.PUBLIC_SUFFIX 會傳回不含前置點的結果。例如,它會傳回「com」而不是「.com」。這與公開尾碼清單中的格式相符。
  • NET.PUBLIC_SUFFIXNET.REG_DOMAIN 支援大寫字母和國際化的網域名稱,TLDDOMAIN 則未支援 (可能會傳回意外的結果)。

邊緣情況中的小差異

  • 如果輸入值不含公開尾碼清單中的任何尾碼,NET.PUBLIC_SUFFIXNET.REG_DOMAIN 會傳回 NULL,而 TLDDOMAIN 會傳回盡力猜測的非 NULL 值。
  • 如果輸入值只含有一個沒有前置標籤的公開尾碼 (例如「http://com」),NET.PUBLIC_SUFFIX 會傳回公開尾碼,TLD 則會傳回空字串。同樣地,NET.REG_DOMAIN 會傳回 NULL,DOMAIN 則會傳回公開尾碼。
  • 對於內含 IPv6 主機的輸入值,NET.HOST 不會將括號從結果中移除,如同 RFC 3986 的規定。
  • 對於內含 IPv4 主機的輸入值,NET.REG_DOMAIN 會傳回 NULL,而 DOMAIN 會傳回前 3 個八位元組。

範例

在下表中,灰色文字表示在舊版和標準 SQL 中都相同的結果。

URL (說明) 主機 NET.HOST TLD NET.PUBLIC _SUFFIX 網域 NET.REG_DOMAIN
"//google.com"
(以「//」開頭)
空值 "google.com" 空值 "com" 空值 "google.com"
"google.com"
(非標準;無「//」)
空值 "google.com" 空值 "com" 空值 "google.com"
"http://user:pass@word@x.com"
(非標準,有多個「@」)
"word@x.com" "x.com" ".com" "com" "word@x.com" "x.com"
"http://foo.com:1:2"
(非標準,有多個「:」)
"foo.com:1" "foo.com" ".com:1" "com" "foo.com" "foo.com"
"http://x.Co.uk"
(大寫字母)
"x.Co.uk" "x.Co.uk" ".uk" "Co.uk" "Co.uk" "x.Co.uk"
"http://a.b"
(找不到公開尾碼)
A2:B6 A2:B6 "b=" 空值 A2:B6 空值
"http://com"
(主機只含有一個公開尾碼)
"com" "com" "" "com" "com" 空值
"http://[::1]"
(IPv6 主機;無公開尾碼)
"::1" "[::1]" "" 空值 "::1" 空值
"http://1.2.3.4"
(IPv4 主機;無公開尾碼)
"1.2.3.4" "1.2.3.4" "" 空值 "1.2.3" 空值

在重複欄位處理中的差異

舊版 SQL 中的 REPEATED 類型相當於標準 SQL 中該類型的 ARRAY。例如,REPEATED INTEGER 相當於標準 SQL 中的 ARRAY<INT64>。下節討論舊版和標準 SQL 在重複欄位運算上的一些差異。

NULL 元素和 NULL 陣列

標準 SQL 支援 NULL 陣列元素,但如果查詢結果中有 NULL 陣列元素,則將引發錯誤。如果查詢結果中有 NULL 陣列欄,標準 SQL 會將它儲存為一個空的陣列。

選取巢狀重複分葉欄位

使用舊版 SQL,您可以「點」入巢狀重複欄位,而無需考量重複發生的位置。在標準 SQL 中,嘗試「點」入巢狀重複欄位的動作會導致錯誤。例如:

#standardSQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

嘗試執行此查詢會傳回:

Cannot access field page_name on a value with type
ARRAY<STRUCT<action STRING, html_url STRING, page_name STRING, ...>>

若要更正錯誤並傳回含有 page_name 陣列的結果,請改用 ARRAY 子查詢。例如:

#standardSQL
SELECT
  repository.url,
  ARRAY(SELECT page_name FROM UNNEST(payload.pages)) AS page_names
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

如要深入瞭解陣列和 ARRAY 子查詢,請參閱使用陣列主題。

篩選重複欄位

使用舊版 SQL,您可以利用 WHERE 子句直接篩選重複的欄位。在標準 SQL 中,您可以利用 JOIN 逗號運算子後接篩選條件,來表達類似的邏輯。例如,請思考以下的舊版 SQL 查詢:

#legacySQL
SELECT
  payload.pages.title
FROM
  [bigquery-public-data:samples.github_nested]
WHERE payload.pages.page_name IN ('db_jobskill', 'Profession');

這個查詢會傳回所有 page_namedb_jobskillProfession 的所有頁面 title。您可以用標準 SQL 表達類似的查詢:

#standardSQL
SELECT
  page.title
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
WHERE page.page_name IN ('db_jobskill', 'Profession');

舊版 SQL 和標準 SQL 查詢之間的一個差異是,如果您未設定扁平化結果選項並執行舊版 SQL 查詢,payload.pages.title 在查詢結果中會是 REPEATED。若要在標準 SQL 中表達同樣的語意,並傳回 title 資料欄的陣列,請改用 ARRAY 子查詢:

#standardSQL
SELECT
  title
FROM (
  SELECT
    ARRAY(SELECT title FROM UNNEST(payload.pages)
          WHERE page_name IN ('db_jobskill', 'Profession')) AS title
  FROM
    `bigquery-public-data.samples.github_nested`)
WHERE ARRAY_LENGTH(title) > 0;

這個查詢會建立一個 title 陣列,其中 page_name'db_jobskill''Profession',然後使用 ARRAY_LENGTH(title) > 0 篩選任何陣列不符合該條件的資料列。

如要深入瞭解陣列,請參閱使用陣列主題。

選取的巢狀分葉欄位的結構

舊版 SQL 未設定扁平化結果選項時,舊版 SQL 會在 SELECT 清單中保留巢狀分葉欄位的結構,標準 SQL 則不會。例如,請思考以下的舊版 SQL 查詢:

#legacySQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  [bigquery-public-data.samples.github_nested]
LIMIT 5;

未設定扁平化結果時,這個查詢會傳回名為 repository 的記錄中的 urlhas_downloads。現在請思考以下的標準 SQL 查詢:

#standardSQL
SELECT
  repository.url,
  repository.has_downloads
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

這個查詢會傳回 urlhas_downloads 並以其作為最上層資料欄;它們並不屬於 repository 記錄或結構中的一部分。若要將其作為結構的一部分傳回,請使用 STRUCT 運算子:

#standardSQL
SELECT
  STRUCT(
    repository.url,
    repository.has_downloads) AS repository
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

利用 FLATTEN 移除重複

標準 SQL 沒有舊版 SQL 中有的 FLATTEN 函式,但您可以使用 JOIN (逗號) 運算子來表達類似的語意。例如,請思考以下的舊版 SQL 查詢:

#legacySQL
SELECT
  repository.url,
  payload.pages.page_name
FROM
  FLATTEN([bigquery-public-data:samples.github_nested], payload.pages.page_name)
LIMIT 5;

您可以用標準 SQL 表達類似的查詢:

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`,
  UNNEST(payload.pages) AS page
LIMIT 5;

或是,使用相對應的 JOIN 而不是逗號 , 運算子:

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

其重要的差別在於,如果 payload.pages 是空的,舊版 SQL 查詢會傳回 payload.pages.page_nameNULL 的資料列。然而,如果 payload.pages 是空的,標準 SQL 查詢不會傳回資料列。若要精確表達相同的語意,請使用 LEFT JOINLEFT OUTER JOIN。例如:

#standardSQL
SELECT
  repository.url,
  page.page_name
FROM
  `bigquery-public-data.samples.github_nested`
LEFT JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

如要深入瞭解陣列,請參閱使用陣列主題。如要深入瞭解 UNNEST,請參閱 UNNEST 主題。

利用 OMIT RECORD IF 篩選資料列

舊版 SQL 的 OMIT IF 子句可讓您依據可套用至重複欄位的條件篩選資料列。在標準 SQL 中,您可以利用 EXISTS 子句、IN 子句,或直接用篩選方式,來塑模 OMIT IF 子句。例如,請思考以下的舊版 SQL 查詢:

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  EVERY(payload.pages.page_name != 'db_jobskill'
        AND payload.pages.page_name != 'Profession');

相對應的標準 SQL 查詢為:

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name = 'db_jobskill'
    OR page_name = 'Profession');

在這裡,如果 payload.pages 中至少有一個頁面名稱為 'db_jobskill''Profession' 的元素,EXISTS 子句會評估為 true

或者,假設舊版 SQL 查詢使用 IN

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF NOT
  SOME(payload.pages.page_name IN ('db_jobskill', 'Profession'));

在標準 SQL 中,您可以使用內含 INEXISTS 子句來表達這個查詢:

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE EXISTS (
  SELECT 1 FROM UNNEST(payload.pages)
  WHERE page_name IN ('db_jobskill', 'Profession'));

請思考以下的舊版 SQL 查詢,此查詢篩選了含有 80 或更少頁面的記錄:

#legacySQL
SELECT
  repository.url,
FROM
  [bigquery-public-data:samples.github_nested]
OMIT RECORD IF
  COUNT(payload.pages.page_name) <= 80;

在標準 SQL 中,您可以使用 ARRAY_LENGTH 來進行同樣的篩選:

#standardSQL
SELECT
  repository.url
FROM
  `bigquery-public-data.samples.github_nested`
WHERE
  ARRAY_LENGTH(payload.pages) > 80;

請注意,ARRAY_LENGTH 函式會直接套用至重複的 payload.pages 欄位,而不是像在舊版 SQL 查詢中套用至巢狀欄位 payload.pages.page_name

如要深入瞭解陣列和 ARRAY 子查詢,請參閱使用陣列主題。

語意差異

舊版和標準 SQL 在一些語意表達上存在差異。

自動資料類型強制轉換

舊版和標準 SQL 都支援特定資料類型的強制轉換 (自動轉換)。例如,如果查詢將 INT64 類型的值傳送到需要以 FLOAT64 作為輸入值的函式,則 BigQuery 會將該類型的值強制轉換為 FLOAT64 類型。標準 SQL 不支援下列由舊版 SQL 支援的強制類型轉換。在標準 SQL 中,您必須改為使用明確的 CAST

  • INT64 常值強制轉換為 TIMESTAMP。改為使用 TIMESTAMP_MICROS(micros_value)
  • STRING 常值強制轉換為 INT64FLOAT64BOOL。改為使用 CAST(str AS INT64)CAST(str AS FLOAT64)CAST(str AS BOOL)
  • STRING 強制轉換為 BYTES。改為使用 CAST(str AS BYTES)

執行階段錯誤

在舊版 SQL 中,有些函式會對無效輸入值傳回 NULL,以掩飾查詢或資料中的問題。標準 SQL 則較為嚴格,會在輸入值無效時引發錯誤。

  • 舊版 SQL 不會對任何數學函式和運算子檢查溢位。標準 SQL 加入了溢位檢查功能,會在運算溢位時引發錯誤。這包括 +-* 運算子,SUMAVGSTDDEV 匯總函式,以及其他。
  • 標準 SQL 會在除數為零時發出錯誤,而舊版 SQL 則會傳回 NULL。如要讓標準 SQL 在除數為零時傳回 NULL,請使用 SAFE_DIVIDE
  • 標準 SQL 會在輸入值格式無效或超出目標類型範圍時發出 CAST 錯誤,而舊版 SQL 則會傳回 NULL。在標準 SQL 中,如要避免因無效類型轉換引發錯誤,請使用 SAFE_CAST

巢狀重複結果

使用標準 SQL 執行的查詢會在結果中保留任何巢狀和重複的資料欄,扁平化結果選項不具有任何作用。若要傳回巢狀欄位的最上層資料欄,請使用與資料欄結構相關的 .* 運算子。例如:

#standardSQL
SELECT
  repository.*
FROM
  `bigquery-public-data.samples.github_nested`
LIMIT 5;

若要傳回重複巢狀欄位的最上層資料欄 (STRUCTARRAY),請使用 JOIN 來從重複巢狀欄位中取得跨產品的資料列和元素。例如:

#standardSQL
SELECT
  repository.url,
  page.*
FROM
  `bigquery-public-data.samples.github_nested`
JOIN
  UNNEST(payload.pages) AS page
LIMIT 5;

如要深入瞭解陣列和 ARRAY 子查詢,請參閱使用陣列主題。

NOT IN 條件和 NULL

舊版 SQL 並不會按照 SQL 標準來處理 NOT IN 條件造成的 NULL,而標準 SQL 則會。請思考以下的舊版 SQL 查詢,它要尋找在 GitHub 範例資料表中未以位置出現的字數:

#legacySQL
SELECT COUNT(*)
FROM [bigquery-public-data.samples.shakespeare]
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM [bigquery-public-data.samples.github_nested]
);

這個查詢傳回的計數為 163,716,表示GitHub 資料表中有 163,716 個未以位置出現的字。現在請思考以下的標準 SQL 查詢:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
);

這個查詢傳回的計數為 0。造成如此差異的原因是,根據 NOT IN 在標準 SQL 中的語意,只要右側的任何值為 NULL,就會傳回 NULL。若要以舊版 SQL 查詢得到相同的結果,則需使用 WHERE 子句來排除 NULL 值:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare`
WHERE word NOT IN (
  SELECT actor_attributes.location
  FROM `bigquery-public-data.samples.github_nested`
  WHERE actor_attributes.location IS NOT NULL
);

這個查詢傳回的計數就會是 163,716。或者,您也可以使用 NOT EXISTS 條件:

#standardSQL
SELECT COUNT(*)
FROM `bigquery-public-data.samples.shakespeare` AS t
WHERE NOT EXISTS (
  SELECT 1
  FROM `bigquery-public-data.samples.github_nested`
  WHERE t.word = actor_attributes.location
);

這個查詢也會傳回計數 163,716。如需進一步資訊,請參閱說明文件的比較運算子一節,當中解說了 INNOT INEXISTS 及其他比較運算子的語意差別。

使用者定義 JavaScript 函式中的差異

使用者定義函式主題介紹如何將 JavaScript 使用者定義函式與標準 SQL 一起使用。本節說明使用者定義函式在舊版和標準 SQL 中的一些主要差異。

查詢文字中的函式

在標準 SQL 中,您需將 CREATE TEMPORARY FUNCTION 當做查詢主體的一部分使用,而不是單獨指定的使用者定義函式。單獨定義函式的範例包括在 BigQuery 網頁版 UI 中使用 UDF 編輯器定義,或是在使用 bq CLI 時透過 --udf_resource 標記定義。

請思考以下的標準 SQL 查詢:

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>)
  RETURNS FLOAT64 LANGUAGE js AS """
var sum_of_reciprocals = 0;
for (var i = 0; i < arr.length; ++i) {
  sum_of_reciprocals += 1 / arr[i];
}
return arr.length / sum_of_reciprocals;
""";

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

這個查詢定義了一個名為 HarmonicMean 的 JavaScript 函式,並將其套用至 T 的陣列資料欄 arr

如要深入瞭解使用者定義函式,請參閱使用者定義函式主題。

作用於值而非列的函式

在舊版 SQL 中,JavaScript 函式作用於資料表中的資料列。在標準 SQL 中,如上例所示,JavaScript 函式作用於值。若要使用標準 SQL 傳送一個資料列值至 JavaScript 函式,請定義一個結構與資料表的資料列類型相同的函式。例如:

#standardSQL
-- Takes a struct of x, y, and z and returns a struct with a new field foo.
CREATE TEMPORARY FUNCTION AddField(s STRUCT<x FLOAT64, y BOOL, z STRING>)
  RETURNS STRUCT<x FLOAT64, y BOOL, z STRING, foo STRING> LANGUAGE js AS """
var new_struct = new Object();
new_struct.x = s.x;
new_struct.y = s.y;
new_struct.z = s.z;
if (s.y) {
  new_struct.foo = 'bar';
} else {
  new_struct.foo = 'baz';
}

return new_struct;
""";

WITH T AS (
  SELECT x, MOD(off, 2) = 0 AS y, CAST(x AS STRING) AS z
  FROM UNNEST([5.0, 4.0, 3.0, 2.0, 1.0]) AS x WITH OFFSET off
)
SELECT AddField(t).*
FROM T AS t;

這個查詢定義了一個結構與 T 的資料列類型相同的 JavaScript 函式,並建立了一個包含名為 foo 的額外欄位的新結構。SELECT 陳述式會將資料列 t 當做輸入值傳送至函式,並使用 .* 在輸出值中傳回生成結構的欄位。

標準 SQL 的重點特色

本節討論標準 SQL 與舊版 SQL 相比的一些重點特色。

可使用 WITH 子句撰寫

本頁面提供一些使用 WITH 子句的標準 SQL 範例,可用來擷取或重新使用已命名的子查詢。例如:

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
)
SELECT x / (SELECT SUM(x) FROM T) AS weighted_x
FROM T;

這個查詢定義了一個含有 1、2、3、4 等 x 個值組的名稱子查詢 T。它從 T 中選取了 x 個值,並將其除以 T 中所有 x 個值的總和。這個查詢等同於內嵌 T 內容的查詢:

#standardSQL
SELECT
  x / (SELECT SUM(x)
       FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x)) AS weighted_x
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

舉另一個例子來說,請思考以下這個使用多個名稱子查詢的查詢:

#standardSQL
WITH T AS (
  SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
),
TPlusOne AS (
  SELECT x + 1 AS y
  FROM T
),
TPlusOneTimesTwo AS (
  SELECT y * 2 AS z
  FROM TPlusOne
)
SELECT z
FROM TPlusOneTimesTwo;

這個查詢定義了原始資料的轉換序列,後接一個針對 TPlusOneTimesTwoSELECT 陳述式。此查詢等同於下面這個內嵌運算式的查詢:

#standardSQL
SELECT (x + 1) * 2 AS z
FROM (SELECT x FROM UNNEST([1, 2, 3, 4]) AS x);

詳情請參閱說明文件中的 WITH 子句主題。

可使用 SQL 函式撰寫

標準 SQL 支援使用者定義 SQL 函式。您可以使用自訂的 SQL 函式來定義常見的運算式,然後從查詢參照它們。例如:

#standardSQL
-- Computes the harmonic mean of the elements in 'arr'.
-- The harmonic mean of x_1, x_2, ..., x_n can be expressed as:
--   n / ((1 / x_1) + (1 / x_2) + ... + (1 / x_n))
CREATE TEMPORARY FUNCTION HarmonicMean(arr ARRAY<FLOAT64>) AS
(
  ARRAY_LENGTH(arr) / (SELECT SUM(1 / x) FROM UNNEST(arr) AS x)
);

WITH T AS (
  SELECT GENERATE_ARRAY(1.0, x * 4, x) AS arr
  FROM UNNEST([1, 2, 3, 4, 5]) AS x
)
SELECT arr, HarmonicMean(arr) AS h_mean
FROM T;

這個查詢定義了一個名為 HarmonicMean 的 SQL 函式,然後將其套用至 T 的陣列資料欄 arr

在更多地方的子查詢

標準 SQL 可以讓您在 SELECT 清單、WHERE 子句和查詢中任何需要有運算式的地方使用子查詢。例如,請思考以下的標準 SQL 查詢,這個查詢計算了 2015 年西雅圖溫暖日的分數:

#standardSQL
WITH SeattleWeather AS (
  SELECT *
  FROM `bigquery-public-data.noaa_gsod.gsod2015`
  WHERE stn = '994014'
)
SELECT
  COUNTIF(max >= 70) /
    (SELECT COUNT(*) FROM SeattleWeather) AS warm_days_fraction
FROM SeattleWeather;

西雅圖氣象站的代號為 '994014'。這個查詢根據氣溫達華氏 70 度或約攝氏 21 度的條件計算出溫暖日數,並將其除以 2015 年該站的總記錄天數。

相互關聯的子查詢

在標準 SQL 中,子查詢可以參照相互關聯的資料欄;也就是說,源自於外部查詢的資料欄。例如,請思考以下的標準 SQL 查詢:

#standardSQL
WITH WashingtonStations AS (
  SELECT weather.stn AS station_id, ANY_VALUE(station.name) AS name
  FROM `bigquery-public-data.noaa_gsod.stations` AS station
  INNER JOIN `bigquery-public-data.noaa_gsod.gsod2015` AS weather
  ON station.usaf = weather.stn
  WHERE station.state = 'WA' AND station.usaf != '999999'
  GROUP BY station_id
)
SELECT washington_stations.name,
  (SELECT COUNT(*)
   FROM `bigquery-public-data.noaa_gsod.gsod2015` AS weather
   WHERE washington_stations.station_id = weather.stn
   AND max >= 70) AS warm_days
FROM WashingtonStations AS washington_stations
ORDER BY warm_days DESC;

這個查詢計算了華盛頓州的氣象站名稱,以及 2015 年中華盛頓州各地氣溫達到華氏 70 度或約攝氏 21 度的日數。請注意,SELECT 清單中有一個子查詢,而該子查詢參照了外部的 washington_stations.station_id,即 FROM WashingtonStations AS washington_stations

陣列和結構

ARRAYSTRUCT 是標準 SQL 中非常強大的概念。兩者的使用範例,請見下方的查詢;該查詢計算了 HackerNews 資料集中每天前兩名的文章:

#standardSQL
WITH TitlesAndScores AS (
  SELECT
    ARRAY_AGG(STRUCT(title, score)) AS titles,
    EXTRACT(DATE FROM time_ts) AS date
  FROM `bigquery-public-data.hacker_news.stories`
  WHERE score IS NOT NULL AND title IS NOT NULL
  GROUP BY date)
SELECT date,
  ARRAY(SELECT AS STRUCT title, score
        FROM UNNEST(titles)
        ORDER BY score DESC
        LIMIT 2)
  AS top_articles
FROM TitlesAndScores
ORDER BY date DESC;

WITH 子句定義了 TitlesAndScores,其中含有兩個資料欄。第一個是一個結構陣列,當中的一個欄位是文章標題,第二個欄位是評分。ARRAY_AGG 運算式會傳回這些結構每天的陣列。

WITH 子句之後的 SELECT 陳述式使用 ARRAY 子查詢來按照 score 排序並傳回每個陣列中前兩名的文章,然後按照日期降序傳回結果。

如要深入瞭解陣列和 ARRAY 子查詢,請參閱使用陣列主題。另請參閱陣列結構的參考資料。

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

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

這個網頁