舊版 SQL 語法、函式和運算子

本文詳細說明舊版 SQL 查詢語法、函式和運算子。BigQuery 偏好的查詢語法是 GoogleSQL 語法。詳情請參閱「舊版 SQL 功能可用性」。

查詢語法

注意：關鍵字不區分大小寫。在本文件中，SELECT 等關鍵字會以大寫表示，僅供說明之用。

SELECT 子句

SELECT 子句會指定要計算的運算式清單。SELECT 子句中的運算式可包含欄位名稱、常值和函式呼叫 (包括匯總函式和視窗函式)，以及這三者的組合。運算式清單以半形逗號分隔。

如要為運算式指定別名，請在運算式後方加上空格和 ID。您也可選擇在運算式和別名之間加上 AS 關鍵字來提升可讀性。在 SELECT 子句中定義的別名，可以在查詢的 GROUP BY、HAVING 和 ORDER BY 子句中參照，但不能由 FROM、WHERE 或 OMIT RECORD IF 子句參照，也不能由相同 SELECT 子句中的其他運算式參照。

注意：

• 如果您在 SELECT 子句中使用匯總函式，則必須在所有運算式中使用匯總函式，或者查詢必須包含 GROUP BY 子句，其中包含 SELECT 子句中所有非匯總欄位做為分組索引鍵。例如：

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word,
    corpus; /* Succeeds because all non-aggregated fields are group keys. */

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word; /* Fails because corpus is not aggregated nor is it a group key. */

• 您可以使用方括號逸出保留字詞，以便將其做為欄位名稱和別名。舉例來說，如果資料欄名稱為「partition」(BigQuery 語法中的保留字詞)，參照該欄位的查詢會失敗，並顯示模糊的錯誤訊息，除非您使用方括號逸出該欄位：

  SELECT [partition] FROM ...

範例

這個範例在 SELECT 子句中定義別名，然後在 ORDER BY 子句中參照其中一個別名。請注意，WHERE 子句中的 word_alias 無法參照 word 欄，必須依名稱參照。len 別名也不會顯示在 WHERE 子句中。HAVING 子句會顯示該變數。

#legacySQL
SELECT
  word AS word_alias,
  LENGTH(word) AS len
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  word CONTAINS 'th'
ORDER BY
  len;

匯總函式的 WITHIN 修飾符

aggregate_function WITHIN RECORD [ [ AS ] alias ]

WITHIN 關鍵字會使匯總函式匯總每筆記錄中的重複值。且只會為每項輸入記錄產生一個匯總結果。這類匯總稱為「範圍匯總」。由於範圍匯總會為每筆記錄產生輸出內容，因此您可以選取非匯總運算式和範圍匯總運算式，而不必使用 GROUP BY 子句。

使用限定範圍匯總時，最常使用 RECORD 範圍。如果您有非常複雜的巢狀重複結構定義，就可能需要在子記錄範圍中執行匯總作業，只要將上述語法中的 RECORD 關鍵字，替換成結構定義中要執行匯總的節點名稱，即可完成這項操作。如要進一步瞭解這項進階行為，請參閱「處理資料」。

範例

這個範例會執行範圍 COUNT 匯總，然後依匯總值篩選及排序記錄。

#legacySQL
SELECT
  repository.url,
  COUNT(payload.pages.page_name) WITHIN RECORD AS page_count
FROM
  [bigquery-public-data:samples.github_nested]
HAVING
  page_count > 80
ORDER BY
  page_count DESC;

FROM 子句

FROM
  [project_name:]datasetId.tableId [ [ AS ] alias ] |
  (subquery) [ [ AS ] alias ] |
  JOIN clause |
  FLATTEN clause |
  table wildcard function

FROM 子句會指定要查詢的來源資料。BigQuery 查詢可以直接對資料表、子查詢、彙整資料表，以及經由下述特殊用途運算子修改的資料表執行。您可以使用逗號 (BigQuery 中的 UNION ALL 運算子) 查詢這些資料來源的組合。

參照資料表

參照資料表時，必須同時指定 datasetId 和 tableId，project_name 則可視需要使用。如未指定 project_name，BigQuery 預設會使用目前的專案。如果專案名稱包含破折號，則必須以括號括住整個資料表參照。

範例

[my-dashed-project:dataset1.tableName]

如要為資料表設定別名，請在資料表名稱後方依序加上空格和 ID。您可以在 tableId 和別名之間加入選用的 AS 關鍵字，提高可讀性。

參照資料表中的資料欄時，您可以使用簡單的資料欄名稱，也可以在資料欄名稱前面加上別名 (如有指定)，或加上 datasetId 和 tableId (只要未指定 project_name 即可)。project_name 不得包含在資料欄前置字元中，因為欄位名稱不得使用冒號字元。

範例

這個範例參照的資料欄沒有資料表前置字元。

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

這個範例會在資料欄名稱前加上 datasetId 和 tableId。請注意，這個範例中不能包含 project_name。只有在資料集位於目前的預設專案中時，這個方法才有效。

#legacySQL
SELECT
  samples.shakespeare.word
FROM
  samples.shakespeare;

這個範例會在資料欄名稱前加上資料表別名。

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

整數範圍分區資料表

舊版 SQL 支援使用資料表修飾符，在整數範圍分區資料表中指定特定分區。範圍分區的定址鍵是範圍的起點。

以下範例查詢以 30 開頭的範圍分區：

#legacySQL
SELECT
  *
FROM
  dataset.table$30;

請注意，您無法使用舊版 SQL 查詢整個整數範圍分區資料表。而是傳回錯誤訊息，如下所示：

Querying tables partitioned on a field is not supported in Legacy SQL

使用子查詢

「子查詢」是巢狀 SELECT 陳述式，以括號括住。在子查詢的 SELECT 子句中運算的運算式，可供外部查詢使用，就像資料表的資料欄一樣。

子查詢可用於計算匯總和其他運算式。且您可以在當中使用所有的 SQL 運算子。這表示子查詢本身可包含其他子查詢，且能夠執行聯結和分組匯總等作業。

逗號為UNION ALL

與 GoogleSQL 不同，舊版 SQL 使用逗號做為 UNION ALL 運算子，而非 CROSS JOIN 運算子。這是舊版行為，因為 BigQuery 過去不支援 CROSS JOIN，而 BigQuery 使用者經常需要編寫 UNION ALL 查詢。在 GoogleSQL 中，執行聯集的查詢特別冗長。而使用逗號做為聯結運算子可讓使用者更有效率地撰寫這類查詢。舉例來說，這項查詢可用於對多天的記錄執行單一查詢。

#legacySQL
SELECT
  FORMAT_UTC_USEC(event.timestamp_in_usec) AS time,
  request_url
FROM
  [applogs.events_20120501],
  [applogs.events_20120502],
  [applogs.events_20120503]
WHERE
  event.username = 'root' AND
  NOT event.source_ip.is_internal;

一般來說，合併大量資料表的查詢，執行速度會比從單一資料表處理相同資料量的查詢慢。兩者的效能差異最高可達每個額外資料表 50 毫秒。單一查詢最多可聯集 1,000 個資料表。

表格萬用字元函式

「表格萬用字元函式」一詞是指 BigQuery 獨有的一種特殊函式。這些函式用於 FROM 子句，可使用多種類型的篩選器之一，比對一組資料表名稱。例如，TABLE_DATE_RANGE 函式可用於僅查詢一組特定的每日資料表。如要進一步瞭解這些函式，請參閱「表格萬用字元函式」。

FLATTEN 運算子

(FLATTEN([project_name:]datasetId.tableId, field_to_be_flattened))
(FLATTEN((subquery), field_to_be_flattened))

與一般 SQL 處理系統不同，BigQuery 專為處理重複資料而設計。因此，BigQuery 使用者有時會需要撰寫操弄重複記錄結構的查詢，其中一個方法是使用 FLATTEN 運算子。

FLATTEN 會將結構定義中的一個節點從重複型式轉換成選用型式。如果記錄中有一或多個重複欄位值，FLATTEN 會建立多筆記錄，每筆記錄對應重複欄位中的一個值。系統會在每個新的輸出記錄中，複製記錄中所有其他已選取的欄位。FLATTEN 可重複套用，以移除多個重複層級。

如需更多資訊和範例，請參閱「處理資料」。

JOIN 運算子

BigQuery 支援在每個 FROM 子句中使用多個 JOIN 運算子。後續的 JOIN 作業會使用前一個 JOIN 作業的結果，做為左側 JOIN 輸入。任何前置 JOIN 輸入內容中的欄位，都可做為後續 JOIN 運算子 ON 子句中的索引鍵。

JOIN 類型

BigQuery 支援 INNER、[FULL|RIGHT|LEFT] OUTER 和 CROSS JOIN 作業。如未指定，預設值為 INNER。

CROSS JOIN 作業不允許 ON 子句。CROSS JOIN 可能會傳回大量資料，導致查詢速度緩慢、效率不彰，或超出每項查詢允許的資源上限。這類查詢會發生錯誤。建議盡可能使用不含 CROSS JOIN 的查詢。舉例來說，CROSS JOIN 通常用於視窗函式更有效率的地方。

EACH 修飾符

EACH 修飾符是提示，指示 BigQuery 使用多個分區執行 JOIN。如果您知道 JOIN 的兩側都很大，這項功能就特別實用。EACH 修飾符無法用於 CROSS JOIN 子句。

過去，我們會鼓勵使用者在許多情況下使用 EACH，如今則不建議這樣做。請盡可能使用不含 EACH 修飾符的 JOIN，以提升效能。如果查詢失敗，並顯示資源超出上限的錯誤訊息，請使用 JOIN EACH。

半聯結和反聯結

除了在 FROM 子句中支援 JOIN 之外，BigQuery 也支援 WHERE 子句中的兩種 join 類型：半 join 和反半 join。半聯結是使用 IN 關鍵字和子查詢指定，反聯結則是使用 NOT IN 關鍵字。

範例

下列查詢使用半聯結來尋找 ngram，其中 ngram 的第一個字詞也是另一個 ngram 的第二個字詞，而該 ngram 的第三個字詞為「AND」。

#legacySQL
SELECT
  ngram
FROM
  [bigquery-public-data:samples.trigrams]
WHERE
  first IN (SELECT
              second
            FROM
              [bigquery-public-data:samples.trigrams]
            WHERE
              third = "AND")
LIMIT 10;

