WITH 子句 - Amazon Redshift
語法參數使用須知遞迴 CTE範例範例：遞迴 CTE

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

WITH 子句

WITH 子句是選用的子句，位於查詢中的 SELECT 前面。WITH 子句會定義一個或多個 common_table_expressions。每個通用資料表運算式 (CTE) 都會定義一個暫存資料表，與檢視定義類似。您可以在 FROM 子句中參考這些暫存資料表。這些資料表僅會在其所屬的查詢執行時使用。WITH 子句中的每個 CTE 都會指定資料表名稱、選用的資料欄名稱清單，以及判斷值為資料表的查詢表達式 (SELECT 陳述式)。當您在定義暫存資料表的相同查詢運算式的 FROM 子句中參考暫存資料表名稱時，CTE 是遞迴的。

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

語法

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

其中 common_table_expression 可以是非遞迴或遞迴的。以下是非遞迴形式：

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

以下是 common_table_expression 的遞迴形式：

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

參數

RECURSIVE

將查詢識別為遞迴 CTE 的關鍵字。如果 WITH 子句中定義的 common_table_expression 是遞迴的，則需要此關鍵字。即使 WITH 子句包含多個遞迴 CTE，您也只能指定一次 RECURSIVE 關鍵字 (緊接在 WITH 關鍵字之後)。一般而言，遞迴 CTE 是具有兩個部分的 UNION ALL 子查詢。

common_table_expression

定義可在 FROM 子句 中參照的暫存資料表，且僅在執行該資料表所屬的查詢期間使用此暫存資料表。

CTE_table_name

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

column_name

WITH 子句子查詢的輸出資料欄名稱清單，以逗號分隔。指定的資料欄名稱數目必須等於或少於子查詢所定義的資料欄數目。對於非遞迴的 CTE 而言，column_name 子句是選擇性的。對於遞迴 CTE，column_name 清單是必要的。

query

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

recursive_query

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

  • 第一個 SELECT 子查詢沒有對相同 CTE_table_name 的遞迴參考。這會傳回一個結果集，也就是遞迴的初始種子。此部分稱為初始成員或種子成員。

  • 第二個 SELECT 子查詢會在其 FROM 子句中參考相同的 CTE_table_name。這就是所謂的遞迴成員。recursive_query 包含一個 WHERE 條件來結束 recursive_query。

使用須知

您可以在下列 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。

另一個常見的用途是多層級材料表，像是產品由許多元件組成，而每個元件本身也包含其他元件或次要組件時。

請務必在遞迴查詢的第二個 SELECT 子查詢中加入 WHERE 子句，以限制遞迴的深度。如需範例，請參閱範例：遞迴 CTE。否則，會發生類似以下內容的錯誤：

  • 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_query 成員不能包含排序依據或限制子句。

範例

下列範例顯示包含 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 個層級。

--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 的部門組織結構圖。