舊版 SQL 函式與運算子

本文件針對舊版的 SQL 函式和運算子提供了詳細的說明。建議使用的 BigQuery 查詢語法為標準 SQL。如要進一步瞭解標準 SQL，請參閱標準 SQL 函式與運算子一文。

支援的函式與運算子

大部分的 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` 不是 NULL，就會傳回 `expr`；如果是 NULL，則會傳回 `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()`	以 `YYYY-MM-DD HH:MM:SS.uuuuuu` 的格式傳回 UNIX 時間戳記。
`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。
`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()`	將最低有效 32 位元的 `integer_value` 轉換為使用者可理解的 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()`	以 BYTES 格式傳回 SHA1 雜湊。
`SOME()`	如果引數的輸入內容中至少有一個是 true，則會傳回 true。
`TO_BASE64()`	將 BYTES 引數轉換成採 Base-64 編碼的字串。

查詢語法

附註：關鍵字「不」區分大小寫。為了方便說明，本文件中的關鍵字 (例如 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 語法中屬於保留字詞，因此參照該欄位的查詢會發生不明錯誤。如要避免這項錯誤，請在「partition」前後加上方括號：
```
SELECT [partition] FROM ...
```

範例

以下範例定義了 SELECT 子句中的別名，並在 ORDER BY 子句中參照了其中一個別名。請注意，「word」資料欄不能透過 WHERE 子句中的「word_alias」參照，而必須透過名稱參照。「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;

使用子查詢

「子查詢」是前後加上括號的巢狀 SELECT 陳述式。如同資料表的資料欄，子查詢的 SELECT 子句中計算用的運算式也可供外部查詢使用。

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

將逗號當做 `UNION ALL`

與標準 SQL 不同，BigQuery 使用逗號當做 UNION ALL 運算子 (而不是 CROSS JOIN 運算子)。由於過去 BigQuery 不支援 CROSS JOIN，且 BigQuery 使用者需要定期撰寫 UNION ALL 查詢，因此這項舊版行為才演變成現在的樣貌。在標準 SQL 中，執行聯集作業的查詢特別繁雜，而使用逗號做為聯結運算子可讓使用者更有效率地撰寫這類查詢。舉例來說，使用者可以透過這種方式，一次查詢不同天 (多個記錄起始日) 的記錄。

#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 子句中使用兩種聯結，分別是半聯結和反半聯結。半聯結的指定方式是使用 IN 關鍵字和子查詢，反聯結的指定方式則是使用 NOT IN 關鍵字。

範例

以下查詢使用了半聯結來找出符合特定條件的 N 元語法，這類 N 元語法的第一個字詞與其他 N 元語法的第二個字詞相同，且後者的第三個字詞是「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;

以下查詢使用了半聯結來傳回符合特定條件的女性數量，這類女性的年齡超過 50 歲，且曾在美國新生兒人數最多的 10 個州生過小孩。

#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) 來傳回符合特定條件的女性數量，這類女性的年齡超過 50 歲，且曾在美國新生兒人數最少的 40 個州生過小孩。

#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>,」這項錯誤，建議切換至標準 SQL。如需瞭解如何將 OMIT...IF 陳述式遷移至標準 SQL，請參閱遷移至標準 SQL 一文。

範例

OMIT RECORD IF 可發揮與 WITHIN 和 HAVING 相同的效果，詳情請見上述有關 WITHIN 修飾符的範例。

#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，如今則不建議這樣做。在不加入 EACH 修飾符的情況下使用 GROUP BY 通常效能較佳。發生查詢資源超出上限的錯誤時才需使用 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 子句的輸出內容。

範例

以下查詢會針對包含字母 a 和發生次數最多 1 萬次的 N 元語法資料集範例，計算這類資料集中最常見的「第一個」字詞。

#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 子句不可包含任何函式，只接受數字常數。

查詢文法

前文已詳細說明 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
    }

標記法：

方括號 [ ] 表示選擇性子句。
大括號 { } 是用來括住一組選項。
分隔號 | 表示邏輯意義上的 OR。
如果方括號裡有逗號或關鍵字且後面接上刪節號 ([, ... ])，表示前方的項目可透過指定分隔符在清單中重複出現。
括號 ( ) 表示文字括號。

匯總函式

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

資料表匯總
使用匯總函式來總結資料表中的所有合格資料列，例如：

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])

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

如果您使用 DISTINCT 關鍵字，函式就會針對指定欄位傳回相異值的總數。請注意，傳回的 DISTINCT 值是概略統計結果，不一定是實際值。

使用 EXACT_COUNT_DISTINCT() 即可取得準確的答案。

如果您想針對 COUNT(DISTINCT) 取得更精確的結果，可以指定第二個參數 n，這樣就能保證低於這個門檻的結果都準確無誤。n 的預設值是 1000，但如果您將 n 設為更大的數字，那麼在到達 n 的值為止，系統會針對 COUNT(DISTINCT) 傳回精確的結果。不過，為 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「欄位」)。

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 函式，結果也不會包含重複欄位。使用會產生中繼結果 (以在相同查詢中立即使用) 的 subselect 時，請採用 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 個二十分位數 (第 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()。如要進一步瞭解母體和樣本標準差之間的差異，請前往 Wikipedia 的標準差頁面。

STDDEV_SAMP(numeric_expr)

針對 numeric_expr 算出的值，計算這個值的樣本標準差。使用 STDDEV_SAMP() 即可根據母體的代表性樣本計算整個母體的標準差。如果您的資料集包含整個母體，請改用 STDDEV_POP()。如要進一步瞭解母體和樣本標準差之間的差異，請前往 Wikipedia 的標準差頁面。

SUM(field)

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

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

依照頻率傳回主要的 max_records 記錄。詳情請參閱下方的 TOP 說明。

UNIQUE(expr)

傳回函式範圍內一組不重複非 NULL 值 (依未定義的順序排列)。與不含 EACH 關鍵字的大型 GROUP BY 子句類似，如果有太多相異值，這項查詢就會發生「Resources Exceeded (資源超出上限)」錯誤。不過與 GROUP BY 不同，UNIQUE 函式可搭配範圍匯總使用，以便有效率地針對所含的值數量有限的巢狀欄位進行運算。

VARIANCE(numeric_expr)

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

VAR_POP(numeric_expr)

針對 numeric_expr 算出的值，計算這個值的母體變異數。如要進一步瞭解母體和樣本標準差之間的差異，請前往 Wikipedia 的標準差頁面。

VAR_SAMP(numeric_expr)

針對 numeric_expr 算出的值，計算這個值的樣本變異數。如要進一步瞭解母體和樣本標準差之間的差異，請前往 Wikipedia 的標準差頁面。

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)

以 bytes 類型的值傳回 string_expr。

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` 不是 NULL，就會傳回 `expr`；如果是 NULL，則會傳回 `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 不是 NULL，就會傳回 expr；如果是 NULL，則會傳回 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 不是 NULL，就會傳回 expr；如果是 NULL，則會傳回 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()`	以 `YYYY-MM-DD HH:MM:SS.uuuuuu` 的格式傳回 UNIX 時間戳記。
`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。
`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>)