下列查詢使用半聯結，傳回在出生人數最多的 10 個州中，50 歲以上女性的生育人數。

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state IN (SELECT
              state
            FROM
              (SELECT
                 state,
                 COUNT(state) total
               FROM
                 [bigquery-public-data:samples.natality]
               GROUP BY
                 state
               ORDER BY
                 total DESC
               LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

如要查看其他 40 個州的數字，可以使用反向連結。下列查詢與前一個範例幾乎相同，但使用 NOT IN 而非 IN，傳回 40 個出生人數最少的州中，50 歲以上女性的出生人數。

#legacySQL
SELECT
  mother_age,
  COUNT(mother_age) total
FROM
  [bigquery-public-data:samples.natality]
WHERE
  state NOT IN (SELECT
                  state
                FROM
                  (SELECT
                     state,
                     COUNT(state) total
                   FROM
                     [bigquery-public-data:samples.natality]
                   GROUP BY
                     state
                   ORDER BY
                     total DESC
                   LIMIT 10))
  AND mother_age > 50
GROUP BY
  mother_age
ORDER BY
  mother_age DESC

注意：

• BigQuery 不支援關聯半聯結或反半聯結。子查詢無法參照外部查詢中的任何欄位。
• 半聯結或反半聯結中使用的子查詢，必須只選取一個欄位。
• 所選欄位的類型，必須與 WHERE 子句中外部查詢使用的欄位類型完全相符。BigQuery 不會對半聯結或反半聯結執行任何類型的強制轉型。

WHERE 子句

WHERE 子句 (有時稱為述詞) 會使用布林運算式，篩選 FROM 子句產生的記錄。您可以使用布林值 AND 和 OR 子句連結多個條件，並視需要使用括號 () 將條件分組。WHERE 子句中列出的欄位不一定要在對應的 SELECT 子句中選取，且 WHERE 子句運算式無法參照在 WHERE 子句所屬查詢的 SELECT 子句中計算的運算式。

注意：匯總函式無法用於 WHERE 子句。如要篩選匯總函式的輸出內容，請使用 HAVING 子句和外部查詢。

範例

以下範例在 WHERE 子句中使用布林運算式的析取，即由 OR 運算子連結的兩個運算式。如果任一運算式傳回 true，輸入記錄就會通過 WHERE 篩選器。

#legacySQL
SELECT
  word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  (word CONTAINS 'prais' AND word CONTAINS 'ing') OR
  (word CONTAINS 'laugh' AND word CONTAINS 'ed');

OMIT RECORD IF 子句

OMIT RECORD IF 子句是 BigQuery 獨有的結構。特別適合用來處理巢狀重複結構定義。這與 WHERE 子句類似，但有兩個重要差異。首先，這個函式會使用排除條件，也就是說，如果運算式傳回 true，系統就會省略記錄，但如果運算式傳回 false 或 null，系統就會保留記錄。第二，OMIT RECORD IF 子句可以在條件中使用限定範圍的匯總函式 (通常會這麼做)。

除了篩選完整記錄外，OMIT...IF 還能指定更精細的範圍，只篩選記錄的部分內容。方法是在 OMIT...IF 子句中使用結構定義中非葉節點的名稱，而非 RECORD。BigQuery 使用者很少使用這項功能。如要進一步瞭解這項進階行為，請參閱上述 WITHIN 說明文件中的連結。

如果您使用 OMIT...IF 排除重複欄位中的部分記錄，且查詢也選取其他獨立重複欄位，BigQuery 會省略查詢中其他重複記錄的一部分。如果看到錯誤訊息 Cannot perform OMIT IF on repeated scope <scope> with independently repeating pass through field <field>, 建議您改用 GoogleSQL。如要瞭解如何將 OMIT...IF 陳述式遷移至 GoogleSQL，請參閱「遷移至 GoogleSQL」。

範例

回顧 WITHIN 修飾符的範例，OMIT RECORD IF 可用來完成與該範例中 WITHIN 和 HAVING 相同的工作。

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

GROUP BY 子句

GROUP BY 子句可讓您將指定欄位或一組欄位中具有相同值的資料列分組，以便計算相關欄位的匯總。分組作業會在 WHERE 子句執行篩選後進行，但在 SELECT 子句中的運算式計算前進行。運算式結果無法做為 GROUP BY 子句中的群組索引鍵。

範例

這項查詢會在三元組樣本資料集中，找出前十個最常見的第一個字詞。除了示範 GROUP BY 子句的用法外，也示範如何在 GROUP BY 和 ORDER BY 子句中使用位置索引，而非欄位名稱。

#legacySQL
SELECT
  first,
  COUNT(ngram)
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
ORDER BY
  2 DESC
LIMIT 10;

使用 GROUP BY 子句執行的匯總稱為「分組匯總」 。與範圍匯總不同，分組匯總在大多數 SQL 處理系統中都很常見。

EACH 修飾符

EACH 修飾符是提示，指示 BigQuery 使用多個分區執行 GROUP BY。如果您知道資料集包含大量不同的群組鍵值，這項功能就特別實用。

過去在許多情況下，我們都鼓勵使用 EACH，但現在已非如此。使用 GROUP BY 時，不加上 EACH 修飾符通常能獲得較佳成效。如果查詢失敗，並顯示資源超出上限的錯誤訊息，請使用 GROUP EACH BY。

ROLLUP 函式

使用 ROLLUP 函式時，BigQuery 會在查詢結果中新增額外的資料列，代表匯總匯總。ROLLUP 後方列出的所有欄位都必須以一組括號括住。在因 ROLLUP 函式而新增的資料列中，NULL 會指出匯總資料匯總的資料欄。

範例

這項查詢會從出生率範例資料集，產生每年男嬰和女嬰的出生人數。

#legacySQL
SELECT
  year,
  is_male,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

這些是查詢結果。請注意，有些資料列的一或兩個群組索引鍵為 NULL。這些資料列就是匯總列。

+------+---------+----------+
| year | is_male |  count   |
+------+---------+----------+
| NULL |    NULL | 12122730 |
| 2000 |    NULL |  4063823 |
| 2000 |   false |  1984255 |
| 2000 |    true |  2079568 |
| 2001 |    NULL |  4031531 |
| 2001 |   false |  1970770 |
| 2001 |    true |  2060761 |
| 2002 |    NULL |  4027376 |
| 2002 |   false |  1966519 |
| 2002 |    true |  2060857 |
+------+---------+----------+

使用 ROLLUP 函式時，您可以使用 GROUPING 函式，區分因 ROLLUP 函式而新增的資料列，以及群組索引鍵實際具有 NULL 值的資料列。

範例

這項查詢會在前一個範例中加入 GROUPING 函式，以便更準確地找出因 ROLLUP 函式而新增的資料列。

#legacySQL
SELECT
  year,
  GROUPING(year) as rollup_year,
  is_male,
  GROUPING(is_male) as rollup_gender,
  COUNT(1) as count
FROM
  [bigquery-public-data:samples.natality]
WHERE
  year >= 2000
  AND year <= 2002
GROUP BY
  ROLLUP(year, is_male)
ORDER BY
  year,
  is_male;

這些是新查詢傳回的結果。

+------+-------------+---------+---------------+----------+
| year | rollup_year | is_male | rollup_gender |  count   |
+------+-------------+---------+---------------+----------+
| NULL |           1 |    NULL |             1 | 12122730 |
| 2000 |           0 |    NULL |             1 |  4063823 |
| 2000 |           0 |   false |             0 |  1984255 |
| 2000 |           0 |    true |             0 |  2079568 |
| 2001 |           0 |    NULL |             1 |  4031531 |
| 2001 |           0 |   false |             0 |  1970770 |
| 2001 |           0 |    true |             0 |  2060761 |
| 2002 |           0 |    NULL |             1 |  4027376 |
| 2002 |           0 |   false |             0 |  1966519 |
| 2002 |           0 |    true |             0 |  2060857 |
+------+-------------+---------+---------------+----------+

注意：

• SELECT 子句中的非匯總欄位「必須」列在 GROUP BY 子句中。

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word,
    corpus; /* Succeeds because all non-aggregated fields are group keys. */

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word)
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word;  /* Fails because corpus is not aggregated nor is it a group key. */

• 在 SELECT 子句中計算的運算式無法用於對應的 GROUP BY 子句。

  #legacySQL
  SELECT
    word,
    corpus,
    COUNT(word) word_count
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th"
  GROUP BY
    word,
    corpus,
    word_count;  /* Fails because word_count is not visible to this GROUP BY clause. */

• 不支援依浮點數和雙精度值分組，因為這些類型的等式函式未妥善定義。
• 由於系統採互動式，因此產生大量群組的查詢可能會失敗。使用 TOP 函式而非 GROUP BY，或許能解決部分縮放問題。

HAVING 子句

HAVING 子句的行為與 WHERE 子句完全相同，但會在 SELECT 子句之後評估，因此 HAVING 子句會顯示所有運算運算式的結果。HAVING 子句只能參照對應 SELECT 子句的輸出內容。

範例

這項查詢會計算 ngram 範例資料集中最常見的第一個字詞，其中包含字母 a 且最多出現 10,000 次。

#legacySQL
SELECT
  first,
  COUNT(ngram) ngram_count
FROM
  [bigquery-public-data:samples.trigrams]
GROUP BY
  1
HAVING
  first contains "a"
  AND ngram_count < 10000
ORDER BY
  2 DESC
LIMIT 10;

ORDER BY 子句

ORDER BY 子句會使用一或多個索引鍵欄位，以遞增或遞減順序排序查詢結果。若要按多個欄位或別名進行排序，請以逗號分隔的清單輸入這些欄位或別名。結果會按照欄位列出的順序排序。 使用 DESC (遞減) 或 ASC (遞增) 指定排序方向。 預設值為 ASC。您可以為每個排序鍵指定不同的排序方向。

ORDER BY 子句會在 SELECT 子句之後評估，因此可以參照 SELECT 中計算的任何運算式輸出內容。如果欄位在 SELECT 子句中獲得別名，則必須在 ORDER BY 子句中使用該別名。

LIMIT 子句

LIMIT 子句會限制傳回結果集中的資料列數量。由於 BigQuery 查詢通常會對大量資料列執行，因此 LIMIT 是避免查詢執行時間過長的好方法，因為它只會處理一部分的資料列。

注意：

• LIMIT 子句會在滿足您的需求時停止處理並傳回結果。這可以縮短某些查詢的處理時間，但如果指定匯總函式 (例如 COUNT 或 ORDER BY 子句)，則仍須處理完整結果集，才能傳回結果。LIMIT 子句是最後評估的子句。
• 如果查詢中沒有可保證輸出結果集排序的運算子，則含有 LIMIT 子句的查詢仍可能不具確定性。這是因為 BigQuery 執行時會使用大量平行運算單元，不保證平行工作的傳回順序。
• LIMIT 子句不得包含任何函式，只能使用數值常數。
• 使用 LIMIT 子句時，同一查詢處理的總位元組數和計費位元組數可能會有所不同。

查詢文法

上文詳細說明瞭 BigQuery SELECT 陳述式的個別子句。以下我們以精簡形式呈現 SELECT 陳述式的完整語法，並提供返回個別章節的連結。

query:
    SELECT { * | field_path.* | expression } [ [ AS ] alias ] [ , ... ]
    [ FROM from_body
      [ WHERE bool_expression ]
      [ OMIT RECORD IF bool_expression]
      [ GROUP [ EACH ] BY [ ROLLUP ] { field_name_or_alias } [ , ... ] ]
      [ HAVING bool_expression ]
      [ ORDER BY field_name_or_alias [ { DESC | ASC } ] [, ... ] ]
      [ LIMIT n ]
    ];

from_body:
    {
      from_item [, ...] |  # Warning: Comma means UNION ALL here
      from_item [ join_type ] JOIN [ EACH ] from_item [ ON join_predicate ] |
      (FLATTEN({ table_name | (query) }, field_name_or_alias)) |
      table_wildcard_function
    }

from_item:
    { table_name | (query) } [ [ AS ] alias ]

join_type:
    { INNER | [ FULL ] [ OUTER ] | RIGHT [ OUTER ] | LEFT [ OUTER ] | CROSS }

join_predicate:
    field_from_one_side_of_the_join = field_from_the_other_side_of_the_join [ AND ...]

expression:
    {
      literal_value |
      field_name_or_alias |
      function_call
    }

bool_expression:
    {
      expression_which_results_in_a_boolean_value |
      bool_expression AND bool_expression |
      bool_expression OR bool_expression |
      NOT bool_expression
    }

標記法：

• 方括號 [ ] 表示選擇性子句。
• 大括號 { } 是用來括住一組選項。
• 分隔號 "|" 表示邏輯上的「或」。
• 逗號或關鍵字後面接著方括號內的省略符號「[, ... ]」，表示前一個項目可以在清單中重複，並以指定的分隔符號分隔。
• 括號 "( )" 表示文字括號。

支援的函式與運算子

大部分的 SELECT 陳述式子句都支援函式。函式中參照的欄位不一定要列在任何 SELECT 子句中。因此，即使未直接顯示 clicks 欄位，下列查詢仍有效：

#legacySQL
SELECT country, SUM(clicks) FROM table GROUP BY country;

匯總函式
AVG()	傳回一組資料列的平均值...
BIT_AND()	傳回位元 AND 運算的結果...
BIT_OR()	傳回位元 OR 運算的結果...
BIT_XOR()	傳回位元 XOR 運算的結果...
CORR()	傳回一組數字對的皮爾森相關係數。
COUNT()	傳回值的總數...
COUNT([DISTINCT])	傳回非 NULL 值的總數...
COVAR_POP()	計算值的母體共變異數...
COVAR_SAMP()	計算值的樣本共變異數...
EXACT_COUNT_DISTINCT()	針對指定欄位傳回相異非 NULL 值的確切數量。
FIRST()	傳回函式範圍內的第一個序列值。
GROUP_CONCAT()	將多個字串連結成單一字串...
GROUP_CONCAT_UNQUOTED()	將多個字串連結成單一字串... 不會加上雙引號...
LAST()	傳回最後一個序列值...
MAX()	傳回最大值...
MIN()	傳回最小值...
NEST()	將目前匯總範圍內的所有值匯總成一個重複欄位。
NTH()	傳回第 n 個序列值...
QUANTILES()	計算概略的最小值、最大值和分位數...
STDDEV()	傳回標準差...
STDDEV_POP()	計算母體標準差...
STDDEV_SAMP()	計算樣本標準差...
SUM()	傳回值的總和...
TOP() ... COUNT(*)	依照頻率傳回主要的 max_records 記錄。
UNIQUE()	傳回一組不重複非 NULL 值...
VARIANCE()	計算值的變異數...
VAR_POP()	計算值的母體變異數...
VAR_SAMP()	計算值的樣本變異數...

算術運算子
+	加
-	減
*	乘
/	除
%	模數

位元函式
&	位元 AND
|	位元 OR
^	位元 XOR
<<	向左移位
>>	向右移位
~	位元 NOT
BIT_COUNT()	傳回位元數量...

轉換函式
BOOLEAN()	轉換成布林值。
BYTES()	轉換成位元組。
CAST(expr AS type)	將 expr 轉換為 type 類型的變數。
FLOAT()	轉換成雙精度浮點數。
HEX_STRING()	轉換成十六進位字串。
INTEGER()	轉換成整數。
STRING()	轉換成字串。

比較函式
expr1 = expr2	如果運算式相等，則傳回 true。
expr1 != expr2 expr1 <> expr2	如果運算式不相等，則傳回 true。
expr1 > expr2	如果 expr1 大於 expr2，則傳回 true。
expr1 < expr2	如果 expr1 小於 expr2，則傳回 true。
expr1 >= expr2	如果 expr1 大於或等於 expr2，則傳回 true。
expr1 <= expr2	如果 expr1 小於或等於 expr2，則傳回 true。
expr1 BETWEEN expr2 AND expr3	如果 expr1 的值介於 expr2 和 expr3 之間 (含頭尾)，則傳回 true。
expr IS NULL	如果 expr 為 NULL，則傳回 true。
expr IN()	如果 expr 與 expr1、expr2 或括號中的任何值相符，則傳回 true。
COALESCE()	傳回第一個非 NULL 的引數。
GREATEST()	傳回最大的 numeric_expr 參數。
IFNULL()	如果引數不是空值，則會傳回引數。
IS_INF()	如果是正無限大或負無限大，則傳回 true。
IS_NAN()	如果引數為 NaN，則傳回 true。
IS_EXPLICITLY_DEFINED()	已淘汰：請改用 expr IS NOT NULL。
LEAST()	傳回最小的引數 numeric_expr 參數。
NVL()	如果 expr 不是空值，則傳回 expr，否則傳回 null_default。

日期和時間函式
CURRENT_DATE()	以 %Y-%m-%d 格式傳回目前日期。
CURRENT_TIME()	以 %H:%M:%S 格式傳回伺服器的目前時間。
CURRENT_TIMESTAMP()	以 %Y-%m-%d %H:%M:%S 格式傳回伺服器的目前時間。
DATE()	傳回日期的格式為 %Y-%m-%d。
DATE_ADD()	在 TIMESTAMP 類型的資料中加上指定時間間隔。
DATEDIFF()	傳回兩個 TIMESTAMP 類型資料之間的天數。
DAY()	傳回 1 到 31 之間的整數，代表當月第幾天。
DAYOFWEEK()	傳回 1 (星期日) 到 7 (星期六) 之間的整數，代表當週第幾天。
DAYOFYEAR()	傳回 1 到 366 之間的整數，代表該年度的第幾天。
FORMAT_UTC_USEC()	傳回 UNIX 時間戳記，格式為 YYYY-MM-DD HH:MM:SS.uuuuuu。
HOUR()	傳回 0 到 23 之間的整數，代表 TIMESTAMP 的小時部分。
MINUTE()	傳回 0 到 59 之間的整數，代表 TIMESTAMP 的分鐘部分。
MONTH()	傳回 1 到 12 之間的整數，代表 TIMESTAMP 的月份。
MSEC_TO_TIMESTAMP()	將以毫秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP。
NOW()	以微秒為單位傳回目前的 UNIX 時間戳記。
PARSE_UTC_USEC()	將日期字串轉換成以微秒為單位的 UNIX 時間戳記。
QUARTER()	傳回 1 到 4 之間的整數，代表 TIMESTAMP 的季別。
SEC_TO_TIMESTAMP()	將以秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP。
SECOND()	傳回 0 到 59 之間的整數，代表 TIMESTAMP 的秒部分。
STRFTIME_UTC_USEC()	以 date_format_str 的格式傳回日期字串。
TIME()	傳回 %H:%M:%S 格式的時間戳記。
TIMESTAMP()	將日期字串轉換成 TIMESTAMP。
TIMESTAMP_TO_MSEC()	將 TIMESTAMP 轉換成以毫秒為單位的 UNIX 時間戳記。
TIMESTAMP_TO_SEC()	將 TIMESTAMP 轉換成以秒為單位的 UNIX 時間戳記。
TIMESTAMP_TO_USEC()	將 TIMESTAMP 轉換成以微秒為單位的 UNIX 時間戳記。
USEC_TO_TIMESTAMP()	將以微秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP。
UTC_USEC_TO_DAY()	將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生日期的開頭。
UTC_USEC_TO_HOUR()	將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生時間 (小時) 的開頭。
UTC_USEC_TO_MONTH()	將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生月份的開頭。
UTC_USEC_TO_WEEK()	傳回以微秒為單位的 UNIX 時間戳記，代表當週的某一天。
UTC_USEC_TO_YEAR()	傳回以微秒為單位的 UNIX 時間戳記，代表年份。
WEEK()	傳回 1 到 53 間的整數，代表 TIMESTAMP 的週數部分。
YEAR()	傳回 TIMESTAMP 的年份。

IP 函式
FORMAT_IP()	將 integer_value 的 32 個最低有效位元轉換為人類可讀的 IPv4 位址字串。
PARSE_IP()	將用來表示 IPv4 位址的字串轉換為無正負號整數值。
FORMAT_PACKED_IP()	以 10.1.5.23 或 2620:0:1009:1:216:36ff:feef:3f 格式傳回人類可讀的 IP 位址。
PARSE_PACKED_IP()	以 BYTES 格式傳回 IP 位址。

JSON 函式
JSON_EXTRACT()	根據 JSONPath 運算式選取值並傳回 JSON 字串。
JSON_EXTRACT_SCALAR()	根據 JSONPath 運算式選取值並傳回 JSON 純量值。

邏輯運算子
expr AND expr	如果兩個運算式皆為 True，則傳回 true。
expr OR expr	如果其中一個或兩個運算式為 True，則傳回 true。
NOT expr	如果運算式為 False，則傳回 true。

數學函式
ABS()	傳回引數的絕對值。
ACOS()	傳回引數的反餘弦。
ACOSH()	傳回引數的反雙曲餘弦。
ASIN()	傳回引數的反正弦。
ASINH()	傳回引數的反雙曲正弦。
ATAN()	傳回引數的反正切。
ATANH()	傳回引數的反雙曲正切。
ATAN2()	傳回兩個引數的反正切。
CEIL()	將引數向上四捨五入至最接近的整數，並傳回四捨五入值。
COS()	傳回引數的餘弦。
COSH()	傳回引數的雙曲餘弦。
DEGREES()	將弧度轉換成角度。
EXP()	傳回 e 的引數次方。
FLOOR()	將引數向下四捨五入至最接近的整數。
LN() LOG()	傳回引數的自然對數。
LOG2()	傳回引數以 2 為底的對數。
LOG10()	傳回引數以 10 為底的對數。
PI()	傳回常數 π。
POW()	傳回第一引數的第二引數次方。
RADIANS()	將角度轉換成弧度。
RAND()	傳回隨機浮點值，範圍為 0.0 <= 值 < 1.0。
ROUND()	將引數向上或向下四捨五入至最接近的整數。
SIN()	傳回引數的正弦。
SINH()	傳回引數的雙曲正弦。
SQRT()	傳回運算式的平方根。
TAN()	傳回引數的正切。
TANH()	傳回引數的雙曲正切。

規則運算式函式
REGEXP_MATCH()	如果引數與規則運算式相符，則會傳回 true。
REGEXP_EXTRACT()	傳回引數在規則運算式中與擷取群組相符的部分。
REGEXP_REPLACE()	取代與規則運算式相符的子字串。

字串函式
CONCAT()	傳回兩個以上字串的串連結果；如果有任何值為 NULL，則會傳回 NULL。
expr CONTAINS 'str'	如果 expr 包含指定的字串引數，則傳回 true。
INSTR()	傳回第一次出現指定字串的索引 (索引從 1 開始)。
LEFT()	傳回字串最左側的字元。
LENGTH()	傳回字串長度。
LOWER()	傳回所有字元都是小寫的原始字串。
LPAD()	在字串左側插入字元。
LTRIM()	從字串左側開始移除字元。
REPLACE()	取代所有的指定子字串。
RIGHT()	傳回字串最右側的字元。
RPAD()	在字串右側插入字元。
RTRIM()	移除字串右側的尾隨字元。
SPLIT()	將字串拆成重複的子字串。
SUBSTR()	傳回子字串...
UPPER()	傳回所有字元都是大寫的原始字串。

表格萬用字元函式
TABLE_DATE_RANGE()	查詢特定時間範圍內的多個每日資料表。
TABLE_DATE_RANGE_STRICT()	查詢特定時間範圍內 (未缺少任何日期) 的多個每日資料表。
TABLE_QUERY()	查詢名稱與指定述詞相符的資料表。

網址函式
HOST()	根據指定網址，以字串形式傳回主機名稱。
DOMAIN()	針對指定網址以字串形式傳回網域。
TLD()	根據指定的網址，傳回網址中的頂層網域和任何國家/地區網域。

窗型函式
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM()	執行與對應匯總函式相同的運算，不過是透過由 OVER 子句所定義的窗體計算。
CUME_DIST()	傳回雙精度浮點數，代表值在一組值中的累積分佈...
DENSE_RANK()	傳回值在一組值中的整數排名。
FIRST_VALUE()	傳回窗體中指定欄位的第一個值。
LAG()	讓您可讀取窗體中前一資料列的資料。
LAST_VALUE()	傳回窗體中指定欄位的最後一個值。
LEAD()	讓您可讀取窗體中下一資料列的資料。
NTH_VALUE()	傳回視窗框架 <n> 位置的 <expr> 值 ...
NTILE()	將窗體分成指定的區塊數。
PERCENT_RANK()	傳回目前資料列在分區所有資料列中的排名。
PERCENTILE_CONT()	傳回對應至窗體相關百分位引數的內插值...
PERCENTILE_DISC()	傳回窗體內與引數百分位數最接近的值。
RANK()	傳回值在一組值中的整數排名。
RATIO_TO_REPORT()	傳回各個值在值總和中所佔的比例。
ROW_NUMBER()	傳回窗體內查詢結果的目前資料列編號。

其他函式
CASE WHEN ... THEN	使用 CASE 即可在查詢中選擇兩個以上的替代運算式。
CURRENT_USER()	傳回執行查詢的使用者所登入的電子郵件地址。
EVERY()	如果引數的所有輸入內容皆為 true，則會傳回 true。
FROM_BASE64()	將採 Base-64 編碼的輸入字串轉換成 BYTES 格式。
HASH()	計算並傳回 64 位元的已簽署雜湊值...
FARM_FINGERPRINT()	計算並傳回 64 位元的已簽署指紋值...
IF()	如果第一個引數為 true，就會傳回第二個引數；如果不是 true，則傳回第三個引數。
POSITION()	傳回引數在序列 (從 1 開始) 中的位置。
SHA1()	傳回 SHA1 雜湊，格式為 BYTES。
SOME()	如果引數的輸入內容中至少有一個是 true，則會傳回 true。
TO_BASE64()	將 BYTES 引數轉換成採 Base-64 編碼的字串。

匯總函式

匯總函式傳回的值代表較大資料集的摘要，因此這類函式格外適合用來分析記錄。匯總函式會針對一組值進行運算，並會針對每個資料表、群組或範圍傳回一個值：

• 資料表匯總

  使用匯總函式，匯總資料表中所有符合條件的資料列。例如：

  SELECT COUNT(f1) FROM ds.Table;

• 群組匯總

  使用匯總函式和指定非匯總欄位的 GROUP BY 子句，按群組匯總資料列。例如：

  SELECT COUNT(f1) FROM ds.Table GROUP BY b1;

  TOP 函式代表群組匯總的特殊案例。

• 範圍匯總

  這項功能僅適用於含有巢狀欄位的資料表。
  使用匯總函式和 WITHIN 關鍵字，匯總定義範圍內的重複值。例如：

  SELECT COUNT(m1.f2) WITHIN RECORD FROM Table;

  範圍可以是 RECORD (對應至整個資料列)，也可以是節點 (資料列中的重複欄位)。匯總函式會對範圍內的值進行運算，然後針對每個紀錄或節點傳回匯總結果。

您可以使用下列其中一個選項，對匯總函式套用限制：

• Subselect 查詢中的別名。限制條件是透過外部 WHERE 子句指定。

  #legacySQL
  SELECT corpus, count_corpus_words
  FROM
    (SELECT corpus, count(word) AS count_corpus_words
    FROM [bigquery-public-data:samples.shakespeare]
    GROUP BY corpus) AS sub_shakespeare
  WHERE count_corpus_words > 4000

• HAVING 子句中的別名。

  #legacySQL
  SELECT corpus, count(word) AS count_corpus_words
  FROM [bigquery-public-data:samples.shakespeare]
  GROUP BY corpus
  HAVING count_corpus_words > 4000;

您也可以在 GROUP BY 或 ORDER BY 子句中參照別名。

語法

匯總函式
AVG()	傳回一組資料列的平均值...
BIT_AND()	傳回位元 AND 運算的結果...
BIT_OR()	傳回位元 OR 運算的結果...
BIT_XOR()	傳回位元 XOR 運算的結果...
CORR()	傳回一組數字對的皮爾森相關係數。
COUNT()	傳回值的總數...
COUNT([DISTINCT])	傳回非 NULL 值的總數...
COVAR_POP()	計算值的母體共變異數...
COVAR_SAMP()	計算值的樣本共變異數...
EXACT_COUNT_DISTINCT()	針對指定欄位傳回相異非 NULL 值的確切數量。
FIRST()	傳回函式範圍內的第一個序列值。
GROUP_CONCAT()	將多個字串連結成單一字串...
GROUP_CONCAT_UNQUOTED()	將多個字串連結成單一字串... 不會加上雙引號...
LAST()	傳回最後一個序列值...
MAX()	傳回最大值...
MIN()	傳回最小值...
NEST()	將目前匯總範圍內的所有值匯總成一個重複欄位。
NTH()	傳回第 n 個序列值...
QUANTILES()	計算概略的最小值、最大值和分位數...
STDDEV()	傳回標準差...
STDDEV_POP()	計算母體標準差...
STDDEV_SAMP()	計算樣本標準差...
SUM()	傳回值的總和...
TOP() ... COUNT(*)	依照頻率傳回主要的 max_records 記錄。
UNIQUE()	傳回一組不重複非 NULL 值...
VARIANCE()	計算值的變異數...
VAR_POP()	計算值的母體變異數...
VAR_SAMP()	計算值的樣本變異數...

AVG(numeric_expr)

傳回由 numeric_expr 計算的一組資料列值的平均值。含有 NULL 值的資料列不會計入計算。

BIT_AND(numeric_expr)

傳回所有資料列中每個 numeric_expr 執行個體之間的位元 AND 運算結果。系統會忽略 NULL 值。如果 numeric_expr 的所有執行個體都評估為 NULL，這個函式會傳回 NULL。

BIT_OR(numeric_expr)

傳回所有資料列中每個 numeric_expr 執行個體之間的位元 OR 運算結果。系統會忽略 NULL 值。如果 numeric_expr 的所有執行個體都評估為 NULL，這個函式會傳回 NULL。

BIT_XOR(numeric_expr)

傳回所有資料列中每個 numeric_expr 執行個體之間的位元 XOR 運算結果。系統會忽略 NULL 值。如果 numeric_expr 的所有執行個體都評估為 NULL，這個函式會傳回 NULL。

CORR(numeric_expr, numeric_expr)

傳回一組數字配對的 皮爾森相關係數。

COUNT(*)

傳回函式範圍內的值總數 (NULL 和非 NULL)。除非您搭配 TOP 函式使用 COUNT(*)，否則最好明確指定要計算的欄位。

COUNT([DISTINCT] field [, n])

傳回函式範圍內非空值的總數。

如果使用 DISTINCT 關鍵字，函式會傳回指定欄位的「不重複」值數量。請注意，DISTINCT 的傳回值是統計近似值，不保證完全準確。

如要取得精確答案，請使用 EXACT_COUNT_DISTINCT()。

如果需要 COUNT(DISTINCT) 提供更高的準確度，可以指定第二個參數 n，這個參數會提供保證準確結果的門檻。n 的預設值為 1000，但如果提供較大的 n，則可取得 COUNT(DISTINCT) 的確切結果，最高可達 n 的值。不過，如果 n 的值較大，這個運算子的擴充性會降低，且查詢執行時間可能會大幅增加，或導致查詢失敗。

如要計算不重複值的確切數量，請使用 EXACT_COUNT_DISTINCT。或者，如要採用更具擴充性的方法，請考慮在相關欄位上使用 GROUP EACH BY，然後套用 COUNT(*)。GROUP EACH BY 方法更具擴充性，但可能會稍微降低前期效能。

COVAR_POP(numeric_expr1, numeric_expr2)

計算 numeric_expr1 和 numeric_expr2 計算值的母體共變異數。

COVAR_SAMP(numeric_expr1, numeric_expr2)

計算 numeric_expr1 和 numeric_expr2 計算值的樣本共變異數。

EXACT_COUNT_DISTINCT(field)

傳回指定欄位中非 NULL 的不重複值確切數量。如要提升擴充性和效能，請使用 COUNT(DISTINCT field)。

FIRST(expr)

傳回函式範圍內的第一個依序值。

GROUP_CONCAT('str' [, separator])

將多個字串串連成單一字串，並以選用的 separator 參數分隔各個值。如果省略 separator，BigQuery 會傳回以半形逗號分隔的字串。

如果來源資料中的字串包含雙引號字元，GROUP_CONCAT 傳回的字串就會加入雙引號。舉例來說，字串 a"b 會傳回 "a""b"。如果不希望這些字串傳回時加上雙引號，請使用 GROUP_CONCAT_UNQUOTED。

範例：

#legacySQL
SELECT
  GROUP_CONCAT(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

GROUP_CONCAT_UNQUOTED('str' [, separator])

將多個字串串連成單一字串，並以選用的 separator 參數分隔各個值。如果省略 separator，BigQuery 會傳回以半形逗號分隔的字串。

與 GROUP_CONCAT 不同，如果傳回的值包含一個雙引號字元，這個函式也不會在這個值中加入雙引號。舉例來說，字串 a"b 會傳回 a"b。

範例：

#legacySQL
SELECT
  GROUP_CONCAT_UNQUOTED(x)
FROM (
  SELECT
    'a"b' AS x),
  (
  SELECT
    'cd' AS x);

LAST(field)

傳回函式範圍內的最後一個依序值。

MAX(field)

傳回函式範圍內的最大值。

MIN(field)

傳回函式範圍內的最小值。

NEST(expr)

將目前匯總範圍內的所有值匯總成一個重複欄位。舉例來說，查詢 "SELECT x, NEST(y) FROM ... GROUP BY x" 會為每個不同的 x 值傳回一筆輸出記錄，並包含一個重複欄位，其中含有查詢輸入內容中與 x 配對的所有 y 值。NEST 函式需要 GROUP BY 子句。

BigQuery 會自動整併查詢結果，因此如果您針對頂層查詢使用 NEST 函式，結果也不會包含重複欄位。如果子選取項目會產生中繼結果，供同一查詢立即使用，請使用 NEST 函式。

NTH(n, field)

傳回函式範圍內的第 n 個依序值，其中 n 為常數。NTH 函式會從 1 開始計算，因此沒有第 0 項。如果函式範圍的值少於 n 個，函式會傳回 NULL。

QUANTILES(expr[, buckets])

針對輸入運算式計算概略的最小值、最大值和分位數。系統會忽略 NULL 輸入值。如果輸入內容空白或只有 NULL，則會輸出 NULL。計算的分位數數量由選用的 buckets 參數控制，其中包含計數中的最小值和最大值。如要計算近似 N 分位數，請使用 N+1 buckets。buckets 的預設值為 100。(注意：如果使用預設值 100，系統就不會估算分位數。如要估算百分位數，請至少使用 101 個 buckets)。如果明確指定，buckets 必須至少為 2。

每個分位數的分數誤差為 epsilon = 1 / buckets，這表示誤差會隨著值區數量增加而減少。例如：

QUANTILES(<expr>, 2) # computes min and max with 50% error.
QUANTILES(<expr>, 3) # computes min, median, and max with 33% error.
QUANTILES(<expr>, 5) # computes quartiles with 25% error.
QUANTILES(<expr>, 11) # computes deciles with 10% error.
QUANTILES(<expr>, 21) # computes vigintiles with 5% error.
QUANTILES(<expr>, 101) # computes percentiles with 1% error.

NTH 函式可用於挑選特定分位數，但請注意，NTH 是以 1 為基準，而 QUANTILES 會在第一個位置傳回最小值 (「第 0 個」分位數)，並在最後一個位置傳回最大值 (「第 100 個」百分位數或「第 N 個」N 分位數)。舉例來說，NTH(11, QUANTILES(expr, 21)) 會預估 expr 的中位數，而 NTH(20, QUANTILES(expr, 21)) 則會預估 expr 的第 19 個 vigintile (第 95 個百分位數)。兩者的預估值都有 5% 的誤差範圍。

如要提高精確度，請使用更多值區。舉例來說，如要將前述運算結果的誤差範圍從 5% 減至 0.1%，請使用 1001 個值區 (而非 21 個)，並根據 NTH 函式調整引數。如要計算中位數 (誤差 0.1%)，請使用 NTH(501, QUANTILES(expr, 1001))；如要計算第 95 個百分位數 (誤差 0.1%)，請使用 NTH(951, QUANTILES(expr, 1001))。

STDDEV(numeric_expr)

傳回 numeric_expr 計算值的標準差。值為 NULL 的資料列不會列入計算。STDDEV 函式是 STDDEV_SAMP 的別名。

STDDEV_POP(numeric_expr)

計算 numeric_expr 運算值的母體標準差。 使用 STDDEV_POP() 計算涵蓋整個相關母體的資料集標準差。 如果資料集僅包含母體的代表性樣本，請改用 STDDEV_SAMP()。 如要進一步瞭解母體與樣本標準差，請參閱維基百科的標準差頁面。

STDDEV_SAMP(numeric_expr)

計算 numeric_expr 運算值的樣本標準差。 使用 STDDEV_SAMP()，根據母體的代表性樣本計算整個母體的標準差。 如果資料集包含整個母體，請改用 STDDEV_POP()。 如要進一步瞭解母體與樣本標準差，請參閱維基百科的標準差頁面。

SUM(field)

傳回函式範圍內值的總和。僅能搭配數字資料類型使用。

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

：依頻率傳回前 max_records 筆記錄。詳情請參閱下方的 TOP 說明。

UNIQUE(expr)

以未定義的順序，傳回函式範圍內不重複的非空值組合。與不含 EACH 關鍵字的大型 GROUP BY 子句類似，如果相異值過多，查詢會失敗並顯示「Resources Exceeded」錯誤。不過，與 GROUP BY 不同的是，UNIQUE 函式可與限定範圍的匯總搭配使用，因此能有效率地對值數量有限的巢狀欄位執行運算。

VARIANCE(numeric_expr)

計算 numeric_expr 所計算值的變異數。值為 NULL 的資料列不會列入計算。VARIANCE 函式是 VAR_SAMP 的別名。

VAR_POP(numeric_expr)

計算 numeric_expr 運算值的母體變異數。如要進一步瞭解母體與樣本標準差，請參閱維基百科的標準差頁面。

VAR_SAMP(numeric_expr)

計算 numeric_expr 運算值的樣本變異數。如要進一步瞭解母體與樣本標準差，請參閱維基百科的標準差頁面。

TOP() 函式

TOP 這個函式是 GROUP BY 子句的替代選擇，可做為 GROUP BY ... ORDER BY ... LIMIT ... 的簡化語法。一般來說，TOP 函式的執行速度比完整 ... GROUP BY ... ORDER BY ... LIMIT ... 查詢快，但可能只會傳回近似結果。TOP 函式的語法如下：

TOP(field|alias[, max_values][,multiplier]) ... COUNT(*)

在 SELECT 子句中使用 TOP 時，必須將 COUNT(*) 納入其中一個欄位。

使用 TOP() 函式的查詢只會傳回兩個欄位，分別是 TOP 欄位和 COUNT(*) 值。

field|alias

要傳回的欄位或別名。

max_values

[選用] 要傳回的結果數上限。預設值為 20。

multiplier

正整數，可將 COUNT(*) 傳回的值乘以指定倍數。

TOP() 範例

• 使用 TOP() 的基本範例查詢

  下列查詢會使用 TOP() 傳回 10 個資料列。

  範例 1：

  #legacySQL
  SELECT
    TOP(word, 10) as word, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.shakespeare]
  WHERE
    word CONTAINS "th";

  範例 2：

  #legacySQL
  SELECT
    word, left(word, 3)
  FROM
    (SELECT TOP(word, 10) AS word, COUNT(*)
       FROM [bigquery-public-data:samples.shakespeare]
       WHERE word CONTAINS "th");

• 比較 TOP() 和 GROUP BY...ORDER BY...LIMIT

  查詢會依序傳回最常使用且包含「th」的 10 個字詞，以及使用這些字詞的文件數量。TOP 查詢的執行速度將會加快許多：

  不含 TOP() 的範例：

  #legacySQL
  SELECT
    word, COUNT(*) AS cnt
  FROM
    ds.Table
  WHERE
    word CONTAINS 'th'
  GROUP BY
    word
  ORDER BY
    cnt DESC LIMIT 10;

  TOP() 範例：

  #legacySQL
  SELECT
    TOP(word, 10), COUNT(*)
  FROM
    ds.Table
  WHERE
    word contains 'th';

• 使用 multiplier 參數。

  下列查詢顯示 multiplier 參數如何影響查詢結果。第一個查詢會傳回懷俄明州每月的出生人數。第二個查詢使用 multiplier 參數，將 cnt 值乘以 100。

  不含 multiplier 參數的範例：

  #legacySQL
  SELECT
    TOP(month,3) as month, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state = "WY";

  傳回：

  +-------+-------+
  | month |  cnt  |
  +-------+-------+
  |   7   | 19594 |
  |   5   | 19038 |
  |   8   | 19030 |
  +-------+-------+
  

  multiplier 參數範例：

  #legacySQL
  SELECT
    TOP(month,3,100) as month, COUNT(*) as cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state = "WY";

  傳回：

  +-------+---------+
  | month |   cnt   |
  +-------+---------+
  |   7   | 1959400 |
  |   5   | 1903800 |
  |   8   | 1903000 |
  +-------+---------+
  

注意：您必須在 SELECT 子句中加入 COUNT(*)，才能使用 TOP。

進階範例

• 按條件分組的平均值和標準差

  以下查詢會傳回俄亥俄州 2003 年出生體重的平均值和標準差 (按吸煙和不吸煙的母親分組)。

  範例：

  #legacySQL
  SELECT
    cigarette_use,
    /* Finds average and standard deviation */
    AVG(weight_pounds) baby_weight,
    STDDEV(weight_pounds) baby_weight_stdev,
    AVG(mother_age) mother_age
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    year=2003 AND state='OH'
  /* Group the result values by those */
  /* who smoked and those who didn't.  */
  GROUP BY
    cigarette_use;

• 使用匯總值篩選查詢結果

  如要使用匯總值篩選查詢結果 (例如依 SUM 的值進行篩選)，請使用 HAVING 函式。HAVING 會將值與匯總函式決定的結果進行比較，而 WHERE 則會在匯總前對每個資料列執行運算。

  範例：

  #legacySQL
  SELECT
    state,
    /* If 'is_male' is True, return 'Male', */
    /* otherwise return 'Female' */
    IF (is_male, 'Male', 'Female') AS sex,
    /* The count value is aliased as 'cnt' */
    /* and used in the HAVING clause below. */
    COUNT(*) AS cnt
  FROM
    [bigquery-public-data:samples.natality]
  WHERE
    state != ''
  GROUP BY
    state, sex
  HAVING
    cnt > 3000000
  ORDER BY
    cnt DESC

  傳回：

  +-------+--------+---------+
  | state |  sex   |   cnt   |
  +-------+--------+---------+
  | CA    | Male   | 7060826 |
  | CA    | Female | 6733288 |
  | TX    | Male   | 5107542 |
  | TX    | Female | 4879247 |
  | NY    | Male   | 4442246 |
  | NY    | Female | 4227891 |
  | IL    | Male   | 3089555 |
  +-------+--------+---------+
  

算術運算子

算術運算子會使用數字引數並傳回數字結果。各個引數可以是數字文字或查詢傳回的數值。如果算術運算得出未定義的結果，則會傳回 NULL。

語法

運算子	說明	範例
+	加	SELECT 6 + (5 - 1); 退貨：10
-	減	SELECT 6 - (4 + 1); 退貨：1
*	乘	SELECT 6 * (5 - 1); 退貨：24
/	除	SELECT 6 / (2 + 2); 傳回：1.5
%	模數	SELECT 6 % (2 + 2); 退貨：2

位元函式

位元函式是以個別位元為單位進行運算，且需要數值格式的引數。如要進一步瞭解位元函式，請參閱位元運算一文。

匯總函式中記載了另外三個位元函式：BIT_AND、BIT_OR 和 BIT_XOR。

語法

運算子	說明	範例
&	位元 AND	SELECT (1 + 3) & 1 傳回值：0
|	位元 OR	SELECT 24 | 12 傳回：28
^	位元 XOR	SELECT 1 ^ 0 退貨：1
<<	向左移位	SELECT 1 << (2 + 2) 傳回：16
>>	向右移位	SELECT (6 + 2) >> 2 退貨：2
~	位元 NOT	SELECT ~2 傳回：-3
BIT_COUNT(<numeric_expr>)	傳回 <numeric_expr> 中設定的位元數。	SELECT BIT_COUNT(29); 傳回：4

轉換函式

轉換函式會改變數字運算式的資料類型，特別適合用來確保比較函式中的引數資料類型一致。

語法

轉換函式
BOOLEAN()	轉換成布林值。
BYTES()	轉換成位元組。
CAST(expr AS type)	將 expr 轉換為 type 類型的變數。
FLOAT()	轉換成雙精度浮點數。
HEX_STRING()	轉換成十六進位字串。
INTEGER()	轉換成整數。
STRING()	轉換成字串。

BOOLEAN(<numeric_expr>)

• 如果 <numeric_expr> 不是 0 且不是 NULL，則傳回 true。
• 如果 <numeric_expr> 為 0，則傳回 false。
• 如果 <numeric_expr> 為 NULL，則傳回 NULL。

BYTES(string_expr)

傳回 string_expr 做為 bytes 類型的值。

CAST(expr AS type)

將 expr 轉換為 type 類型的變數。

FLOAT(expr)

以雙精度浮點數傳回 expr。expr 可以是 '45.78' 等字串，但函式會針對非數值傳回 NULL。

HEX_STRING(numeric_expr)

以十六進位字串形式傳回 numeric_expr。

INTEGER(expr)

將 expr 轉換為 64 位元整數。

• 如果 expr 是不對應整數值的字串，則會傳回 NULL。
• 如果 expr 是時間戳記，則傳回自 Unix 紀元起算的微秒數。

STRING(numeric_expr)

：以字串形式傳回 numeric_expr。

比較函式

比較函式會根據下列比較類型傳回 true 或 false：

• 比較兩個運算式。
• 根據某項條件 (例如位於指定清單、為 NULL 或為非預設選用值) 比較一個或一組運算式。

下列部分函式會傳回 true 或 false 以外的值，但傳回的值是根據比較運算。

您可以使用數值或字串運算式做為比較函式的引數。(字串常數必須以單引號或雙引號括住)。運算式可以是查詢所擷取的實字或值。比較函式最常在 WHERE 子句中當成篩選條件使用，但也可以用於其他子句中。

語法

比較函式
expr1 = expr2	如果運算式相等，則傳回 true。
expr1 != expr2 expr1 <> expr2	如果運算式不相等，則傳回 true。
expr1 > expr2	如果 expr1 大於 expr2，則傳回 true。
expr1 < expr2	如果 expr1 小於 expr2，則傳回 true。
expr1 >= expr2	如果 expr1 大於或等於 expr2，則傳回 true。
expr1 <= expr2	如果 expr1 小於或等於 expr2，則傳回 true。
expr1 BETWEEN expr2 AND expr3	如果 expr1 的值介於 expr2 和 expr3 之間 (含頭尾)，則傳回 true。
expr IS NULL	如果 expr 為 NULL，則傳回 true。
expr IN()	如果 expr 與 expr1、expr2 或括號中的任何值相符，則傳回 true。
COALESCE()	傳回第一個非 NULL 的引數。
GREATEST()	傳回最大的 numeric_expr 參數。
IFNULL()	如果引數不是空值，則會傳回引數。
IS_INF()	如果是正無限大或負無限大，則傳回 true。
IS_NAN()	如果引數為 NaN，則傳回 true。
IS_EXPLICITLY_DEFINED()	已淘汰：請改用 expr IS NOT NULL。
LEAST()	傳回最小的引數 numeric_expr 參數。
NVL()	如果 expr 不是空值，則傳回 expr，否則傳回 null_default。

expr1 = expr2

：如果運算式相等，則傳回 true。

expr1 != expr2
expr1 <> expr2

：如果運算式不相等，則傳回 true。

expr1 > expr2

如果 expr1 大於 expr2，則傳回 true。

expr1 < expr2

如果 expr1 小於 expr2，則傳回 true。

expr1 >= expr2

如果 expr1 大於或等於 expr2，則傳回 true。

expr1 <= expr2

如果 expr1 小於或等於 expr2，則傳回 true。

expr1 BETWEEN expr2 AND expr3

如果 expr1 的值大於或等於 expr2，且小於或等於 expr3，則傳回 true。

expr IS NULL

如果 expr 為 NULL，則傳回 true。

expr IN(expr1, expr2, ...)

如果 expr 符合 expr1、expr2 或括號中的任何值，則傳回 true。IN 關鍵字是 (expr = expr1 || expr = expr2 || ...) 的有效簡寫。與 IN 關鍵字搭配使用的運算式必須是常數，且必須與 expr 的資料類型相符。IN 子句也可用於建立半聯結和反聯結。詳情請參閱「半彙整和反彙整」。

COALESCE(<expr1>, <expr2>, ...)

傳回第一個非 NULL 的引數。

GREATEST(numeric_expr1, numeric_expr2, ...)

傳回最大的 numeric_expr 參數。所有參數都必須是數字，且類型必須保持一致。如果任何參數為 NULL，這個函式會傳回 NULL。

如要忽略 NULL 值，請使用 IFNULL 函式來將 NULL 值變更為不會影響比較的值。在以下程式碼範例中，IFNULL 函式用於將 NULL 值變更為 -1，這不會影響正數之間的比較。

SELECT GREATEST(IFNULL(a,-1), IFNULL(b,-1)) FROM (SELECT 1 as a, NULL as b);

IFNULL(expr, null_default)

如果 expr 不是空值，則傳回 expr，否則傳回 null_default。

IS_INF(numeric_expr)

如果 numeric_expr 為正無限大或負無限大，則傳回 true。

IS_NAN(numeric_expr)

如果 numeric_expr 是特殊 NaN 數值，則傳回 true。

IS_EXPLICITLY_DEFINED(expr)

這個函式已遭淘汰，請改用 expr IS NOT NULL。

LEAST(numeric_expr1, numeric_expr2, ...)

傳回最小的 numeric_expr 參數。所有參數都必須是數字，且類型必須保持一致。如果任何參數為 NULL，這個函式會傳回 NULL

NVL(expr, null_default)

如果 expr 不是空值，則傳回 expr，否則傳回 null_default。NVL 函式是 IFNULL 的別名。

日期和時間函式

下列函式可讓您針對 UNIX 時間戳記、日期字串和 TIMESTAMP 類型的資料控制日期和時間。如要進一步瞭解如何使用 TIMESTAMP 類型的資料，請參閱使用 TIMESTAMP 一文。

與 UNIX 時間戳記搭配使用的日期和時間函式是根據 UNIX 時間進行運算。日期和時間函式會根據世界標準時間時區傳回值。

語法

日期和時間函式
CURRENT_DATE()	以 %Y-%m-%d 格式傳回目前日期。
CURRENT_TIME()	以 %H:%M:%S 格式傳回伺服器的目前時間。
CURRENT_TIMESTAMP()	以 %Y-%m-%d %H:%M:%S 格式傳回伺服器的目前時間。
DATE()	傳回日期的格式為 %Y-%m-%d。
DATE_ADD()	在 TIMESTAMP 類型的資料中加上指定時間間隔。
DATEDIFF()	傳回兩個 TIMESTAMP 類型資料之間的天數。
DAY()	傳回 1 到 31 之間的整數，代表當月第幾天。
DAYOFWEEK()	傳回 1 (星期日) 到 7 (星期六) 之間的整數，代表當週第幾天。
DAYOFYEAR()	傳回 1 到 366 之間的整數，代表該年度的第幾天。
FORMAT_UTC_USEC()	傳回 UNIX 時間戳記，格式為 YYYY-MM-DD HH:MM:SS.uuuuuu。
HOUR()	傳回 0 到 23 之間的整數，代表 TIMESTAMP 的小時部分。
MINUTE()	傳回 0 到 59 之間的整數，代表 TIMESTAMP 的分鐘部分。
MONTH()	傳回 1 到 12 之間的整數，代表 TIMESTAMP 的月份。
MSEC_TO_TIMESTAMP()	將以毫秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP。
NOW()	以微秒為單位傳回目前的 UNIX 時間戳記。
PARSE_UTC_USEC()	將日期字串轉換成以微秒為單位的 UNIX 時間戳記。
QUARTER()	傳回 1 到 4 之間的整數，代表 TIMESTAMP 的季別。
SEC_TO_TIMESTAMP()	將以秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP。
SECOND()	傳回 0 到 59 之間的整數，代表 TIMESTAMP 的秒部分。
STRFTIME_UTC_USEC()	以 date_format_str 的格式傳回日期字串。
TIME()	傳回 %H:%M:%S 格式的時間戳記。
TIMESTAMP()	將日期字串轉換成 TIMESTAMP。
TIMESTAMP_TO_MSEC()	將 TIMESTAMP 轉換成以毫秒為單位的 UNIX 時間戳記。
TIMESTAMP_TO_SEC()	將 TIMESTAMP 轉換成以秒為單位的 UNIX 時間戳記。
TIMESTAMP_TO_USEC()	將 TIMESTAMP 轉換成以微秒為單位的 UNIX 時間戳記。
USEC_TO_TIMESTAMP()	將以微秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP。
UTC_USEC_TO_DAY()	將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生日期的開頭。
UTC_USEC_TO_HOUR()	將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生時間 (小時) 的開頭。
UTC_USEC_TO_MONTH()	將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生月份的開頭。
UTC_USEC_TO_WEEK()	傳回以微秒為單位的 UNIX 時間戳記，代表當週的某一天。
UTC_USEC_TO_YEAR()	傳回以微秒為單位的 UNIX 時間戳記，代表年份。
WEEK()	傳回 1 到 53 間的整數，代表 TIMESTAMP 的週數部分。
YEAR()	傳回 TIMESTAMP 的年份。

CURRENT_DATE()

傳回目前日期的使用者可理解字串，格式為 %Y-%m-%d。

範例：

SELECT CURRENT_DATE();

傳回：2013-02-01

CURRENT_TIME()

傳回人類可讀的伺服器目前時間字串，格式為 %H:%M:%S。

範例：

SELECT CURRENT_TIME();

傳回：01:32:56

CURRENT_TIMESTAMP()

傳回伺服器目前時間的 TIMESTAMP 資料類型，格式為 %Y-%m-%d %H:%M:%S。

範例：

SELECT CURRENT_TIMESTAMP();

傳回：2013-02-01 01:33:35 UTC

DATE(<timestamp>)

以 %Y-%m-%d 格式傳回 TIMESTAMP 資料類型的人類可讀字串。

範例：

SELECT DATE(TIMESTAMP('2012-10-01 02:03:04'));

傳回：2012-10-01

DATE_ADD(<timestamp>,<interval>,
                 <interval_units>)

在 TIMESTAMP 類型的資料中加上指定時間間隔。可能的 interval_units 值包括 YEAR、MONTH、DAY、HOUR、MINUTE 和 SECOND。如果 interval 為負數，則會從 TIMESTAMP 資料類型中減去間隔。

範例：

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), 5, "YEAR");

