SELECT - Amazon Athena
概要參數在 Amazon S3 中取得來源資料的檔案位置 逸出單引號其他資源

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

SELECT

從零個或多個資料表中擷取資料列。

注意

本主題提供摘要資訊以供參考。關於使用 SELECT 和 SQL 語言的完整資訊已超出本文件的範圍。如需有關使用 Athena 特定 SQL 的資訊，請參閱在 Amazon Athena 中進行 SQL 查詢的考量事項與限制和使用 Amazon Athena 執行 SQL 查詢。如需在 Athena 建立資料庫、建立資料表和執行資料表 SELECT 查詢的範例，請參閱 開始使用。

概要

[ WITH with_query [, ...] ]
SELECT [ ALL | DISTINCT ] select_expression [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] ]
[ HAVING condition ]
[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]
[ OFFSET count [ ROW | ROWS ] ]
[ LIMIT [ count | ALL ] ]
注意

SQL SELECT 陳述式中的保留字必須以雙引號括住。如需詳細資訊，請參閱SQL SELECT 陳述式中的預留關鍵字清單。

參數

[ WITH with_query [, ....] ]

您可以使用 WITH 來展開巢狀查詢，或簡化子查詢。

Athena 引擎版本 3 開始支援使用 WITH 子句建立遞迴查詢。最大遞歸深度為 10。

WITH 子句位於查詢中的 SELECT 清單前面，可定義一或多個子查詢以用於 SELECT 查詢內。

每個子查詢定義臨時資料表，類似於檢視定義，供您在 FROM 子句中參考。這些資料表只在查詢執行時才使用。

with_query 語法是：

subquery_table_name [ ( column_name [, ...] ) ] AS (subquery)

其中：

  • subquery_table_name 是臨時資料表的唯一名稱，定義 WITH 子句子查詢的結果。每個 subquery 必須有可在 FROM 子句中參考的資料表名稱。

  • column_name [, ...] 是選用的輸出欄名稱清單。欄名稱的數目必須等於或少於 subquery 所定義的欄數。

  • subquery 是任何查詢陳述式。

[全部 | 不同] 選擇表達式

select_expression 決定要選取的列。A select_expression 可以使用下列格式之一：

expression [ [ AS ] column_alias ] [, ...]
row_expression.* [ AS ( column_alias [, ...] ) ]
relation.*
*

  • expression [ [ AS ] column_alias ]語法指定一個輸出列。可選[AS] column_alias語法指定要用於輸出中欄的自訂標題名稱。

  • For row_expression.* [ AS ( column_alias [, ...] ) ] row_expression 是資料類型的任意運算式ROW。資料列的欄位定義要包含在結果中的輸出資料行。

  • 對於relation.*，的欄relation會包括在結果中。此語法不允許使用資料行別名。

  • 星號*指定所有資料行都包含在結果集中。

  • 在結果集中，欄的順序與 select 運算式的規格順序相同。如果 select 表達式返回多個列，則列順序遵循源關係或行類型表達式中使用的順序。

  • 指定欄別名後，別名會覆寫預先存在的欄或列欄位名稱。如果 select 表達式沒有列名，則輸出中將顯示零索引的匿名列名稱（_col0_col1，，_col2, ...）。

  • 預設值為 ALL。ALL 使用與省略不用都一樣；所有欄的所有列都會選取，重複的列也會保留。

  • 當欄包含重複值時，可使用 DISTINCT 只傳回獨特值。

FROM from_item [, ...]

指出查詢的輸入，其中 from_item 可以是檢視、聯結建構或子查詢，如下所示。

from_item 可以是以下任一項：

  • table_name [ [ AS ] alias [ (column_alias [, ...]) ] ]

    其中，table_name 是從中選取列的目標表格的名稱，alias 是提供給 SELECT 陳述式輸出的名稱，column_alias 為指定的 alias 定義欄。

-或-

  • join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

    其中 join_type 是以下其中一個：

    • [ INNER ] JOIN

    • LEFT [ OUTER ] JOIN

    • RIGHT [ OUTER ] JOIN

    • FULL [ OUTER ] JOIN

    • CROSS JOIN

    • ON join_condition | USING (join_column [, ...]) 其中，使用 join_condition 可讓您指定多個資料表中聯結索引鍵的欄名稱，而使用 join_column 之前，兩個資料表中必須都有 join_column。

