DENGAN klausa

Klausa WITH adalah klausa opsional yang mendahului daftar SELECT dalam kueri. Klausa WITH mendefinisikan satu atau lebih common_table_expressions. Setiap ekspresi tabel umum (CTE) mendefinisikan tabel sementara, yang mirip dengan definisi tampilan. Anda dapat mereferensikan tabel sementara ini di klausa FROM. Mereka hanya digunakan saat kueri milik mereka berjalan. Setiap CTE dalam klausa WITH menentukan nama tabel, daftar opsional nama kolom, dan ekspresi kueri yang mengevaluasi tabel (pernyataan SELECT).

Dengan subquery klausa adalah cara yang efisien untuk mendefinisikan tabel yang dapat digunakan selama eksekusi query tunggal. Dalam semua kasus, hasil yang sama dapat dicapai dengan menggunakan subquery di bagian utama pernyataan SELECT, tetapi dengan subquery klausa mungkin lebih mudah untuk ditulis dan dibaca. Jika memungkinkan, subkueri klausa WITH yang direferensikan beberapa kali dioptimalkan sebagai subexpressions umum; yaitu, dimungkinkan untuk mengevaluasi subquery WITH sekali dan menggunakan kembali hasilnya. (Perhatikan bahwa subexpressions umum tidak terbatas pada yang didefinisikan dalam klausa WITH.)

Sintaks

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

dimana common_table_expression bisa non-rekursif. Berikut ini adalah bentuk non-rekursif:

CTE_table_name AS ( query )

Parameter

common_table_expression

Mendefinisikan tabel sementara yang dapat Anda referensikan di Klausa FROM dan hanya digunakan selama eksekusi kueri yang dimilikinya.

CTE_TABLE_NAME

Nama unik untuk tabel sementara yang mendefinisikan hasil subquery klausa WITH. Anda tidak dapat menggunakan nama duplikat dalam satu klausa WITH. Setiap subquery harus diberi nama tabel yang dapat direferensikan di. Klausa FROM

kueri

Setiap kueri SELECT yang AWS Clean Rooms mendukung. Lihat SELECT.

Catatan penggunaan

Anda dapat menggunakan klausa WITH dalam pernyataan SQL berikut:

• PILIH, DENGAN, UNION, INTERSECT, dan KECUALI

Jika klausa FROM dari kueri yang berisi klausa WITH tidak mereferensikan salah satu tabel yang ditentukan oleh klausa WITH, klausa WITH diabaikan dan kueri berjalan seperti biasa.

Sebuah tabel yang didefinisikan oleh subquery klausa WITH dapat direferensikan hanya dalam lingkup kueri SELECT bahwa klausa WITH dimulai. Misalnya, Anda dapat mereferensikan tabel tersebut dalam klausa FROM dari subquery dalam daftar SELECT, klausa WHERE, atau HAVING. Anda tidak dapat menggunakan klausa WITH dalam subquery dan mereferensikan tabelnya di klausa FROM dari kueri utama atau subquery lainnya. Pola kueri ini menghasilkan pesan kesalahan formulir relation table_name doesn't exist untuk tabel klausa WITH.

Anda tidak dapat menentukan klausa WITH lain di dalam subquery klausa WITH.

Anda tidak dapat meneruskan referensi ke tabel yang ditentukan oleh subkueri klausa WITH. Misalnya, query berikut mengembalikan kesalahan karena referensi forward ke tabel W2 dalam definisi tabel W1:

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

Contoh-contoh

Contoh berikut menunjukkan kasus yang paling sederhana dari query yang berisi klausa WITH. Query WITH bernama VENUECOPY memilih semua baris dari tabel VENUE. Kueri utama pada gilirannya memilih semua baris dari VENUECOPY. Tabel VENUECOPY hanya ada selama durasi kueri ini.

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)

Contoh berikut menunjukkan klausa WITH yang menghasilkan dua tabel, bernama VENUE_SALES dan TOP_VENUES. Tabel WITH query kedua memilih dari yang pertama. Pada gilirannya, klausa WHERE dari blok kueri utama berisi subquery yang membatasi tabel 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)

Dua contoh berikut menunjukkan aturan untuk ruang lingkup referensi tabel berdasarkan subquery klausa WITH. Kueri pertama berjalan, tetapi yang kedua gagal dengan kesalahan yang diharapkan. Kueri pertama memiliki subquery klausa WITH di dalam daftar SELECT dari kueri utama. Tabel yang ditentukan oleh klausa WITH (HOLIDAYS) direferensikan dalam klausa FROM subquery dalam daftar SELECT:

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)

Kueri kedua gagal karena mencoba mereferensikan tabel HOLIDAYS di kueri utama serta dalam subquery daftar SELECT. Referensi kueri utama berada di luar cakupan.

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