傳回：2017-10-01 02:03:04 UTC

SELECT DATE_ADD(TIMESTAMP("2012-10-01 02:03:04"), -5, "YEAR");

傳回：2007-10-01 02:03:04 UTC

DATEDIFF(<timestamp1>,<timestamp2>)

傳回兩個 TIMESTAMP 類型資料之間的天數。如果第一個 TIMESTAMP 類型資料出現在第二個 TIMESTAMP 類型資料之後，結果就會是正數，反之則是負數。

範例：

SELECT DATEDIFF(TIMESTAMP('2012-10-02 05:23:48'), TIMESTAMP('2011-06-24 12:18:35'));

傳回：466

範例：

SELECT DATEDIFF(TIMESTAMP('2011-06-24 12:18:35'), TIMESTAMP('2012-10-02 05:23:48'));

傳回：-466

DAY(<timestamp>)

傳回 TIMESTAMP 類型資料的日期是當月第幾天，以 1 到 31 之間的整數 (包括 1 和 31) 表示。

範例：

SELECT DAY(TIMESTAMP('2012-10-02 05:23:48'));

退貨：2

DAYOFWEEK(<timestamp>)

傳回 TIMESTAMP 類型資料的日期是當週第幾天，以 1 (星期日) 到 7 (星期六) 之間的整數 (包括 1 和 7) 表示。

