Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 Python UDF 將繼續正常運作至 2026 年 6 月 30 日。如需詳細資訊，請參閱[部落格文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。 本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。 # SELECT 從資料表、檢視及使用者定義的函數中刪除資料列。 **注意** 單一 SQL 陳述式的大小上限為 16 MB。 ## 語法 ``` [ WITH with_subquery [, ...] ] SELECT [ TOP number | [ ALL | DISTINCT ] * | expression [ AS output_name ] [, ...] ] [ EXCLUDE column_list ] [ FROM table_reference [, ...] ] [ WHERE condition ] [ [ START WITH expression ] CONNECT BY expression ] [ GROUP BY ALL | expression [, ...] ] [ HAVING condition ] [ QUALIFY condition ] [ { UNION | ALL | INTERSECT | EXCEPT | MINUS } query ] [ ORDER BY expression [ ASC | DESC ] ] [ LIMIT { number | ALL } ] [ OFFSET start ] ``` **Topics** + [語法](#r_SELECT_synopsis-synopsis) + [WITH 子句](r_WITH_clause.md) + [SELECT 清單](r_SELECT_list.md) + [EXCLUDE column\$1list](r_EXCLUDE_list.md) + [FROM 子句](r_FROM_clause30.md) + [WHERE 子句](r_WHERE_clause.md) + [GROUP BY 子句](r_GROUP_BY_clause.md) + [HAVING 子句](r_HAVING_clause.md) + [QUALIFY 子句](r_QUALIFY_clause.md) + [UNION、INTERSECT 和 EXCEPT](r_UNION.md) + [ORDER BY 子句](r_ORDER_BY_clause.md) + [CONNECT BY 子句](r_CONNECT_BY_clause.md) + [子查詢範例](r_Subquery_examples.md) + [相互關聯子查詢](r_correlated_subqueries.md) # WITH 子句 WITH 子句是選用的子句，位於查詢中的 SELECT 前面。WITH 子句會定義一個或多個 *common\$1table\$1expressions*。每個通用資料表運算式 (CTE) 都會定義一個暫存資料表，與檢視定義類似。您可以在 FROM 子句中參考這些暫存資料表。這些資料表僅會在其所屬的查詢執行時使用。WITH 子句中的每個 CTE 都會指定資料表名稱、選用的資料欄名稱清單，以及判斷值為資料表的查詢表達式 (SELECT 陳述式)。當您在定義暫存資料表的相同查詢運算式的 FROM 子句中參考暫存資料表名稱時，CTE 是遞迴的。 WITH 子句子查詢是定義資料表時較有效率的方式，可在執行單一查詢的過程中使用。在所有任何情況下，於 SELECT 陳述式的本體中使用子查詢都可產生相同的結果，但 WITH 子句子查詢對於寫入和讀取來說可能較為簡單。參考多次的 WITH 子句子查詢會盡可能最佳化為通用子表達式；也就是說，或許可以評估 WITH 子查詢一次並重複使用其結果 (請注意，通用子表達式不限於 WITH 子句中所定義者)。 ## 語法 ``` [ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ] ``` 其中 *common\$1table\$1expression* 可以是非遞迴或遞迴的。以下是非遞迴形式： ``` CTE_table_name [ ( column_name [, ...] ) ] AS ( query ) ``` 以下是 *common\$1table\$1expression* 的遞迴形式： ``` CTE_table_name (column_name [, ...] ) AS ( recursive_query ) ``` ## Parameters RECURSIVE 將查詢識別為遞迴 CTE 的關鍵字。如果 WITH 子句中定義的 *common\$1table\$1expression* 是遞迴的，則需要此關鍵字。即使 WITH 子句包含多個遞迴 CTE，您也只能指定一次 RECURSIVE 關鍵字 (緊接在 WITH 關鍵字之後)。一般而言，遞迴 CTE 是具有兩個部分的 UNION ALL 子查詢。 *common\$1table\$1expression* 定義可在 [FROM 子句](r_FROM_clause30.md) 中參照的暫存資料表，且僅在執行該資料表所屬的查詢期間使用此暫存資料表。 *CTE\$1table\$1name* 此臨時資料表的唯一名稱會定義 WITH 子句子查詢的結果。您無法在單一 WITH 子句內使用重複的名稱。每個子查詢都必須有可在 [FROM 子句](r_FROM_clause30.md) 中參考的資料表名稱。 *column\$1name* WITH 子句子查詢的輸出資料欄名稱清單，以逗號分隔。指定的資料欄名稱數目必須等於或少於子查詢所定義的資料欄數目。對於非遞迴的 CTE 而言，*column\$1name* 子句是選擇性的。對於遞迴 CTE，*column\$1name* 清單是必要的。 *query* Amazon Redshift 支援的任何 SELECT 查詢。請參閱 [SELECT](r_SELECT_synopsis.md)。 *recursive\$1query* 由兩個 SELECT 子查詢組成的 UNION ALL 查詢： + 第一個 SELECT 子查詢沒有對相同 *CTE\$1table\$1name* 的遞迴參考。這會傳回一個結果集，也就是遞迴的初始種子。此部分稱為初始成員或種子成員。 + 第二個 SELECT 子查詢會在其 FROM 子句中參考相同的 *CTE\$1table\$1name*。這就是所謂的遞迴成員。*recursive\$1query* 包含一個 WHERE 條件來結束 *recursive\$1query*。 ## 使用須知 您可以在下列 SQL 陳述式中使用 WITH 子句： + SELECT + SELECT INTO + CREATE TABLE AS + CREATE VIEW + DECLARE + EXPLAIN + INSERT INTO...SELECT + PREPARE + 更UPDATE (在 WHERE 子句子查詢中。您無法在子查詢中定義遞迴 CTE。遞迴 CTE 必須位於 UPDATE 子句之前。) + DELETE 如果查詢的 FROM 子句包含 WITH 子句，但未參考 WITH 子句定義的任何資料表，則會忽略 WITH 子句，而查詢會照常執行。 WITH 子句子查詢定義的資料表只能在 WITH 子句開始的 SELECT 查詢範圍內參考。例如，您可以在 SELECT 清單、WHERE 子句或 HAVING 子句中，子查詢的 FROM 子句內參考這類資料表。您無法在子查詢中使用 WITH 子句，並於主查詢或其他子查詢的 FROM 子句內參考其資料表。此查詢模式會針對 WITH 子句資料表產生 `relation table_name doesn't exist` 形式的錯誤訊息。 您無法在 WITH 子句子查詢內指定另一個 WITH 子句。 您無法對 WITH 子句子查詢定義的資料表進行向前參考。例如，以下查詢會傳回錯誤訊息，因為資料表 W1 的定義中有對資料表 W2 的向前參考： ``` with w1 as (select * from w2), w2 as (select * from w1) select * from sales; ERROR: relation "w2" does not exist ``` WITH 子句子查詢不一定包含 SELECT INTO 陳述式；不過您可以在 SELECT INTO 陳述式中使用 WITH 子句。 ## 遞迴一般資料表表達式 遞迴 *common table expression (CTE)* 是參考其本身的 CTE。遞迴 CTE 在查詢階層式資料時非常有用，例如顯示員工與管理者之間責任關係的組織圖。請參閱 [範例：遞迴 CTE](#r_WITH_clause-recursive-cte-example)。 另一個常見的用途是多層級材料表，像是產品由許多元件組成，而每個元件本身也包含其他元件或次要組件時。 請務必在遞迴查詢的第二個 SELECT 子查詢中加入 WHERE 子句，以限制遞迴的深度。如需範例，請參閱 [範例：遞迴 CTE](#r_WITH_clause-recursive-cte-example)。否則，會發生類似以下內容的錯誤： + `Recursive CTE out of working buffers.` + `Exceeded recursive CTE max rows limit, please add correct CTE termination predicates or change the max_recursion_rows parameter.` **注意** `max_recursion_rows` 是一個參數，可設定遞迴 CTE 可傳回的最大資料列數，以防止無限遞迴迴圈。我們建議您不要將此值變更為大於預設值的值。這樣可以防止查詢中的無限遞迴佔用叢集中過多空間的問題。 您可以指定遞迴 CTE 結果的排序順序和限制。您可以在遞迴 CTE 的最終結果中包含群組依據和相異選項。 您無法在子查詢內指定 WITH RECURSIVE 子句。*recursive\$1query* 成員不能包含排序依據或限制子句。 ## 範例 下列範例顯示包含 WITH 子句的最簡單查詢案例。名為 VENUECOPY 的 WITH 查詢會從 VENUE 資料表選取所有資料列。主查詢會接著從 VENUECOPY 選取所有資料列。VENUECOPY 資料表僅在此查詢期間存在。 ``` with venuecopy as (select * from venue) select * from venuecopy order by 1 limit 10; ``` ``` venueid | venuename | venuecity | venuestate | venueseats ---------+----------------------------+-----------------+------------+------------ 1 | Toyota Park | Bridgeview | IL | 0 2 | Columbus Crew Stadium | Columbus | OH | 0 3 | RFK Stadium | Washington | DC | 0 4 | CommunityAmerica Ballpark | Kansas City | KS | 0 5 | Gillette Stadium | Foxborough | MA | 68756 6 | New York Giants Stadium | East Rutherford | NJ | 80242 7 | BMO Field | Toronto | ON | 0 8 | The Home Depot Center | Carson | CA | 0 9 | Dick's Sporting Goods Park | Commerce City | CO | 0 v 10 | Pizza Hut Park | Frisco | TX | 0 (10 rows) ``` 下列範例顯示 WITH 子句，它會產生兩個資料表，分別名為 VENUE\$1SALES 和 TOP\$1VENUES。第二個 WITH 查詢資料表會從第一個資料表選取。接著主查詢區塊的 WHERE 子句會包含限制 TOP\$1VENUES 資料表的子查詢。 ``` with venue_sales as (select venuename, venuecity, sum(pricepaid) as venuename_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid group by venuename, venuecity), top_venues as (select venuename from venue_sales where venuename_sales > 800000) select venuename, venuecity, venuestate, sum(qtysold) as venue_qty, sum(pricepaid) as venue_sales from sales, venue, event where venue.venueid=event.venueid and event.eventid=sales.eventid and venuename in(select venuename from top_venues) group by venuename, venuecity, venuestate order by venuename; ``` ``` venuename | venuecity | venuestate | venue_qty | venue_sales ------------------------+---------------+------------+-----------+------------- August Wilson Theatre | New York City | NY | 3187 | 1032156.00 Biltmore Theatre | New York City | NY | 2629 | 828981.00 Charles Playhouse | Boston | MA | 2502 | 857031.00 Ethel Barrymore Theatre | New York City | NY | 2828 | 891172.00 Eugene O'Neill Theatre | New York City | NY | 2488 | 828950.00 Greek Theatre | Los Angeles | CA | 2445 | 838918.00 Helen Hayes Theatre | New York City | NY | 2948 | 978765.00 Hilton Theatre | New York City | NY | 2999 | 885686.00 Imperial Theatre | New York City | NY | 2702 | 877993.00 Lunt-Fontanne Theatre | New York City | NY | 3326 | 1115182.00 Majestic Theatre | New York City | NY | 2549 | 894275.00 Nederlander Theatre | New York City | NY | 2934 | 936312.00 Pasadena Playhouse | Pasadena | CA | 2739 | 820435.00 Winter Garden Theatre | New York City | NY | 2838 | 939257.00 (14 rows) ``` 以下兩個範例將示範根據 WITH 子句子查詢的資料表參考範圍規則。第一個查詢會執行，但第二個會失敗，並產生預期的錯誤。第一個查詢會在主查詢的 SELECT 清單內包含 WITH 子句子查詢。WITH 子句定義的資料表 (HOLIDAYS) 會在 SELECT 清單中子查詢的 FROM 子句中參考： ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join date on sales.dateid=date.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; caldate | daysales | dec25sales -----------+----------+------------ 2008-12-25 | 70402.00 | 70402.00 2008-12-31 | 12678.00 | 70402.00 (2 rows) ``` 第二個查詢會失敗，因為它會嘗試參考主查詢以及 SELECT 清單子查詢中的 HOLIDAYS 資料表。而主查詢參考超出範圍。 ``` select caldate, sum(pricepaid) as daysales, (with holidays as (select * from date where holiday ='t') select sum(pricepaid) from sales join holidays on sales.dateid=holidays.dateid where caldate='2008-12-25') as dec25sales from sales join holidays on sales.dateid=holidays.dateid where caldate in('2008-12-25','2008-12-31') group by caldate order by caldate; ERROR: relation "holidays" does not exist ``` ## 範例：遞迴 CTE 以下是遞迴 CTE 的範例，此範例會傳回直接或間接向 John 報告的員工。遞迴查詢包含 WHERE 子句，可將遞迴深度限制為少於 4 個層級。 ``` --create and populate the sample table create table employee ( id int, name varchar (20), manager_id int ); insert into employee(id, name, manager_id) values (100, 'Carlos', null), (101, 'John', 100), (102, 'Jorge', 101), (103, 'Kwaku', 101), (110, 'Liu', 101), (106, 'Mateo', 102), (110, 'Nikki', 103), (104, 'Paulo', 103), (105, 'Richard', 103), (120, 'Saanvi', 104), (200, 'Shirley', 104), (201, 'Sofía', 102), (205, 'Zhang', 104); --run the recursive query with recursive john_org(id, name, manager_id, level) as ( select id, name, manager_id, 1 as level from employee where name = 'John' union all select e.id, e.name, e.manager_id, level + 1 as next_level from employee e, john_org j where e.manager_id = j.id and level < 4 ) select distinct id, name, manager_id from john_org order by manager_id; ``` 以下為查詢結果。 ``` id name manager_id ------+-----------+-------------- 101 John 100 102 Jorge 101 103 Kwaku 101 110 Liu 101 201 Sofía 102 106 Mateo 102 110 Nikki 103 104 Paulo 103 105 Richard 103 120 Saanvi 104 200 Shirley 104 205 Zhang 104 ``` 以下是 John 的部門組織結構圖。 ![\[John 的部門組織結構圖。\]](http://docs.aws.amazon.com/zh_tw/redshift/latest/dg/images/org-chart.png) # SELECT 清單 **Topics** + [語法](#r_SELECT_list-synopsis) + [Parameters](#r_SELECT_list-parameters) + [使用須知](#r_SELECT_list_usage_notes) + [範例](#r_SELECT_list-examples) SELECT 清單會指出您要查詢傳回的資料欄、函數及表達式的名稱。清單查詢的輸出。 如需 SQL 函數的相關資訊，請參閱 [SQL 函數參考](c_SQL_functions.md)。如需運算式的相關資訊，請參閱[條件式運算式](c_conditional_expressions.md)。 ## 語法 ``` SELECT [ TOP number ] [ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...] ``` ## Parameters TOP *number* TOP 會採用正整數做為其引數，此引數定義傳回至用戶端的資料列數。TOP 子句的行為與 LIMIT 子句的行為相同。傳回的資料列數是固定的，但資料列集卻不是。若要傳回一致的資料列集，請搭配 ORDER BY 子句使用 TOP 或 LIMIT。 ALL 您未指定 DISTINCT 時，用來定義預設行為的備援關鍵字。`SELECT ALL *` 的意義與 `SELECT *` 相同 (選取所有資料欄的所有資料列並保留重複項目)。 DISTINCT 此選項會根據一個或多個資料欄中相符的值，從結果集中消除重複的資料列。 如果您的應用程式允許無效的外部索引鍵或主索引鍵，則可能造成查詢傳回不正確的結果。例如，如果主索引鍵欄並非包含全部是唯一的值，則 SELECT DISTINCT 查詢可能傳回重複的列。如需詳細資訊，請參閱[定義資料表限制條件](https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html)。 \$1 (星號) 傳回資料表的整個內容 (所有資料欄和所有資料列)。 *表達式* 表達式是由查詢所參考資料表中的一個或多個資料欄構成。表達式可包含 SQL 函數。例如： ``` avg(datediff(day, listtime, saletime)) ``` AS *column\$1alias* 資料欄的暫時名稱，會在最終結果集中使用。AS 關鍵字為選用。例如： ``` avg(datediff(day, listtime, saletime)) as avgwait ``` 若您沒有為表達式指定非簡單資料欄名稱的別名，結果集將會套用預設名稱至該資料欄。 別名在目標清單中定義之後立即直接辨識。您可以在相同目標清單中後續定義的其他表達式後面使用別名。下列的範例示範了這一點。 ``` select clicks / impressions as probability, round(100 * probability, 1) as percentage from raw_data; ``` 側邊別名參考的好處在於，您在相同目標清單中建構更複雜的表達式時，不需要重複有別名的表達式。當 Amazon Redshift 剖析此類型參考時，會直接內嵌先前定義的別名。若 `FROM` 子句中定義了與先前具有別名之表達式同名的資料欄，則 `FROM` 子句中的資料欄優先順序較高。例如，在上方查詢中，若 raw\$1data 資料表中有名為 'probability' 的資料欄，目標清單中第二個表達式內的 'probability' 會參考該資料欄，而不是 'probability' 這個別名。 ## 使用須知 TOP 是 SQL 延伸模組；它提供了 LIMIT 行為的替代方式。您無法在相同查詢中同時使用 TOP 和 LIMIT。 ## 範例 下列範例會從 SALES 資料表中傳回 10 個資料列。雖然查詢使用 TOP 子句，但仍然會傳回無法預測的資料列集，因為沒有指定 ORDER BY 子句。 ``` select top 10 * from sales; ``` 下列查詢具同等功能，但使用 LIMIT 子句而非 TOP 子句： ``` select * from sales limit 10; ``` 下列範例會使用 TOP 子句從 SALES 資料表傳回前 10 列，並依 QTYSOLD 資料欄遞減排序。 ``` select top 10 qtysold, sellerid from sales order by qtysold desc, sellerid; qtysold | sellerid --------+---------- 8 | 518 8 | 520 8 | 574 8 | 718 8 | 868 8 | 2663 8 | 3396 8 | 3726 8 | 5250 8 | 6216 (10 rows) ``` 下列範例會從 SALES 資料表傳回前兩個 QTYSOLD 和 SELLERID 值，並依 QTYSOLD 資料欄排序： ``` select top 2 qtysold, sellerid from sales order by qtysold desc, sellerid; qtysold | sellerid --------+---------- 8 | 518 8 | 520 (2 rows) ``` 下列範例顯示 CATEGORY 資料表中不同類別群組的清單： ``` select distinct catgroup from category order by 1; catgroup ---------- Concerts Shows Sports (3 rows) --the same query, run without distinct select catgroup from category order by 1; catgroup ---------- Concerts Concerts Concerts Shows Shows Shows Sports Sports Sports Sports Sports (11 rows) ``` 下列範例會傳回 2008 年 12 月的不同週數組。如果沒有 DISTINCT 子句，陳述式會傳回 31 個資料列，或是針對每月的每一天傳回 1 列。 ``` select distinct week, month, year from date where month='DEC' and year=2008 order by 1, 2, 3; week | month | year -----+-------+------ 49 | DEC | 2008 50 | DEC | 2008 51 | DEC | 2008 52 | DEC | 2008 53 | DEC | 2008 (5 rows) ``` # EXCLUDE column\$1list EXCLUDE column\$1list 會為從查詢結果中排除的欄命名。在只需從*很寬* (也就是包含許多欄的資料表) 的資料表中排除一小部分欄時，使用 EXCLUDE 選項會很有幫助。 **Topics** + [語法](#r_EXCLUDE_list-synopsis) + [Parameters](#r_EXCLUDE_list-parameters) + [範例](#r_EXCLUDE_list-examples) ## 語法 ``` EXCLUDE column_list ``` ## Parameters *column\$1list* 逗號分隔的一或多個欄名稱清單，在資料表中供查詢參考。*column\$1list* 可以選擇性地在前後加上括號。在排除欄名稱清單中僅支援欄名稱，不支援表達式 (例如 `upper(col1)`) 或星號 (\$1)。 ``` column-name, ... | ( column-name, ... ) ``` 例如： ``` SELECT * EXCLUDE col1, col2 FROM tablea; ``` ``` SELECT * EXCLUDE (col1, col2) FROM tablea; ``` ## 範例 下列範例使用的 SALES 資料表包含以下各欄：salesid、listid、sellerid、buyerid、eventid、dateid、qtysold、pricepaid、commission 和 saletime。如需 SALES 資料表的詳細資訊，請參閱 [範本資料庫](c_sampledb.md)。 下列範例會從 SALES 資料表中傳回列，但會排除 SALETIME 欄。 ``` SELECT * EXCLUDE saletime FROM sales; salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission --------+---------+----------+---------+---------+---------+----------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3 ... ``` 下列範例會從 SALES 資料表中傳回列，但會排除 QTYSOLD 和 SALETIME 欄。 ``` SELECT * EXCLUDE (qtysold, saletime) FROM sales; salesid | listid | sellerid | buyerid | eventid | dateid | pricepaid | commission --------+---------+----------+---------+---------+---------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 482 | 72.3 ... ``` 下列範例會建立一個檢視，以從 SALES 資料表中傳回列，但會排除 SALETIME 欄。 ``` CREATE VIEW sales_view AS SELECT * EXCLUDE saletime FROM sales; SELECT * FROM sales_view; salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission --------+---------+----------+---------+---------+---------+----------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3 ... ``` 下列範例只會選取未排除的欄並放入暫存資料表中。 ``` SELECT * EXCLUDE saletime INTO TEMP temp_sales FROM sales; SELECT * FROM temp_sales; salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission --------+---------+----------+---------+---------+---------+----------+------------+----------- 150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2 8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75 46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3 ... ``` # FROM 子句 查詢中的 FROM 子句列出資料表參考 (資料表、檢視和子查詢)，此為選取資料的來源位置。若列出多個資料表參考，則必須在 FROM 子句或 WHERE 子句中使用適當的語法聯結資料表。若未指定聯結條件，則系統會將查詢當做交叉聯結 (笛卡兒乘積) 處理。 **Topics** + [語法](#r_FROM_clause30-synopsis) + [Parameters](#r_FROM_clause30-parameters) + [使用須知](#r_FROM_clause_usage_notes) + [PIVOT 和 UNPIVOT 範例](r_FROM_clause-pivot-unpivot-examples.md) + [JOIN 範例](r_Join_examples.md) + [UNNEST 範例](r_FROM_clause-unnest-examples.md) ## 語法 ``` FROM table_reference [, ...] ``` 其中 *table\$1reference* 是下列其中一項： ``` with_subquery_table_name [ table_alias ] table_name [ * ] [ table_alias ] ( subquery ) [ table_alias ] table_reference [ NATURAL ] join_type table_reference [ ON join_condition | USING ( join_column [, ...] ) ] table_reference join_type super_expression [ ON join_condition ] table_reference PIVOT ( aggregate(expr) [ [ AS ] aggregate_alias ] FOR column_name IN ( expression [ AS ] in_alias [, ...] ) ) [ table_alias ] table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] ( value_column_name FOR name_column_name IN ( column_reference [ [ AS ] in_alias ] [, ...] ) ) [ table_alias ] UNPIVOT expression AS value_alias [ AT attribute_alias ] ( super_expression.attribute_name ) AS value_alias [ AT index_alias ] UNNEST ( column_reference ) [AS] table_alias ( unnested_column_name ) UNNEST ( column_reference ) WITH OFFSET [AS] table_alias ( unnested_column_name, [offset_column_name] ) ``` 選用的 *table\$1alias* 可用來為資料表和複雜資料表參考提供暫時的名稱，如有需要，也可以提供其欄的名稱，如下所示： ``` [ AS ] alias [ ( column_alias [, ...] ) ] ``` ## Parameters *with\$1subquery\$1table\$1name* [WITH 子句](r_WITH_clause.md) 中子查詢所定義的資料表。 *table\$1name* 資料表或檢視的名稱。 *alias* 資料表或檢視的暫時替代名稱。必須為衍生自子查詢的資料表提供別名。在其他資料表參考中，別名是選用的。AS 關鍵字一律為選用。資料表別名提供了方便在查詢的其他部分中識別資料表的捷徑，例如 WHERE 子句。例如： ``` select * from sales s, listing l where s.listid=l.listid ``` *column\$1alias* 資料表或檢視中資料欄的暫時替代名稱。 *subquery* 判斷值為資料表的查詢表達式。資料表只會在查詢期間存在，通常會為其命名或提供*別名*。不過，不需要別名。您也可以為衍生自子查詢的資料表定義資料欄名稱。當您想要將子查詢的結果與其他資料表聯結時，以及您想要在查詢中的其他位置選取或限制這些資料欄時，為資料欄指定別名就很重要。 子查詢可包含 ORDER BY 子句，但是，若未指定 LIMIT 或 OFFSET 子句，則此子句不一定有作用。 NATURAL 定義聯結，此聯結會自動使用兩個資料表中所有同名資料欄的配對做為聯結資料欄。不需要明確的聯結條件。例如，若 CATEGORY 和 EVENT 資料表都有名為 CATID 的資料欄，則這兩個資料表的 natural 聯結會是透過其 CATID 資料欄的聯結。 若已指定 NATURAL 聯結，但是要聯結的資料表中並沒有同名的資料欄配對，則查詢會預設為交叉聯結。 *join\$1type* 指定下列其中一種聯結類型： + [INNER] JOIN + LEFT [OUTER] JOIN + RIGHT [OUTER] JOIN + FULL [OUTER] JOIN + CROSS JOIN 交叉聯結是沒有限定的聯結，會傳回兩個資料表的笛卡兒乘積。 內部和外部聯結為限定聯結。它們會以隱含方式 (在 natural 聯結中)、在 FROM 子句中使用 ON 或 USING 語法，或使用 WHERE 子句條件限定。 內部聯結只會根據聯結條件或聯結資料欄清單傳回相符的資料列。外部聯結會傳回對等內部聯結傳回的所有資料列，加上「左側」資料表和/或「右側」資料表的不相符資料列。左側資料表是最先列出的資料表，右側資料表是其次列出的資料表。不相符的資料列包含要填入輸出資料欄中空處的 NULL 值。 ON *join\$1condition* 聯結規格的類型，其中聯結資料欄會做為條件陳述，後面接著 ON 關鍵字。例如： ``` sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid ``` USING ( *join\$1column* [, ...] ) 聯結規格的類型，其中聯結資料欄會在括號內列出。若指定了多個聯結資料欄，則會以逗號分隔。USING 關鍵字必須放在清單前面。例如： ``` sales join listing using (listid,eventid) ``` PIVOT 將輸出從資料列旋轉為資料欄，以便以易於閱讀的格式表示資料表資料。輸出會橫跨多個資料欄表示。PIVOT 類似於具使用彙總的 GROUP BY 查詢，並且會使用彙總運算式來指定輸出格式。但是，與 GROUP BY 相反，其結果會以資料欄傳回，而不是資料列。 如需示範如何使用 PIVOT 和 UNPIVOT 進行查詢的範例，請參閱 [PIVOT 和 UNPIVOT 範例](r_FROM_clause-pivot-unpivot-examples.md)。 UNPIVOT *使用 UNPIVOT 將欄輪換到列中* - 運算子會將輸入資料表或查詢結果的結果欄轉換為列，讓輸出內容更容易閱讀。UNPIVOT 會將其輸入資料欄的資料合併為兩個結果資料欄：名稱資料欄和值資料欄。名稱資料欄包含來自輸入中做為資料列項目的資料欄名稱。值資料欄包含來自輸入資料欄的值，例如彙總的結果。例如，各種類別中的項目計數。 *使用 UNPIVOT (SUPER) 取消樞紐的物件* - 您可以執行物件取消樞紐，其中 *expression* 是參考另一個 FROM 子句項目的 SUPER 表達式。如需詳細資訊，請參閱[物件取消樞紐](query-super.md#unpivoting)。另外也有範例示範如何查詢半結構化資料，例如 JSON 格式的資料。 *super\$1expression* 有效的 SUPER 表達式。Amazon Redshift 會針對指定的屬性中的每個值傳回一列。如需 SUPER 資料類型的詳細資訊，請參閱 [SUPER 類型](r_SUPER_type.md)。如需非巢狀 SUPER 值的詳細資訊，請參閱 [解除巢狀化查詢](query-super.md#unnest)。 *屬性\$1名稱* SUPER 表達式中屬性的名稱。 *index\$1alias* 索引的別名，表示 SUPER 表達式中值的位置。 UNNEST 展開巢狀結構 (通常是 SUPER 陣列) 成為包含非巢狀元素的欄。如需解除巢狀化 SUPER 資料的詳細資訊，請參閱 [查詢半結構化資料](query-super.md)。如需範例，請參閱 [UNNEST 範例](r_FROM_clause-unnest-examples.md)。 *unnested\$1column\$1name* 包含非巢狀元素的欄名稱。 UNNEST ... WITH OFFSET 將位移欄新增至非巢狀輸出，其位移代表陣列中每個元素從零開始的索引。當您想要查看陣列內元素的位置時，此變體會很有用。如需解除巢狀化 SUPER 資料的詳細資訊，請參閱 [查詢半結構化資料](query-super.md)。如需範例，請參閱 [UNNEST 範例](r_FROM_clause-unnest-examples.md)。 *offset\$1column\$1name* 位移欄的自訂名稱，可讓您明確定義索引欄在輸出中的顯示方式。此為選用參數。根據預設，位移欄名稱為 `offset_col`。 ## 使用須知 聯結資料欄必須採用可比較的資料類型。 NATURAL 或 USING 聯結只會針對中繼結果集內每個聯結資料欄配對保留一個。 使用 ON 語法的聯結則會保留其中繼結果集內的兩個聯結資料欄。 另請參閱[WITH 子句](r_WITH_clause.md)。 # PIVOT 和 UNPIVOT 範例 PIVOT 和 UNPIVOT 是 FROM 子句中的參數，這兩個參數會分別將查詢輸出從資料列旋轉為資料欄，以及將資料欄旋轉為資料列。這會以易於閱讀的格式呈現資料表式查詢結果。下列範例會使用測試資料和查詢來顯示如何使用這些參數。 如需這些參數與其他參數的相關資訊，請參閱 [FROM 子句](https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html)。 ## PIVOT 範例 設定範例資料表和資料，並用其來執行後續的範例查詢。 ``` CREATE TABLE part ( partname varchar, manufacturer varchar, quality int, price decimal(12, 2) ); INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00); INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00); INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00); INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50); INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75); INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90); INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50); INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20); INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80); ``` `partname` 上的 PIVOT 搭配 `price` 上的 `AVG` 彙總。 ``` SELECT * FROM (SELECT partname, price FROM part) PIVOT ( AVG(price) FOR partname IN ('prop', 'rudder', 'wing') ); ``` 此查詢結果為下列輸出。 ``` prop | rudder | wing ---------+----------+--------- 10.33 | 2.71 | 11.50 ``` 在上一個範例中，結果會轉換為資料欄。下列範例會顯示以資料列 (不是資料欄) 傳回平均價格的 `GROUP BY` 查詢。 ``` SELECT partname, avg(price) FROM (SELECT partname, price FROM part) WHERE partname IN ('prop', 'rudder', 'wing') GROUP BY partname; ``` 此查詢結果為下列輸出。 ``` partname | avg ----------+------- prop | 10.33 rudder | 2.71 wing | 11.50 ``` 以 `manufacturer` 作為隱含資料欄的 `PIVOT` 範例。 ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) FOR quality IN (1, 2, NULL) ); ``` 此查詢結果為下列輸出。 ``` manufacturer | 1 | 2 | null -------------------+----+----+------ local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2 ``` 未在 `PIVOT` 定義中參考的輸入資料表資料欄會隱含地新增至結果資料表。上一個範例中的 `manufacturer` 資料欄就是這種情況。此範例也會顯示 `NULL` 是 `IN` 運算子的有效值。 `PIVOT` 在上述範例中會傳回類似下列查詢的資訊，其中包括 `GROUP BY`。不同之處在於 `PIVOT` 傳回資料欄 `2` 的值 `0` 和製造商 `small parts co`。`GROUP BY` 查詢不包含對應的資料列。在大多數情況下，如果資料列沒有給定資料欄的輸入資料，`PIVOT` 會插入 `NULL`。但是，計數彙總不會傳回 `NULL`，`0` 是預設值。 ``` SELECT manufacturer, quality, count(*) FROM (SELECT quality, manufacturer FROM part) WHERE quality IN (1, 2) OR quality IS NULL GROUP BY manufacturer, quality ORDER BY manufacturer; ``` 此查詢結果為下列輸出。 ``` manufacturer | quality | count ---------------------+---------+------- big parts co | | 1 big parts co | 2 | 1 big parts co | 1 | 1 local parts co | 2 | 1 local parts co | 1 | 1 local parts co | | 1 small parts co | 1 | 1 small parts co | | 2 ``` PIVOT 運算子會接受彙總運算式上及 `IN` 運算子每個值上的選用別名。使用別名來自訂資料欄名稱。如果沒有彙總別名，則只會使用 `IN` 清單別名。否則，彙總別名會附加至資料欄名稱，並加上底線來分隔名稱。 ``` SELECT * FROM (SELECT quality, manufacturer FROM part) PIVOT ( count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na) ); ``` 此查詢結果為下列輸出。 ``` manufacturer | high_count | low_count | na_count -------------------+-------------+-----------+---------- local parts co | 1 | 1 | 1 big parts co | 1 | 1 | 1 small parts co | 1 | 0 | 2 ``` 設定下列範例資料表和資料，並用其來執行後續的範例查詢。資料表示飯店集合的預訂日期。 ``` CREATE TABLE bookings ( booking_id int, hotel_code char(8), booking_date date, price decimal(12, 2) ); INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12); INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00); INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54); INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00); INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00); INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00); INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00); INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00); INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00); INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00); INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00); INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00); INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00); INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00); INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00); INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00); INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00); INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00); INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00); INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00); INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00); INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00); INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00); INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00); INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00); INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00); INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00); INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00); INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00); INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00); INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00); INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00); INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00); INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00); INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00); INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00); ``` 在此查詢範例中，預訂記錄會計算為每週的總數。每週的結束日期會變成資料欄名稱。 ``` SELECT * FROM (SELECT booking_id, (date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate, hotel_code AS "hotel code" FROM bookings ) PIVOT ( count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18') ); ``` 此查詢結果為下列輸出。 ``` hotel code | 2023-02-04 | 2023-02-11 | 2023-02-18 ------------+-------------+------------+---------- FOREST_L | 3 | 2 | 1 DESERT_S | 4 | 3 | 2 OCEAN_WV | 3 | 3 | 3 CITY_BLD | 3 | 1 | 2 ``` Amazon Redshift 不支援使用 CROSSTAB 在多個資料欄上轉移。但是，您可以使用類似於 PIVOT 的彙總方式將資料列資料變更為資料欄，並使用類似於以下內容的查詢。這會使用與前一個範例相同的預訂範例資料。 ``` SELECT booking_date, MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L, MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S, MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END) AS OCEAN_WV FROM bookings GROUP BY booking_date ORDER BY booking_date asc; ``` 範例查詢結果會顯示預訂日期，並列在指出已預訂飯店的短語旁邊。 ``` booking_date | forest_l | desert_s | ocean_wv ---------------+------------------+------------------+-------------------- 2023-02-01 | forest is booked | desert is booked | ocean is booked 2023-02-02 | forest is booked | desert is booked | ocean is booked 2023-02-04 | forest is booked | desert is booked | ocean is booked 2023-02-05 | | desert is booked | 2023-02-06 | | desert is booked | ``` 以下是 `PIVOT` 的使用須知： + `PIVOT` 可套用至資料表、子查詢和通用資料表運算式 (CTE)。`PIVOT` 無法套用至任何 `JOIN` 運算式、遞迴 CTE、`PIVOT` 或 `UNPIVOT` 運算式。`SUPER` 非巢狀運算式和 Redshift Spectrum 巢狀資料表也不支援。 + `PIVOT` 支援 `COUNT`、`SUM`、`MIN`、`MAX` 和 `AVG` 彙總函數。 + `PIVOT` 彙總運算式必須是受支援彙總函式的呼叫。不支援彙總頂端的複雜運算式。彙總引數不能包含對 `PIVOT` 輸入資料表以外資料表的參考。也不支援父查詢的相關參考。彙總參數可以包含子查詢。這些可以在內部或 `PIVOT` 輸入資料表上相互關聯。 + `PIVOT IN` 清單值不能是資料欄參考或子查詢。每個值必須是與 `FOR` 資料欄參考相容的類型。 + 如果 `IN` 清單值沒有別名，則 `PIVOT` 會產生預設資料欄名稱。對於常數 `IN` 值，如 'abc' 或 5，預設資料欄名稱是常值本身。對於任何複雜的運算式，資料欄名稱都是標準的 Amazon Redshift 預設名稱，例如 `?column?`. ## UNPIVOT 範例 設定範例資料，並用其來執行後續的範例。 ``` CREATE TABLE count_by_color (quality varchar, red int, green int, blue int); INSERT INTO count_by_color VALUES ('high', 15, 20, 7); INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40); INSERT INTO count_by_color VALUES ('low', 10, 23, NULL); ``` `UNPIVOT` 在輸入欄紅色、綠色和藍色上。 ``` SELECT * FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` 此查詢結果為下列輸出。 ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | 23 blue | 7 blue | 40 ``` 依預設，輸入資料欄中的 `NULL` 值會略過，且不會產生結果資料列。 下列範例會顯示包括 `INCLUDE NULLS` 的 `UNPIVOT`。 ``` SELECT * FROM ( SELECT red, green, blue FROM count_by_color ) UNPIVOT INCLUDE NULLS ( cnt FOR color IN (red, green, blue) ); ``` 以下為其輸出。 ``` color | cnt -------+----- red | 15 red | 35 red | 10 green | 20 green | green | 23 blue | 7 blue | 40 blue | ``` 如果設定 `INCLUDING NULLS` 參數，`NULL` 輸入值會產生結果列。 以 `quality` 作為隱含資料欄的 `The following query shows UNPIVOT`。 ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red, green, blue) ); ``` 此查詢結果為下列輸出。 ``` quality | color | cnt ---------+-------+----- high | red | 15 normal | red | 35 low | red | 10 high | green | 20 low | green | 23 high | blue | 7 normal | blue | 40 ``` 未在 `UNPIVOT` 定義中參考的輸入資料表資料欄會隱含地新增至結果資料表。在範例中，`quality` 資料欄就是這種情況。 下列範例顯示 `IN` 清單中具有值別名的 `UNPIVOT`。 ``` SELECT * FROM count_by_color UNPIVOT ( cnt FOR color IN (red AS r, green AS g, blue AS b) ); ``` 上述查詢結果為下列輸出。 ``` quality | color | cnt ---------+-------+----- high | r | 15 normal | r | 35 low | r | 10 high | g | 20 low | g | 23 high | b | 7 normal | b | 40 ``` `UNPIVOT` 運算仔會接受每個 `IN` 清單值上的選用別名。每個別名都會提供每個 `value` 資料欄中的資料定義。 以下是 `UNPIVOT` 的使用須知。 + `UNPIVOT` 可套用至資料表、子查詢和通用資料表運算式 (CTE)。`UNPIVOT` 無法套用至任何 `JOIN` 運算式、遞迴 CTE、`PIVOT` 或 `UNPIVOT` 運算式。`SUPER` 非巢狀運算式和 Redshift Spectrum 巢狀資料表也不支援。 + `UNPIVOT IN` 清單必須只包含輸入資料表資料欄參考。`IN` 清單欄必須具有與之相容的通用類型。`UNPIVOT` 值資料欄具有這種通用類型。`UNPIVOT` 名稱資料欄的類型為 `VARCHAR`。 + 如果 `IN` 清單值沒有別名，`UNPIVOT` 會使用資料欄名稱做為預設值。 # JOIN 範例 SQL JOIN 子句用於根據通用欄位，結合兩個或多個資料表中的資料。結果可能會或可能不會改變，具體取決於指定的聯結方法。如需 JOIN 子句語法的相關資訊，請參閱 [Parameters](r_FROM_clause30.md#r_FROM_clause30-parameters)。 下列範例會使用 `TICKIT` 範例資料中的資料。如需資料庫結構描述的相關資訊，請參閱 [範本資料庫](c_sampledb.md)。若要了解如何載入範例資料，請參閱《Amazon Redshift 入門指南》**中的[載入資料](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html)。 下列查詢是 LISTING 資料表和 SALES 資料表之間的內部聯結 (沒有 JOIN 關鍵字)，其中 LISTING 資料表中的 LISTID 是介於 1 和 5 之間。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示 LISTID 1、4 和 5 符合條件。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing, sales where listing.listid = sales.listid and listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 下列查詢是左側外部聯結。未在另一個資料表中找到相符項目時，左和右外部聯結會保留來自其中一個聯結資料表的值。左側和右側資料表分別是語法中最先和其次列出的資料表。NULL 值會用來填入結果集中的「空處」。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示，LISTID 2 和 3 並未產生任何銷售。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing left outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 下列查詢是右側外部聯結。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示 LISTID 1、4 和 5 符合條件。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing right outer join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 下列查詢是完全聯結。未在另一個資料表中找到相符項目時，完全聯結會保留來自聯結資料表的值。左側和右側資料表分別是語法中最先和其次列出的資料表。NULL 值會用來填入結果集中的「空處」。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示，LISTID 2 和 3 並未產生任何銷售。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 2 | NULL | NULL 3 | NULL | NULL 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 下列查詢是完全聯結。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。只有不會產生任何銷售 (ListID 2 和 3) 的資料列才會顯示在結果中。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing full join sales on sales.listid = listing.listid where listing.listid between 1 and 5 and (listing.listid IS NULL or sales.listid IS NULL) group by 1 order by 1; listid | price | comm -------+--------+-------- 2 | NULL | NULL 3 | NULL | NULL ``` 下列範例是一個內部聯結搭配 ON 子句。在此情況下，不會傳回 NULL 資料列。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from sales join listing on sales.listid=listing.listid and sales.eventid=listing.eventid where listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm -------+--------+-------- 1 | 728.00 | 109.20 4 | 76.00 | 11.40 5 | 525.00 | 78.75 ``` 下列查詢是 LISTING 資料表和 SALES 資料表的交叉聯結或笛卡爾聯結，並且使用述詞來限制結果。此查詢會在 SALES 資料表和 LISTING 資料表中比對 LISTID 資料欄值，以找出兩個資料表中的 LISTID 1、2、3、4 和 5。結果顯示 20 個符合條件的資料列。 ``` select sales.listid as sales_listid, listing.listid as listing_listid from sales cross join listing where sales.listid between 1 and 5 and listing.listid between 1 and 5 order by 1,2; sales_listid | listing_listid -------------+--------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 4 | 1 4 | 2 4 | 3 4 | 4 4 | 5 5 | 1 5 | 1 5 | 2 5 | 2 5 | 3 5 | 3 5 | 4 5 | 4 5 | 5 5 | 5 ``` 下列範例是兩個資料表之間的自然聯結。在這種情況下，兩個資料表中的 listid、sellerid、eventid 和 dateid 資料欄會具有相同的名稱和資料類型，因此會被用來作為聯結資料欄。結果限制為 5 個資料列。 ``` select listid, sellerid, eventid, dateid, numtickets from listing natural join sales order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+-----------+---------+--------+----------- 113 | 29704 | 4699 | 2075 | 22 115 | 39115 | 3513 | 2062 | 14 116 | 43314 | 8675 | 1910 | 28 118 | 6079 | 1611 | 1862 | 9 163 | 24880 | 8253 | 1888 | 14 ``` 下列範例是兩個資料表之間的聯結和 USING 子句。在這種情況下，資料欄 listid 和 eventid 會被用來作為聯結資料欄。結果限制為 5 個資料列。 ``` select listid, listing.sellerid, eventid, listing.dateid, numtickets from listing join sales using (listid, eventid) order by 1 limit 5; listid | sellerid | eventid | dateid | numtickets -------+----------+---------+--------+----------- 1 | 36861 | 7872 | 1850 | 10 4 | 8117 | 4337 | 1970 | 8 5 | 1616 | 8647 | 1963 | 4 5 | 1616 | 8647 | 1963 | 4 6 | 47402 | 8240 | 2053 | 18 ``` 以下查詢為 FROM 子句中兩個子查詢的內部聯結。查詢會尋找不同類別活動 (演奏會和表演) 的已售出和未售出票券數目。FROM 子句子查詢是*資料表*子查詢，可傳回多個資料欄和資料列。 ``` select catgroup1, sold, unsold from (select catgroup, sum(qtysold) as sold from category c, event e, sales s where c.catid = e.catid and e.eventid = s.eventid group by catgroup) as a(catgroup1, sold) join (select catgroup, sum(numtickets)-sum(qtysold) as unsold from category c, event e, sales s, listing l where c.catid = e.catid and e.eventid = s.eventid and s.listid = l.listid group by catgroup) as b(catgroup2, unsold) on a.catgroup1 = b.catgroup2 order by 1; catgroup1 | sold | unsold ----------+--------+-------- Concerts | 195444 |1067199 Shows | 149905 | 817736 ``` # UNNEST 範例 UNNEST 是 FROM 子句中的參數，可將巢狀資料展開，成為包含資料的非巢狀元素的欄。如需解除巢狀化資料的相關資訊，請參閱 [查詢半結構化資料](query-super.md)。 下列陳述式會建立並填入 `orders` 資料表，其中包含的 `products` 欄內含產品 ID 的陣列。本節中的範例使用此資料表中的範例資料。 ``` CREATE TABLE orders ( order_id INT, products SUPER ); -- Populate table INSERT INTO orders VALUES (1001, JSON_PARSE('[ { "product_id": "P456", "name": "Monitor", "price": 299.99, "quantity": 1, "specs": { "size": "27 inch", "resolution": "4K" } } ] ')), (1002, JSON_PARSE(' [ { "product_id": "P567", "name": "USB Cable", "price": 9.99, "quantity": 3 }, { "product_id": "P678", "name": "Headphones", "price": 159.99, "quantity": 1, "specs": { "type": "Wireless", "battery_life": "20 hours" } } ] ')); ``` 以下範例說明如何使用 PartiQL 語法將包含範例資料的查詢解除巢狀化。 ## 將沒有 OFFSET 欄的陣列解除巢狀化 下列查詢會將產品欄中的 SUPER 陣列解除巢狀化，其中每一列代表 `order_id` 中訂單的某一個項目。 ``` SELECT o.order_id, unnested_products.product FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (3 rows) ``` 下列查詢會尋找每個訂單中最昂貴的產品。 ``` SELECT o.order_id, MAX(unnested_products.product) FROM orders o, UNNEST(o.products) AS unnested_products(product); order_id | product ----------+----------------------------------------------------------------------------------------------------------------------------- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} (2 rows) ``` ## 將具有隱含 OFFSET 欄的陣列解除巢狀化 下列查詢使用 `UNNEST ... WITH OFFSET` 參數來顯示其訂單陣列內每個產品從零開始的位置。 ``` SELECT o.order_id, up.product, up.offset_col FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product); order_id | product | offset_col ----------+-----------------------------------------------------------------------------------------------------------------------------+------------ 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ``` 由於陳述式未指定位移欄的別名，因此 Amazon Redshift 預設會將其命名為 `offset_col`。 ## 將具有明確 OFFSET 欄的陣列解除巢狀化 下列查詢同樣使用 `UNNEST ... WITH OFFSET` 參數來顯示其訂單陣列內的產品。相較於先前範例中的查詢，此查詢的差異在於其使用別名 `idx` 明確命名位移欄。 ``` SELECT o.order_id, up.product, up.idx FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product, idx); order_id | product | idx ----------+-----------------------------------------------------------------------------------------------------------------------------+----- 1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0 1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0 1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1 (3 rows) ``` # WHERE 子句 WHERE 子句包含聯結資料表或套用述詞至資料表中資料欄的條件。資料表可以藉由在 WHERE 子句或 FROM 子句中使用適當的語法進行內部聯結。外部連結條件必須在 FROM 子句中指定。 ## 語法 ``` [ WHERE condition ] ``` ## *條件* 任何產生布林值結果的搜尋條件，例如，資料表資料欄的聯結條件或述詞。以下範例為有效的聯結條件： ``` sales.listid=listing.listid sales.listid<>listing.listid ``` 以下範例對於資料表中的資料欄是有效的條件： ``` catgroup like 'S%' venueseats between 20000 and 50000 eventname in('Jersey Boys','Spamalot') year=2008 length(catdesc)>25 date_part(month, caldate)=6 ``` 條件可分成簡單和複雜；若是複雜條件，您可以使用括號來隔離邏輯單位。在下列範例中，聯結條件會以括號包圍。 ``` where (category.catid=event.catid) and category.catid in(6,7,8) ``` ## 使用須知 您無法在 WHERE 子句中使用別名來參考選取清單表達式。 您無法限制 WHERE 子句中彙整函數的結果；請使用 HAVING 子句達成此目的。 WHERE 子句中限制的資料欄必須衍生自 FROM 子句中的資料表參考。 ## 範例 以下查詢使用不同 WHERE 子句限制的組合，包括 SALES 和 EVENT 資料表的聯結條件、EVENTNAME 資料欄上的述詞，以及 STARTTIME 資料欄上的兩個述詞。 ``` select eventname, starttime, pricepaid/qtysold as costperticket, qtysold from sales, event where sales.eventid = event.eventid and eventname='Hannah Montana' and date_part(quarter, starttime) in(1,2) and date_part(year, starttime) = 2008 order by 3 desc, 4, 2, 1 limit 10; eventname | starttime | costperticket | qtysold ----------------+---------------------+-------------------+--------- Hannah Montana | 2008-06-07 14:00:00 | 1706.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 1658.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 3 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 1 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 2 Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 4 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 1 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 2 Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 4 (10 rows) ``` # WHERE 子句中 Oracle 樣式的外部聯結 為了提供 Oracle 相容性，Amazon Redshift 在 WHERE 子句聯結條件中支援 Oracle 外部聯結運算子 (\$1)。此運算子主要僅用於定義外部聯結條件；請勿嘗試在其他內容中使用它。大多數情況下，此運算子的其他用途都會加以忽略，且不會顯示任何訊息。 外部聯結會傳回對等內部聯結傳回的所有資料列，加上其中一個資料表或兩個資料表的不相符資料列。在 FROM 子句中，您可以指定左、右和完整外部聯結。在 WHERE 子句中，您只能指定左和右外部聯結。 若要對 TABLE1 和 TABLE2 進行外部聯結並從 TABLE1 傳回不相符的資料列 (左外部聯結)，請在 FROM 子句中指定 `TABLE1 LEFT OUTER JOIN TABLE2`，或在 WHERE 子句中對來自 TABLE2 的所有聯結資料欄套用 (\$1) 運算子。對於 TABLE1 中在 TABLE2 中沒有相符資料列的所有資料列，查詢的結果會對包含 TABLE2 中資料欄的任何選取清單表達式包含 null。 若要對 TABLE2 中在 TABLE1 中沒有相符資料列的所有資料列產生相同的行為，請在 FROM 子句中指定 `TABLE1 RIGHT OUTER JOIN TABLE2`，或在 WHERE 子句中對來自 TABLE1 的所有聯結資料欄套用 (\$1) 運算子。 ## 基本語法 ``` [ WHERE { [ table1.column1 = table2.column1(+) ] [ table1.column1(+) = table2.column1 ] } ``` 第一個條件相當於： ``` from table1 left outer join table2 on table1.column1=table2.column1 ``` 第二個條件相當於： ``` from table1 right outer join table2 on table1.column1=table2.column1 ``` **注意** 這裡顯示的語法採用一個聯結資料欄配對說明簡單的 equijoin 案例。不過，其他類型的比較條件和多個聯結資料欄配對同樣有效。 例如，下列 WHERE 子句會定義兩個資料欄配對之間的外部聯結。(\$1) 運算子在這兩個條件中必須連接至相同資料表： ``` where table1.col1 > table2.col1(+) and table1.col2 = table2.col2(+) ``` ## 使用須知 盡可能使用標準 FROM 子句 OUTER JOIN 語法，而不要在 WHERE 子句中使用 (\$1) 運算子。包含 (\$1) 運算子的查詢受到下列規則限制： + 您只能在 WHERE 子句中使用 (\$1) 運算子，而且只能參考資料表或檢視的資料欄。 + 您無法將 (\$1) 運算子套用至表達式。不過，表達式可包含使用 (\$1) 運算子的資料欄。例如，下列聯結條件會傳回語法錯誤： ``` event.eventid*10(+)=category.catid ``` 不過，下列聯結條件有效： ``` event.eventid(+)*10=category.catid ``` + 您無法在同時包含 FROM 子句聯結語法的查詢區塊中使用 (\$1) 運算子。 + 若兩個資料表是透過多個聯結條件聯結，您必須在所有條件中使用 (\$1) 運算子，或完全不使用。採用混合語法樣式的聯結會做為內部聯結執行，但不會產生警告。 + 若您將外部查詢中的資料表與產生自內部查詢的資料表聯結，則 (\$1) 運算子不會產生外部聯結。 + 若要使用 (\$1) 運算子將資料表與其本身進行外部聯結，您必須在 FROM 子句中定義資料表別名，並且在聯結條件中參考這些別名： ``` select count(*) from event a, event b where a.eventid(+)=b.catid; count ------- 8798 (1 row) ``` + 您無法將包含 (\$1) 運算子的聯結條件與 OR 條件或 IN 條件結合。例如： ``` select count(*) from sales, listing where sales.listid(+)=listing.listid or sales.salesid=0; ERROR: Outer join operator (+) not allowed in operand of OR or IN. ``` + 在與兩個以上資料表進行外部聯結的 WHERE 子句中，(\$1) 運算子只能對特定資料表套用一次。在以下範例中，SALES 資料表無法在兩個連續聯結中使用 (\$1) 運算子參考。 ``` select count(*) from sales, listing, event where sales.listid(+)=listing.listid and sales.dateid(+)=date.dateid; ERROR: A table may be outer joined to at most one other table. ``` + 若 WHERE 子句的外部聯結條件會將 TABLE2 的資料欄與常數進行比較，則將 (\$1) 運算子套用至資料欄。若您未包含運算子，就會消除 TABLE1 中的外部聯結資料列 (當中限制的資料欄會包含 null)。請參閱下方範例一節。 ## 範例 以下聯結查詢會在 LISTID 資料欄上指定 SALES 和 LISTING 資料表的左外部聯結： ``` select count(*) from sales, listing where sales.listid = listing.listid(+); count -------- 172456 (1 row) ``` 下列對等查詢會產生相同的結果，但使用 FROM 子句聯結語法： ``` select count(*) from sales left outer join listing on sales.listid = listing.listid; count -------- 172456 (1 row) ``` SALES 資料表未包含 LISTING 資料表中所有清單的記錄，因為並非所有清單都產生銷售。以下查詢會將 SALES 和 LISTING 進行外部聯結，並從 LISTING 傳回資料列，即使 SALES 資料表回報特定清單 ID 沒有銷售。PRICE 和 COMM 資料欄衍生自 SALES 資料表，其結果集中的不相符資料列會包含 null。 ``` select listing.listid, sum(pricepaid) as price, sum(commission) as comm from listing, sales where sales.listid(+) = listing.listid and listing.listid between 1 and 5 group by 1 order by 1; listid | price | comm --------+--------+-------- 1 | 728.00 | 109.20 2 | | 3 | | 4 | 76.00 | 11.40 5 | 525.00 | 78.75 (5 rows) ``` 請注意，使用 WHERE 子句聯結運算子時，資料表在 FROM 子句中的順序並不重要。 WHERE 子句中較複雜的外部聯結條件範例，就是條件由兩個資料表資料欄之間的比較，*以及*與常數的比較所構成： ``` where category.catid=event.catid(+) and eventid(+)=796; ``` 請注意，(\$1) 運算子會在兩處使用：一處是資料表之間的對等比較中，另一處是 EVENTID 資料欄的比較條件中。此語法的結果是在評估 EVENTID 的限制時，保留外部聯結資料列。若您從 EVENTID 限制中移除 (\$1) 運算子，則查詢會將此限制視為篩選條件，而非外部聯結條件的一部分。接著就會從結果集中消除 EVENTID 包含 null 的外部聯結資料列。 以下是說明此行為的完整查詢： ``` select catname, catgroup, eventid from category, event where category.catid=event.catid(+) and eventid(+)=796; catname | catgroup | eventid -----------+----------+--------- Classical | Concerts | Jazz | Concerts | MLB | Sports | MLS | Sports | Musicals | Shows | 796 NBA | Sports | NFL | Sports | NHL | Sports | Opera | Shows | Plays | Shows | Pop | Concerts | (11 rows) ``` 使用 FROM 子句語法的對等查詢如下所示： ``` select catname, catgroup, eventid from category left join event on category.catid=event.catid and eventid=796; ``` 若您從此查詢的 WHERE 子句版本中移除第二個 (\$1) 運算子，則只會傳回 1 個資料列 (`eventid=796` 的資料列)。 ``` select catname, catgroup, eventid from category, event where category.catid=event.catid(+) and eventid=796; catname | catgroup | eventid -----------+----------+--------- Musicals | Shows | 796 (1 row) ``` # GROUP BY 子句 GROUP BY 子句會識別查詢的分組資料欄。它用於將資料表中所有列出的欄中具有相同值的列分組在一起。列出欄的順序並不重要。結果是將具有共同值的每一組列合併成一個群組列，代表群組中的所有列。使用 GROUP BY 消除輸出中的重複項目，並計算套用至群組的彙總。分組資料欄必須在查詢使用標準函數運算彙整時宣告，像是 SUM、AVG 和 COUNT。如需詳細資訊，請參閱[彙總函數](c_Aggregate_Functions.md)。 ## 語法 ``` [ GROUP BY expression [, ...] | ALL | aggregation_extension ] ``` 其中 *aggregation\$1extension* 為下列其中之一： ``` GROUPING SETS ( () | aggregation_extension [, ...] ) | ROLLUP ( expr [, ...] ) | CUBE ( expr [, ...] ) ``` ## Parameters *表達式* 在查詢的選取清單中，資料欄或表達式的清單必須符合非彙整表達式的清單。例如，請考量以下簡單查詢。 ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by listid, eventid order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` 在此查詢中，選取清單是由兩個彙整表達式所構成。第一個使用 SUM 函數，第二個使用 COUNT 函數。其餘兩個資料欄 LISTID 和 EVENTID 必須宣告為分組資料欄。 GROUP BY 子句中的表達式也可以使用序數來參考選取清單。例如，前一個範例可縮減如下。 ``` select listid, eventid, sum(pricepaid) as revenue, count(qtysold) as numtix from sales group by 1,2 order by 3, 4, 2, 1 limit 5; listid | eventid | revenue | numtix -------+---------+---------+-------- 89397 | 47 | 20.00 | 1 106590 | 76 | 20.00 | 1 124683 | 393 | 20.00 | 1 103037 | 403 | 20.00 | 1 147685 | 429 | 20.00 | 1 (5 rows) ``` ALL ALL 表示依 SELECT 清單中指定的所有欄分組，但彙總的欄除外。例如，請考慮下列查詢，其依據 `col1` 和 `col2` 分組，因此不需要在 GROUP BY 子句中個別指定。欄 `col3` 是 `SUM` 函式的引數，因此不會分組。 ``` SELECT col1, col2 sum(col3) FROM testtable GROUP BY ALL ``` 如果您在 SELECT 清單中排除 (EXCLUDE) 某一欄，則 GROUP BY ALL 子句不會將依據該特定欄的結果分組。 ``` SELECT * EXCLUDE col3 FROM testtable GROUP BY ALL ``` * *aggregation\$1extension* * 您可以使用彙總延伸項目 GROUPING SETS、ROLLUP 和 CUBE，在單一陳述式中執行多個 GROUP BY 操作的工作。如需彙總延伸項目及相關函數的相關資訊，請參閱 [彙總延伸項目](r_GROUP_BY_aggregation-extensions.md)。 ## 範例 下列範例使用的 SALES 資料表包含以下各欄：salesid、listid、sellerid、buyerid、eventid、dateid、qtysold、pricepaid、commission 和 saletime。如需 SALES 資料表的詳細資訊，請參閱 [範本資料庫](c_sampledb.md)。 下列查詢依據 `salesid` 和 `listid` 分組，因此不需要在 GROUP BY 子句中個別指定。欄 `qtysold` 是 `SUM` 函式的引數，因此不會分組。 ``` SELECT salesid, listid, sum(qtysold) FROM sales GROUP BY ALL; salesid | listid | sum --------+---------+------ 33095 | 36572 | 2 88268 | 100813 | 4 110917 | 127048 | 1 ... ``` 下列範例查詢會在 SELECT 清單中排除數個欄，因此 GROUP BY ALL 只會將 salesid 和 listid 分組。 ``` SELECT * EXCLUDE sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, saletime FROM sales GROUP BY ALL; salesid | listid --------+--------- 33095 | 36572 88268 | 100813 110917 | 127048 ... ``` # 彙總延伸項目 Amazon Redshift 支援彙總延伸項目，可在單一陳述式中執行多個 GROUP BY 操作的工作。 彙總延伸項目的範例會使用 `orders` 資料表，該資料表會保留電子公司的銷售資料。您可以執行下列操作來建立 `orders`： ``` CREATE TABLE ORDERS ( ID INT, PRODUCT CHAR(20), CATEGORY CHAR(20), PRE_OWNED CHAR(1), COST DECIMAL ); INSERT INTO ORDERS VALUES (0, 'laptop', 'computers', 'T', 1000), (1, 'smartphone', 'cellphones', 'T', 800), (2, 'smartphone', 'cellphones', 'T', 810), (3, 'laptop', 'computers', 'F', 1050), (4, 'mouse', 'computers', 'F', 50); ``` ## *GROUPING SETS* 在單一陳述式中計算一或多個群組集。群組集是單一 GROUP BY 子句的集合，也就是一組 0 個或多個資料行，您可以以此將查詢的結果集分組。GROUP BY GROUPING SETS 等同於在由不同資料欄分組的一個結果集上執行 UNION ALL 查詢。例如，GROUP BY GROUPING SETS((a), (b)) 等同於 GROUP BY a UNION ALL GROUP BY b。 下列範例會傳回訂單資料表的產品根據產品類別和銷售產品種類進行分組的成本。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY GROUPING SETS(category, product); category | product | total ----------------------+----------------------+------- computers | | 2100 cellphones | | 1610 | laptop | 2050 | smartphone | 1610 | mouse | 50 (5 rows) ``` ## *ROLLUP* 假設有一個階層，其中之前的資料欄被視為後續資料欄的父項。ROLLUP 會依提供的資料欄將資料分組，並傳回額外的小計資料列 (代表所有分組資料欄層級的總計)，以及已分組的資料列。例如，您可以使用 GROUP BY ROLLUP((a), (b)) 傳回一個先按 a 分組，然後按 b 分組的結果集 (假設 b 是 a 的子區段)。ROLLUP 也會傳回包含整個結果集的資料列，而不會將資料欄分組。 GROUP BY ROLLUP((a), (b)) 等於 GROUP BY GROUPING SETS((a,b), (a), ())。 下列範例會傳回訂單資料表產品先依類別分組，然後依產品分組的成本，其中以產品做為類別的細項。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY ROLLUP(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | | 3710 (6 rows) ``` ## *CUBE* 依提供的資料欄將資料分組，並傳回額外的小計資料列 (代表所有分組資料欄層級的總計)，以及已分組的資料列。CUBE 會傳回與 ROLLUP 相同的資料列，同時針對 ROLUP 未涵蓋的每個分組資料欄組合新增額外的小計資料列。例如，您可以使用 GROUP BY CUBE ((a), (b)) 傳回一個先按 a 分組，然後按 b 分組的結果集 (假設 b 是 a 的子區段)，然後再獨自按 b 分組。CUBE 也會傳回包含整個結果集的資料列，而不會將資料欄分組。 GROUP BY CUBE((a), (b)) 等於 GROUP BY GROUPING SETS((a, b), (a), (b), ())。 下列範例會傳回訂單資料表產品先依類別分組，然後依產品分組的成本，其中以產品做為類別的細項。與前面的 ROLLUP 範例不同，陳述式會傳回每個分組資料欄組合的結果。 ``` SELECT category, product, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 1,2; category | product | total ----------------------+----------------------+------- cellphones | smartphone | 1610 cellphones | | 1610 computers | laptop | 2050 computers | mouse | 50 computers | | 2100 | laptop | 2050 | mouse | 50 | smartphone | 1610 | | 3710 (9 rows) ``` ## *GROUPING/GROUPING\$1ID 函數* ROLLUP 和 CUBE 會將 NULL 值新增至結果集，以指出小計資料列。例如，GROUP BY ROLLUP((a), (b)) 會傳回 b 分組資料欄中值為 NULL 的一或多個資料列，以指出這些資料列是分組資料欄中欄位的小計。這些 NULL 值僅用於滿足傳回元組的格式。 當您在儲存 NULL 值本身的關係上使用 ROLLUP 和 CUBE 執行 GROUP BY 操作時，這可能會產生資料列看起來具有相同分組資料欄的結果集。返回上一個範例，如果 b 分組資料欄包含一個儲存的 NULL 值，GROUP BY ROLLUP((a), (b)) 會在 b 分組資料欄中 (不是小計) 傳回值為 NULL 的資料列。 若要區分 ROLLUP 和 CUBE 建立的 NULL 值，以及儲存在資料表本身的 NULL 值，您可以使用 GROUPING 函數或其別名 GROUPING\$1ID。GROBING 採用單個分組集作為其引數，並且對於結果集中的每一個資料列傳回對應於該位置中分組資料欄的 0 或 1 位元值，然後將該值轉換為整數。如果該位置中的值是由彙總延伸項目建立的 NULL 值，則 GROUPING 會傳回 1。對於所有其他值，包括儲存的 NULL 值，則傳回 0。 例如，GROUPING(category, product) 可以針對指定的資料列傳回下列值，視該資料列的分組資料欄值而定。就此範例而言，資料表中的所有 NULL 值都是彙總延伸項目所建立的 NULL 值。 [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/zh_tw/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html) GROUPING 函數會以下列格式顯示在查詢的 SELECT 清單部分中。 ``` SELECT ... [GROUPING( expr )...] ... GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ... ``` 下列範例與前面的 CUBE 範例相同，但為其分組集增加了 GROUPING 函數。 ``` SELECT category, product, GROUPING(category) as grouping0, GROUPING(product) as grouping1, GROUPING(category, product) as grouping2, sum(cost) as total FROM orders GROUP BY CUBE(category, product) ORDER BY 3,1,2; category | product | grouping0 | grouping1 | grouping2 | total ----------------------+----------------------+-----------+-----------+-----------+------- cellphones | smartphone | 0 | 0 | 0 | 1610 cellphones | | 0 | 1 | 1 | 1610 computers | laptop | 0 | 0 | 0 | 2050 computers | mouse | 0 | 0 | 0 | 50 computers | | 0 | 1 | 1 | 2100 | laptop | 1 | 0 | 2 | 2050 | mouse | 1 | 0 | 2 | 50 | smartphone | 1 | 0 | 2 | 1610 | | 1 | 1 | 3 | 3710 (9 rows) ``` ## *部份 ROLLUP 與 CUBE* 您只能使用小計的一部分來執行 ROLLUP 和 CUBE 操作。 部分 ROLLUP 和 CUBE 操作的語法如下。 ``` GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...]) ``` 在這裡，GROUP BY 子句僅在表達式 *expr2* 和以後的層級上建立小計資料列。 下列範例會顯示訂單資料表上的部份 ROLLUP 和 CUBE 操作，並先依產品是否為二手產品分組，然後在類別與產品資料欄上執行 ROLLUP 和 CUBE。 ``` SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | | 6 | 1100 T | | | 6 | 2610 (9 rows) SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 (13 rows) ``` 由於二手資料欄不包含在 ROLLUP 和 CUBE 作業中，因此沒有包含所有其他資料列的總計資料列。 ## *串連分組* 您可以串連多個 GROUPING SETS/ROLLUP/CUBE 子句，以計算不同層次的小計。串連分組會傳回所提供分組集的笛卡爾乘積。 串連 GROUPING SETS/ROLLUP/CUBE 子句的語法如下。 ``` GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]), {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...] ``` 請考慮下列範例，看看小型串連分組如何產生大型的最終結果集。 ``` SELECT pre_owned, category, product, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ()) ORDER BY 4,1,2,3; pre_owned | category | product | group_id | total -----------+----------------------+----------------------+----------+------- F | computers | laptop | 0 | 1050 F | computers | mouse | 0 | 50 T | cellphones | smartphone | 0 | 1610 T | computers | laptop | 0 | 1000 | cellphones | smartphone | 1 | 1610 | computers | laptop | 1 | 2050 | computers | mouse | 1 | 50 F | computers | | 2 | 1100 T | cellphones | | 2 | 1610 T | computers | | 2 | 1000 | cellphones | | 3 | 1610 | computers | | 3 | 2100 F | | laptop | 4 | 1050 F | | mouse | 4 | 50 T | | laptop | 4 | 1000 T | | smartphone | 4 | 1610 | | laptop | 5 | 2050 | | mouse | 5 | 50 | | smartphone | 5 | 1610 F | | | 6 | 1100 T | | | 6 | 2610 | | | 7 | 3710 (22 rows) ``` ## *巢狀分組* 您可以使用 GROUPING SETS/ROLLUP/CUBE 操作作為您的 GROUPING SETS *expr*，形成一個巢狀分組。巢狀 GROUPING SETS 中的子分組會被扁平化。 巢狀分組的語法如下。 ``` GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...]) ``` 請考量下列範例。 ``` SELECT category, product, pre_owned, GROUPING(category, product, pre_owned) as group_id, sum(cost) as total FROM orders GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned)) ORDER BY 4,1,2,3; category | product | pre_owned | group_id | total ----------------------+----------------------+-----------+----------+------- cellphones | | | 3 | 1610 computers | | | 3 | 2100 | laptop | F | 4 | 1050 | laptop | T | 4 | 1000 | mouse | F | 4 | 50 | smartphone | T | 4 | 1610 | laptop | | 5 | 2050 | mouse | | 5 | 50 | smartphone | | 5 | 1610 | | F | 6 | 1100 | | T | 6 | 2610 | | | 7 | 3710 | | | 7 | 3710 (13 rows) ``` 請注意，由於 ROLLUP(category) 和 CUBE(product, pre\$1owned) 都包含分組集 ()，因此代表總計的資料列將重複。 ## *使用須知* + GROUP BY 子句最多支援 64 個分組集。如果是 ROLLUP 和 CUBE，或是 GROUPING SETS、ROLLUP 和 CUBE 的某些組合，則此限制會套用至隱含的分組集數目。例如，GROUP BY CUBE((a), (b)) 計為 4 個分組集，而不是 2 個。 + 使用彙總延伸項目時，您無法使用常數作為分組資料欄。 + 您無法建立包含重複資料欄的分組集。 # HAVING 子句 HAVING 子句會將條件套用至查詢傳回的中繼分組結果集。 ## 語法 ``` [ HAVING condition ] ``` 例如，您可以限制 SUM 函數的結果： ``` having sum(pricepaid) >10000 ``` HAVING 會在套用所有 WHERE 子句條件且完成 GROUP BY 操作之後套用。 條件本身會採用與任何 WHERE 子句條件相同的形式。 ## 使用須知 + HAVING 子句條件中參考的任何資料欄必須是分組資料欄，或是參考彙整函數結果的資料欄。 + 在 HAVING 子句中，您無法指定： + 參考選取清單項目的序數。只有 GROUP BY 和 ORDER BY 子句接受序數。 ## 範例 以下查詢會依名稱計算售票總金額，然後消除總金額低於 800,000 USD 的活動。HAVING 條件會套用至選取清單中彙整函數的結果：`sum(pricepaid)`。 ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(pricepaid) > 800000 order by 2 desc, 1; eventname | sum -----------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 ``` 以下查詢會計算類似的結果集。不過，在此情況下，HAVING 條件會套用至選取清單中未指定的彙整：`sum(qtysold)`。未銷售超過 2,000 張票的活動將從最終結果中消除。 ``` select eventname, sum(pricepaid) from sales join event on sales.eventid = event.eventid group by 1 having sum(qtysold) >2000 order by 2 desc, 1; eventname | sum -----------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 Chicago | 790993.00 Spamalot | 714307.00 ``` 以下查詢會依名稱計算售票總金額，然後消除總金額低於 800,000 USD 的活動。HAVING 條件會套用至使用別名 `pp` 代表 `sum(pricepaid)` 的選取清單中彙總函數的結果。 ``` select eventname, sum(pricepaid) as pp from sales join event on sales.eventid = event.eventid group by 1 having pp > 800000 order by 2 desc, 1; eventname | pp -----------------+----------- Mamma Mia! | 1135454.00 Spring Awakening | 972855.00 The Country Girl | 910563.00 Macbeth | 862580.00 Jersey Boys | 811877.00 Legally Blonde | 804583.00 ``` # QUALIFY 子句 QUALIFY 子句會根據使用者指定的搜尋條件，篩選先前計算的視窗函數結果。您可以使用子句將篩選條件套用至視窗函數的結果，而不需要使用子查詢。 其類似於 [HAVING 子句](https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html)，該子句適用於從 WHERE 子句進一步篩選列的條件。QUALIFY 和 HAVING 之間的區別在於，從 QUALIFY 子句篩選的結果可以基於在資料上執行窗口函數的結果。您可以在一個查詢中同時使用 QUALIFY 和 HAVING 子句。 ## 語法 ``` QUALIFY condition ``` **注意** 如果您直接在 FROM 子句之後使用 QUALIFY 子句，則 FROM 關係名稱必須在 QUALIFY 子句之前指定別名。 ## 範例 本節中的範例使用以下範例資料。 ``` create table store_sales (ss_sold_date date, ss_sold_time time, ss_item text, ss_sales_price float); insert into store_sales values ('2022-01-01', '09:00:00', 'Product 1', 100.0), ('2022-01-01', '11:00:00', 'Product 2', 500.0), ('2022-01-01', '15:00:00', 'Product 3', 20.0), ('2022-01-01', '17:00:00', 'Product 4', 1000.0), ('2022-01-01', '18:00:00', 'Product 5', 30.0), ('2022-01-02', '10:00:00', 'Product 6', 5000.0), ('2022-01-02', '16:00:00', 'Product 7', 5.0); ``` 下列範例示範如何找到每天 12:00 之後售出的兩個最昂貴的物品。 ``` SELECT * FROM store_sales ss WHERE ss_sold_time > time '12:00:00' QUALIFY row_number() OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2 ss_sold_date | ss_sold_time | ss_item | ss_sales_price --------------+--------------+-----------+---------------- 2022-01-01 | 17:00:00 | Product 4 | 1000 2022-01-01 | 18:00:00 | Product 5 | 30 2022-01-02 | 16:00:00 | Product 7 | 5 ``` 然後，您可以找到每天售出的最後一件物品。 ``` SELECT * FROM store_sales ss QUALIFY last_value(ss_item) OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) = ss_item; ss_sold_date | ss_sold_time | ss_item | ss_sales_price --------------+--------------+-----------+---------------- 2022-01-01 | 18:00:00 | Product 5 | 30 2022-01-02 | 16:00:00 | Product 7 | 5 ``` 下列範例會傳回與前一個查詢相同的記錄，也就是每天售出的最後一件物品，但其不使用 QUALIFY 子句。 ``` SELECT * FROM ( SELECT *, last_value(ss_item) OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ss_last_item FROM store_sales ss ) WHERE ss_last_item = ss_item; ss_sold_date | ss_sold_time | ss_item | ss_sales_price | ss_last_item --------------+--------------+-----------+----------------+-------------- 2022-01-02 | 16:00:00 | Product 7 | 5 | Product 7 2022-01-01 | 18:00:00 | Product 5 | 30 | Product 5 ``` # UNION、INTERSECT 和 EXCEPT **Topics** + [語法](#r_UNION-synopsis) + [Parameters](#r_UNION-parameters) + [集合運算子的評估順序](#r_UNION-order-of-evaluation-for-set-operators) + [使用須知](#r_UNION-usage-notes) + [範例 UNION 查詢](c_example_union_query.md) + [範例 UNION ALL 查詢](c_example_unionall_query.md) + [範例 INTERSECT 查詢](c_example_intersect_query.md) + [範例 EXCEPT 查詢](c_Example_MINUS_query.md) UNION、INTERSECT 和 EXCEPT *集合運算子*可用來比較和合併兩種不同查詢表達式的結果。例如，如果您想知道哪些網站使用者同時是買方和賣家，但其使用者名稱儲存在不同的資料欄或資料表中，您可以找出這兩種類型使用者的*交集*。如果您想知道哪些網站使用者是買方，但不是賣家，您可以使用 EXCEPT 運算子找出兩份使用者清單之間的*差異*。如果您想要建構所有使用者的清單，但不考慮角色，您可以使用 UNION 運算子。 ## 語法 ``` query { UNION [ ALL ] | INTERSECT | EXCEPT | MINUS } query ``` ## Parameters *query* 此查詢表達式會以其選取清單形式，對應至接在 UNION、INTERSECT 或 EXCEPT 運算子後面的另一個查詢表達式。兩個表達式必須包含採用相容資料類型的相同輸出資料欄數，否則就無法比較和合併這兩個結果集。集合操作不允許在不同類別的資料類型之間進行隱含轉換；如需詳細資訊，請參閱 [類型相容性與轉換](c_Supported_data_types.md#r_Type_conversion)。 您可以建構包含無限查詢表達式數目的查詢，並將它們與 UNION、INTERSECT 和 EXCEPT 運算子的任意組合連結。例如，假設資料表 T1、T2 和 T3 包含相容的資料欄集，則以下查詢結構有效： ``` select * from t1 union select * from t2 except select * from t3 order by c1; ``` UNION 此集合操作會從兩個查詢表達式傳回資料列，無論資料列衍生自其中一個或兩個表達式。 INTERSECT 此集合操作會傳回衍生自兩個查詢表達式的資料列。未由兩個表達式傳回的資料列則會遭到捨棄。 EXCEPT \$1 MINUS 此集合操作會傳回衍生自兩個查詢表達式之一的資料列。若要限定結果，資料列必須存在第一個結果資料表中，但不能存在第二個資料表中。MINUS 和 EXCEPT 是一模一樣的同義詞。 ALL ALL 關鍵字會保留 UNION 所產生的任何重複資料列。未使用 ALL 關鍵字時的預設行為是捨棄這些重複項目。不支援 INTERSECT ALL、EXCEPT ALL 和 MINUS ALL。 ## 集合運算子的評估順序 UNION 和 EXCEPT 集合運算子為左關聯。若未指定括號來影響優先順序，則會從左到右評估這些集合運算子的組合。例如，在下列查詢中，T1 和 T2 的 UNION 會先評估，然後在 UNION 結果上執行 EXCEPT 操作： ``` select * from t1 union select * from t2 except select * from t3 order by c1; ``` 在相同查詢中使用運算子組合時，INTERSECT 運算子的優先順序高於 UNION 和 EXCEPT 運算子。例如，下列查詢會先評估 T2 和 T3 的交集，再將結果與 T1 進行聯集： ``` select * from t1 union select * from t2 intersect select * from t3 order by c1; ``` 加入括號就可以強制執行不同的評估順序。在下列案例中，T1 和 T2 的聯集結果會與 T3 交集，而查詢可能會產生不同的結果。 ``` (select * from t1 union select * from t2) intersect (select * from t3) order by c1; ``` ## 使用須知 + 集合操作查詢的結果中傳回的資料欄名稱，是來自第一個查詢表達式的資料表中的資料欄名稱 (或別名)。這些資料欄名稱可能會造成誤導，因為資料欄中的值是從任一邊集合運算子的資料表衍生，所以建議您為結果集提供有意義的別名。 + 先於集合運算子的查詢表達式不應包含 ORDER BY 子句。只有在包含集合運算子的查詢結尾使用 ORDER BY 子句時，該子句才會產生有意義的排序結果。在此情況下，ORDER BY 子句會套用至所有集合操作的最終結果。最外層的查詢也可包含標準 LIMIT 和 OFFSET 子句。 + 當集合運算子查詢傳回小數結果時，對應的結果資料欄就會提升，以傳回相同的精確度和小數位數。例如，在以下查詢中，T1.REVENUE 是 DECIMAL(10,2) 資料欄，而 T2.REVENUE 是 DECIMAL(8,4) 資料欄，小數結果會提升為 DECIMAL(12,4)： ``` select t1.revenue union select t2.revenue; ``` 小數位數為 `4`，因為這是兩個資料欄的小數位數上限。精確度為 `12`，因為 T1.REVENUE 要求小數點左邊有 8 位數 (12 - 4 = 8)。此類型提升可確保 UNION 兩邊的所有值都能納入結果中。若是 64 位元值，最高結果精確度為 19，而結果小數位數上限為 18。若是 128 位元值，最高結果精確度為 38，而結果小數位數上限為 37。 若產生的資料類型超過 Amazon Redshift 精確度和小數位數限制，查詢就會傳回錯誤。 + 在集合操作中，若每個對應資料欄配對的這兩個資料值為*等於*或*兩者皆為 NULL*，則這兩個資料列會視為相同。例如，若資料表 T1 和 T2 都包含一個資料欄和一個資料列，而該資料列在兩個資料表中都是 NULL，則對這些資料表執行 INTERSECT 操作就會傳回該資料列。 # 範例 UNION 查詢 在下列 UNION 查詢中，SALES 資料表中的資料列會與 LISTING 資料表中的資料列合併。會從每個資料表選取三個相容的資料欄；在此情況下，對應的資料欄會有相同的名稱和資料類型。 最終結果集是依 LISTING 資料表中的第一欄排序，且限於擁有最高 LISTID 值的 5 個資料列。 ``` select listid, sellerid, eventid from listing union select listid, sellerid, eventid from sales order by listid, sellerid, eventid desc limit 5; listid | sellerid | eventid --------+----------+--------- 1 | 36861 | 7872 2 | 16002 | 4806 3 | 21461 | 4256 4 | 8117 | 4337 5 | 1616 | 8647 (5 rows) ``` 以下範例說明如何將常值新增至 UNION 查詢的輸出，以便查看結果集中的每個資料列是由哪個查詢表達式所產生。查詢會將來自第一個查詢表達式的資料列識別為 "B" (表示買方)，來自第二個查詢表達式的資料列識別為 "S" (表示賣家)。 查詢會識別價值 10,000 USD 以上的票券交易買方和賣家。UNION 運算子任一邊的兩個查詢表達式之間唯一的差異，就是 SALES 資料表的聯結資料欄。 ``` select listid, lastname, firstname, username, pricepaid as price, 'S' as buyorsell from sales, users where sales.sellerid=users.userid and pricepaid >=10000 union select listid, lastname, firstname, username, pricepaid, 'B' as buyorsell from sales, users where sales.buyerid=users.userid and pricepaid >=10000 order by 1, 2, 3, 4, 5; listid | lastname | firstname | username | price | buyorsell --------+----------+-----------+----------+-----------+----------- 209658 | Lamb | Colette | VOR15LYI | 10000.00 | B 209658 | West | Kato | ELU81XAA | 10000.00 | S 212395 | Greer | Harlan | GXO71KOC | 12624.00 | S 212395 | Perry | Cora | YWR73YNZ | 12624.00 | B 215156 | Banks | Patrick | ZNQ69CLT | 10000.00 | S 215156 | Hayden | Malachi | BBG56AKU | 10000.00 | B (6 rows) ``` 以下範例使用 UNION ALL 運算子，因為重複的資料列 (若找到) 需保留在結果中。若是特定活動 ID 系列，查詢會針對與各個活動相關聯的每筆銷售傳回 0 或更多資料列，並針對該活動的每份清單傳回 0 或 1。LISTING 和 EVENT 資料表中每個資料列的活動 ID 都是唯一的，但 SALES 資料表中相同的活動和清單 ID 組合可能會有多筆銷售。 結果集中的第三個資料欄會識別資料列的來源。若是來自 SALES 資料表，則會在 SALESROW 資料欄中標示為「Yes (是)」 (SALESROW 是 SALES.LISTID 的別名)。若資料列來自 LISTING 資料表，則會在 SALESROW 資料欄中標示為「No (否)」。 在此情況下，結果集會包含活動 7787、清單 500 的三個銷售資料列。換句話說，此清單與活動組合發生了三筆不同的交易。另外兩份清單 501 和 502 並未產生任何銷售，因此查詢針對這些清單 ID 產生的唯一資料列是來自 LISTING 資料表 (SALESROW = 'No')。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (6 rows) ``` 若您執行相同查詢，但未使用 ALL 關鍵字，結果只會保留其中一項銷售交易。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (4 rows) ``` # 範例 UNION ALL 查詢 以下範例使用 UNION ALL 運算子，因為重複的資料列 (若找到) 需保留在結果中。若是特定活動 ID 系列，查詢會針對與各個活動相關聯的每筆銷售傳回 0 或更多資料列，並針對該活動的每份清單傳回 0 或 1。LISTING 和 EVENT 資料表中每個資料列的活動 ID 都是唯一的，但 SALES 資料表中相同的活動和清單 ID 組合可能會有多筆銷售。 結果集中的第三個資料欄會識別資料列的來源。若是來自 SALES 資料表，則會在 SALESROW 資料欄中標示為「Yes (是)」 (SALESROW 是 SALES.LISTID 的別名)。若資料列來自 LISTING 資料表，則會在 SALESROW 資料欄中標示為「No (否)」。 在此情況下，結果集會包含活動 7787、清單 500 的三個銷售資料列。換句話說，此清單與活動組合發生了三筆不同的交易。另外兩份清單 501 和 502 並未產生任何銷售，因此查詢針對這些清單 ID 產生的唯一資料列是來自 LISTING 資料表 (SALESROW = 'No')。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union all select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 7787 | 500 | Yes 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (6 rows) ``` 若您執行相同查詢，但未使用 ALL 關鍵字，結果只會保留其中一項銷售交易。 ``` select eventid, listid, 'Yes' as salesrow from sales where listid in(500,501,502) union select eventid, listid, 'No' from listing where listid in(500,501,502) order by listid asc; eventid | listid | salesrow ---------+--------+---------- 7787 | 500 | No 7787 | 500 | Yes 6473 | 501 | No 5108 | 502 | No (4 rows) ``` # 範例 INTERSECT 查詢 比較下列範例與第一個 UNION 範例。這兩個範例的唯一差異在於使用的集合運算子，但結果非常不同。只有其中一個資料列相同： ``` 235494 | 23875 | 8771 ``` 這是在限制的 5 個資料列結果中，唯一同時存在兩個資料表中的資料列。 ``` select listid, sellerid, eventid from listing intersect select listid, sellerid, eventid from sales order by listid desc, sellerid, eventid limit 5; listid | sellerid | eventid --------+----------+--------- 235494 | 23875 | 8771 235482 | 1067 | 2667 235479 | 1589 | 7303 235476 | 15550 | 793 235475 | 22306 | 7848 (5 rows) ``` 以下查詢會尋找三月份同時於紐約市和洛杉磯的場館舉行的活動 (有售票)。兩個查詢表達式之間唯一的差異就是 VENUECITY 資料欄的限制條件。 ``` select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='Los Angeles' intersect select distinct eventname from event, sales, venue where event.eventid=sales.eventid and event.venueid=venue.venueid and date_part(month,starttime)=3 and venuecity='New York City' order by eventname asc; eventname ---------------------------- A Streetcar Named Desire Dirty Dancing Electra Running with Annalise Hairspray Mary Poppins November Oliver! Return To Forever Rhinoceros South Pacific The 39 Steps The Bacchae The Caucasian Chalk Circle The Country Girl Wicked Woyzeck (16 rows) ``` # 範例 EXCEPT 查詢 TICKIT 資料庫中的 CATEGORY 資料表包含以下 11 個資料列： ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts (11 rows) ``` 假設 CATEGORY\$1STAGE 資料表 (臨時資料表) 包含一個額外的資料列： ``` catid | catgroup | catname | catdesc -------+----------+-----------+-------------------------------------------- 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association 5 | Sports | MLS | Major League Soccer 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 9 | Concerts | Pop | All rock and pop music concerts 10 | Concerts | Jazz | All jazz singers and bands 11 | Concerts | Classical | All symphony, concerto, and choir concerts 12 | Concerts | Comedy | All stand up comedy performances (12 rows) ``` 傳回兩個資料表之間的差異。換句話說，會傳回 CATEGORY\$1STAGE 資料表而非 CATEGORY 資料表中的資料列： ``` select * from category_stage except select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` 以下對等查詢使用同義詞 MINUS。 ``` select * from category_stage minus select * from category; catid | catgroup | catname | catdesc -------+----------+---------+---------------------------------- 12 | Concerts | Comedy | All stand up comedy performances (1 row) ``` 若您將 SELECT 表達式的順序反轉，則查詢不會傳回任何資料列。 # ORDER BY 子句 **Topics** + [語法](#r_ORDER_BY_clause-synopsis) + [Parameters](#r_ORDER_BY_clause-parameters) + [使用須知](#r_ORDER_BY_usage_notes) + [ORDER BY 的範例](r_Examples_with_ORDER_BY.md) ORDER BY 子句會排序查詢的結果集。 ## 語法 ``` [ ORDER BY expression [ ASC | DESC ] ] [ NULLS FIRST | NULLS LAST ] [ LIMIT { count | ALL } ] [ OFFSET start ] ``` ## Parameters *表達式* 此表達式會排序查詢結果集的順序，通常是藉由指定選取清單中的一個或多個資料欄。結果會根據二進位 UTF-8 順序傳回。您還可以指定下列項目： + 不在選取清單中的資料欄 + 由查詢所參考資料表中的一個或多個資料欄構成的表達式 + 代表選取清單項目位置的序數 (或是，若沒有選取清單的話，則為資料欄在資料表中的位置) + 定義選取清單項目的別名 當 ORDER BY 子句包含多個表達式時，結果集會根據第一個表達式排序，然後對擁有與第一個表達式相符之值的資料列套用第二個表達式，以此類推。 ASC \$1 DESC 此選項會定義表達式的排序順序，如下所示： + ASC：遞增 (例如，數值從低到高，字元字串 'A' 到 'Z')。若未指定選項，資料會預設為遞增排序。 + DESC：遞減 (數值從高到低，字串 'Z' 到 'A')。 NULLS FIRST \$1 NULLS LAST 這些選項指定 NULL 值應該排序在最前 (在非 null 值之前) 或排序在最後 (在非 null 值之後)。根據預設，依 ASC 順序排序時，NULL 值排在最後面，而依 DESC 順序排序時，則排在最前面。 LIMIT *number* \$1 ALL 此選項會控制查詢傳回的排序資料列數。LIMIT 數字必須是正整數；最大值為 `2147483647`。 LIMIT 0 不會傳回任何資料列。您可以使用此語法進行測試：查看查詢執行情形 (不顯示任何資料列)，或從資料表傳回資料欄清單。若您使用 LIMIT 0 傳回資料欄清單，則 ORDER BY 子句是多餘的。預設值為 LIMIT ALL。 OFFSET *start* 此選項會指定先略過 *start* 之前的資料列數，再開始傳回資料列。OFFSET 數字必須是正整數；最大值為 `2147483647`。搭配 LIMIT 選項使用時，會先略過 OFFSET 資料列，再開始計算傳回的 LIMIT 資料列。如果未使用 LIMIT 選項，則結果集中的資料列數會減掉略過的資料列數。OFFSET 子句略過的資料列仍須經過掃描，因此使用較大的 OFFSET 值可能會導致效率不佳。 ## 使用須知 請注意以下使用 ORDER BY 子句的預期行為： + NULL 值會視為「高於」所有其他值。使用預設的遞增排序順序時，NULL 值會排列在最後面。若要變更此行為，請使用 NULLS FIRST 選項。 + 若查詢未包含 ORDER BY 子句，系統傳回的結果集當中就不會有可預測的資料列排列順序。執行相同的查詢兩次，可能會傳回依不同順序排列的結果集。 + LIMIT 和 OFFSET 選項可在沒有 ORDER BY 子句的情況下使用；不過，若要傳回一致的資料列集，請使用這些選項搭配 ORDER BY。 + 在任何平行系統中，例如 Amazon Redshift，當 ORDER BY 子句無法產生唯一排列順序時，資料列的順序便會不確定。也就是說，如果 ORDER BY 表達式產生重複的值，則這些資料列的傳回順序可能與其他系統不同，也可能隨著每次執行 Amazon Redshift 而有所不同。 + Amazon Redshift 不支援 ORDER BY 子句中的字符文字。 # ORDER BY 的範例 從 CATEGORY 資料表傳回全部 11 列，並依第二個資料欄 CATGROUP 排序。若結果擁有相同的 CATGROUP 值，則依字元字串的長度排列 CATDESC 資料欄的值。然後，依 CATID 和 CATNAME 欄排序。 ``` select * from category order by 2, length(catdesc), 1, 3; catid | catgroup | catname | catdesc ------+----------+-----------+---------------------------------------- 10 | Concerts | Jazz | All jazz singers and bands 9 | Concerts | Pop | All rock and pop music concerts 11 | Concerts | Classical | All symphony, concerto, and choir conce 6 | Shows | Musicals | Musical theatre 7 | Shows | Plays | All non-musical theatre 8 | Shows | Opera | All opera and light opera 5 | Sports | MLS | Major League Soccer 1 | Sports | MLB | Major League Baseball 2 | Sports | NHL | National Hockey League 3 | Sports | NFL | National Football League 4 | Sports | NBA | National Basketball Association (11 rows) ``` 從 SALES 資料表傳回選取的欄，並依最高 QTYSOLD 值排序。將結果限制為前 10 個資料列： ``` select salesid, qtysold, pricepaid, commission, saletime from sales order by qtysold, pricepaid, commission, salesid, saletime desc limit 10; salesid | qtysold | pricepaid | commission | saletime --------+---------+-----------+------------+--------------------- 15401 | 8 | 272.00 | 40.80 | 2008-03-18 06:54:56 61683 | 8 | 296.00 | 44.40 | 2008-11-26 04:00:23 90528 | 8 | 328.00 | 49.20 | 2008-06-11 02:38:09 74549 | 8 | 336.00 | 50.40 | 2008-01-19 12:01:21 130232 | 8 | 352.00 | 52.80 | 2008-05-02 05:52:31 55243 | 8 | 384.00 | 57.60 | 2008-07-12 02:19:53 16004 | 8 | 440.00 | 66.00 | 2008-11-04 07:22:31 489 | 8 | 496.00 | 74.40 | 2008-08-03 05:48:55 4197 | 8 | 512.00 | 76.80 | 2008-03-23 11:35:33 16929 | 8 | 568.00 | 85.20 | 2008-12-19 02:59:33 (10 rows) ``` 使用 LIMIT 0 語法會傳回一份資料欄清單，但沒有資料列： ``` select * from venue limit 0; venueid | venuename | venuecity | venuestate | venueseats ---------+-----------+-----------+------------+------------ (0 rows) ``` # CONNECT BY 子句 CONNECT BY 子句會指定階層中資料列之間的關係。您可以使用 CONNECT BY，透過將資料表聯結至自身並處理階層式資料，以階層順序選取資料列。例如，您可以使用它來遞迴循環組織結構圖和列出資料。 階層式查詢會以下列順序處理： 1. 如果 FROM 子句具有聯結，則會先處理它。 1. CONNECT BY 子句會受到評估。 1. WHERE 子句會受到評估。 ## 語法 ``` [START WITH start_with_conditions] CONNECT BY connect_by_conditions ``` **注意** 雖然 START 和 CONNECT 不是保留字，但如果您在查詢中使用 START 和 CONNECT 做為資料表別名，請使用分隔識別碼 (雙引號) 或 AS，以避免在執行階段失敗。 ``` SELECT COUNT(*) FROM Employee "start" CONNECT BY PRIOR id = manager_id START WITH name = 'John' ``` ``` SELECT COUNT(*) FROM Employee AS start CONNECT BY PRIOR id = manager_id START WITH name = 'John' ``` ## Parameters *start\$1with\$1conditions* 指定階層根資料列的條件 *connect\$1by\$1conditions* 指定階層父項資料列與子資料列之間關係的條件。至少有一個條件必須使用用來參照父資料列的 ` ` 一元運算子來限定。 ``` PRIOR column = expression -- or expression > PRIOR column ``` ## 運算子 您可以在 CONNECT BY 查詢中使用以下運算子。 *LEVEL* 傳回階層中目前資料列層級的虛擬資料欄。針對根資料列傳回 1，針對根資料列的子項傳回 2，依此類推。 *PRIOR* 一元運算子，用於評估階層中目前資料列之父資料列的運算式。 ## 範例 下列範例是 CONNECT BY 查詢，此查詢會傳回直接或間接向 John 報告的員工人數，不超過 4 個層級。 ``` SELECT id, name, manager_id FROM employee WHERE LEVEL < 4 START WITH name = 'John' CONNECT BY PRIOR id = manager_id; ``` 以下為查詢結果。 ``` id name manager_id ------+----------+-------------- 101 John 100 102 Jorge 101 103 Kwaku 101 110 Liu 101 201 Sofía 102 106 Mateo 102 110 Nikki 103 104 Paulo 103 105 Richard 103 120 Saanvi 104 200 Shirley 104 205 Zhang 104 ``` 此範例的資料表定義： ``` CREATE TABLE employee ( id INT, name VARCHAR(20), manager_id INT ); ``` 以下是插入到資料表中的資料列。 ``` INSERT INTO employee(id, name, manager_id) VALUES (100, 'Carlos', null), (101, 'John', 100), (102, 'Jorge', 101), (103, 'Kwaku', 101), (110, 'Liu', 101), (106, 'Mateo', 102), (110, 'Nikki', 103), (104, 'Paulo', 103), (105, 'Richard', 103), (120, 'Saanvi', 104), (200, 'Shirley', 104), (201, 'Sofía', 102), (205, 'Zhang', 104); ``` 以下是 John 的部門組織結構圖。 ![\[John 的部門組織結構圖。\]](http://docs.aws.amazon.com/zh_tw/redshift/latest/dg/images/org-chart.png) # 子查詢範例 下列範例顯示將子查詢納入 SELECT 查詢的不同方式。請參閱 [JOIN 範例](r_Join_examples.md)，了解另一個使用子查詢的範例。 ## SELECT 清單子查詢 以下範例包含 SELECT 清單中的子查詢。此子查詢為*純量*：它只會傳回一個資料欄和一個值，該值會在從外部查詢傳回的每個資料列的結果中重複出現。查詢會比較子查詢運算的 Q1SALES 值與 2008 年另兩季 (2 和 3) 的銷售數字，如外部查詢所定義。 ``` select qtr, sum(pricepaid) as qtrsales, (select sum(pricepaid) from sales join date on sales.dateid=date.dateid where qtr='1' and year=2008) as q1sales from sales join date on sales.dateid=date.dateid where qtr in('2','3') and year=2008 group by qtr order by qtr; qtr | qtrsales | q1sales -------+-------------+------------- 2 | 30560050.00 | 24742065.00 3 | 31170237.00 | 24742065.00 (2 rows) ``` ## WHERE 子句子查詢 以下範例包含 WHERE 子句中的資料表子查詢。此子查詢會產生多個資料列。在此情況下，資料列只會包含一個資料欄，但資料表子查詢可包含多個資料欄和資料列，就像任何其他資料表一樣。 查詢會尋找票券銷售量最高的前 10 名賣家。前 10 名清單受到子查詢的限制，會移除居住在有售票場地之城市的使用者。此查詢可透過不同的方式撰寫；例如，子查詢可重寫為主查詢內的聯結。 ``` select firstname, lastname, city, max(qtysold) as maxsold from users join sales on users.userid=sales.sellerid where users.city not in(select venuecity from venue) group by firstname, lastname, city order by maxsold desc, city desc limit 10; firstname | lastname | city | maxsold -----------+-----------+----------------+--------- Noah | Guerrero | Worcester | 8 Isadora | Moss | Winooski | 8 Kieran | Harrison | Westminster | 8 Heidi | Davis | Warwick | 8 Sara | Anthony | Waco | 8 Bree | Buck | Valdez | 8 Evangeline | Sampson | Trenton | 8 Kendall | Keith | Stillwater | 8 Bertha | Bishop | Stevens Point | 8 Patricia | Anderson | South Portland | 8 (10 rows) ``` ## WITH 子句子查詢 請參閱 [WITH 子句](r_WITH_clause.md)。 # 相互關聯子查詢 以下範例包含 WHERE 子句中的*相互關聯子查詢*；這類子查詢的資料欄與外部查詢產生的資料欄之間包含一項或多項相互關聯。在此情況下，相互關聯為 `where s.listid=l.listid`。針對外部查詢產生的每個資料列，子查詢會執行以限定或取消限定資料列。 ``` select salesid, listid, sum(pricepaid) from sales s where qtysold= (select max(numtickets) from listing l where s.listid=l.listid) group by 1,2 order by 1,2 limit 5; salesid | listid | sum --------+--------+---------- 27 | 28 | 111.00 81 | 103 | 181.00 142 | 149 | 240.00 146 | 152 | 231.00 194 | 210 | 144.00 (5 rows) ``` ## 不支援的相互關聯子查詢模式 查詢規劃器會使用一種查詢重寫方法，稱為子查詢解除相互關聯，在 MPP 環境中最佳化數種相互關聯子查詢模式以供執行。有幾種類型的相互關聯子查詢採用了 Amazon Redshift 無法解除相互關聯也不支援的模式。包含下列相互關聯參考的查詢會傳回錯誤： + 略過查詢區塊的相互關聯參考，也稱為「略過層級相互關聯參考」。例如，在下列查詢中，包含相互關聯參考的區塊和略過的區塊會以 NOT EXISTS 述詞連接： ``` select event.eventname from event where not exists (select * from listing where not exists (select * from sales where event.eventid=sales.eventid)); ``` 在此案例中略過的區塊是對 LISTING 資料表的子查詢。相互關聯參考會將 EVENT 和 SALES 資料表相互關聯。 + 來自子查詢的相互關聯參考，它是外部查詢中 ON 子句的一部分： ``` select * from category left join event on category.catid=event.catid and eventid = (select max(eventid) from sales where sales.eventid=event.eventid); ``` ON 子句包含從子查詢中 SALES 對外部查詢中 EVENT 的相互關聯參考。 + Null 敏感的相互關聯會參考 Amazon Redshift 系統資料表。例如： ``` select attrelid from stv_locks sl, pg_attribute where sl.table_id=pg_attribute.attrelid and 1 not in (select 1 from pg_opclass where sl.lock_owner = opcowner); ``` + 來自子查詢內的相互關聯參考，當中包含視窗函數。 ``` select listid, qtysold from sales s where qtysold not in (select sum(numtickets) over() from listing l where s.listid=l.listid); ``` + GROUP BY 資料欄中對相互關聯子查詢結果的參考。例如： ``` select listing.listid, (select count (sales.listid) from sales where sales.listid=listing.listid) as list from listing group by list, listing.listid; ``` + 子查詢中使用彙整函數和 GROUP BY 子句的相互關聯參考，會透過 IN 述詞連接至外部查詢。(此限制不會套用至 MIN 和 MAX 彙整函數)。例如： ``` select * from listing where listid in (select sum(qtysold) from sales where numtickets>4 group by salesid); ```