[ WHERE condition ]

根據您指定的 condition 篩選結果，其中 condition 通常具有以下語法。

column_name operator value [[[AND | OR] column_name operator value] ...]

operator 可以是其中一個比較子：=、>、<、>=、<=、<>、!=。

下面的子查詢表達式也可以用在 WHERE 子句中。

  • [NOT] BETWEEN integer_A AND integer_B – 指定兩個整數之間的範圍，如下列範例所示。如果資料欄資料類型是 varchar，則必須先將資料欄轉換為整數。

    SELECT DISTINCT processid FROM "webdata"."impressions"
WHERE cast(processid as int) BETWEEN 1500 and 1800
ORDER BY processid

  • [NOT] LIKE value – 搜尋指定的模式。使用百分比符號 (%) 作為萬用字元，如下列範例所示。

    SELECT * FROM "webdata"."impressions"
WHERE referrer LIKE '%.org'

  • [NOT] IN (value[, value[, ...]) – 指定資料欄可能的值之清單，如下列範例所示。

    SELECT * FROM "webdata"."impressions"
WHERE referrer IN ('example.com','example.net','example.org')
[ GROUP BY [ ALL | DISTINCT ] grouping_expressions [, ...] ]

將 SELECT 陳述式的輸出分割成具有相符值的列。

ALL 和 DISTINCT 指定重複的分組集是否各產生相異的輸出列。如果省略，會採用 ALL。

grouping_expressions 可讓您執行複雜的分組操作。您可以使用複雜的分組操作來執行需要對單一查詢中的多組資料欄進行彙總的分析。

grouping_expressions 元素可以是任何函數，例如對輸入資料欄執行的 SUM、AVG 或 COUNT。

GROUP BY 表達式可依未出現在 SELECT 陳述式輸出中的輸入欄名稱，將輸出分組。

所有輸出表達式必須是彙總函數，或存在於 GROUP BY 子句中的欄。

您可以使用單一查詢來執行需要彙總多個欄集的分析。

Athena 支援使用 GROUPING SETS、CUBE 和 ROLLUP 的複雜彙總。GROUP BY GROUPING SETS 指定要分組的多個資料欄清單。GROUP BY CUBE 會針對一組給定的資料欄，產生所有可能的分組集。GROUP BY ROLLUP 會針對一組給定的資料欄，產生所有可能的小計。複雜的分組操作不支援對由輸入資料欄組成的表達式進行分組。只允許資料欄名稱。

您通常可以使用 UNION ALL 來達到與這些 GROUP BY 操作相同的結果，但使用 GROUP BY 的查詢好處是只讀取資料一次，而 UNION ALL 會讀取基礎資料三次，而且當資料來源易於變更時，可能會產生不一致的結果。

[ HAVING condition ]

與彙總函數和 GROUP BY 子句一起使用。控制要選取哪些群組，排除不符合 condition 的群組。計算群組和彙總之後會執行此篩選。

[ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] union_query] ]

UNION、INTERSECT 和 EXCEPT 將一個以上的 SELECT 陳述式的結果結合為單一查詢。ALL 或 DISTINCT 控制最終結果集包含的資料行的唯一性。

UNION 將第一個查詢產生的資料行與第二個查詢產生的資料行組合起來。為消除重複項，UNION 構建了一個消耗記憶體的雜湊資料表。為了更好的性能，如果您的查詢不需要消除重複項，則請考慮使用 UNION ALL。除非您使用括號來明確定義處理順序，否則多個 UNION 子句是由左向右處理。

INTERSECT 只傳回存在於第一個和第二個查詢之結果中的資料行。

EXCEPT 傳回第一個查詢之結果的資料行，不包括第二個查詢找到的資料行。

ALL 會包含所有資料行，即使資料行都相同也一樣。

DISTINCT 只會讓唯一的資料行包含在合併結果集中。

[ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST] [, ...] ]

依一或多個輸出 expression 排序結果集。

當子句包含多個表達式時，結果集會根據第一個 expression 排序。然後，第二個 expression 套用的列有來自第一個表達式的相符值，以此類推。

每個 expression 可以指定 SELECT 的輸出欄，或依位置指定輸出欄的序數 (從 1 開始)。

ORDER BY 是在任何 GROUP BY 或 HAVING 子句之後當作最後一個步驟來評估。ASC 和 DESC 決定結果依遞增或遞減順序來排序。