範例：

SELECT DAYOFWEEK(TIMESTAMP("2012-10-01 02:03:04"));

退貨：2

DAYOFYEAR(<timestamp>)

傳回 TIMESTAMP 類型資料的日期是該年度第幾天，以 1 到 366 之間的整數 (包括 1 和 366) 表示。整數 1 代表 1 月 1 日。

範例：

SELECT DAYOFYEAR(TIMESTAMP("2012-10-01 02:03:04"));

傳回：275

FORMAT_UTC_USEC(<unix_timestamp>)

以人類可讀的字串表示法傳回 UNIX 時間戳記，格式為 YYYY-MM-DD HH:MM:SS.uuuuuu。

範例：

SELECT FORMAT_UTC_USEC(1274259481071200);

傳回：2010-05-19 08:58:01.071200

HOUR(<timestamp>)

傳回 TIMESTAMP 類型資料時間的小時部分，以 0 到 23 之間的整數 (包括 0 和 23) 表示。

範例：

SELECT HOUR(TIMESTAMP('2012-10-02 05:23:48'));

傳回：5

MINUTE(<timestamp>)

傳回 TIMESTAMP 類型資料時間的分鐘部分，以 0 到 59 之間的整數 (包括 0 和 59) 表示。

範例：

SELECT MINUTE(TIMESTAMP('2012-10-02 05:23:48'));