以使用者可理解的字串傳回 TIMESTAMP 類型的資料，格式為 %Y-%m-%d。

範例：

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 點 0 分 (午夜) 的 UNIX 時間戳記。

範例：

SELECT UTC_USEC_TO_DAY(1274259481071200);

傳回：1274227200000000

UTC_USEC_TO_HOUR(<unix_timestamp>)

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

舉例來說，如果 unix_timestamp 發生在 8 點 58 分，這個函式就會傳回同一天 8 點整的 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() 函式來顯示每位 Wikipedia 內容提供者每個月在修訂註解中使用的字元數量。

範例：

#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()`	將最低有效 32 位元的 `integer_value` 轉換為使用者可理解的 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): 將最低有效 32 位元的 integer_value 轉換為使用者可理解的 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)

以 10.1.5.23 或 2620:0:1009:1:216:36ff:feef:3f 的格式傳回使用者可理解的 IP 位址。範例：

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')

傳回 str1 中第一次出現 str2 的索引 (索引從 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')

在 str2 左側另外補上 str1，並不斷重複 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')

在 str2 右側另外補上 str1，並不斷重複 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 子句指定。

如果您在查詢中使用了資料表萬用字元函式，就不再需要在函式前後加上括號。舉例來說，在下列範例中，有些使用了括號，有些則沒有括號。

使用萬用字元函式針對多個資料表進行查詢時，無法使用快取結果 (即使已勾選 [使用快取的結果] 選項也一樣)。如果您執行相同的萬用字元查詢很多次，系統會針對每一筆查詢向您收費。

語法

資料表萬用字元函式
`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 使用窗型函式，或打算進一步對窗型函式的輸出內容進行彙整或匯總，請使用 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：根據指定資料欄中一定範圍的值定義窗體 (相較於目前資料列中該資料欄的值)。系統只會針對數字和日期進行運算，其中日期的值為簡單的整數 (紀元開始算起的微秒數)。包含相同值的鄰近資料列叫做「對等」資料列。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> 是從一開始的索引。

NTILE(<num_buckets>)

將一系列的資料列分成 <num_buckets> 個區塊，並以整數針對每一個資料列指派對應的區塊編號。ntile() 函式會盡可能均等指派區塊編號，並針對每一個資料列傳回 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()`	以 BYTES 格式傳回 SHA1 雜湊。
`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)

根據 CityHash 資料庫 (1.0.3 版) 的定義，針對 expr 的位元組計算並傳回 64 位元的已簽署雜湊值。任何字串或整數運算式都受支援，而且函式會針對字串使用 IGNORE CASE，因此會傳回大小寫不變的值。

FARM_FINGERPRINT(expr)

使用開放原始碼 FarmHash 資料庫中的 Fingerprint64 函式，計算及傳回 STRING 或 BYTES 輸入內容的 64 位元已簽署指紋值。這個函式針對特定輸入內容而輸出的結果永遠不會改變，而且與使用標準 SQL 時 FARM_FINGERPRINT 函式的輸出結果相同。系統會針對字串採用 IGNORE CASE，因此會傳回大小寫不變的值。

IF(condition, true_return, false_return)

會傳回 true_return 或 false_return (視 condition 為 true 或 false 而定)。傳回值可以是實字或資料欄擷取值，但必須是同一種資料類型。您不需要在 SELECT 子句中加入衍伸自欄位的值。

POSITION(field)

傳回 field 在一組重複欄位中的位置 (以從 1 開始的序號表示)。

SHA1(<str>)

以 BYTES 格式傳回輸入字串 str 的 SHA1 雜湊值。您可以使用 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 |
+--------+--------------------+--------------------+-------+------+

模擬資料透視表

您可以使用條件陳述式，將 subselect 查詢的結果歸入不同的資料列和資料欄。在下方範例中，使用者搜尋最常修訂且開頭為「Google」這個值的 Wikipedia 文章後，搜尋結果會歸入不同的資料欄，當中的修訂次數則會在滿足各種條件的情況下顯示。

範例：

#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」調高為較大的數字。HASH 可能會傳回負數值，且用於負數值的模數運算子會產生負數值，因此查詢會結合使用 ABS 函式和 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;
```

支援的函式與運算子

查詢語法

SELECT 子句

範例

匯總函式的 WITHIN 修飾符

範例

FROM 子句

參照資料表

範例

範例

使用子查詢

將逗號當做 UNION ALL

資料表萬用字元函式

FLATTEN 運算子

JOIN 運算子

JOIN 類型

EACH 修飾符

半聯結和反聯結

範例

WHERE 子句

範例

OMIT RECORD IF 子句

範例

GROUP BY 子句

範例

EACH 修飾符

ROLLUP 函式

範例

範例

HAVING 子句

範例

ORDER BY 子句

LIMIT 子句

查詢文法

匯總函式

語法

TOP() 函式

TOP() 範例

進階範例

算術運算子

語法

位元函式

語法

轉換函式

語法

比較函式

語法

日期和時間函式

語法

進階範例

IP 函式

語法

語法

JSON 函式

語法

邏輯運算子

語法

數學函式

語法

進階範例

規則運算式函式

語法

進階範例

字串函式

語法

資料表萬用字元函式

語法

網址函式

語法

進階範例

窗型函式

語法

其他函式

語法

進階範例

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

將逗號當做 `UNION ALL`

`EACH` 修飾符

`ROLLUP` 函式