WITH 子句 - Amazon Redshift
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 Amazon Web Services 服务入门

WITH 子句

WITH 子句是一个可选子句,该子句在查询中位于 SELECT 列表之前。WITH 子句定义一个或多个 common_table_expressions。每个公用表表达式 (CTE) 均定义一个临时表,它与视图定义类似。您可以在 FROM 子句中引用这些临时表。它们仅在它们所属的查询运行时使用。WITH 子句中的每个子 CTE 均指定一个表名、一个可选的列名称列表以及一个计算结果为表的查询表达式(SELECT 语句)。当您在定义临时表的同一查询表达式的 FROM 子句中引用临时表名时,CTE 是递归的。

WITH 子句子查询是定义可在单个查询的执行过程中使用的表的有效方式。在所有情况下,在 SELECT 语句的主体中使用子查询可获得相同的结果,不过 WITH 子句子查询可能在编写和阅读方面更加简单。如果可能,会将已引用多次的 WITH 子句子查询优化为常用子表达式;即,可以计算 WITH 子查询一次并重用其结果。(请注意,常用子表达式不只是限于 WITH 子句中定义的子表达式。)

Syntax

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

Parameters

RECURSIVE

将查询标识为递归 CTE 的关键词。如果 WITH 子句中定义的任何 common_table_expression 是递归的,则需要此关键词。即使 WITH 子句包含多个递归 CTE,您也只能紧跟 WITH 关键词之后指定一次 RECURSIVE 关键词。通常,递归 CTE 是一个包含两个部分的 UNION ALL 子查询。

common_table_expression

定义一个您可以在 FROM 子句 中引用并且仅在执行其所属的查询期间使用的临时表。

CTE_table_name

临时表的唯一名称,该临时表用于定义 WITH 子句子查询的结果。不能在单个 WITH 子句中使用重复名称。必须为每个子查询提供一个可在 FROM 子句中引用的表名。

column_name

WITH 子句子查询的输出列名称的列表(用逗号分隔)。指定的列名数目必须等于或小于子查询定义的列数。对于非递归的 CTE,column_name 子句是可选的。对于递归的 CTE,column_name 列表是必需的。

query

Amazon Redshif 支持的任何 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

如果包含 WITH 子句的查询的 FROM 子句未引用 WITH 子句所定义的任何表,则将忽略 WITH 子句,并且查询将正常执行。

WITH 子句子查询所定义的表只能在 WITH 子句开始的 SELECT 查询范围内引用。例如,可以在 SELECT 列表的子查询的 FROM 子句、WHERE 子句或 HAVING 子句中引用这样的表。不能在子查询中使用 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

另一个常见用途是多级物料清单,当产品由多个组件组成并且每个组件本身也包含其它组件或子装配件时。

通过在递归查询的第二个 SSELECT 子查询中包含 WHERE 子句来确保限制递归的深度。有关示例,请参阅示例:递归 CTE。否则,可能会出现类似于以下内容的错误:

  • Recursive CTE out of working buffers.

  • Exceeded recursive CTE max rows limit.

您可以指定递归 CTE 结果的排序顺序和限制。您可以在递归 CTE 的最终结果中包含分组依据和不同选项。

不能在子查询中指定 WITH RECURSIVE 子句。recursive_query 成员不能包含排序依据或限制子句。

Examples

以下示例说明了包含 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 子句子查询。SELECT 列表中的子查询的 FROM 子句中将引用 WITH 子句定义的表 (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 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);

以下是 John 所在部门的组织结构图。