傳回：23

MONTH(<timestamp>)

傳回 TIMESTAMP 類型資料的月份，以 1 到 12 之間的整數 (包括 1 和 12) 表示。

範例：

SELECT MONTH(TIMESTAMP('2012-10-02 05:23:48'));

退貨：10

MSEC_TO_TIMESTAMP(<expr>)

將 UNIX 時間戳記 (以毫秒為單位) 轉換為 TIMESTAMP 資料類型。

範例：

SELECT MSEC_TO_TIMESTAMP(1349053323000);

傳回：2012-10-01 01:02:03 UTC

SELECT MSEC_TO_TIMESTAMP(1349053323000 + 1000)

傳回：2012-10-01 01:02:04 UTC

NOW()

以微秒為單位傳回目前的 UNIX 時間戳記。

範例：

SELECT NOW();

傳回：1359685811687920

PARSE_UTC_USEC(<date_string>)

將日期字串轉換為 UNIX 時間戳記 (以微秒為單位)。date_string 必須採用 YYYY-MM-DD HH:MM:SS[.uuuuuu] 格式。秒的小數部分最長可為 6 位數，或可直接省略。

TIMESTAMP_TO_USEC 是等效函式，可轉換 TIMESTAMP 資料類型引數，而非日期字串。

範例：

SELECT PARSE_UTC_USEC("2012-10-01 02:03:04");

傳回：1349056984000000

QUARTER(<timestamp>)

傳回 TIMESTAMP 類型資料的季別，以 1 到 4 之間的整數 (包括 1 和 4) 表示。

範例：

SELECT QUARTER(TIMESTAMP("2012-10-01 02:03:04"));

傳回：4

SEC_TO_TIMESTAMP(<expr>)

將以秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP 類型的資料。

範例：

SELECT SEC_TO_TIMESTAMP(1355968987);

傳回：2012-12-20 02:03:07 UTC

SELECT SEC_TO_TIMESTAMP(INTEGER(1355968984 + 3));

傳回：2012-12-20 02:03:07 UTC

SECOND(<timestamp>)

傳回 TIMESTAMP 類型資料的秒部分，以 0 到 59 之間的整數 (包括 0 和 59) 表示。

如果有閏秒，則以 0 到 60 之間的整數 (包括 0 和 60) 表示。

範例：

SELECT SECOND(TIMESTAMP('2012-10-02 05:23:48'));

退貨：48

STRFTIME_UTC_USEC(<unix_timestamp>,
                  <date_format_str>)

以 date_format_str 格式傳回人類可讀的日期字串。 date_format_str 可包含日期相關標點符號 (例如 / 和 -)，以及 C++ 中的 strftime 函式接受的特殊字元 (例如 %d 代表日期)。

如果您打算依時間間隔將查詢資料分組 (例如取得特定月份的所有資料)，請使用 UTC_USEC_TO_<function_name> 函式，因為這些函式的效率較高。

範例：

SELECT STRFTIME_UTC_USEC(1274259481071200, "%Y-%m-%d");

傳回：2010-05-19

TIME(<timestamp>)

傳回 TIMESTAMP 資料類型的人類可讀字串，格式為 %H:%M:%S。

範例：

SELECT TIME(TIMESTAMP('2012-10-01 02:03:04'));

傳回：02:03:04

TIMESTAMP(<date_string>)

將日期字串轉換成 TIMESTAMP 類型的資料。

範例：

SELECT TIMESTAMP("2012-10-01 01:02:03");

傳回：2012-10-01 01:02:03 UTC

TIMESTAMP_TO_MSEC(<timestamp>)

將 TIMESTAMP 類型的資料轉換成以毫秒為單位的 UNIX 時間戳記。

範例：

SELECT TIMESTAMP_TO_MSEC(TIMESTAMP("2012-10-01 01:02:03"));

傳回：1349053323000

TIMESTAMP_TO_SEC(<timestamp>)

將 TIMESTAMP 資料類型轉換為 UNIX 時間戳記 (以秒為單位)。

範例：

SELECT TIMESTAMP_TO_SEC(TIMESTAMP("2012-10-01 01:02:03"));

傳回：1349053323

TIMESTAMP_TO_USEC(<timestamp>)

將 TIMESTAMP 類型的資料轉換成以微秒為單位的 UNIX 時間戳記。

PARSE_UTC_USEC 是等效函式，可轉換資料字串引數，而非 TIMESTAMP 資料類型。

範例：

SELECT TIMESTAMP_TO_USEC(TIMESTAMP("2012-10-01 01:02:03"));

傳回：1349053323000000

USEC_TO_TIMESTAMP(<expr>)

將以微秒為單位的 UNIX 時間戳記轉換成 TIMESTAMP 類型的資料。

範例：

SELECT USEC_TO_TIMESTAMP(1349053323000000);

傳回：2012-10-01 01:02:03 UTC

SELECT USEC_TO_TIMESTAMP(1349053323000000 + 1000000)

傳回：2012-10-01 01:02:04 UTC

UTC_USEC_TO_DAY(<unix_timestamp>)

將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生日期的開頭。

舉例來說，如果 unix_timestamp 發生在 5 月 19 日上午 8 點 58 分，這個函式會傳回 5 月 19 日凌晨 0 點 (午夜) 的 UNIX 時間戳記。

範例：

SELECT UTC_USEC_TO_DAY(1274259481071200);

傳回：1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生時間 (小時) 的開頭。

舉例來說，如果 unix_timestamp 發生在 08:58，這個函式會傳回當天 08:00 的 UNIX 時間戳記。

範例：

SELECT UTC_USEC_TO_HOUR(1274259481071200);

傳回：1274256000000000

UTC_USEC_TO_MONTH(<unix_timestamp>)

將以微秒為單位的 UNIX 時間戳記轉換成時間戳記發生月份的開頭。

舉例來說，如果 unix_timestamp 發生在 3 月 19 日，這個函式會傳回同年 3 月 1 日的 UNIX 時間戳記。

範例：

SELECT UTC_USEC_TO_MONTH(1274259481071200);

傳回：1272672000000000

UTC_USEC_TO_WEEK(<unix_timestamp>,
                 <day_of_week>)

傳回以微秒為單位的 UNIX 時間戳記，代表 unix_timestamp 引數中一週的某一天。這個函式會使用兩個引數，分別是以微秒為單位的 UNIX 時間戳記，以及 0 (星期日) 到 6 (星期六) 之間一個代表星期幾的值。

舉例來說，如果 unix_timestamp 是 2008 年 4 月 11 日星期五，而您將 day_of_week 設為 2 (星期二)，則函式會傳回 2008 年 4 月 8 日星期二的 UNIX 時間戳記。

範例：

SELECT UTC_USEC_TO_WEEK(1207929480000000, 2) AS tuesday;

傳回：1207612800000000

UTC_USEC_TO_YEAR(<unix_timestamp>)

傳回 UNIX 時間戳記 (以微秒為單位)，代表 unix_timestamp 引數的年份。

舉例來說，如果 unix_timestamp 發生在 2010 年，函式會傳回 1274259481071200，也就是 2010-01-01 00:00 的微秒表示法。

範例：

SELECT UTC_USEC_TO_YEAR(1274259481071200);

傳回：1262304000000000

WEEK(<timestamp>)

傳回 TIMESTAMP 類型資料的週數部分，以 1 到 53 之間的整數 (包括 1 和 53) 表示。

每一週的第一天是星期日，因此如果 1 月 1 日不是星期日，那麼第 1 週就會少於 7 天，且該年度第一個星期日是第 2 週的第一天。

範例：

SELECT WEEK(TIMESTAMP('2014-12-31'));

退貨：53

YEAR(<timestamp>)

傳回 TIMESTAMP 資料類型的年份。

範例：

SELECT YEAR(TIMESTAMP('2012-10-02 05:23:48'));

傳回：2012

進階範例

• 將整數時間戳記結果轉換為人類可讀的格式

  以下查詢會找出 Wikipedia 出現最多修訂的 5 大時刻。如要以人類可讀的格式顯示結果，請使用 BigQuery 的 FORMAT_UTC_USEC() 函式，該函式會以微秒為單位，將時間戳記做為輸入內容。這項查詢會將 Wikipedia POSIX 格式的時間戳記 (以秒為單位) 乘以 1000000，藉此轉換成以微秒為單位的值。

  範例：

  #legacySQL
  SELECT
    /* Multiply timestamp by 1000000 and convert */
    /* into a more human-readable format. */
    TOP (FORMAT_UTC_USEC(timestamp * 1000000), 5)
      AS top_revision_time,
    COUNT (*) AS revision_count
  FROM
    [bigquery-public-data:samples.wikipedia];

  傳回：

  +----------------------------+----------------+
  |     top_revision_time      | revision_count |
  +----------------------------+----------------+
  | 2002-02-25 15:51:15.000000 |          20976 |
  | 2002-02-25 15:43:11.000000 |          15974 |
  | 2010-02-02 03:34:51.000000 |              3 |
  | 2010-02-02 01:04:59.000000 |              3 |
  | 2010-02-01 23:55:05.000000 |              3 |
  +----------------------------+----------------+
  

• 依時間戳記將結果分組

  您可以使用日期和時間函式，將查詢結果分成對應至特定年分、月份或日期的值區，這樣做會很有幫助。以下範例使用 UTC_USEC_TO_MONTH() 函式，顯示每位維基百科貢獻者每月在修訂版本註解中使用的字元數。

  範例：

  #legacySQL
  SELECT
    contributor_username,
    /* Return the timestamp shifted to the
     * start of the month, formatted in
     * a human-readable format. Uses the
     * 'LEFT()' string function to return only
     * the first 7 characters of the formatted timestamp.
     */
    LEFT (FORMAT_UTC_USEC(
      UTC_USEC_TO_MONTH(timestamp * 1000000)),7)
      AS month,
    SUM(LENGTH(comment)) as total_chars_used
  FROM
    [bigquery-public-data:samples.wikipedia]
  WHERE
    (contributor_username != '' AND
     contributor_username IS NOT NULL)
    AND timestamp > 1133395200
    AND timestamp < 1157068800
  GROUP BY
    contributor_username, month
  ORDER BY
    total_chars_used DESC;

  傳回 (截斷)：

  +--------------------------------+---------+-----------------------+
  |      contributor_username      |  month  | total_chars_used      |
  +--------------------------------+---------+-----------------------+
  | Kingbotk                       | 2006-08 |              18015066 |
  | SmackBot                       | 2006-03 |               7838365 |
  | SmackBot                       | 2006-05 |               5148863 |
  | Tawkerbot2                     | 2006-05 |               4434348 |
  | Cydebot                        | 2006-06 |               3380577 |
  etc ...
  

IP 函式

IP 函式可將 IP 位址轉換為人類可讀的形式，反之亦然。

語法

IP 函式
FORMAT_IP()	將 integer_value 的 32 個最低有效位元轉換為人類可讀的 IPv4 位址字串。
PARSE_IP()	將用來表示 IPv4 位址的字串轉換為無正負號整數值。
FORMAT_PACKED_IP()	以 10.1.5.23 或 2620:0:1009:1:216:36ff:feef:3f 格式傳回人類可讀的 IP 位址。
PARSE_PACKED_IP()	以 BYTES 格式傳回 IP 位址。

FORMAT_IP(integer_value)

：將 integer_value 的 32 個最低有效位元轉換為人類可讀的 IPv4 位址字串。舉例來說，FORMAT_IP(1) 會傳回字串 '0.0.0.1'。

PARSE_IP(readable_ip)

：將代表 IPv4 位址的字串轉換為不帶正負號的整數值。舉例來說，PARSE_IP('0.0.0.1') 會傳回 1。如果字串不是有效的 IPv4 位址，PARSE_IP 會傳回 NULL。

BigQuery 支援在封裝字串中寫入 IPv4 和 IPv6 位址，採用的形式為 4 位元組或 16 位元組二進位資料，並以網路位元組為順序排列。下方所述的函式支援將位址剖析為使用者可理解的格式，反之亦然。這類函式只適用於含有 IP 的字串欄位。

語法

FORMAT_PACKED_IP(packed_ip)

傳回人類可讀的 IP 位址，格式為 10.1.5.23 或 2620:0:1009:1:216:36ff:feef:3f。範例：

• FORMAT_PACKED_IP('0123456789@ABCDE') 傳回 '3031:3233:3435:3637:3839:4041:4243:4445'
• FORMAT_PACKED_IP('0123') 傳回 '48.49.50.51'

PARSE_PACKED_IP(readable_ip)

以 BYTES 格式傳回 IP 位址。如果輸入字串不是有效的 IPv4 或 IPv6 位址，PARSE_PACKED_IP 會傳回 NULL。範例：

