WITH-Klausel

Eine WITH-Klausel ist eine optionale Klausel, die der SELECT-Liste in einer Abfrage vorangeht. Die WITH-Klausel definiert einen oder mehrere allgemeine Tabellenausdrücke (CTE). Jeder allgemeine Tabellenausdruck (CTE) definiert eine temporäre Tabelle, die einer Ansichtdefinition ähnelt. Sie können diese temporären Tabellen in der FROM-Klausel referenzieren. Sie werden nur verwendet, während die Abfrage, zu der sie gehören, ausgeführt wird. Jede CTE in der WITH-Klausel gibt einen Tabellennamen, eine optionale Liste von Spaltennamen und einen Abfrageausdruck an, der in eine Tabelle evaluiert wird (eine SELECT-Anweisung). Wenn Sie den Namen der temporären Tabelle in der FROM-Klausel desselben Abfrageausdrucks referenzieren, der ihn definiert, ist der CTE rekursiv.

Unterabfragen mit einer WITH-Klausel sind eine effiziente Art, Tabellen zu definieren, die während der Ausführung einer einzelnen Abfrage verwendet werden können. In allen Fällen können dieselben Ergebnisse erzielt werden, indem im Hauptteil der SELECT-Anweisung Unterabfragen verwendet werden. Unterabfragen mit WITH-Klauseln können jedoch leichter geschrieben und gelesen werden. Wenn möglich, werden Unterabfragen mit WITH-Klauseln, die mehrmals referenziert werden, als gemeinsame Unterausdrücke optimiert. Das bedeutet, dass es möglich sein kann, eine WITH-Unterabfrage einmal zu evaluieren und die Ergebnisse wiederzuverwenden. (Beachten Sie, dass gemeinsame Unterausdrücke nicht auf diejenigen begrenzt sind, die in der WITH-Klausel definiert sind.)

Syntax

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

Der allgemeine Tabellenausdruck where kann entweder nicht rekursiv oder rekursiv sein. Dies ist die nicht-rekursive Form:

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

Dies ist die rekursive Form des allgemeinen Tabellenausdrucks:

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

Parameter

RECURSIVE

Schlüsselwort, das die Abfrage als rekursiven CTE identifiziert. Dieses Schlüsselwort ist erforderlich, wenn ein in der WITH-Klausel definierter allgemeiner Tabellenausdruck rekursiv ist. Sie können das Schlüsselwort RECURSIVE nur einmal angeben, unmittelbar nach dem WITH-Schlüsselwort, selbst wenn die WITH-Klausel mehrere rekursive CTEs enthält. Im Allgemeinen ist ein rekursiver CTE eine UNION ALL-Unterabfrage mit zwei Teilen.

common_table_expression

Definiert eine temporäre Tabelle, auf die Sie in der FROM-Klausel verweisen können und die nur während der Ausführung der Abfrage verwendet wird, zu der sie gehört.

CTE_table_name

Ein eindeutiger Name für eine temporäre Tabelle, die die Ergebnisse einer Unterabfrage mit WITH-Klausel definiert. Sie können in einer einzelnen WITH-Klausel keine duplizierten Namen verwenden. Jede Unterabfrage muss einen Tabellennamen erhalten, der in der referenziert werden kann FROM-Klausel.

column_name

Eine Liste von Ausgabespaltennamen für die Unterabfrage der WITH-Klausel, kommagetrennt. Die Anzahl der angegebenen Spaltennamen muss größer als oder gleich der Anzahl der Spalten sein, die von der Unterabfrage definiert wird. Bei einem CTE, das nicht rekursiv ist, ist die column_name-Klausel optional. Für einen rekursiven CTE ist die column_name-Liste erforderlich.

query

Alle SELECT-Abfragen, die Amazon Redshift unterstützt. Siehe SELECT.

recursive_query

Eine UNION ALL-Abfrage, die aus zwei SELECT-Unterabfragen besteht:

• Die erste SELECT-Unterabfrage hat keinen rekursiven Verweis auf denselben CTE_table_name. Sie gibt eine Ergebnismenge zurück, die den Ausgangspunkt der Rekursion bildet. Dieses Teil wird als erstes Element oder Ausgangselement bezeichnet.

• Die zweite SELECT-Unterabfrage verweist in ihrer FROM-Klausel auf denselben CTE_table_name. Dies wird als rekursives Mitglied bezeichnet. Die recursive_query enthält eine WHERE-Bedingung, um die recursive_query zu beenden.

Nutzungshinweise

Sie können in den folgenden SQL-Anweisungen eine WITH-Klausel verwenden:

• SELECT

• SELECT INTO

• CREATE TABLE AS

• CREATE VIEW

• DECLARE

• EXPLAIN

• INSERT INTO...SELECT

• PREPARE

• UPDATE (innerhalb einer WHERE-Klausel-Unterabfrage. Sie können keinen rekursiven CTE in der Unterabfrage definieren. Der rekursive CTE muss der UPDATE-Klausel vorausgehen).

• DELETE

Wenn die FROM-Klausel einer Abfrage, die eine WITH-Klausel enthält, keine der Tabellen referenziert, die von der WITH-Klausel definiert werden, wird die WITH-Klausel ignoriert, und die Abfrage wird wie normal ausgeführt.

