WITH 子句 - Amazon Redshift

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

WITH 子句

WITH 子句是選用的子句,位於查詢中的 SELECT 前面。WITH 子句會定義一個或多個公用表表達式。每個公用資料表達式 (CTE) 會定義一個臨時資料表,類似檢視定義。您可以在 FROM 子句中引用這些臨時表。它們僅在他們所屬的查詢運行時使用。WITH 子句中的每個 CTE 都會指定資料表名稱、選用的資料欄名稱清單,以及判斷值為資料表的查詢表達式 (SELECT 陳述式)。當您在定義它的同一查詢表達式的 FROM 子句中引用臨時表名時,CTE 是遞歸的。

WITH 子句子查詢是定義資料表時較有效率的方式,可在執行單一查詢的過程中使用。在所有任何情況下,於 SELECT 陳述式的本體中使用子查詢都可產生相同的結果,但 WITH 子句子查詢對於寫入和讀取來說可能較為簡單。參考多次的 WITH 子句子查詢會盡可能最佳化為通用子表達式;也就是說,或許可以評估 WITH 子查詢一次並重複使用其結果 (請注意,通用子表達式不限於 WITH 子句中所定義者)。

Syntax (語法)

[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]

哪裡公用表達式可以是非遞歸或遞歸的。以下是非遞歸形式:

CTE_table_name [ ( column_name [, ...] ) ] AS ( query )

以下是遞歸形式公用表達式

CTE_table_name (column_name [, ...] ) AS ( recursive_query )

參數

RECURSIVE

將查詢標識為遞歸 CTE 的關鍵字。此關鍵字是必需的,如果有公用表達式在 WITH 子句中定義的是遞歸的。即使 WITH 子句包含多個遞歸 CTE,也只能在 WITH 關鍵字之後指定一次遞歸關鍵字。一般來説,遞歸 CTE 是一個由兩個部分組成的 UNION ALL 子查詢。

公用表達式

定義一個臨時表,您可以在FROM 子句,並於執行其所屬的查詢時使用。

CTE_表名稱

此臨時資料表的唯一名稱會定義 WITH 子句子查詢的結果。您無法在單一 WITH 子句內使用重複的名稱。每個子查詢都必須有可在 FROM 子句 中參考的資料表名稱。

column_name

WITH 子句子查詢的輸出資料欄名稱列表,以逗號分隔。指定的資料欄名稱數目必須等於或少於子查詢所定義的資料欄數目。對於非遞歸的 CTE,column_name子句是選用的。對於遞歸 CTE,column_name列表是必需的。

query

Amazon Redshift 支援的任何 SELECT 查詢。請參閱 SELECT

遞歸查詢

由兩個 SELECT 子查詢組成的 UNION ALL 查詢:

  • 第一個 SELECT 子查詢沒有對相同的CTE_表名稱。它返回一個結果集,該結果集是遞歸的初始種子。此部分稱為初始成員或種子成員。

  • 第二個 SELECT 子查詢引用相同的CTE_表名稱在其 FROM 子句中。這稱為遞歸成員。所以此遞歸查詢包含一個 WHERE 條件來結束遞歸查詢

使用須知

您可以在下列 SQL 陳述式中使用 WITH 子句:

  • SELECT

  • SELECT INTO

  • CREATE TABLE AS

  • CREATE VIEW

  • DECLARE

  • EXPLAIN

  • INSERT INTO...SELECT

  • PREPARE

  • 更新(在 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 子句。

遞迴一般資料表表達式

遞歸公用表表達式 (CTE)是一個引用自身的 CTE。遞歸 CTE 在查詢分層數據時非常有用,例如顯示員工和經理之間報告關係的組織圖。請參閱 範例:遞回 CTE

另一個常見用途是多層物料清單,當產品由多個組件組成,而每個元件本身也由其它元件或子裝配件組成時。

確保通過在遞歸查詢的第二個 SELECT 子查詢中包含 WHERE 子句來限制遞歸深度。如需範例,請參閱 範例:遞回 CTE。否則,可能會發生類似下列內容的錯誤:

  • Recursive CTE out of working buffers.

  • Exceeded recursive CTE max rows limit.

您可以指定對遞歸 CTE 結果的排序順序和限制。您可以在遞歸 CTE 的最終結果中包含分組依據和不同的選項。

您無法在子查詢內指定 WITH 遞歸子句。所以此遞歸查詢成員不能包含訂單依據或限制子句。

範例

下列範例顯示包含 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_SALES 和 TOP_VENUES。第二個 WITH 查詢資料表會從第一個資料表選取。接著主查詢區塊的 WHERE 子句會包含限制 TOP_VENUES 資料表的子查詢。

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 個級別以下。

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 from employee e, john_org j where e.manager_id = j.id and level < 4 ) select 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

下列為此範例的資料表定義。

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

以下是約翰部門的組織結構圖。