• PARSE_PACKED_IP('48.49.50.51') 傳回 'MDEyMw=='
• PARSE_PACKED_IP('3031:3233:3435:3637:3839:4041:4243:4445') 傳回 'MDEyMzQ1Njc4OUBBQkNERQ=='

JSON 函式

BigQuery 的 JSON 函式可讓您使用類似 JSONPath 的運算式，在您儲存的 JSON 資料中找出特定值。

相較於在資料表結構定義中宣告所有個別欄位，儲存 JSON 資料或許更有彈性，但成本也可能較高。當您選取 JSON 字串中的資料時，掃描整個字串會產生費用，而相較於讓各個欄位分佈在不同資料欄中，這樣做的成本較高。此外，由於系統必須在查詢時間剖析整個字串，因此查詢的執行速度也會較慢。不過如果是特殊或頻繁變動的結構定義，您可以考慮多花點成本來享有 JSON 的彈性。

如果是在處理結構化資料，請使用 JSON 函式 (而非 BigQuery 的規則運算式函式)，原因是 JSON 函式較容易使用。

語法

JSON 函式
JSON_EXTRACT()	根據 JSONPath 運算式選取值並傳回 JSON 字串。
JSON_EXTRACT_SCALAR()	根據 JSONPath 運算式選取值並傳回 JSON 純量值。

JSON_EXTRACT(json, json_path)

根據 JSONPath 運算式 json_path 選取 json 中的值。json_path 必須是字串常數。系統會以 JSON 字串的格式傳回值。

JSON_EXTRACT_SCALAR(json, json_path)

根據 JSONPath 運算式 json_path 選取 json 中的值。json_path 必須是字串常數。系統會傳回 JSON 純量值。

邏輯運算子

邏輯運算子會對運算式執行二進位或三進位邏輯運算。二進位邏輯會傳回 true 或 false。三元邏輯可容納 NULL 值，並傳回 true、false 或 NULL。

語法

邏輯運算子
expr AND expr	如果兩個運算式皆為 True，則傳回 true。
expr OR expr	如果其中一個或兩個運算式為 True，則傳回 true。
NOT expr	如果運算式為 False，則傳回 true。

expr AND expr

• 如果兩個運算式皆為 True，則傳回 true。
• 如果其中一個或兩個運算式為 false，則傳回 false。
• 如果兩個運算式都為 NULL，或者一個為 true 一個為 NULL，則會傳回 NULL。

expr OR expr

• 如果其中一個或兩個運算式為 true，則傳回 true。
• 如果兩個運算式皆為 false，則傳回 false。
• 如果兩個運算式都為 NULL，或者一個為 false 一個為 NULL，則會傳回 NULL。

NOT expr

• 如果運算式為 False，則傳回 true。
• 如果運算式為 true，則傳回 false。
• 如果運算式為 NULL，則會傳回 NULL。

NOT 可與其他函式搭配使用，做為否定運算子。例如 NOT IN(expr1, expr2) 或 IS NOT NULL。

數學函式

數學函式會使用數字引數並傳回數字結果。各個引數可以是數字文字或查詢傳回的數值。如果數學函式得出未定義的結果，則會傳回 NULL。

語法

數學函式
ABS()	傳回引數的絕對值。
ACOS()	傳回引數的反餘弦。
ACOSH()	傳回引數的反雙曲餘弦。
ASIN()	傳回引數的反正弦。
ASINH()	傳回引數的反雙曲正弦。
ATAN()	傳回引數的反正切。
ATANH()	傳回引數的反雙曲正切。
ATAN2()	傳回兩個引數的反正切。
CEIL()	將引數向上四捨五入至最接近的整數，並傳回四捨五入值。
COS()	傳回引數的餘弦。
COSH()	傳回引數的雙曲餘弦。
DEGREES()	將弧度轉換成角度。
EXP()	傳回 e 的引數次方。
FLOOR()	將引數向下四捨五入至最接近的整數。
LN() LOG()	傳回引數的自然對數。
LOG2()	傳回引數以 2 為底的對數。
LOG10()	傳回引數以 10 為底的對數。
PI()	傳回常數 π。
POW()	傳回第一引數的第二引數次方。
RADIANS()	將角度轉換成弧度。
RAND()	傳回隨機浮點值，範圍為 0.0 <= 值 < 1.0。
ROUND()	將引數向上或向下四捨五入至最接近的整數。
SIN()	傳回引數的正弦。
SINH()	傳回引數的雙曲正弦。
SQRT()	傳回運算式的平方根。
TAN()	傳回引數的正切。
TANH()	傳回引數的雙曲正切。

ABS(numeric_expr)

傳回引數的絕對值。

ACOS(numeric_expr)

傳回引數的反餘弦值。

ACOSH(numeric_expr)

傳回引數的反雙曲餘弦值。

ASIN(numeric_expr)

傳回引數的反正弦值。

ASINH(numeric_expr)

傳回引數的反雙曲正弦值。

ATAN(numeric_expr)

傳回引數的反正切值。

ATANH(numeric_expr)

傳回引數的反雙曲正切值。

ATAN2(numeric_expr1, numeric_expr2)

傳回兩個引數的反正切值。

CEIL(numeric_expr)

將引數無條件進位至最接近的整數，並傳回進位後的值。

COS(numeric_expr)

傳回引數的餘弦值。

COSH(numeric_expr)

傳回引數的雙曲餘弦值。

DEGREES(numeric_expr)

傳回 numeric_expr，從弧度轉換為角度。

EXP(numeric_expr)

傳回常數「e」(自然對數的底數) 乘以 numeric_expr 次方的結果。

FLOOR(numeric_expr)

將引數無條件捨去至最接近的整數，並傳回捨去後的值。

LN(numeric_expr)
LOG(numeric_expr)

傳回引數的自然對數。

LOG2(numeric_expr)

傳回引數的底數 2 對數。

LOG10(numeric_expr)

傳回引數的對數 (以 10 為底數)。

PI()

傳回常數 π。PI() 函式需要括號來表示它是函式，但括號中不含引數。您可以將 PI() 當做常數，搭配數學和算術函式使用。

POW(numeric_expr1, numeric_expr2)

傳回 numeric_expr1 的 numeric_expr2 次方結果。

RADIANS(numeric_expr)

傳回 numeric_expr，從角度轉換為弧度。請注意，π 弧度等於 180 度。

RAND([int32_seed])

傳回介於 0.0 <= 值 < 1.0 範圍內的隨機浮點值。只要不使用 LIMIT 子句，每個 int32_seed 值在特定查詢中一律會產生相同的隨機號碼序列。如未指定 int32_seed，BigQuery 會使用目前的時間戳記做為種子值。

ROUND(numeric_expr [, digits])

將引數四捨五入至最接近的整數 (或指定位數)，並傳回四捨五入後的值。

SIN(numeric_expr)

傳回引數的正弦值。

SINH(numeric_expr)

傳回引數的雙曲正弦值。

SQRT(numeric_expr)

傳回運算式的平方根。

TAN(numeric_expr)

傳回引數的正切函數。

TANH(numeric_expr)

傳回引數的雙曲正切值。

進階範例

• 定界框查詢

  以下查詢會以舊金山 (37.46, -122.50) 為中心，傳回其矩形定界框內的一組點位。

  範例：

  #legacySQL
  SELECT
    year, month,
    AVG(mean_temp) avg_temp,
    MIN(min_temperature) min_temp,
    MAX(max_temperature) max_temp
  FROM
    [weather_geo.table]
  WHERE
    /* Return values between a pair of */
    /* latitude and longitude coordinates */
    lat / 1000 > 37.46 AND
    lat / 1000 < 37.65 AND
    long / 1000 > -122.50 AND
    long / 1000 < -122.30
  GROUP BY
    year, month
  ORDER BY
    year, month ASC;

• 近似邊界圓形查詢

  使用球面餘弦定理，以科羅拉多州丹佛 (39.73, -104.98) 為中心，傳回近似圓形內最多 100 個點的集合。這項查詢會使用 BigQuery 的數學和三角函數，例如 PI()、SIN() 和 COS()。

  由於地球並非絕對球體，且經緯度會在兩極交會，因此這項查詢會傳回近似值，可用於多種類型的資料。

  範例：

  #legacySQL
  SELECT
    distance, lat, long, temp
  FROM
    (SELECT
      ((ACOS(SIN(39.73756700 * PI() / 180) *
             SIN((lat/1000) * PI() / 180) +
             COS(39.73756700 * PI() / 180) *
             COS((lat/1000) * PI() / 180) *
             COS((-104.98471790 -
             (long/1000)) * PI() / 180)) *
             180 / PI()) * 60 * 1.1515)
        AS distance,
       AVG(mean_temp) AS temp,
       AVG(lat/1000) lat, AVG(long/1000) long
  FROM
    [weather_geo.table]
  WHERE
    month=1 GROUP BY distance)
  WHERE
    distance < 100
  ORDER BY
    distance ASC
  LIMIT 100;

規則運算式函式

BigQuery 使用 re2 程式庫提供規則運算式支援；請參閱該說明文件，瞭解其規則運算式語法。

請注意，規則運算式採用全域比對；若要在字詞開頭開始進行比對，您必須使用 ^ 字元。

語法

規則運算式函式
REGEXP_MATCH()	如果引數與規則運算式相符，則會傳回 true。
REGEXP_EXTRACT()	傳回引數在規則運算式中與擷取群組相符的部分。
REGEXP_REPLACE()	取代與規則運算式相符的子字串。

REGEXP_MATCH('str', 'reg_exp')

如果 str 與規則運算式相符，則會傳回 true。如要比對不含規則運算式的字串，請使用 CONTAINS，而非 REGEXP_MATCH。

範例：

#legacySQL
SELECT
   word,
   COUNT(word) AS count
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   (REGEXP_MATCH(word,r'\w\w\'\w\w'))
GROUP BY word
ORDER BY count DESC
LIMIT 3;

傳回：

+-------+-------+
| word  | count |
+-------+-------+
| ne'er |    42 |
| we'll |    35 |
| We'll |    33 |
+-------+-------+

REGEXP_EXTRACT('str', 'reg_exp')

傳回 str 在規則運算式中與擷取群組相符的部分。

範例：

#legacySQL
SELECT
   REGEXP_EXTRACT(word,r'(\w\w\'\w\w)') AS fragment
FROM
   [bigquery-public-data:samples.shakespeare]
GROUP BY fragment
ORDER BY fragment
LIMIT 3;

傳回：

+----------+
| fragment |
+----------+
| NULL     |
| Al'ce    |
| As'es    |
+----------+

REGEXP_REPLACE('orig_str', 'reg_exp', 'replace_str')

傳回一個字串，其中系統會將任何與 reg_exp 相符的 orig_str 子字串替換成 replace_str。舉例來說，REGEXP_REPLACE ('Hello', 'lo', 'p') 會傳回 Help。

範例：

#legacySQL
SELECT
  REGEXP_REPLACE(word, r'ne\'er', 'never') AS expanded_word
FROM
  [bigquery-public-data:samples.shakespeare]
WHERE
  REGEXP_MATCH(word, r'ne\'er')
GROUP BY expanded_word
ORDER BY expanded_word
LIMIT 5;

傳回：

+---------------+
| expanded_word |
+---------------+
| Whenever      |
| never         |
| nevertheless  |
| whenever      |
+---------------+

進階範例

• 依規則運算式比對篩選結果集

  BigQuery 的規則運算式函式可用來篩選 WHERE 子句中的結果，還可在 SELECT 中顯示結果。以下範例將這兩種規則運算式用途結合為一項查詢。

  範例：

  #legacySQL
  SELECT
    /* Replace white spaces in the title with underscores. */
    REGEXP_REPLACE(title, r'\s+', '_') AS regexp_title, revisions
  FROM
    (SELECT title, COUNT(revision_id) as revisions
    FROM
      [bigquery-public-data:samples.wikipedia]
    WHERE
      wp_namespace=0
      /* Match titles that start with 'G', end with
       * 'e', and contain at least two 'o's.
       */
      AND REGEXP_MATCH(title, r'^G.*o.*o.*e$')
    GROUP BY
      title
    ORDER BY
      revisions DESC
    LIMIT 100);

• 對整數或浮點資料使用規則運算式

  雖然 BigQuery 的規則運算式函式僅適用於字串資料，但您可以使用 STRING() 函式，將整數或浮點數資料轉換為字串格式。在這個範例中，STRING() 用於將整數值 corpus_date 轉換為字串，然後由 REGEXP_REPLACE 更改。

  範例：

  #legacySQL
  SELECT
    corpus_date,
    /* Cast the corpus_date to a string value  */
    REGEXP_REPLACE(STRING(corpus_date),
      '^16',
      'Written in the sixteen hundreds, in the year \''
      ) AS date_string
  FROM [bigquery-public-data:samples.shakespeare]
  /* Cast the corpus_date to string, */
  /* match values that begin with '16' */
  WHERE
    REGEXP_MATCH(STRING(corpus_date), '^16')
  GROUP BY
    corpus_date, date_string
  ORDER BY
    date_string DESC
  LIMIT 5;

字串函式

字串函式會對字串資料進行運算。字串常數必須以單引號或雙引號括住。根據預設，字串函式會區分大小寫。您可以在查詢結尾附加 IGNORE CASE，啟用不區分大小寫的比對。IGNORE CASE 僅適用於 ASCII 字元，且僅適用於查詢的頂層。

這類函式不支援萬用字元；如需規則運算式功能，請使用規則運算式函式。

語法

字串函式
CONCAT()	傳回兩個以上字串的串連結果；如果有任何值為 NULL，則會傳回 NULL。
expr CONTAINS 'str'	如果 expr 包含指定的字串引數，則傳回 true。
INSTR()	傳回第一次出現指定字串的索引 (索引從 1 開始)。
LEFT()	傳回字串最左側的字元。
LENGTH()	傳回字串長度。
LOWER()	傳回所有字元都是小寫的原始字串。
LPAD()	在字串左側插入字元。
LTRIM()	從字串左側開始移除字元。
REPLACE()	取代所有的指定子字串。
RIGHT()	傳回字串最右側的字元。
RPAD()	在字串右側插入字元。
RTRIM()	移除字串右側的尾隨字元。
SPLIT()	將字串拆成重複的子字串。
SUBSTR()	傳回子字串...
UPPER()	傳回所有字元都是大寫的原始字串。

CONCAT('str1', 'str2', '...')
str1 + str2 + ...

傳回兩個以上字串的串連結果，如果任何值為 NULL，則傳回 NULL。範例：如果 str1 為 Java，str2 為 Script，則 CONCAT 會傳回 JavaScript。

expr CONTAINS 'str'

如果 expr 包含指定的字串引數，則傳回 true。這是一種區分大小寫的比較。

INSTR('str1', 'str2')

傳回 str2 在 str1 中首次出現的位置 (以 1 為起始索引)，如果 str2 未出現在 str1 中，則傳回 0。

LEFT('str', numeric_expr)

傳回 str 最左側的 numeric_expr 個字元。如果數字長度超過 str，系統會傳回完整字串。範例： LEFT('seattle', 3) 會傳回 sea。

LENGTH('str')

傳回字串長度的數值。範例：如果 str 為 '123456'，LENGTH 會傳回 6。

LOWER('str')

傳回原始字串，所有字元均為小寫。

LPAD('str1', numeric_expr, 'str2')

在 str1 左側填補 str2，重複 str2 直到結果字串長度剛好為 numeric_expr 個字元。範例： LPAD('1', 7, '?') 會傳回 ??????1。

LTRIM('str1' [, str2])

從 str1 左側開始移除字元。如果省略 str2，LTRIM 會移除 str1 左側的空格。否則，LTRIM 會從 str1 左側移除 str2 中的任何字元 (區分大小寫)。

範例：

SELECT LTRIM("Say hello", "yaS") 會傳回 " hello"。

SELECT LTRIM("Say hello", " ySa") 會傳回 "hello"。

REPLACE('str1', 'str2', 'str3')

以 str3 取代 str1 中所有的 str2。

RIGHT('str', numeric_expr)

傳回 str 最右側的 numeric_expr 個字元。如果這個數字比該字串還長，則會傳回整個字串。範例： RIGHT('kirkland', 4) 會傳回 land。

RPAD('str1', numeric_expr, 'str2')

