萬用字元資料表

萬用字元資料表可讓您使用精簡的 SQL 陳述式查詢多個資料表。只有標準 SQL 提供萬用字元資料表。如要在舊版 SQL 中使用對等的功能,請參閱資料表萬用字元函式

萬用字元資料表語法

萬用字元資料表語法:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression
<project-id>
Cloud Platform 專案 ID。使用預設專案 ID 時可省略。
<dataset-id>
BigQuery 資料集 ID。
<table-prefix>
經萬用字元比對出的所有資料表通用的字串。資料表前置字串可省略,省略時會比對出資料集中的所有資料表。
* (萬用字元)
萬用字元「*」代表資料表名稱的多一個字元。萬用字元只能顯示在萬用字元資料表名稱的結尾,做為最後一個字元。

萬用字元資料表 WHERE 子句也支援 _TABLE_SUFFIX 虛擬資料欄,其中包含經萬用字元比對出的值。您可以搭配使用 _TABLE_SUFFIX比較運算子來篩選查詢。舉例來說,以下 WHERE 子句使用了比較運算子:

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

如要深入瞭解 _TABLE_SUFFIX 虛擬資料欄,請參閱使用 _TABLE_SUFFIX 篩選所選資料表一節。

在倒引號中以萬用字元括住資料表名稱

萬用字元資料表名稱包含特殊字元 (*),這代表您必須將萬用字元資料表名稱括在倒引號 (`) 字元內。舉例來說,以下查詢使用了倒引號,屬於有效的查詢:

#standardSQL
/* Valid standard SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

以下查詢因沒有正確使用倒引號而「無效」:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

引號沒有作用:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

遷移舊版 SQL 資料表萬用字元函式

在舊版 SQL 中,您可以使用下列資料表萬用字元函式查詢多個資料表。

  • TABLE_DATE_RANGE()TABLE_DATE_RANGE_STRICT()
  • TABLE_QUERY()

TABLE_DATE_RANGE() 函式

舊版 SQL TABLE_DATE_RANGE() 函式適用於符合特定命名配置的資料表:<prefix>YYYYMMDD,其中的 <prefix> 代表資料表名稱的第一部分,YYYYMMDD 則代表與該資料表的資料相關聯的日期。

舉例來說,以下舊版 SQL 查詢會從內含西雅圖地區氣象資料的每日資料表集合中找出平均氣溫:

#legacySQL
SELECT
  ROUND(AVG(TemperatureF),1) AS AVG_TEMP_F
FROM
  TABLE_DATE_RANGE([mydataset.sea_weather_],
                    TIMESTAMP("2016-05-01"),
                    TIMESTAMP("2016-05-09"))

在標準 SQL 中,對等的查詢會使用資料表萬用字元和 BETWEEN 子句。

#standardSQL
SELECT
  ROUND(AVG(TemperatureF),1) AS AVG_TEMP_F
FROM
  `mydataset.sea_weather_*`
WHERE
  _TABLE_SUFFIX BETWEEN '20160501' AND '20160509'

TABLE_QUERY() 函式

舊版 SQL TABLE_QUERY() 函式可讓您根據模式尋找資料表名稱。將 TABLE_QUERY() 函式遷移至標準 SQL 時,由於標準 SQL 不支援 TABLE_QUERY() 函式,您可以改用 _TABLE_SUFFIX 虛擬資料欄進行篩選。遷移時請注意下列差異:

  • 在舊版 SQL 中,請將 TABLE_QUERY() 函式放在 FROM 子句中;在標準 SQL 中,請在 WHERE 子句中使用 _TABLE_SUFFIX 虛擬資料欄進行篩選。

  • 在舊版 SQL 中,TABLE_QUERY() 函式會針對完整資料表名稱 (或 table_id) 運作;在標準 SQL 中,_TABLE_SUFFIX 虛擬資料欄則包含部分或完整資料表名稱 (取決於萬用字元的使用方式)。

WHERE 子句中的篩選器

從舊版 SQL 遷移至標準 SQL 時,請將篩選器移至 WHERE 子句。舉例來說,以下查詢會找出整年中尾數為 0 的最高氣溫:

#legacySQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  TABLE_QUERY([bigquery-public-data:noaa_gsod],
               'REGEXP_MATCH(table_id, r"0$")')
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
ORDER BY
  max DESC

在標準 SQL 中,對等的查詢會使用資料表萬用字元,並且將規則運算式函數 REGEXP_CONTAINS() 放在 WHERE 子句中:

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
  AND REGEXP_CONTAINS(_TABLE_SUFFIX, r"0$")
ORDER BY
  max DESC

table_id 和 _TABLE_SUFFIX 之間的差異

在舊版 SQL TABLE_QUERY(dataset, expr) 函式中,第二個參數是針對完整資料表名稱運作的運算式 (使用 table_id 值)。遷移至標準 SQL 時,您在 WHERE 子句中建立的篩選器會針對 _TABLE_SUFFIX 的值運作,該值包含部分或完整資料表名稱 (取決於萬用字元的使用方式)。

舉例來說,以下舊版 SQL 查詢會在規則運算式中使用完整資料表名稱找出整年中尾數為 0 的最高氣溫:

#legacySQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  TABLE_QUERY([bigquery-public-data:noaa_gsod],
               'REGEXP_MATCH(table_id, r"gsod\d{3}0")')
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
ORDER BY
  max DESC

在標準 SQL 中,對等的查詢可以使用完整資料表名稱,也可以僅使用部分資料表名稱。您可以在標準 SQL 中使用空白前置字串,讓您的篩選器針對完整資料表名稱運作:

# Standard SQL empty prefix
FROM
  `bigquery-public-data.noaa_gsod.*`

不過,較長前置字串的執行效能優於空白前置字串,因此以下範例使用較長的前置字串,這代表 _TABLE_SUFFIX 的值只是資料表名稱的一部分。

#standardSQL
SELECT
  max,
  ROUND((max-32)*5/9,1) celsius,
  year
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND max > 100 # to improve ORDER BY performance
  AND REGEXP_CONTAINS(_TABLE_SUFFIX, r"\d{3}0")
ORDER BY
  max DESC
本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