Eine Tabelle, die von einer Unterabfrage mit WITH-Klausel definiert ist, kann nur im Bereich der SELECT-Abfrage referenziert werden, die die WITH-Klausel beginnt. Sie können beispielsweise eine solche Tabelle in der FROM-Klausel einer Unterabfrage in der SELECT-Liste, in einer WHERE-Klausel oder in einer HAVING-Klausel referenzieren. Sie können eine WITH-Klausel nicht in einer Unterabfrage verwenden und ihre Tabelle in der FROM-Klausel der Hauptabfrage oder einer anderen Unterabfrage referenzieren. Dieses Abfragemuster führt zu einer Fehlermeldung der Art relation table_name doesn't exist für die Tabelle der WITH-Klausel.

Sie können innerhalb einer Unterabfrage mit WITH-Klausel keine weitere WITH-Klausel angeben.

Sie können keine Vorausreferenzen auf Tabellen erstellen, die durch Unterabfragen mit WITH-Klauseln definiert werden. Die folgende Abfrage gibt beispielsweise aufgrund der Vorausreferenz auf die Tabelle W2 in der Definition der Tabelle W1 einen Fehler zurück:

with w1 as (select * from w2), w2 as (select * from w1)
select * from sales;
ERROR:  relation "w2" does not exist

Eine Unterabfrage mit WITH-Klausel darf nicht aus einer SELECT INTO-Anweisung bestehen. Sie können jedoch eine WITH-Klausel in einer SELECT-Anweisung verwenden.

Rekursive allgemeine Tabellenausdrücke

Eine rekursiver allgemeiner Tabellenausdruck (CTE) ist ein CTE, der sich selbst referenziert. Ein rekursiver CTE ist nützlich für die Abfrage hierarchischer Daten, wie z. B. Organigramme, die Hierarchien zwischen Mitarbeitern und Führungskräften zeigen. Siehe Beispiel: Rekursiver CTE.

Eine weitere häufige Anwendung ist eine hierarchisch aufgebaute Stückliste, wenn ein Produkt aus vielen Komponenten besteht und jede Komponente selbst auch aus anderen Komponenten oder Unterbaugruppen besteht.

Achten Sie darauf, die Rekursionstiefe zu begrenzen, indem Sie eine WHERE-Klausel in die zweite SELECT-Unterabfrage der rekursiven Abfrage aufnehmen. Ein Beispiel finden Sie unter Beispiel: Rekursiver CTE. Andernfalls kann ein Fehler wie der folgende auftreten:

• 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.

Anmerkung

max_recursion_rows ist ein Parameter, der die maximale Anzahl von Zeilen festlegt, die ein rekursiver CTE zurückgeben kann, um endlose Rekursionsschleifen zu verhindern. Wir raten davon ab, diesen Wert in einen höheren Wert als den Standardwert zu ändern. Dadurch wird verhindert, dass aufgrund von Problemen mit endlosen Rekursionen in Ihren Abfragen übermäßig viel Speicherplatz in Ihrem Cluster beansprucht wird.

Sie können eine Sortierreihenfolge und ein Limit für das Ergebnis des rekursiven CTE angeben. Sie können die group by- und distinct-Optionen auf das Endergebnis des rekursiven CTE anwenden.

Sie können keine WITH RECURSIVE-Klausel innerhalb einer Unterabfrage angeben. Das recursive_query-Mitglied kann keine order by- oder limit-Klausel enthalten.

Beispiele

Im folgenden Beispiel wird der einfachste mögliche Fall einer Abfrage gezeigt, die eine WITH-Klausel enthält. Die WITH-Abfrage namens VENUECOPY wählt alle Zeilen aus der Tabelle VENUE aus. Die Hauptabfrage wählt anschließend alle Zeilen aus VENUECOPY aus. Die Tabelle VENUECOPY besteht nur für die Dauer dieser Abfrage.

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)

Im folgenden Beispiel wird eine WITH-Klausel gezeigt, die zwei Tabellen namens VENUE_SALES und TOP_VENUES erstellt. Die zweite WITH-Abfragetabelle wählt aus der ersten aus. Die WHERE-Klausel des Hauptabfrageblocks enthält eine Unterabfrage, die die Tabelle TOP_VENUES einschränkt.

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)

In den folgenden beiden Beispielen werden die Regeln für den Bereich der Tabellenreferenzen auf der Basis von Unterabfragen mit WITH-Klausel gezeigt. Die erste Abfrage wird ausgeführt. Die zweite Abfrage schlägt jedoch mit einem erwarteten Fehler fehl. Die erste Abfrage enthält eine Unterabfrage mit WITH-Klausel innerhalb der SELECT-Liste der Hauptabfrage. Die von der WITH-Klausel definierte Tabelle (HOLIDAYS) wird in der FROM-Klausel der Unterabfrage in der SELECT-Liste referenziert:

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)

Die zweite Abfrage schlägt fehl, weil sie versucht, die Tabelle HOLIDAYS in der Hauptabfrage und in der Unterabfrage der SELECT-Liste zu referenzieren. Die Referenzen der Hauptabfrage liegen außerhalb des Bereichs.

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

Beispiel: Rekursiver CTE

Im Folgenden finden Sie ein Beispiel für einen rekursiven CTE, der die Mitarbeiter zurückgibt, die direkt oder indirekt an John berichten. Die rekursive Abfrage enthält eine WHERE-Klausel, um die Rekursionstiefe auf weniger als 4 Ebenen zu begrenzen.

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

Nachfolgend das Ergebnis der Abfrage.

    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

Im Folgenden finden Sie ein Organigramm für Johns Abteilung.