在 str1 右側填補 str2，重複 str2 直到結果字串長度剛好為 numeric_expr 個字元。範例： RPAD('1', 7, '?') 會傳回 1??????。

RTRIM('str1' [, str2])

移除 str1 右側的尾隨字元。如果省略 str2，RTRIM 會移除 str1 結尾的空格。否則，RTRIM 會從 str1 右側移除 str2 中的所有字元 (區分大小寫)。

範例：

SELECT RTRIM("Say hello", "leo") 會傳回 "Say h"。

SELECT RTRIM("Say hello ", " hloe") 會傳回 "Say"。

SPLIT('str' [, 'delimiter'])

將字串分割成重複的子字串。如果指定 delimiter，SPLIT 函式會使用 delimiter 做為分隔符號，將 str 拆分為子字串。

SUBSTR('str', index [, max_len])

：傳回 str 的子字串，從 index 開始。如果使用選用的 max_len 參數，傳回的字串長度上限為 max_len 個字元。計算從 1 開始，因此字串中的第一個字元是在第 1 (而非 0) 位。如果 index 為 5，子字串會從 str 左側第 5 個字元開始。如果 index 為 -4，子字串會從 str 右側第 4 個字元開始。範例： SUBSTR('awesome', -4, 4) 會傳回子字串 some。

UPPER('str')

傳回原始字串，所有字元均為大寫。

逸出字串中的特殊字元

若要逸出特殊字元，請使用以下其中一種方式：

• 請使用 '\xDD' 標記法，其中 '\x' 後面會加上字元的兩位數十六進位表示法。
• 在斜線、單引號和雙引號前面使用逸出斜線。
• 其他字元請使用 C 樣式序列 ('\a', '\b', '\f', '\n', '\r', '\t', 和 '\v')。

以下是幾個逸出範例：

'this is a space: \x20'
'this string has \'single quote\' inside it'
'first line \n second line'
"double quotes are also ok"
'\070' -> ERROR: octal escaping is not supported

表格萬用字元函式

表格萬用字元函式可讓您輕鬆查詢一組特定資料表中的資料。表格萬用字元函式等同於以半形逗號分隔的聯集，其中包含萬用字元函式比對出的所有表格。使用表格萬用字元函式時，BigQuery 只會存取符合萬用字元的表格，並向您收取費用。表格萬用字元函式是在查詢的 FROM 子句中指定。

如果您在查詢中使用表格萬用字元函式，則函式不再需要包含在括號中。舉例來說，在下列範例中，有些使用了括號，有些則沒有括號。

使用萬用字元函式查詢多個資料表時，不支援快取結果 (即使已勾選「Use Cached Results」(使用快取結果) 選項)。如果多次執行相同的萬用字元查詢，系統會按次計費。

語法

表格萬用字元函式
TABLE_DATE_RANGE()	查詢特定時間範圍內的多個每日資料表。
TABLE_DATE_RANGE_STRICT()	查詢特定時間範圍內 (未缺少任何日期) 的多個每日資料表。
TABLE_QUERY()	查詢名稱與指定述詞相符的資料表。

TABLE_DATE_RANGE(prefix, timestamp1, timestamp2)

查詢與 <timestamp1>和 <timestamp2>之間日期範圍重疊的每日資料表。

資料表名稱必須採用下列格式：<prefix><day>，其中 <day> 的格式為 YYYYMMDD。

您可以使用日期和時間函式產生時間戳記參數，例如：

• TIMESTAMP('2012-10-01 02:03:04')
• DATE_ADD(CURRENT_TIMESTAMP(), -7, 'DAY')

範例：取得兩天之間的資料表

這個範例假設下列資料表確實存在：

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

#legacySQL
SELECT
  name
FROM
  TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
WHERE
  age >= 35

符合比對條件的資料表如下：

• mydata.people20140325
• mydata.people20140326
• mydata.people20140327

範例：取得兩天內 (截至「目前」) 的資料表

本範例假設名為 myproject-1234 的專案中存在下列資料表：

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE([myproject-1234:mydata.people],
                    DATE_ADD(CURRENT_TIMESTAMP(), -2, 'DAY'),
                    CURRENT_TIMESTAMP()))
WHERE
  age >= 35

符合比對條件的資料表如下：

• mydata.people20140323
• mydata.people20140324
• mydata.people20140325

TABLE_DATE_RANGE_STRICT(prefix, timestamp1, timestamp2)

這個函式相當於 TABLE_DATE_RANGE。唯一的差別在於，如果序列中缺少任何每日資料表，TABLE_DATE_RANGE_STRICT 就會失敗並傳回 Not Found: Table <table_name> 錯誤。

範例：缺少資料表時發生錯誤

這個範例假設下列資料表確實存在：

• people20140325
• people20140327

#legacySQL
SELECT
  name