無論是遞增或遞減排序，預設空值排序是 NULLS LAST。

[ OFFSET count [ ROW | ROWS ] ]

使用 OFFSET 子句來捨棄結果集中的數個前導列。如果 ORDER BY 子句存在時，OFFSET 子句會根據排序的結果集進行評估，並且該集會在跳過的資料列遭到捨棄後保持排序。如果查詢沒有 ORDER BY 子句，則可以任意捨棄資料列。如果 OFFSET 指定的計數等於或超過結果集的大小，則最終結果為空白。

LIMIT [ count | ALL ]

將結果集的列數限制為 count。LIMIT ALL 和省略 LIMIT 子句的效果相同。如果查詢沒有 ORDER BY 子句，則結果是任意順序。

TABLESAMPLE [ BERNOULLI | SYSTEM ] (percentage)

根據取樣方法從資料表中選擇列的選用運算子。

BERNOULLI 以機率 percentage 選取要放入資料表樣本中的每一列。將會掃描資料表的所有實體區塊，並根據範本 percentage 和執行時間計算的隨機值之間的比較，略過特定的資料行。

使用 SYSTEM 時，資料表會分割為邏輯的資料區段，並以此精細程式來取樣資料表。

將會選取特定區段的所有資料行，或根據取樣 percentage 和執行時間計算的隨機值之間的比較，略過區段。SYSTEM 取樣依賴連接器。這個方法不保證獨立取樣機率。

[ UNNEST (array_or_map) [WITH ORDINALITY] ]

將陣列或映射展開成關係。陣列會展開成單一欄。映射會展開成兩欄 (索引鍵、值)。

您可以使用 UNNEST 搭配多個參數，以展開成多個欄，且列數與最高基數引數相等。

其他欄以空值填補。

WITH ORDINALITY 子句會將基數欄新增至尾端。

UNNEST 通常與 JOIN 一起使用，且可以參考 JOIN 左側關係中的欄。

在 Amazon S3 中取得來源資料的檔案位置

若要查看資料表資料行中資料的 Amazon S3 檔案位置，可以使用 SELECT 查詢中的 "$path"，如下列範例所示：

SELECT "$path" FROM "my_database"."my_table" WHERE year=2019;

此舉會傳回如下所示的結果：

s3://DOC-EXAMPLE-BUCKET/datasets_mytable/year=2019/data_file1.json

若要傳回資料表中資料的 S3 檔案名稱路徑經排序的單一清單，可以使用 SELECT DISTINCT 和 ORDER BY，如下範例所示。

SELECT DISTINCT "$path" AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

若要僅傳回沒有路徑的檔案名稱，則可將 "$path" 作為參數傳遞給 regexp_extract 函數，如下列範例所示。

SELECT DISTINCT regexp_extract("$path", '[^/]+$') AS data_source_file
FROM sampledb.elb_logs
ORDER By data_source_file ASC

若要從特定檔案傳回資料，請在 WHERE 子句中指定檔案，如下列範例所示。

SELECT *,"$path" FROM my_database.my_table WHERE "$path" = 's3://DOC-EXAMPLE-BUCKET/my_table/my_partition/file-01.csv'

如需詳細資訊和範例，請參閱知識中心文章：如何查看 Athena 資料表中某資料行的 Amazon S3 來源檔案？。

注意

在 Athena 中，檢視不支援 Hive 或 Iceberg 隱藏的中繼資料欄 $bucket、$file_modified_time、$file_size 和 $partition。

逸出單引號

若要逸出單引號，請在它前面加上另一個單引號，如下列範例所示。請勿將此與雙引號混淆。

Select 'O''Reilly'
結果

O'Reilly

其他資源

如需有關使用 Athena 中 SELECT 陳述式的詳細資訊，請參閱以下資源。

如需有關以下內容的資訊 參閱以下資源
在 Athena 中執行查詢 使用 Amazon Athena 執行 SQL 查詢
使用 SELECT 建立資料表 從查詢結果建立資料表 (CTAS)
將一個 SELECT 查詢的資料插入其他資料表 INSERT INTO
使用 SELECT 陳述式中內建的函數 Amazon Athena 中的函數
使用 SELECT 陳述式中使用者定義函數 利用使用者定義函數來查詢
查詢資料目錄中繼資料 查詢 AWS Glue Data Catalog