FROM
  (TABLE_DATE_RANGE_STRICT([myproject-1234:mydata.people],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27')))
WHERE age >= 35

上方範例會針對資料表「people20140326」傳回「Not Found」(找不到) 錯誤。

TABLE_QUERY(dataset, expr)

查詢名稱與提供的 expr 相符的資料表。expr 參數必須以字串表示，且必須包含要評估的運算式。例如：'length(table_id) < 3'。

範例：比對名稱包含「oo」且長度大於 4 的資料表

這個範例假設下列資料表確實存在：

• mydata.boo
• mydata.fork
• mydata.ooze
• mydata.spoon

#legacySQL
SELECT
  speed
FROM (TABLE_QUERY([myproject-1234:mydata],
                  'table_id CONTAINS "oo" AND length(table_id) >= 4'))

符合比對條件的資料表如下：

• mydata.ooze
• mydata.spoon

範例：比對名稱開頭為「boo」，後接 3 到 5 個數字的資料表

本範例假設名為 myproject-1234 的專案中存在下列資料表：

• mydata.book4
• mydata.book418
• mydata.boom12345
• mydata.boom123456789
• mydata.taboo999

#legacySQL
SELECT
  speed
FROM
  TABLE_QUERY([myproject-1234:mydata],
               'REGEXP_MATCH(table_id, r"^boo[\d]{3,5}")')

符合比對條件的資料表如下：

• mydata.book418
• mydata.boom12345

網址函式

語法

網址函式
HOST()	根據指定網址，以字串形式傳回主機名稱。
DOMAIN()	針對指定網址以字串形式傳回網域。
TLD()	根據指定的網址，傳回網址中的頂層網域和任何國家/地區網域。

HOST('url_str')

根據網址，以字串形式傳回主機名稱。範例： HOST('http://www.google.com:80/index.html') 會傳回「www.google.com」

DOMAIN('url_str')

根據網址，以字串形式傳回網域。範例： DOMAIN('http://www.google.com:80/index.html') 會傳回「google.com」。

TLD('url_str')

根據指定的網址，傳回頂層網域和網址中的任何國家/地區網域。 範例：TLD('http://www.google.com:80/index.html') 會傳回「.com」。 TLD('http://www.google.co.uk:80/index.html') 會傳回 '.co.uk'。

注意：

• 這些函式不會執行反向 DNS 查詢，因此如果您使用 IP 位址呼叫這些函式，函式會傳回 IP 位址的區段，而非主機名稱的區段。
• 所有的網址剖析函式都必須使用小寫字元。如網址中有大寫字元，就會出現 NULL 或其他錯誤結果。如果您的資料包含大寫和小寫字母，建議您透過 LOWER() 將輸入內容傳入這個函式。

進階範例

從網址資料剖析網域名稱

這項查詢會使用 DOMAIN() 函式，傳回 GitHub 上列為存放區首頁的最熱門網域。請注意，您必須使用 HAVING，才能透過 DOMAIN() 函式的結果篩選記錄。這項功能可用於判斷網址資料中的參照網址資訊。

範例：

#legacySQL
SELECT
  DOMAIN(repository_homepage) AS user_domain,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_domain
HAVING
  user_domain IS NOT NULL AND user_domain != ''
ORDER BY
  activity_count DESC
LIMIT 5;

傳回：

+-----------------+----------------+
|   user_domain   | activity_count |
+-----------------+----------------+
| github.com      |         281879 |
| google.com      |          34769 |
| khanacademy.org |          17316 |
| sourceforge.net |          15103 |
| mozilla.org     |          14091 |
+-----------------+----------------+

如要專門查看 TLD 資訊，請使用 TLD() 函式。以下範例會顯示未列入常見範例的熱門 TLD。

#legacySQL
SELECT
  TLD(repository_homepage) AS user_tld,
  COUNT(*) AS activity_count
FROM
  [bigquery-public-data:samples.github_timeline]
GROUP BY
  user_tld
HAVING
  /* Only consider TLDs that are NOT NULL */
  /* or in our list of common TLDs */
  user_tld IS NOT NULL AND NOT user_tld
  IN ('','.com','.net','.org','.info','.edu')
ORDER BY
  activity_count DESC
LIMIT 5;

傳回：

+----------+----------------+
| user_tld | activity_count |
+----------+----------------+
| .de      |          22934 |
| .io      |          17528 |
| .me      |          13652 |
| .fr      |          12895 |
| .co.uk   |           9135 |
+----------+----------------+

窗型函式

窗型函式又稱為分析函式，可針對結果集的特定子集 (又稱「窗體」) 進行運算。有了窗型函式，就能輕鬆建立包含複雜分析資料 (例如過去期間的平均值和累積總計) 的報表。

每個窗型函式都需要指定窗體頂層和窗體底層的 OVER 子句。OVER 子句的三個元件 (分區、排序和框架) 可進一步控管視窗。分區功能可讓您將輸入資料劃分為具有共同特徵的邏輯群組。排序可讓您對某個分區內的結果進行排序。加框可讓您在分區中建立滑動式窗框，這個窗框會根據目前的資料列移動。您可以根據資料列數量或值的範圍 (例如時間間隔) 設定移動式窗框的大小。

#legacySQL
SELECT <window_function>
  OVER (
      [PARTITION BY <expr>]
      [ORDER BY <expr> [ASC | DESC]]
      [<window-frame-clause>]
     )

PARTITION BY

定義這個函式運作的基本分區。指定一或多個以半形逗號分隔的資料欄名稱；系統會為這些資料欄的每組不重複值建立一個分區，類似於 GROUP BY 子句。如果省略 PARTITION BY，基本分區會是視窗函式輸入內容中的所有資料列。

PARTITION BY 子句也可讓視窗函式分割資料並平行執行。如要搭配 allowLargeResults 使用 window 函式，或打算對 window 函式的輸出內容套用進一步的 join 或匯總，請使用 PARTITION BY 平行處理。

JOIN EACH 和 GROUP EACH BY 子句無法用於視窗函式的輸出內容。使用視窗函式產生大型查詢結果時，必須使用 PARTITION BY。

ORDER BY

排序分區。如果沒有 ORDER BY，則無法保證任何預設排列順序。排序作業發生在分區層級，且會在套用任何窗框子句前進行。如果指定 RANGE 視窗，請務必新增 ORDER BY 子句。預設順序為 ASC。

ORDER BY 在某些情況下為選用，但特定視窗函式 (例如 rank() 或 dense_rank()) 必須使用這個子句。

如果使用 ORDER BY，但未指定 ROWS 或 RANGE，ORDER BY 會暗示視窗從分區開頭延伸至目前資料列。如果沒有 ORDER BY 子句，視窗就是整個分區。

<window-frame-clause>

{ROWS | RANGE} {BETWEEN <start> AND <end> | <start> | <end>}

要執行作業的分區子集。其大小可以與分區相同或比分區小。如果使用 ORDER BY 時未指定 window-frame-clause，預設視窗框架為 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。如果同時省略 ORDER BY 和 window-frame-clause，預設視窗框架會是整個分區。

• ROWS：根據資料列位置定義窗體 (相對於目前的資料列)。舉例來說，如要新增資料欄，顯示前 5 列薪資值的總和，請查詢 SUM(salary) OVER (ROWS BETWEEN 5 PRECEDING AND CURRENT ROW)。資料列組合通常會包含目前的資料列 (但非必要)。
• RANGE：根據指定資料欄中的值範圍，定義相對於該資料欄在目前資料列中的值的視窗。僅適用於數字和日期，其中日期值為簡單整數 (自 Epoch 時間起算的微秒數)。具有相同值的相鄰資料列稱為「對等」資料列。CURRENT ROW 的對等資料列會包含在指定 CURRENT ROW 的視窗框架中。舉例來說，如果您將視窗結尾指定為 CURRENT ROW，且視窗中的下一列具有相同的值，則該列會納入函式計算。
• BETWEEN <start> AND <end>：範圍，包含起始和結束資料列。範圍不一定要包含目前資料列，但 <start> 必須小於或等於 <end>。
• <start>：指定這個視窗相對於目前資料列的起始偏移量。以下是支援的選項：

  {UNBOUNDED PRECEDING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  其中 <expr> 是正整數，PRECEDING 表示前一個列號或範圍值，FOLLOWING 則表示後一個列號或範圍值。UNBOUNDED PRECEDING 表示分區的第一列。如果開頭是在窗體之前，就會被設為分區的第一個資料列。
• <end>：指定這個視窗相對於目前資料列的結束偏移值。以下是支援的選項：

  {UNBOUNDED FOLLOWING | CURRENT ROW | <expr> PRECEDING | <expr> FOLLOWING}

  其中 <expr> 是正整數，PRECEDING 表示前一個資料列編號或範圍值，FOLLOWING 則表示後一個資料列編號或範圍值。UNBOUNDED FOLLOWING 代表分區的最後一列。如果結尾超出窗體的範圍，就會被設為分區的最後一個資料列。

匯總函式會將多個輸入資料列合併為一個輸出資料列，而視窗函式則會為每個輸入資料列傳回一個輸出資料列。這項功能可讓您更輕鬆地建立查詢，計算累計總和和移動平均值。舉例來說，下列查詢會傳回由 SELECT 陳述式定義的五列小型資料集的累計總和：

#legacySQL
SELECT name, value, SUM(value) OVER (ORDER BY value) AS RunningTotal
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

傳回值：

+------+-------+--------------+
| name | value | RunningTotal |
+------+-------+--------------+
| a    |     0 |            0 |
| b    |     1 |            1 |
| c    |     2 |            3 |
| d    |     3 |            6 |
| e    |     4 |           10 |
+------+-------+--------------+

以下範例會針對目前資料列和前一資料列中的值計算移動平均值。窗框包含與目前資料列一起移動的兩個資料列。

#legacySQL
SELECT
  name,
  value,
  AVG(value)
    OVER (ORDER BY value
          ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
    AS MovingAverage
FROM
  (SELECT "a" AS name, 0 AS value),
  (SELECT "b" AS name, 1 AS value),
  (SELECT "c" AS name, 2 AS value),
  (SELECT "d" AS name, 3 AS value),
  (SELECT "e" AS name, 4 AS value);

傳回值：

+------+-------+---------------+
| name | value | MovingAverage |
+------+-------+---------------+
| a    |     0 |           0.0 |
| b    |     1 |           0.5 |
| c    |     2 |           1.5 |
| d    |     3 |           2.5 |
| e    |     4 |           3.5 |
+------+-------+---------------+

語法

窗型函式
AVG() COUNT(*) COUNT([DISTINCT]) MAX() MIN() STDDEV() SUM()	執行與對應匯總函式相同的運算，不過是透過由 OVER 子句所定義的窗體計算。
CUME_DIST()	傳回雙精度浮點數，代表值在一組值中的累積分佈...
DENSE_RANK()	傳回值在一組值中的整數排名。
FIRST_VALUE()	傳回窗體中指定欄位的第一個值。
LAG()	讓您可讀取窗體中前一資料列的資料。
LAST_VALUE()	傳回窗體中指定欄位的最後一個值。
LEAD()	讓您可讀取窗體中下一資料列的資料。
NTH_VALUE()	傳回視窗框架 <n> 位置的 <expr> 值 ...
NTILE()	將窗體分成指定的區塊數。
PERCENT_RANK()	傳回目前資料列在分區所有資料列中的排名。
PERCENTILE_CONT()	傳回對應至窗體相關百分位引數的內插值...
PERCENTILE_DISC()	傳回窗體內與引數百分位數最接近的值。
RANK()	傳回值在一組值中的整數排名。
RATIO_TO_REPORT()	傳回各個值在值總和中所佔的比例。
ROW_NUMBER()	傳回窗體內查詢結果的目前資料列編號。

AVG(numeric_expr)
COUNT(*)
COUNT([DISTINCT] field)
MAX(field)
MIN(field)
STDDEV(numeric_expr)
SUM(field)

這些視窗函式執行的作業與對應的匯總函式相同，但會針對 OVER 子句定義的視窗進行運算。

另一個重大差異是，COUNT([DISTINCT] field) 函式做為視窗函式使用時，會產生確切結果，行為與 EXACT_COUNT_DISTINCT() 匯總函式類似。

在範例查詢中，ORDER BY 子句會導致視窗從分區開頭計算到目前資料列，進而產生該年度的累計總和。

#legacySQL
SELECT
   corpus_date,
   corpus,
   word_count,
   SUM(word_count) OVER (
     PARTITION BY corpus_date
     ORDER BY word_count) annual_total
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   word='love'
ORDER BY
   corpus_date, word_count
        

傳回：

corpus_date	corpus	word_count	annual_total
0	various	37	37
0	sonnets	157	194
1590	2kinghenryvi	18	18
1590	1kinghenryvi	24	42
1590	3kinghenryvi	40	82

CUME_DIST()

傳回雙精度浮點數，表示一組值中某個值的累積分布，計算公式為 <number of rows preceding or tied with the current row> / <total rows>。相等值會傳回相同的累積分佈值。

這個視窗函式需要 OVER 子句中的 ORDER BY。

#legacySQL
SELECT
   word,
   word_count,
   CUME_DIST() OVER (PARTITION BY corpus ORDER BY word_count DESC) cume_dist,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	cume_dist
handkerchief	29	0.2
satisfaction	5	0.4
displeasure	4	0.8
instruments	4	0.8
circumstance	3	1.0

DENSE_RANK()

傳回值在一組值中的整數排名。系統會根據與群組中其他值的比較結果，計算出排名。

相等值的排名會相同。下一個值的排名會按 1 遞增。舉例來說，如果有兩個值並列第 2 名，下一個值的排名就是 3。如果希望排名清單出現間隔，請使用 rank()。

這個視窗函式需要 OVER 子句中的 ORDER BY。

#legacySQL
SELECT
   word,
   word_count,
   DENSE_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) dense_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	dense_rank
handkerchief	29	1
satisfaction	5	2
displeasure	4	3
instruments	4	3
circumstance	3	4

FIRST_VALUE(<field_name>)

傳回視窗中 <field_name> 的第一個值。

#legacySQL
SELECT
   word,
   word_count,
   FIRST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) fv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

傳回：

word	word_count	fv
imperfectly	1	imperfectly

LAG(<expr>[, <offset>[, <default_value>]])

可讓您讀取視窗中前一列的資料。具體來說，LAG() 會傳回目前資料列前 <offset> 列的 <expr> 值。如果資料列不存在，則會傳回 <default_value>。

#legacySQL
SELECT
   word,
   word_count,
   LAG(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lag,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	lag
handkerchief	29	null
satisfaction	5	handkerchief
displeasure	4	satisfaction
instruments	4	displeasure
circumstance	3	instruments

LAST_VALUE(<field_name>)

傳回視窗中 <field_name> 的最後一個值。

#legacySQL
SELECT
   word,
   word_count,
   LAST_VALUE(word) OVER (PARTITION BY corpus ORDER BY word_count DESC) lv,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 1

傳回：

word	word_count	lv
imperfectly	1	imperfectly

LEAD(<expr>[, <offset>[, <default_value>]])

可讓您讀取視窗中後續資料列的資料。具體來說，LEAD() 會傳回目前資料列後方第 <offset> 列的 <expr> 值。如果資料列不存在，則會傳回 <default_value>。

#legacySQL
SELECT
   word,
   word_count,
   LEAD(word, 1) OVER (PARTITION BY corpus ORDER BY word_count DESC) lead,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	lead
handkerchief	29	satisfaction
satisfaction	5	displeasure
displeasure	4	instruments
instruments	4	circumstance
circumstance	3	null

NTH_VALUE(<expr>, <n>)

傳回視窗框架中 <n> 位置的 <expr> 值，其中 <n> 是從 1 開始計算的索引。

NTILE(<num_buckets>)

將一連串資料列分成 <num_buckets> 個值區，並為每個資料列指派對應的值區編號 (整數)。ntile() 函式會盡可能平均分配 bucket 編號，並為每個資料列傳回 1 到 <num_buckets> 之間的值。

#legacySQL
SELECT
   word,
   word_count,
   NTILE(2) OVER (PARTITION BY corpus ORDER BY word_count DESC) ntile,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	ntile
handkerchief	29	1
satisfaction	5	1
displeasure	4	1
instruments	4	2
circumstance	3	2

PERCENT_RANK()

傳回目前資料列在分區所有資料列中的排名。傳回的值會介於 0 到 1 之間 (包括 0 和 1)。傳回的第一個值是 0.0。

這個視窗函式需要 OVER 子句中的 ORDER BY。

#legacySQL
SELECT
   word,
   word_count,
   PERCENT_RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) p_rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	p_rank
handkerchief	29	0.0
satisfaction	5	0.25
displeasure	4	0.5
instruments	4	0.5
circumstance	3	1.0

PERCENTILE_CONT(<percentile>)

根據 ORDER BY 子句排序後，傳回與視窗百分位數引數對應的內插值。

<percentile> 必須介於 0 和 1 之間。

這個視窗函式需要 OVER 子句中的 ORDER BY。

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_CONT(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_cont,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	p_cont
handkerchief	29	4
satisfaction	5	4
displeasure	4	4
instruments	4	4
circumstance	3	4

PERCENTILE_DISC(<percentile>)

傳回窗體內與引數百分位數最接近的值。

<percentile> 必須介於 0 和 1 之間。

這個視窗函式需要 OVER 子句中的 ORDER BY。

#legacySQL
SELECT
   word,
   word_count,
   PERCENTILE_DISC(0.5) OVER (PARTITION BY corpus ORDER BY word_count DESC) p_disc,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	p_disc
handkerchief	29	4
satisfaction	5	4
displeasure	4	4
instruments	4	4
circumstance	3	4

RANK()

傳回值在一組值中的整數排名。系統會根據與群組中其他值的比較結果，計算出排名。

相等值的排名會相同。下一個值的排名會根據先前出現的相同值數量遞增。舉例來說，如果有兩個值並列第 2 名，下一個值的排名會是 4，而非 3。如果不想在排名清單中留空，請使用 dense_rank()。

這個視窗函式需要 OVER 子句中的 ORDER BY。

#legacySQL
SELECT
   word,
   word_count,
   RANK() OVER (PARTITION BY corpus ORDER BY word_count DESC) rank,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	rank
handkerchief	29	1
satisfaction	5	2
displeasure	4	3
instruments	4	3
circumstance	3	5

RATIO_TO_REPORT(<column>)

以介於 0 到 1 之間的雙精度浮點數，傳回各個值在值總和中所佔的比例。

#legacySQL
SELECT
   word,
   word_count,
   RATIO_TO_REPORT(word_count) OVER (PARTITION BY corpus ORDER BY word_count DESC) r_to_r,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	r_to_r
handkerchief	29	0.6444444444444445
satisfaction	5	0.1111111111111111
displeasure	4	0.08888888888888889
instruments	4	0.08888888888888889
circumstance	3	0.06666666666666667

ROW_NUMBER()

傳回窗體內查詢結果的目前資料列編號 (從 1 開始)。

#legacySQL
SELECT
   word,
   word_count,
   ROW_NUMBER() OVER (PARTITION BY corpus ORDER BY word_count DESC) row_num,
FROM
   [bigquery-public-data:samples.shakespeare]
WHERE
   corpus='othello' and length(word) > 10
LIMIT 5

傳回：

word	word_count	row_num
handkerchief	29	1
satisfaction	5	2
displeasure	4	3
instruments	4	4
circumstance	3	5

其他函式

語法

其他函式
CASE WHEN ... THEN	使用 CASE 即可在查詢中選擇兩個以上的替代運算式。
CURRENT_USER()	傳回執行查詢的使用者所登入的電子郵件地址。
EVERY()	如果引數的所有輸入內容皆為 true，則會傳回 true。
FROM_BASE64()	將採 Base-64 編碼的輸入字串轉換成 BYTES 格式。
HASH()	計算並傳回 64 位元的已簽署雜湊值...
FARM_FINGERPRINT()	計算並傳回 64 位元的已簽署指紋值...
IF()	如果第一個引數為 true，就會傳回第二個引數；如果不是 true，則傳回第三個引數。
POSITION()	傳回引數在序列 (從 1 開始) 中的位置。
SHA1()	傳回 SHA1 雜湊，格式為 BYTES。
SOME()	如果引數的輸入內容中至少有一個是 true，則會傳回 true。
TO_BASE64()	將 BYTES 引數轉換成採 Base-64 編碼的字串。

CASE WHEN when_expr1 THEN then_expr1
  WHEN when_expr2 THEN then_expr2 ...
  ELSE else_expr END

使用 CASE，在查詢中選擇兩個或多個替代運算式。WHEN 運算式必須是布林值，且 THEN 子句和 ELSE 子句中的所有運算式都必須是相容類型。

CURRENT_USER()

傳回執行查詢的使用者電子郵件地址。

EVERY(<condition>)

如果 condition 對所有輸入內容皆為 true，則傳回 true。與 OMIT IF 子句搭配使用時，這個函式適用於涉及重複欄位的查詢。

FROM_BASE64(<str>)

將 Base64 編碼的輸入字串 str 轉換為 BYTES 格式。如要將 BYTES 轉換為 Base64 編碼字串，請使用 TO_BASE64()。

HASH(expr)

計算並傳回 expr 位元組的 64 位元帶正負號雜湊值，如 CityHash 程式庫 (1.0.3 版) 所定義。支援任何字串或整數運算式，且函式會遵循字串的 IGNORE CASE，傳回不區分大小寫的值。

FARM_FINGERPRINT(expr)

使用開放原始碼 FarmHash 程式庫中的 Fingerprint64 函式，計算並傳回 STRING 或 BYTES 輸入內容的 64 位元帶正負號指紋值。針對特定輸入內容，這個函式的輸出內容一律不會變更，且與使用 GoogleSQL 時 FARM_FINGERPRINT 函式的輸出內容相符。遵循 IGNORE CASE 的字串規則，傳回不區分大小寫的值。

IF(condition, true_return, false_return)

根據 condition 為 true 或 false，傳回 true_return 或 false_return。傳回值可以是常值或衍生自欄位的值，但必須是相同的資料類型。欄位衍生值不需要包含在 SELECT 子句中。

POSITION(field)

傳回一組重複欄位中欄位的 1 型索引依序位置。

SHA1(<str>)

傳回輸入字串 str 的 SHA1 雜湊，格式為 BYTES。 您可以使用 TO_BASE64() 將結果轉換為 base64。例如：

#legacySQL
SELECT
  TO_BASE64(SHA1(corpus))
FROM
  [bigquery-public-data:samples.shakespeare]
LIMIT
  100;

SOME(<condition>)

如果至少有一個輸入內容的 condition 為 true，則傳回 true。與 OMIT IF 子句搭配使用時，這個函式適用於涉及重複欄位的查詢。

TO_BASE64(<bin_data>)

將 BYTES 輸入內容 bin_data 轉換為 Base64 編碼字串。例如：

#legacySQL
SELECT
  TO_BASE64(SHA1(title))
FROM
  [bigquery-public-data:samples.wikipedia]
LIMIT
  100;

如要將 Base64 編碼字串轉換為 BYTES，請使用 FROM_BASE64()。

進階範例

• 使用條件將結果分類

  下列查詢使用 CASE/WHEN 區塊，根據州/省清單將結果分類為「區域」類別。如果狀態未顯示為其中一個 WHEN 陳述式的選項，狀態值會預設為「None」。

  範例：

  #legacySQL
  SELECT
    CASE
      WHEN state IN ('WA', 'OR', 'CA', 'AK', 'HI', 'ID',
                     'MT', 'WY', 'NV', 'UT', 'CO', 'AZ', 'NM')
        THEN 'West'
      WHEN state IN ('OK', 'TX', 'AR', 'LA', 'TN', 'MS', 'AL',
                     'KY', 'GA', 'FL', 'SC', 'NC', 'VA', 'WV',
                     'MD', 'DC', 'DE')
        THEN 'South'
      WHEN state IN ('ND', 'SD', 'NE', 'KS', 'MN', 'IA',
                     'MO', 'WI', 'IL', 'IN', 'MI', 'OH')
        THEN 'Midwest'
      WHEN state IN ('NY', 'PA', 'NJ', 'CT',
                     'RI', 'MA', 'VT', 'NH', 'ME')
        THEN 'Northeast'
      ELSE 'None'
    END as region,
    average_mother_age,
    average_father_age,
    state, year
  FROM
    (SELECT
       year, state,
       SUM(mother_age)/COUNT(mother_age) as average_mother_age,
       SUM(father_age)/COUNT(father_age) as average_father_age
     FROM
       [bigquery-public-data:samples.natality]
     WHERE
       father_age < 99
     GROUP BY
       year, state)
  ORDER BY
    year
  LIMIT 5;

  傳回：

  +--------+--------------------+--------------------+-------+------+
  | region | average_mother_age | average_father_age | state | year |
  +--------+--------------------+--------------------+-------+------+
  | South  | 24.342600163532296 | 27.683769419460344 | AR    | 1969 |
  | West   | 25.185041908446163 | 28.268214055448098 | AK    | 1969 |
  | West   | 24.780776677578217 | 27.831181063905248 | CA    | 1969 |
  | West   | 25.005834769924412 | 27.942978384829598 | AZ    | 1969 |
  | South  | 24.541730952905738 | 27.686430093306885 | AL    | 1969 |
  +--------+--------------------+--------------------+-------+------+
  

• 模擬資料透視表

  使用條件陳述式，將子選取查詢結果整理成資料列和資料欄。在以下範例中，搜尋結果會顯示以「Google」開頭且修訂次數最多的維基百科文章，並依資料欄整理，顯示符合各種條件的修訂次數。

  範例：

  #legacySQL
  SELECT
    page_title,
    /* Populate these columns as True or False, */
    /*  depending on the condition */
    IF (page_title CONTAINS 'search',
        INTEGER(total), 0) AS search,
    IF (page_title CONTAINS 'Earth' OR
        page_title CONTAINS 'Maps', INTEGER(total), 0) AS geo,
  FROM
    /* Subselect to return top revised Wikipedia articles */
    /* containing 'Google', followed by additional text. */
    (SELECT
      TOP (title, 5) as page_title,
      COUNT (*) as total
     FROM
       [bigquery-public-data:samples.wikipedia]
     WHERE
       REGEXP_MATCH (title, r'^Google.+') AND wp_namespace = 0
    );

  傳回：

  +---------------+--------+------+
  |  page_title   | search | geo  |
  +---------------+--------+------+
  | Google search |   4261 |    0 |
  | Google Earth  |      0 | 3874 |
  | Google Chrome |      0 |    0 |
  | Google Maps   |      0 | 2617 |
  | Google bomb   |      0 |    0 |
  +---------------+--------+------+
  

• 使用 HASH 選取資料的隨機樣本

  某些查詢可以對結果集進行隨機抽樣，以提供更加實用的結果。如要擷取值的隨機樣本，請使用 HASH 函式來傳回結果，其中雜湊的模數「n」等於零。

  舉例來說，下列查詢會找出「title」值的 HASH()，然後檢查該值模數「2」是否為零。這會使大約 50% 的值被標示為「sampled」(已取樣)。如要減少抽樣的值，請將模數運算的值從「2」調高為較大的數字。這項查詢會搭配使用 ABS 函式和 HASH，因為 HASH 可能會傳回負值，而負值的模數運算子會產生負值。

  範例：

  #legacySQL
  SELECT
    title,
    HASH(title) AS hash_value,
    IF(ABS(HASH(title)) % 2 == 1, 'True', 'False')
      AS included_in_sample
  FROM
    [bigquery-public-data:samples.wikipedia]
  WHERE
    wp_namespace = 0
  LIMIT 5;