Amazon Redshift tidak akan lagi mendukung pembuatan Python UDFs baru mulai Patch 198. Python yang ada UDFs akan terus berfungsi hingga 30 Juni 2026. Untuk informasi lebih lanjut, lihat [posting blog](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/).
Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
# SELECT
Mengembalikan baris dari tabel, tampilan, dan fungsi yang ditentukan pengguna.
**catatan**
Ukuran maksimum untuk satu pernyataan SQL adalah 16 MB.
## Sintaksis
```
[ WITH with_subquery [, ...] ]
SELECT
[ TOP number | [ ALL | DISTINCT ]
* | expression [ AS output_name ] [, ...] ]
[ EXCLUDE column_list ]
[ FROM table_reference [, ...] ]
[ WHERE condition ]
[ [ START WITH expression ] CONNECT BY expression ]
[ GROUP BY ALL | expression [, ...] ]
[ HAVING condition ]
[ QUALIFY condition ]
[ { UNION | ALL | INTERSECT | EXCEPT | MINUS } query ]
[ ORDER BY expression [ ASC | DESC ] ]
[ LIMIT { number | ALL } ]
[ OFFSET start ]
```
**Topics**
+ [Sintaksis](#r_SELECT_synopsis-synopsis)
+ [DENGAN klausa](r_WITH_clause.md)
+ [PILIH daftar](r_SELECT_list.md)
+ [KECUALIKAN column\$1list](r_EXCLUDE_list.md)
+ [Klausa FROM](r_FROM_clause30.md)
+ [Klausa WHERE](r_WHERE_clause.md)
+ [Klausa GROUP BY](r_GROUP_BY_clause.md)
+ [Klausa HAVING](r_HAVING_clause.md)
+ [Klausul KUALIFIKASI](r_QUALIFY_clause.md)
+ [UNION, INTERSECT, dan KECUALI](r_UNION.md)
+ [Klausa ORDER BY](r_ORDER_BY_clause.md)
+ [CONNECT BY klausa](r_CONNECT_BY_clause.md)
+ [Contoh subquery](r_Subquery_examples.md)
+ [Subquery yang berkorelasi](r_correlated_subqueries.md)
# DENGAN klausa
Klausa WITH adalah klausa opsional yang mendahului daftar SELECT dalam kueri. *Klausa WITH mendefinisikan satu atau lebih common\$1table\$1expressions.* 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). Saat Anda mereferensikan nama tabel sementara dalam klausa FROM dari ekspresi kueri yang sama yang mendefinisikannya, CTE bersifat rekursif.
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.)
## Sintaksis
```
[ WITH [RECURSIVE] common_table_expression [, common_table_expression , ...] ]
```
dimana *common\$1table\$1expression* dapat berupa non-rekursif atau rekursif. Berikut ini adalah bentuk non-rekursif:
```
CTE_table_name [ ( column_name [, ...] ) ] AS ( query )
```
Berikut ini adalah bentuk rekursif *common\$1table\$1expression*:
```
CTE_table_name (column_name [, ...] ) AS ( recursive_query )
```
## Parameter
REKURSIF
Kata kunci yang mengidentifikasi kueri sebagai CTE rekursif. Kata kunci ini diperlukan jika *common\$1table\$1expression* yang didefinisikan dalam klausa WITH bersifat rekursif. Anda hanya dapat menentukan kata kunci RECURSIVE sekali, segera mengikuti kata kunci WITH, bahkan ketika klausa WITH berisi beberapa rekursif. CTEs Secara umum, CTE rekursif adalah subquery UNION ALL dengan dua bagian.
*common\$1table\$1expression*
Mendefinisikan tabel sementara yang dapat Anda referensikan di [Klausa FROM](r_FROM_clause30.md) dan hanya digunakan selama eksekusi kueri yang dimilikinya.
*CTE\$1TABLE\$1NAME*
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](r_FROM_clause30.md)
*column\$1name*
Daftar nama kolom output untuk subquery klausa WITH, dipisahkan dengan koma. Jumlah nama kolom yang ditentukan harus sama dengan atau kurang dari jumlah kolom yang ditentukan oleh subquery. Untuk CTE yang non-rekursif, klausa *column\$1name* adalah opsional. Untuk CTE rekursif, daftar *column\$1name* diperlukan.
*query*
Kueri SELECT apa pun yang didukung Amazon Redshift. Lihat [SELECT](r_SELECT_synopsis.md).
*recursive\$1query*
Kueri UNION ALL yang terdiri dari dua subquery SELECT:
+ *Subquery SELECT pertama tidak memiliki referensi rekursif ke CTE\$1TABLE\$1NAME yang sama.* Ia mengembalikan set hasil yang merupakan benih awal rekursi. Bagian ini disebut anggota awal atau anggota benih.
+ Subquery SELECT kedua mereferensikan *CTE\$1TABLE\$1NAME* yang sama dalam klausa FROM. Ini disebut anggota rekursif. *Recursive\$1query* *berisi kondisi WHERE untuk mengakhiri recursive\$1query.*
## Catatan penggunaan
Anda dapat menggunakan klausa WITH dalam pernyataan SQL berikut:
+ SELECT
+ PILIH KE DALAM
+ CREATE TABLE AS
+ CREATE VIEW
+ MENYATAKAN
+ EXPLAIN
+ MASUKKAN KE... PILIH
+ MEMPERSIAPKAN
+ UPDATE (dalam subquery klausa WHERE. Anda tidak dapat mendefinisikan CTE rekursif di subquery. CTE rekursif harus mendahului klausa UPDATE.)
+ DELETE
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
```
Subquery klausa WITH mungkin tidak terdiri dari pernyataan SELECT INTO; Namun, Anda dapat menggunakan klausa WITH dalam pernyataan SELECT INTO.
## Ekspresi tabel umum rekursif
*Ekspresi tabel umum rekursif (CTE) adalah CTE* yang mereferensikan dirinya sendiri. CTE rekursif berguna dalam kueri data hierarkis, seperti bagan organisasi yang menunjukkan hubungan pelaporan antara karyawan dan manajer. Lihat [Contoh: CTE rekursif](#r_WITH_clause-recursive-cte-example).
Penggunaan umum lainnya adalah tagihan bahan bertingkat, ketika suatu produk terdiri dari banyak komponen dan setiap komponen itu sendiri juga terdiri dari komponen atau subrakitan lain.
Pastikan untuk membatasi kedalaman rekursi dengan menyertakan klausa WHERE di subquery SELECT kedua dari kueri rekursif. Sebagai contoh, lihat [Contoh: CTE rekursif](#r_WITH_clause-recursive-cte-example). Jika tidak, kesalahan dapat terjadi serupa dengan yang berikut:
+ `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.`
**catatan**
`max_recursion_rows`adalah parameter yang mengatur jumlah maksimum baris yang dapat dikembalikan oleh CTE rekursif untuk mencegah loop rekursi tak terbatas. Kami merekomendasikan untuk tidak mengubah ini ke nilai yang lebih besar daripada default. Ini mencegah masalah rekursi tak terbatas dalam kueri Anda mengambil ruang berlebihan di cluster Anda.
Anda dapat menentukan urutan pengurutan dan membatasi hasil CTE rekursif. Anda dapat menyertakan opsi grup demi dan berbeda pada hasil akhir CTE rekursif.
Anda tidak dapat menentukan klausa WITH RECURSIVE di dalam subquery. Anggota *recursive\$1query* tidak dapat menyertakan klausa order by atau limit.
## 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\$1SALES dan TOP\$1VENUES. Tabel WITH query kedua memilih dari yang pertama. Pada gilirannya, klausa WHERE dari blok kueri utama berisi subquery yang membatasi tabel TOP\$1VENUES.
```
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
```
## Contoh: CTE rekursif
Berikut ini adalah contoh CTE rekursif yang mengembalikan karyawan yang melapor secara langsung atau tidak langsung kepada John. Kueri rekursif berisi klausa WHERE untuk membatasi kedalaman rekursi hingga kurang dari 4 level.
```
--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;
```
Berikut ini adalah hasil dari query.
```
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
```
Berikut ini adalah bagan organisasi untuk departemen John.
![\[Diagram bagan organisasi untuk departemen John.\]](http://docs.aws.amazon.com/id_id/redshift/latest/dg/images/org-chart.png)
# PILIH daftar
**Topics**
+ [Sintaksis](#r_SELECT_list-synopsis)
+ [Parameter](#r_SELECT_list-parameters)
+ [Catatan penggunaan](#r_SELECT_list_usage_notes)
+ [Contoh](#r_SELECT_list-examples)
Daftar SELECT menamai kolom, fungsi, dan ekspresi yang ingin Anda kembalikan dari kueri. Daftar ini mewakili output kueri.
Untuk informasi selengkapnya tentang fungsi SQL, lihat[Referensi fungsi SQL](c_SQL_functions.md). Untuk informasi selengkapnya tentang ekspresi, lihat[Ekspresi bersyarat](c_conditional_expressions.md).
## Sintaksis
```
SELECT
[ TOP number ]
[ ALL | DISTINCT ] * | expression [ AS column_alias ] [, ...]
```
## Parameter
*Nomor* TOP
TOP mengambil integer positif sebagai argumennya, yang mendefinisikan jumlah baris yang dikembalikan ke klien. Perilaku dengan klausa TOP sama dengan perilaku dengan klausa LIMIT. Jumlah baris yang dikembalikan adalah tetap, tetapi himpunan baris tidak. Untuk mengembalikan satu set baris yang konsisten, gunakan TOP atau LIMIT bersama dengan klausa ORDER BY.
SEMUA
Kata kunci redundan yang mendefinisikan perilaku default jika Anda tidak menentukan DISTINCT. `SELECT ALL *`berarti sama dengan `SELECT *` (pilih semua baris untuk semua kolom dan pertahankan duplikat).
DISTINCT
Opsi yang menghilangkan baris duplikat dari set hasil, berdasarkan nilai yang cocok dalam satu atau beberapa kolom.
Jika aplikasi Anda mengizinkan kunci asing atau kunci utama yang tidak valid, itu dapat menyebabkan kueri mengembalikan hasil yang salah. Misalnya, kueri SELECT DISTINCT mungkin menampilkan baris duplikat jika kolom kunci primer tidak berisi semua nilai unik. Untuk informasi selengkapnya, lihat [Mendefinisikan batasan tabel](https://docs.aws.amazon.com/redshift/latest/dg/t_Defining_constraints.html).
\$1 (tanda bintang)
Mengembalikan seluruh isi tabel (semua kolom dan semua baris).
*ekspresi*
Ekspresi yang terbentuk dari satu atau lebih kolom yang ada di tabel yang direferensikan oleh kueri. Ekspresi dapat berisi fungsi SQL. Contoh:
```
avg(datediff(day, listtime, saletime))
```
AS *column\$1alias*
Nama sementara untuk kolom yang digunakan dalam set hasil akhir. Kata kunci AS adalah opsional. Contoh:
```
avg(datediff(day, listtime, saletime)) as avgwait
```
Jika Anda tidak menentukan alias untuk ekspresi yang bukan nama kolom sederhana, set hasil akan menerapkan nama default ke kolom tersebut.
Alias dikenali tepat setelah didefinisikan dalam daftar target. Anda dapat menggunakan alias dalam ekspresi lain yang ditentukan setelahnya dalam daftar target yang sama. Contoh berikut menggambarkan hal ini.
```
select clicks / impressions as probability, round(100 * probability, 1) as percentage from raw_data;
```
Manfaat referensi alias lateral adalah Anda tidak perlu mengulangi ekspresi alias saat membangun ekspresi yang lebih kompleks dalam daftar target yang sama. Saat Amazon Redshift mem-parsing jenis referensi ini, itu hanya sebaris dengan alias yang ditentukan sebelumnya. Jika ada kolom dengan nama yang sama yang didefinisikan dalam `FROM` klausa sebagai ekspresi alias sebelumnya, kolom dalam `FROM` klausa akan diprioritaskan. Misalnya, dalam kueri di atas jika ada kolom bernama 'probabilitas' dalam tabel raw\$1data, 'probabilitas' dalam ekspresi kedua dalam daftar target mengacu pada kolom itu alih-alih nama alias 'probabilitas'.
## Catatan penggunaan
TOP adalah ekstensi SQL; ini memberikan alternatif untuk perilaku LIMIT. Anda tidak dapat menggunakan TOP dan LIMIT dalam kueri yang sama.
## Contoh
Contoh berikut mengembalikan 10 baris dari tabel PENJUALAN. Meskipun kueri menggunakan klausa TOP, ia masih mengembalikan sekumpulan baris yang tidak dapat diprediksi karena tidak ada klausa ORDER BY yang ditentukan,
```
select top 10 *
from sales;
```
Kueri berikut secara fungsional setara, tetapi menggunakan klausa LIMIT alih-alih klausa TOP:
```
select *
from sales
limit 10;
```
Contoh berikut mengembalikan 10 baris pertama dari tabel PENJUALAN menggunakan klausa TOP, diurutkan oleh kolom QTYSOLD dalam urutan menurun.
```
select top 10 qtysold, sellerid
from sales
order by qtysold desc, sellerid;
qtysold | sellerid
--------+----------
8 | 518
8 | 520
8 | 574
8 | 718
8 | 868
8 | 2663
8 | 3396
8 | 3726
8 | 5250
8 | 6216
(10 rows)
```
Contoh berikut mengembalikan dua nilai QTYSOLD dan SELLERID pertama dari tabel PENJUALAN, diurutkan oleh kolom QTYSOLD:
```
select top 2 qtysold, sellerid
from sales
order by qtysold desc, sellerid;
qtysold | sellerid
--------+----------
8 | 518
8 | 520
(2 rows)
```
Contoh berikut menunjukkan daftar kelompok kategori yang berbeda dari tabel CATEGORY:
```
select distinct catgroup from category
order by 1;
catgroup
----------
Concerts
Shows
Sports
(3 rows)
--the same query, run without distinct
select catgroup from category
order by 1;
catgroup
----------
Concerts
Concerts
Concerts
Shows
Shows
Shows
Sports
Sports
Sports
Sports
Sports
(11 rows)
```
Contoh berikut mengembalikan kumpulan angka minggu yang berbeda untuk Desember 2008. Tanpa klausa DISTINCT, pernyataan akan mengembalikan 31 baris, atau satu untuk setiap hari dalam sebulan.
```
select distinct week, month, year
from date
where month='DEC' and year=2008
order by 1, 2, 3;
week | month | year
-----+-------+------
49 | DEC | 2008
50 | DEC | 2008
51 | DEC | 2008
52 | DEC | 2008
53 | DEC | 2008
(5 rows)
```
# KECUALIKAN column\$1list
EXCLUDE column\$1list menamai kolom yang dikecualikan dari hasil kueri. Menggunakan opsi EXCLUDE sangat membantu ketika hanya sebagian kolom yang perlu dikecualikan dari tabel *lebar*, yang merupakan tabel yang berisi banyak kolom.
**Topics**
+ [Sintaksis](#r_EXCLUDE_list-synopsis)
+ [Parameter](#r_EXCLUDE_list-parameters)
+ [Contoh](#r_EXCLUDE_list-examples)
## Sintaksis
```
EXCLUDE column_list
```
## Parameter
*column\$1list*
Daftar dipisahkan koma dari satu atau beberapa nama kolom yang ada dalam tabel yang direferensikan oleh kueri. *Column\$1list* secara opsional dapat diapit dalam tanda kurung. Hanya nama kolom yang didukung dalam daftar pengecualian nama kolom, bukan ekspresi (seperti`upper(col1)`) atau tanda bintang (\$1).
```
column-name, ... | ( column-name, ... )
```
Contoh:
```
SELECT * EXCLUDE col1, col2 FROM tablea;
```
```
SELECT * EXCLUDE (col1, col2) FROM tablea;
```
## Contoh
Contoh berikut menggunakan tabel PENJUALAN yang berisi kolom: salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, dan saletime. Untuk informasi selengkapnya tentang tabel PENJUALAN, lihat[Database sampel](c_sampledb.md).
Contoh berikut mengembalikan baris dari tabel PENJUALAN, tetapi tidak termasuk kolom SALETIME.
```
SELECT * EXCLUDE saletime FROM sales;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission
--------+---------+----------+---------+---------+---------+----------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3
...
```
Contoh berikut mengembalikan baris dari tabel PENJUALAN, tetapi tidak termasuk kolom QTYSOLD dan SALETIME.
```
SELECT * EXCLUDE (qtysold, saletime) FROM sales;
salesid | listid | sellerid | buyerid | eventid | dateid | pricepaid | commission
--------+---------+----------+---------+---------+---------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 482 | 72.3
...
```
Contoh berikut membuat tampilan yang mengembalikan baris dari tabel PENJUALAN, tetapi tidak termasuk kolom SALETIME.
```
CREATE VIEW sales_view AS SELECT * EXCLUDE saletime FROM sales;
SELECT * FROM sales_view;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission
--------+---------+----------+---------+---------+---------+----------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3
...
```
Contoh berikut hanya memilih kolom yang tidak dikecualikan ke dalam tabel temp.
```
SELECT * EXCLUDE saletime INTO TEMP temp_sales FROM sales;
SELECT * FROM temp_sales;
salesid | listid | sellerid | buyerid | eventid | dateid | qtysold | pricepaid | commission
--------+---------+----------+---------+---------+---------+----------+------------+-----------
150314 | 173969 | 48680 | 816 | 8762 | 1827 | 2 | 688 | 103.2
8325 | 8942 | 23600 | 1078 | 2557 | 1828 | 5 | 525 | 78.75
46807 | 52711 | 34388 | 1047 | 2046 | 1828 | 2 | 482 | 72.3
...
```
# Klausa FROM
Klausa FROM dalam kueri mencantumkan referensi tabel (tabel, tampilan, dan subkueri) tempat data dipilih. Jika beberapa referensi tabel terdaftar, tabel harus digabungkan, menggunakan sintaks yang sesuai baik dalam klausa FROM atau klausa WHERE. Jika tidak ada kriteria gabungan yang ditentukan, sistem memproses kueri sebagai cross-join (produk Cartesian).
**Topics**
+ [Sintaksis](#r_FROM_clause30-synopsis)
+ [Parameter](#r_FROM_clause30-parameters)
+ [Catatan penggunaan](#r_FROM_clause_usage_notes)
+ [Contoh PIVOT dan UNPIVOT](r_FROM_clause-pivot-unpivot-examples.md)
+ [JOIN contoh](r_Join_examples.md)
+ [Contoh UNNEST](r_FROM_clause-unnest-examples.md)
## Sintaksis
```
FROM table_reference [, ...]
```
di mana *table\$1reference* adalah salah satu dari berikut ini:
```
with_subquery_table_name [ table_alias ]
table_name [ * ] [ table_alias ]
( subquery ) [ table_alias ]
table_reference [ NATURAL ] join_type table_reference
[ ON join_condition | USING ( join_column [, ...] ) ]
table_reference join_type super_expression
[ ON join_condition ]
table_reference PIVOT (
aggregate(expr) [ [ AS ] aggregate_alias ]
FOR column_name IN ( expression [ AS ] in_alias [, ...] )
) [ table_alias ]
table_reference UNPIVOT [ INCLUDE NULLS | EXCLUDE NULLS ] (
value_column_name
FOR name_column_name IN ( column_reference [ [ AS ]
in_alias ] [, ...] )
) [ table_alias ]
UNPIVOT expression AS value_alias [ AT attribute_alias ]
( super_expression.attribute_name ) AS value_alias [ AT index_alias ]
UNNEST ( column_reference )
[AS] table_alias ( unnested_column_name )
UNNEST ( column_reference ) WITH OFFSET
[AS] table_alias ( unnested_column_name, [offset_column_name] )
```
*Table\$1alias* opsional dapat digunakan untuk memberikan nama sementara ke tabel dan referensi tabel kompleks dan, jika diinginkan, kolomnya juga, seperti berikut ini:
```
[ AS ] alias [ ( column_alias [, ...] ) ]
```
## Parameter
*dengan\$1subquery\$1table\$1name*
Sebuah tabel didefinisikan oleh subquery di. [DENGAN klausa](r_WITH_clause.md)
*table\$1name*
Nama tabel atau tampilan.
*alias*
Nama alternatif sementara untuk tabel atau tampilan. Alias harus disediakan untuk tabel yang berasal dari subquery. Dalam referensi tabel lainnya, alias bersifat opsional. Kata kunci AS selalu opsional. Alias tabel menyediakan pintasan yang nyaman untuk mengidentifikasi tabel di bagian lain dari kueri, seperti klausa WHERE. Contoh:
```
select * from sales s, listing l
where s.listid=l.listid
```
*column\$1alias*
Nama alternatif sementara untuk kolom dalam tabel atau tampilan.
*subkueri*
Ekspresi kueri yang mengevaluasi ke tabel. Tabel hanya ada selama durasi kueri dan biasanya diberi nama atau *alias*. Namun, alias tidak diperlukan. Anda juga dapat menentukan nama kolom untuk tabel yang berasal dari subquery. Penamaan alias kolom penting saat Anda ingin menggabungkan hasil subkueri ke tabel lain dan saat Anda ingin memilih atau membatasi kolom tersebut di tempat lain dalam kueri.
Subquery mungkin berisi klausa ORDER BY, tetapi klausa ini mungkin tidak berpengaruh jika klausa LIMIT atau OFFSET tidak juga ditentukan.
ALAMI
Mendefinisikan gabungan yang secara otomatis menggunakan semua pasangan kolom bernama identik dalam dua tabel sebagai kolom bergabung. Tidak diperlukan kondisi gabungan eksplisit. Misalnya, jika tabel CATEGORY dan EVENT keduanya memiliki kolom bernama CATID, gabungan alami dari tabel tersebut adalah gabungan di atas kolom CATID mereka.
Jika gabungan NATURAL ditentukan tetapi tidak ada pasangan kolom bernama identik yang ada di tabel yang akan digabungkan, kueri default ke cross-join.
*join\$1type*
Tentukan salah satu jenis join berikut:
+ [BATIN] BERGABUNG
+ KIRI [LUAR] BERGABUNG
+ KANAN [LUAR] BERGABUNG
+ PENUH [LUAR] BERGABUNG
+ CROSS JOIN
Cross-join adalah gabungan yang tidak memenuhi syarat; mereka mengembalikan produk Cartesian dari dua tabel.
Gabungan dalam dan luar adalah gabungan yang memenuhi syarat. Mereka memenuhi syarat baik secara implisit (dalam gabungan alami); dengan sintaks ON atau USING dalam klausa FROM; atau dengan kondisi klausa WHERE.
Gabungan bagian dalam mengembalikan baris yang cocok saja, berdasarkan kondisi gabungan atau daftar kolom yang bergabung. Gabungan luar mengembalikan semua baris yang akan dikembalikan oleh gabungan dalam yang setara ditambah baris yang tidak cocok dari tabel “kiri”, tabel “kanan”, atau kedua tabel. Tabel kiri adalah tabel yang terdaftar pertama, dan tabel kanan adalah tabel kedua yang terdaftar. Baris yang tidak cocok berisi nilai NULL untuk mengisi celah di kolom output.
PADA *join\$1condition*
Jenis spesifikasi gabungan di mana kolom bergabung dinyatakan sebagai kondisi yang mengikuti kata kunci ON. Contoh:
```
sales join listing
on sales.listid=listing.listid and sales.eventid=listing.eventid
```
MENGGUNAKAN (*join\$1column* [,...])
Jenis spesifikasi gabungan di mana kolom bergabung tercantum dalam tanda kurung. Jika beberapa kolom bergabung ditentukan, mereka dibatasi oleh koma. Kata kunci USING harus mendahului daftar. Contoh:
```
sales join listing
using (listid,eventid)
```
POROS
Memutar output dari baris ke kolom, untuk tujuan mewakili data tabular dalam format yang mudah dibaca. Output direpresentasikan secara horizontal di beberapa kolom. PIVOT mirip dengan kueri GROUP BY dengan agregasi, menggunakan ekspresi agregat untuk menentukan format output. Namun, berbeda dengan GROUP BY, hasilnya dikembalikan dalam kolom, bukan baris.
Untuk contoh yang menunjukkan cara melakukan kueri dengan PIVOT dan UNPIVOT, lihat. [Contoh PIVOT dan UNPIVOT](r_FROM_clause-pivot-unpivot-examples.md)
UNPIVOT
*Memutar kolom menjadi baris dengan UNPIVOT* - Operator mengubah kolom hasil, dari tabel input atau hasil kueri, menjadi baris, untuk membuat output lebih mudah dibaca. UNPIVOT menggabungkan data kolom masukannya menjadi dua kolom hasil: kolom nama dan kolom nilai. Kolom nama berisi nama kolom dari input, sebagai entri baris. Kolom nilai berisi nilai-nilai dari kolom masukan, seperti hasil agregasi. Misalnya, jumlah item dalam berbagai kategori.
*Object unpivoting with UNPIVOT (SUPER)* - Anda dapat melakukan unpivoting objek, di mana *ekspresi adalah ekspresi* SUPER mengacu pada item klausa FROM lainnya. Untuk informasi selengkapnya, lihat [Objek tidak berputar](query-super.md#unpivoting). Ini juga memiliki contoh yang menunjukkan cara menanyakan data semi-terstruktur, seperti data yang diformat JSON.
*super\$1ekspresi*
Ekspresi SUPER yang valid. Amazon Redshift mengembalikan satu baris untuk setiap nilai dalam atribut yang ditentukan. Untuk informasi selengkapnya tentang tipe data SUPER, lihat[Tipe SUPER](r_SUPER_type.md). Untuk informasi selengkapnya tentang nilai SUPER unnested, lihat. [Kueri yang tidak bersarang](query-super.md#unnest)
*atribut\$1nama*
Nama atribut dalam ekspresi SUPER.
*index\$1alias*
Alias untuk indeks yang menandakan posisi nilai dalam ekspresi SUPER.
UNNEST
Memperluas struktur bersarang, biasanya array SUPER, ke dalam kolom yang berisi elemen unnested. Untuk informasi selengkapnya tentang menghapus data SUPER, lihat. [Meminta data semi-terstruktur](query-super.md) Sebagai contoh, lihat [Contoh UNNEST](r_FROM_clause-unnest-examples.md).
*unnested\$1column\$1name*
Nama kolom yang berisi elemen unnested.
TIDAK BERSARANG... DENGAN OFFSET
Menambahkan kolom offset ke output unnested, dengan offset mewakili indeks berbasis nol dari setiap elemen dalam array. Varian ini berguna ketika Anda ingin melihat posisi elemen dalam array. Untuk informasi selengkapnya tentang menghapus data SUPER, lihat. [Meminta data semi-terstruktur](query-super.md) Sebagai contoh, lihat [Contoh UNNEST](r_FROM_clause-unnest-examples.md).
*offset\$1column\$1name*
Nama kustom untuk kolom offset yang memungkinkan Anda secara eksplisit menentukan bagaimana kolom indeks akan muncul di output. Parameter ini bersifat opsional. Secara default, nama kolom offset adalah`offset_col`.
## Catatan penggunaan
Kolom yang bergabung harus memiliki tipe data yang sebanding.
Gabungan ALAMI atau MENGGUNAKAN hanya mempertahankan satu dari setiap pasangan kolom penggabungan dalam kumpulan hasil perantara.
Gabungan dengan sintaks ON mempertahankan kedua kolom yang bergabung dalam kumpulan hasil perantara.
Lihat juga [DENGAN klausa](r_WITH_clause.md).
# Contoh PIVOT dan UNPIVOT
PIVOT dan UNPIVOT adalah parameter dalam klausa FROM yang memutar output kueri dari baris ke kolom dan kolom ke baris, masing-masing. Mereka mewakili hasil kueri tabel dalam format yang mudah dibaca. Contoh berikut menggunakan data uji dan kueri untuk menunjukkan cara menggunakannya.
Untuk informasi selengkapnya tentang parameter ini dan parameter lainnya, lihat [klausa FROM](https://docs.aws.amazon.com/redshift/latest/dg/r_FROM_clause30.html).
## Contoh PIVOT
Siapkan tabel sampel dan data dan gunakan untuk menjalankan contoh query berikutnya.
```
CREATE TABLE part (
partname varchar,
manufacturer varchar,
quality int,
price decimal(12, 2)
);
INSERT INTO part VALUES ('prop', 'local parts co', 2, 10.00);
INSERT INTO part VALUES ('prop', 'big parts co', NULL, 9.00);
INSERT INTO part VALUES ('prop', 'small parts co', 1, 12.00);
INSERT INTO part VALUES ('rudder', 'local parts co', 1, 2.50);
INSERT INTO part VALUES ('rudder', 'big parts co', 2, 3.75);
INSERT INTO part VALUES ('rudder', 'small parts co', NULL, 1.90);
INSERT INTO part VALUES ('wing', 'local parts co', NULL, 7.50);
INSERT INTO part VALUES ('wing', 'big parts co', 1, 15.20);
INSERT INTO part VALUES ('wing', 'small parts co', NULL, 11.80);
```
PIVOT aktif `partname` dengan `AVG` agregasi aktif. `price`
```
SELECT *
FROM (SELECT partname, price FROM part) PIVOT (
AVG(price) FOR partname IN ('prop', 'rudder', 'wing')
);
```
Hasil query dalam output berikut.
```
prop | rudder | wing
---------+----------+---------
10.33 | 2.71 | 11.50
```
Pada contoh sebelumnya, hasilnya diubah menjadi kolom. Contoh berikut menunjukkan `GROUP BY` kueri yang mengembalikan harga rata-rata dalam baris, bukan di kolom.
```
SELECT partname, avg(price)
FROM (SELECT partname, price FROM part)
WHERE partname IN ('prop', 'rudder', 'wing')
GROUP BY partname;
```
Hasil query dalam output berikut.
```
partname | avg
----------+-------
prop | 10.33
rudder | 2.71
wing | 11.50
```
`PIVOT`Contoh dengan `manufacturer` sebagai kolom implisit.
```
SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
count(*) FOR quality IN (1, 2, NULL)
);
```
Hasil query dalam output berikut.
```
manufacturer | 1 | 2 | null
-------------------+----+----+------
local parts co | 1 | 1 | 1
big parts co | 1 | 1 | 1
small parts co | 1 | 0 | 2
```
Kolom tabel masukan yang tidak direferensikan dalam `PIVOT` definisi ditambahkan secara implisit ke tabel hasil. Ini adalah kasus untuk `manufacturer` kolom pada contoh sebelumnya. Contoh ini juga menunjukkan bahwa `NULL` adalah nilai yang valid untuk `IN` operator.
`PIVOT`dalam contoh di atas mengembalikan informasi yang sama sebagai query berikut, yang meliputi`GROUP BY`. Perbedaannya adalah bahwa `PIVOT` mengembalikan nilai `0` untuk kolom `2` dan produsen`small parts co`. `GROUP BY`Kueri tidak berisi baris yang sesuai. Dalam kebanyakan kasus, `PIVOT` menyisipkan `NULL` jika baris tidak memiliki data input untuk kolom tertentu. Namun, agregat hitungan tidak kembali `NULL` dan `0` merupakan nilai default.
```
SELECT manufacturer, quality, count(*)
FROM (SELECT quality, manufacturer FROM part)
WHERE quality IN (1, 2) OR quality IS NULL
GROUP BY manufacturer, quality
ORDER BY manufacturer;
```
Hasil query dalam output berikut.
```
manufacturer | quality | count
---------------------+---------+-------
big parts co | | 1
big parts co | 2 | 1
big parts co | 1 | 1
local parts co | 2 | 1
local parts co | 1 | 1
local parts co | | 1
small parts co | 1 | 1
small parts co | | 2
```
Operator PIVOT menerima alias opsional pada ekspresi agregat dan pada setiap nilai untuk operator. `IN` Gunakan alias untuk menyesuaikan nama kolom. Jika tidak ada alias agregat, hanya alias `IN` daftar yang digunakan. Jika tidak, alias agregat ditambahkan ke nama kolom dengan garis bawah untuk memisahkan nama.
```
SELECT *
FROM (SELECT quality, manufacturer FROM part) PIVOT (
count(*) AS count FOR quality IN (1 AS high, 2 AS low, NULL AS na)
);
```
Hasil query dalam output berikut.
```
manufacturer | high_count | low_count | na_count
-------------------+-------------+-----------+----------
local parts co | 1 | 1 | 1
big parts co | 1 | 1 | 1
small parts co | 1 | 0 | 2
```
Siapkan tabel sampel dan data berikut dan gunakan untuk menjalankan contoh query berikutnya. Data tersebut menunjukkan tanggal pemesanan untuk koleksi hotel.
```
CREATE TABLE bookings (
booking_id int,
hotel_code char(8),
booking_date date,
price decimal(12, 2)
);
INSERT INTO bookings VALUES (1, 'FOREST_L', '02/01/2023', 75.12);
INSERT INTO bookings VALUES (2, 'FOREST_L', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (3, 'FOREST_L', '02/04/2023', 85.54);
INSERT INTO bookings VALUES (4, 'FOREST_L', '02/08/2023', 75.00);
INSERT INTO bookings VALUES (5, 'FOREST_L', '02/11/2023', 75.00);
INSERT INTO bookings VALUES (6, 'FOREST_L', '02/14/2023', 90.00);
INSERT INTO bookings VALUES (7, 'FOREST_L', '02/21/2023', 60.00);
INSERT INTO bookings VALUES (8, 'FOREST_L', '02/22/2023', 85.00);
INSERT INTO bookings VALUES (9, 'FOREST_L', '02/27/2023', 90.00);
INSERT INTO bookings VALUES (10, 'DESERT_S', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (11, 'DESERT_S', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (12, 'DESERT_S', '02/04/2023', 85.00);
INSERT INTO bookings VALUES (13, 'DESERT_S', '02/05/2023', 75.00);
INSERT INTO bookings VALUES (14, 'DESERT_S', '02/06/2023', 34.00);
INSERT INTO bookings VALUES (15, 'DESERT_S', '02/09/2023', 85.00);
INSERT INTO bookings VALUES (16, 'DESERT_S', '02/12/2023', 23.00);
INSERT INTO bookings VALUES (17, 'DESERT_S', '02/13/2023', 76.00);
INSERT INTO bookings VALUES (18, 'DESERT_S', '02/14/2023', 85.00);
INSERT INTO bookings VALUES (19, 'OCEAN_WV', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (20, 'OCEAN_WV', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (21, 'OCEAN_WV', '02/04/2023', 85.00);
INSERT INTO bookings VALUES (22, 'OCEAN_WV', '02/06/2023', 75.00);
INSERT INTO bookings VALUES (23, 'OCEAN_WV', '02/09/2023', 34.00);
INSERT INTO bookings VALUES (24, 'OCEAN_WV', '02/12/2023', 85.00);
INSERT INTO bookings VALUES (25, 'OCEAN_WV', '02/13/2023', 23.00);
INSERT INTO bookings VALUES (26, 'OCEAN_WV', '02/14/2023', 76.00);
INSERT INTO bookings VALUES (27, 'OCEAN_WV', '02/16/2023', 85.00);
INSERT INTO bookings VALUES (28, 'CITY_BLD', '02/01/2023', 98.00);
INSERT INTO bookings VALUES (29, 'CITY_BLD', '02/02/2023', 75.00);
INSERT INTO bookings VALUES (30, 'CITY_BLD', '02/04/2023', 85.00);
INSERT INTO bookings VALUES (31, 'CITY_BLD', '02/12/2023', 75.00);
INSERT INTO bookings VALUES (32, 'CITY_BLD', '02/13/2023', 34.00);
INSERT INTO bookings VALUES (33, 'CITY_BLD', '02/17/2023', 85.00);
INSERT INTO bookings VALUES (34, 'CITY_BLD', '02/22/2023', 23.00);
INSERT INTO bookings VALUES (35, 'CITY_BLD', '02/23/2023', 76.00);
INSERT INTO bookings VALUES (36, 'CITY_BLD', '02/24/2023', 85.00);
```
Dalam contoh kueri ini, catatan pemesanan dihitung untuk memberikan total untuk setiap minggu. Tanggal akhir untuk setiap minggu menjadi nama kolom.
```
SELECT * FROM
(SELECT
booking_id,
(date_trunc('week', booking_date::date) + '5 days'::interval)::date as enddate,
hotel_code AS "hotel code"
FROM bookings
) PIVOT (
count(booking_id) FOR enddate IN ('2023-02-04','2023-02-11','2023-02-18')
);
```
Hasil query dalam output berikut.
```
hotel code | 2023-02-04 | 2023-02-11 | 2023-02-18
------------+-------------+------------+----------
FOREST_L | 3 | 2 | 1
DESERT_S | 4 | 3 | 2
OCEAN_WV | 3 | 3 | 3
CITY_BLD | 3 | 1 | 2
```
Amazon Redshift tidak mendukung CROSSTAB untuk berputar di beberapa kolom. Tetapi Anda dapat mengubah data baris ke kolom, dengan cara yang mirip dengan agregasi dengan PIVOT, dengan kueri seperti berikut ini. Ini menggunakan data sampel pemesanan yang sama dengan contoh sebelumnya.
```
SELECT
booking_date,
MAX(CASE WHEN hotel_code = 'FOREST_L' THEN 'forest is booked' ELSE '' END) AS FOREST_L,
MAX(CASE WHEN hotel_code = 'DESERT_S' THEN 'desert is booked' ELSE '' END) AS DESERT_S,
MAX(CASE WHEN hotel_code = 'OCEAN_WV' THEN 'ocean is booked' ELSE '' END) AS OCEAN_WV
FROM bookings
GROUP BY booking_date
ORDER BY booking_date asc;
```
Contoh kueri menghasilkan tanggal pemesanan yang tercantum di sebelah frasa singkat yang menunjukkan hotel mana yang dipesan.
```
booking_date | forest_l | desert_s | ocean_wv
---------------+------------------+------------------+--------------------
2023-02-01 | forest is booked | desert is booked | ocean is booked
2023-02-02 | forest is booked | desert is booked | ocean is booked
2023-02-04 | forest is booked | desert is booked | ocean is booked
2023-02-05 | | desert is booked |
2023-02-06 | | desert is booked |
```
Berikut ini adalah catatan penggunaan untuk`PIVOT`:
+ `PIVOT`dapat diterapkan ke tabel, sub-query, dan ekspresi tabel umum ()CTEs. `PIVOT`tidak dapat diterapkan pada `JOIN` ekspresi, rekursif CTEs`PIVOT`, atau `UNPIVOT` ekspresi apa pun. Juga tidak didukung adalah ekspresi `SUPER` unnested dan tabel bersarang Redshift Spectrum.
+ `PIVOT`mendukung fungsi`COUNT`,`SUM`,`MIN`,`MAX`, dan `AVG` agregat.
+ Ekspresi `PIVOT` agregat harus berupa panggilan dari fungsi agregat yang didukung. Ekspresi kompleks di atas agregat tidak didukung. Argumen agregat tidak dapat berisi referensi ke tabel selain tabel `PIVOT` input. Referensi berkorelasi ke kueri induk juga tidak didukung. Argumen agregat mungkin berisi sub-kueri. Ini dapat dikorelasikan secara internal atau pada tabel `PIVOT` input.
+ Nilai `PIVOT IN` daftar tidak dapat berupa referensi kolom atau sub-kueri. Setiap nilai harus jenis yang kompatibel dengan referensi `FOR` kolom.
+ Jika nilai `IN` daftar tidak memiliki alias, `PIVOT` menghasilkan nama kolom default. Untuk `IN` nilai konstan seperti 'abc' atau 5 nama kolom default adalah konstanta itu sendiri. Untuk ekspresi kompleks apa pun, nama kolom adalah nama default Amazon Redshift standar seperti. `?column?`
## Contoh UNPIVOT
Siapkan data sampel dan gunakan untuk menjalankan contoh berikutnya.
```
CREATE TABLE count_by_color (quality varchar, red int, green int, blue int);
INSERT INTO count_by_color VALUES ('high', 15, 20, 7);
INSERT INTO count_by_color VALUES ('normal', 35, NULL, 40);
INSERT INTO count_by_color VALUES ('low', 10, 23, NULL);
```
`UNPIVOT`pada kolom input merah, hijau, dan biru.
```
SELECT *
FROM (SELECT red, green, blue FROM count_by_color) UNPIVOT (
cnt FOR color IN (red, green, blue)
);
```
Hasil query dalam output berikut.
```
color | cnt
-------+-----
red | 15
red | 35
red | 10
green | 20
green | 23
blue | 7
blue | 40
```
Secara default, `NULL` nilai di kolom input dilewati dan tidak menghasilkan baris hasil.
Contoh berikut menunjukkan `UNPIVOT` dengan`INCLUDE NULLS`.
```
SELECT *
FROM (
SELECT red, green, blue
FROM count_by_color
) UNPIVOT INCLUDE NULLS (
cnt FOR color IN (red, green, blue)
);
```
Berikut ini adalah output yang dihasilkan.
```
color | cnt
-------+-----
red | 15
red | 35
red | 10
green | 20
green |
green | 23
blue | 7
blue | 40
blue |
```
Jika `INCLUDING NULLS` parameter diatur, nilai `NULL` masukan menghasilkan baris hasil.
`The following query shows UNPIVOT`dengan `quality` sebagai kolom implisit.
```
SELECT *
FROM count_by_color UNPIVOT (
cnt FOR color IN (red, green, blue)
);
```
Hasil query dalam output berikut.
```
quality | color | cnt
---------+-------+-----
high | red | 15
normal | red | 35
low | red | 10
high | green | 20
low | green | 23
high | blue | 7
normal | blue | 40
```
Kolom tabel input yang tidak direferensikan dalam `UNPIVOT` definisi ditambahkan secara implisit ke tabel hasil. Dalam contoh, ini adalah kasus untuk `quality` kolom.
Contoh berikut menunjukkan `UNPIVOT` dengan alias untuk nilai-nilai dalam `IN` daftar.
```
SELECT *
FROM count_by_color UNPIVOT (
cnt FOR color IN (red AS r, green AS g, blue AS b)
);
```
Hasil query sebelumnya dalam output berikut.
```
quality | color | cnt
---------+-------+-----
high | r | 15
normal | r | 35
low | r | 10
high | g | 20
low | g | 23
high | b | 7
normal | b | 40
```
`UNPIVOT`Operator menerima alias opsional pada setiap nilai `IN` daftar. Setiap alias menyediakan kustomisasi data di setiap `value` kolom.
Berikut ini adalah catatan penggunaan untuk`UNPIVOT`.
+ `UNPIVOT`dapat diterapkan ke tabel, sub-query, dan ekspresi tabel umum ()CTEs. `UNPIVOT`tidak dapat diterapkan pada `JOIN` ekspresi, rekursif CTEs`PIVOT`, atau `UNPIVOT` ekspresi apa pun. Juga tidak didukung adalah ekspresi `SUPER` unnested dan tabel bersarang Redshift Spectrum.
+ `UNPIVOT IN`Daftar harus berisi hanya referensi kolom tabel masukan. Kolom `IN` daftar harus memiliki tipe umum yang semuanya kompatibel dengannya. Kolom `UNPIVOT` nilai memiliki tipe umum ini. Kolom `UNPIVOT` nama adalah tipe`VARCHAR`.
+ Jika nilai `IN` daftar tidak memiliki alias, `UNPIVOT` menggunakan nama kolom sebagai nilai default.
# JOIN contoh
Klausa SQL JOIN digunakan untuk menggabungkan data dari dua atau lebih tabel berdasarkan bidang umum. Hasilnya mungkin atau mungkin tidak berubah tergantung pada metode gabungan yang ditentukan. Untuk informasi selengkapnya tentang sintaks klausa JOIN, lihat. [Parameter](r_FROM_clause30.md#r_FROM_clause30-parameters)
Contoh berikut menggunakan data dari data `TICKIT` sampel. Untuk informasi selengkapnya tentang skema database, lihat[Database sampel](c_sampledb.md). Untuk mempelajari cara memuat data sampel, lihat [Memuat data](https://docs.aws.amazon.com/redshift/latest/gsg/rs-gsg-create-sample-db.html) di Panduan *Memulai Pergeseran Merah Amazon*.
Kueri berikut adalah gabungan dalam (tanpa kata kunci JOIN) antara tabel LISTING dan tabel PENJUALAN, di mana LISTID dari tabel LISTING adalah antara 1 dan 5. Kueri ini cocok dengan nilai kolom LISTID dalam tabel LISTING (tabel kiri) dan tabel PENJUALAN (tabel kanan). Hasilnya menunjukkan bahwa LISTID 1, 4, dan 5 sesuai dengan kriteria.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing, sales
where listing.listid = sales.listid
and listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
Kueri berikut adalah gabungan luar kiri. Gabungan luar kiri dan kanan mempertahankan nilai dari salah satu tabel yang digabungkan ketika tidak ada kecocokan yang ditemukan di tabel lainnya. Tabel kiri dan kanan adalah tabel pertama dan kedua yang tercantum dalam sintaks. Nilai NULL digunakan untuk mengisi “celah” di set hasil. Kueri ini cocok dengan nilai kolom LISTID dalam tabel LISTING (tabel kiri) dan tabel PENJUALAN (tabel kanan). Hasilnya menunjukkan bahwa LISTIDs 2 dan 3 tidak menghasilkan penjualan apa pun.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing left outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
2 | NULL | NULL
3 | NULL | NULL
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
Kueri berikut adalah gabungan luar kanan. Kueri ini cocok dengan nilai kolom LISTID dalam tabel LISTING (tabel kiri) dan tabel PENJUALAN (tabel kanan). Hasilnya menunjukkan bahwa LISTIDs 1, 4, dan 5 sesuai dengan kriteria.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing right outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
Kueri berikut adalah gabungan penuh. Gabungan penuh mempertahankan nilai dari tabel yang digabungkan ketika tidak ada kecocokan yang ditemukan di tabel lainnya. Tabel kiri dan kanan adalah tabel pertama dan kedua yang tercantum dalam sintaks. Nilai NULL digunakan untuk mengisi “celah” di set hasil. Kueri ini cocok dengan nilai kolom LISTID dalam tabel LISTING (tabel kiri) dan tabel PENJUALAN (tabel kanan). Hasilnya menunjukkan bahwa LISTIDs 2 dan 3 tidak menghasilkan penjualan apa pun.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
2 | NULL | NULL
3 | NULL | NULL
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
Kueri berikut adalah gabungan penuh. Kueri ini cocok dengan nilai kolom LISTID dalam tabel LISTING (tabel kiri) dan tabel PENJUALAN (tabel kanan). Hanya baris yang tidak menghasilkan penjualan apa pun (LISTIDs 2 dan 3) yang ada di hasil.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
and (listing.listid IS NULL or sales.listid IS NULL)
group by 1
order by 1;
listid | price | comm
-------+--------+--------
2 | NULL | NULL
3 | NULL | NULL
```
Contoh berikut adalah gabungan batin dengan klausa ON. Dalam hal ini, baris NULL tidak dikembalikan.
```
select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from sales join listing
on sales.listid=listing.listid and sales.eventid=listing.eventid
where listing.listid between 1 and 5
group by 1
order by 1;
listid | price | comm
-------+--------+--------
1 | 728.00 | 109.20
4 | 76.00 | 11.40
5 | 525.00 | 78.75
```
Kueri berikut adalah gabungan silang atau gabungan Cartesian dari tabel LISTING dan tabel PENJUALAN dengan predikat untuk membatasi hasil. Kueri ini cocok dengan nilai kolom LISTID dalam tabel PENJUALAN dan tabel LISTING untuk LISTIDs 1, 2, 3, 4, dan 5 di kedua tabel. Hasilnya menunjukkan bahwa 20 baris cocok dengan kriteria.
```
select sales.listid as sales_listid, listing.listid as listing_listid
from sales cross join listing
where sales.listid between 1 and 5
and listing.listid between 1 and 5
order by 1,2;
sales_listid | listing_listid
-------------+---------------
1 | 1
1 | 2
1 | 3
1 | 4
1 | 5
4 | 1
4 | 2
4 | 3
4 | 4
4 | 5
5 | 1
5 | 1
5 | 2
5 | 2
5 | 3
5 | 3
5 | 4
5 | 4
5 | 5
5 | 5
```
Contoh berikut adalah gabungan alami antara dua tabel. Dalam hal ini, kolom listid, sellerid, eventid, dan dateid memiliki nama dan tipe data yang identik di kedua tabel dan digunakan sebagai kolom gabungan. Hasilnya dibatasi hingga lima baris.
```
select listid, sellerid, eventid, dateid, numtickets
from listing natural join sales
order by 1
limit 5;
listid | sellerid | eventid | dateid | numtickets
-------+-----------+---------+--------+-----------
113 | 29704 | 4699 | 2075 | 22
115 | 39115 | 3513 | 2062 | 14
116 | 43314 | 8675 | 1910 | 28
118 | 6079 | 1611 | 1862 | 9
163 | 24880 | 8253 | 1888 | 14
```
Contoh berikut adalah gabungan antara dua tabel dengan klausa USING. Dalam hal ini, kolom listid dan eventid digunakan sebagai kolom gabungan. Hasilnya dibatasi hingga lima baris.
```
select listid, listing.sellerid, eventid, listing.dateid, numtickets
from listing join sales
using (listid, eventid)
order by 1
limit 5;
listid | sellerid | eventid | dateid | numtickets
-------+----------+---------+--------+-----------
1 | 36861 | 7872 | 1850 | 10
4 | 8117 | 4337 | 1970 | 8
5 | 1616 | 8647 | 1963 | 4
5 | 1616 | 8647 | 1963 | 4
6 | 47402 | 8240 | 2053 | 18
```
Kueri berikut adalah gabungan batin dari dua subquery dalam klausa FROM. Kueri menemukan jumlah tiket yang terjual dan tidak terjual untuk berbagai kategori acara (konser dan pertunjukan). Subquery klausa FROM adalah subquery *tabel*; mereka dapat mengembalikan beberapa kolom dan baris.
```
select catgroup1, sold, unsold
from
(select catgroup, sum(qtysold) as sold
from category c, event e, sales s
where c.catid = e.catid and e.eventid = s.eventid
group by catgroup) as a(catgroup1, sold)
join
(select catgroup, sum(numtickets)-sum(qtysold) as unsold
from category c, event e, sales s, listing l
where c.catid = e.catid and e.eventid = s.eventid
and s.listid = l.listid
group by catgroup) as b(catgroup2, unsold)
on a.catgroup1 = b.catgroup2
order by 1;
catgroup1 | sold | unsold
----------+--------+--------
Concerts | 195444 |1067199
Shows | 149905 | 817736
```
# Contoh UNNEST
UNNEST adalah parameter dalam klausa FROM yang memperluas data bersarang ke dalam kolom yang menyimpan elemen data yang tidak bersarang. Untuk informasi tentang data yang tidak bersarang, lihat. [Meminta data semi-terstruktur](query-super.md)
Pernyataan berikut membuat dan mengisi `orders` tabel, yang berisi `products` kolom yang berisi array produk. IDs Contoh di bagian ini menggunakan data sampel dalam tabel ini.
```
CREATE TABLE orders (
order_id INT,
products SUPER
);
-- Populate table
INSERT INTO orders VALUES
(1001, JSON_PARSE('[
{
"product_id": "P456",
"name": "Monitor",
"price": 299.99,
"quantity": 1,
"specs": {
"size": "27 inch",
"resolution": "4K"
}
}
]
')),
(1002, JSON_PARSE('
[
{
"product_id": "P567",
"name": "USB Cable",
"price": 9.99,
"quantity": 3
},
{
"product_id": "P678",
"name": "Headphones",
"price": 159.99,
"quantity": 1,
"specs": {
"type": "Wireless",
"battery_life": "20 hours"
}
}
]
'));
```
Berikut ini adalah beberapa contoh query unnesting dengan data sampel menggunakan sintaks PartiQL.
## Melepaskan array tanpa kolom OFFSET
Kueri berikut menghapus array SUPER di kolom produk, dengan setiap baris mewakili item dari urutan dalam. `order_id`
```
SELECT o.order_id, unnested_products.product
FROM orders o, UNNEST(o.products) AS unnested_products(product);
order_id | product
----------+-----------------------------------------------------------------------------------------------------------------------------
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}
1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3}
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}}
(3 rows)
```
Kueri berikut menemukan produk paling mahal di setiap pesanan.
```
SELECT o.order_id, MAX(unnested_products.product)
FROM orders o, UNNEST(o.products) AS unnested_products(product);
order_id | product
----------+-----------------------------------------------------------------------------------------------------------------------------
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}}
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}}
(2 rows)
```
## Melepaskan array dengan kolom OFFSET implisit
Kueri berikut menggunakan `UNNEST ... WITH OFFSET` parameter untuk menunjukkan posisi berbasis nol dari setiap produk dalam array urutannya.
```
SELECT o.order_id, up.product, up.offset_col
FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product);
order_id | product | offset_col
----------+-----------------------------------------------------------------------------------------------------------------------------+------------
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0
1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1
(3 rows)
```
Karena pernyataan tidak menentukan alias untuk kolom offset, Amazon Redshift secara default menamainya. `offset_col`
## Melepaskan array dengan kolom OFFSET eksplisit
Kueri berikut juga menggunakan `UNNEST ... WITH OFFSET` parameter untuk menampilkan produk dalam array pesanan mereka. Perbedaan dalam kueri ini dibandingkan dengan kueri pada contoh sebelumnya adalah bahwa ia secara eksplisit menamai kolom offset dengan alias. `idx`
```
SELECT o.order_id, up.product, up.idx
FROM orders o, UNNEST(o.products) WITH OFFSET AS up(product, idx);
order_id | product | idx
----------+-----------------------------------------------------------------------------------------------------------------------------+-----
1001 | {"product_id":"P456","name":"Monitor","price":299.99,"quantity":1,"specs":{"size":"27 inch","resolution":"4K"}} | 0
1002 | {"product_id":"P567","name":"USB Cable","price":9.99,"quantity":3} | 0
1002 | {"product_id":"P678","name":"Headphones","price":159.99,"quantity":1,"specs":{"type":"Wireless","battery_life":"20 hours"}} | 1
(3 rows)
```
# Klausa WHERE
Klausa WHERE berisi kondisi yang menggabungkan tabel atau menerapkan predikat ke kolom dalam tabel. Tabel dapat bergabung dalam dengan menggunakan sintaks yang sesuai baik dalam klausa WHERE atau klausa FROM. Kriteria gabungan luar harus ditentukan dalam klausa FROM.
## Sintaksis
```
[ WHERE condition ]
```
## *ketentuan*
Setiap kondisi pencarian dengan hasil Boolean, seperti kondisi gabungan atau predikat pada kolom tabel. Contoh berikut adalah ketentuan gabungan yang valid:
```
sales.listid=listing.listid
sales.listid<>listing.listid
```
Contoh berikut adalah kondisi yang valid pada kolom dalam tabel:
```
catgroup like 'S%'
venueseats between 20000 and 50000
eventname in('Jersey Boys','Spamalot')
year=2008
length(catdesc)>25
date_part(month, caldate)=6
```
Kondisi bisa sederhana atau kompleks; untuk kondisi kompleks, Anda dapat menggunakan tanda kurung untuk mengisolasi unit logis. Dalam contoh berikut, kondisi bergabung diapit oleh tanda kurung.
```
where (category.catid=event.catid) and category.catid in(6,7,8)
```
## Catatan penggunaan
Anda dapat menggunakan alias dalam klausa WHERE untuk referensi ekspresi daftar pilih.
Anda tidak dapat membatasi hasil fungsi agregat dalam klausa WHERE; gunakan klausa HAVING untuk tujuan ini.
Kolom yang dibatasi dalam klausa WHERE harus berasal dari referensi tabel dalam klausa FROM.
## Contoh
Kueri berikut menggunakan kombinasi batasan klausa WHERE yang berbeda, termasuk kondisi gabungan untuk tabel PENJUALAN dan EVENT, predikat pada kolom EVENTNAME, dan dua predikat pada kolom STARTTIME.
```
select eventname, starttime, pricepaid/qtysold as costperticket, qtysold
from sales, event
where sales.eventid = event.eventid
and eventname='Hannah Montana'
and date_part(quarter, starttime) in(1,2)
and date_part(year, starttime) = 2008
order by 3 desc, 4, 2, 1 limit 10;
eventname | starttime | costperticket | qtysold
----------------+---------------------+-------------------+---------
Hannah Montana | 2008-06-07 14:00:00 | 1706.00000000 | 2
Hannah Montana | 2008-05-01 19:00:00 | 1658.00000000 | 2
Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 1
Hannah Montana | 2008-06-07 14:00:00 | 1479.00000000 | 3
Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 1
Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 2
Hannah Montana | 2008-06-07 14:00:00 | 1163.00000000 | 4
Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 1
Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 2
Hannah Montana | 2008-05-01 19:00:00 | 497.00000000 | 4
(10 rows)
```
# Bagian luar Gaya Oracle bergabung dalam klausa WHERE
Untuk kompatibilitas Oracle, Amazon Redshift mendukung operator outer-join Oracle (\$1) dalam ketentuan gabungan klausa WHERE. Operator ini dimaksudkan untuk digunakan hanya dalam menentukan kondisi outer-join; jangan mencoba menggunakannya dalam konteks lain. Penggunaan lain dari operator ini secara diam-diam diabaikan dalam banyak kasus.
Gabungan luar mengembalikan semua baris yang akan dikembalikan oleh gabungan dalam yang setara, ditambah baris yang tidak cocok dari satu atau kedua tabel. Dalam klausa FROM, Anda dapat menentukan gabungan luar kiri, kanan, dan penuh. Dalam klausa WHERE, Anda dapat menentukan gabungan luar kiri dan kanan saja.
Untuk menggabungkan tabel luar TABLE1 dan TABLE2 dan mengembalikan baris yang tidak cocok dari TABLE1 (gabungan luar kiri), tentukan `TABLE1 LEFT OUTER JOIN TABLE2` dalam klausa FROM atau terapkan operator (\$1) ke semua kolom yang bergabung dari TABLE2 dalam klausa WHERE. Untuk semua baris TABLE1 yang tidak memiliki baris yang cocok TABLE2, hasil kueri berisi nol untuk setiap ekspresi daftar pilih yang berisi kolom dari. TABLE2
Untuk menghasilkan perilaku yang sama untuk semua baris yang tidak memiliki baris TABLE2 yang cocok TABLE1, tentukan `TABLE1 RIGHT OUTER JOIN TABLE2` dalam klausa FROM atau terapkan operator (\$1) ke semua kolom yang bergabung dari TABLE1 dalam klausa WHERE.
## Sintaks dasar
```
[ WHERE {
[ table1.column1 = table2.column1(+) ]
[ table1.column1(+) = table2.column1 ]
}
```
Kondisi pertama setara dengan:
```
from table1 left outer join table2
on table1.column1=table2.column1
```
Kondisi kedua setara dengan:
```
from table1 right outer join table2
on table1.column1=table2.column1
```
**catatan**
Sintaks yang ditampilkan di sini mencakup kasus sederhana dari equijoin atas satu pasang kolom yang bergabung. Namun, jenis kondisi perbandingan lainnya dan beberapa pasang kolom penggabungan juga valid.
Misalnya, klausa WHERE berikut mendefinisikan gabungan luar lebih dari dua pasang kolom. Operator (\$1) harus dilampirkan ke tabel yang sama dalam kedua kondisi:
```
where table1.col1 > table2.col1(+)
and table1.col2 = table2.col2(+)
```
## Catatan penggunaan
Jika memungkinkan, gunakan sintaks standar FROM klausa OUTER JOIN alih-alih operator (\$1) di klausa WHERE. Kueri yang berisi operator (\$1) tunduk pada aturan berikut:
+ Anda hanya dapat menggunakan operator (\$1) di klausa WHERE, dan hanya mengacu pada kolom dari tabel atau tampilan.
+ Anda tidak dapat menerapkan operator (\$1) ke ekspresi. Namun, ekspresi dapat berisi kolom yang menggunakan operator (\$1). Misalnya, kondisi bergabung berikut mengembalikan kesalahan sintaks:
```
event.eventid*10(+)=category.catid
```
Namun, kondisi bergabung berikut ini valid:
```
event.eventid(+)*10=category.catid
```
+ Anda tidak dapat menggunakan operator (\$1) di blok kueri yang juga berisi sintaks gabungan klausa FROM.
+ Jika dua tabel digabungkan dalam beberapa kondisi gabungan, Anda harus menggunakan operator (\$1) di semua atau tidak ada kondisi ini. Gabungan dengan gaya sintaks campuran berjalan sebagai gabungan dalam, tanpa peringatan.
+ Operator (\$1) tidak menghasilkan gabungan luar jika Anda menggabungkan tabel di kueri luar dengan tabel yang dihasilkan dari kueri dalam.
+ Untuk menggunakan operator (\$1) untuk menggabungkan tabel ke luar ke dirinya sendiri, Anda harus menentukan alias tabel dalam klausa FROM dan mereferensikannya dalam kondisi gabungan:
```
select count(*)
from event a, event b
where a.eventid(+)=b.catid;
count
-------
8798
(1 row)
```
+ Anda tidak dapat menggabungkan kondisi gabungan yang berisi operator (\$1) dengan kondisi OR atau kondisi IN. Contoh:
```
select count(*) from sales, listing
where sales.listid(+)=listing.listid or sales.salesid=0;
ERROR: Outer join operator (+) not allowed in operand of OR or IN.
```
+ Dalam klausa WHERE yang menggabungkan lebih dari dua tabel, operator (\$1) hanya dapat diterapkan sekali ke tabel tertentu. Dalam contoh berikut, tabel PENJUALAN tidak dapat direferensikan dengan operator (\$1) dalam dua gabungan berturut-turut.
```
select count(*) from sales, listing, event
where sales.listid(+)=listing.listid and sales.dateid(+)=date.dateid;
ERROR: A table may be outer joined to at most one other table.
```
+ Jika klausa WHERE kondisi outer-join membandingkan kolom dari TABLE2 dengan konstanta, terapkan operator (\$1) ke kolom. Jika Anda tidak menyertakan operator, baris gabungan luar dari TABLE1, yang berisi nol untuk kolom terbatas, akan dihilangkan. Lihat bagian Contoh di bawah ini.
## Contoh
Kueri gabungan berikut menentukan gabungan luar kiri tabel SALES dan LISTING di atas kolom LISTID mereka:
```
select count(*)
from sales, listing
where sales.listid = listing.listid(+);
count
--------
172456
(1 row)
```
Kueri setara berikut menghasilkan hasil yang sama tetapi menggunakan sintaks gabungan klausa FROM:
```
select count(*)
from sales left outer join listing on sales.listid = listing.listid;
count
--------
172456
(1 row)
```
Tabel PENJUALAN tidak berisi catatan untuk semua listing dalam tabel LISTING karena tidak semua listing menghasilkan penjualan. Kueri berikut menggabungkan bagian luar PENJUALAN dan LISTING dan mengembalikan baris dari LISTING bahkan ketika tabel PENJUALAN melaporkan tidak ada penjualan untuk ID daftar yang diberikan. Kolom PRICE dan COMM, yang berasal dari tabel SALES, berisi nol dalam set hasil untuk baris yang tidak cocok tersebut.
```
select listing.listid, sum(pricepaid) as price,
sum(commission) as comm
from listing, sales
where sales.listid(+) = listing.listid and listing.listid between 1 and 5
group by 1 order by 1;
listid | price | comm
--------+--------+--------
1 | 728.00 | 109.20
2 | |
3 | |
4 | 76.00 | 11.40
5 | 525.00 | 78.75
(5 rows)
```
Perhatikan bahwa ketika operator bergabung klausa WHERE digunakan, urutan tabel dalam klausa FROM tidak menjadi masalah.
Contoh kondisi gabungan luar yang lebih kompleks dalam klausa WHERE adalah kasus di mana kondisi terdiri dari perbandingan antara dua kolom tabel *dan* perbandingan dengan konstanta:
```
where category.catid=event.catid(+) and eventid(+)=796;
```
Perhatikan bahwa operator (\$1) digunakan di dua tempat: pertama dalam perbandingan kesetaraan antara tabel dan kedua dalam kondisi perbandingan untuk kolom EVENTID. Hasil dari sintaks ini adalah pelestarian baris terluar saat pembatasan EVENTID dievaluasi. Jika Anda menghapus operator (\$1) dari pembatasan EVENTID, kueri memperlakukan pembatasan ini sebagai filter, bukan sebagai bagian dari kondisi outer-join. Pada gilirannya, baris gabungan luar yang berisi nol untuk EVENTID dihilangkan dari kumpulan hasil.
Berikut adalah kueri lengkap yang menggambarkan perilaku ini:
```
select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid(+)=796;
catname | catgroup | eventid
-----------+----------+---------
Classical | Concerts |
Jazz | Concerts |
MLB | Sports |
MLS | Sports |
Musicals | Shows | 796
NBA | Sports |
NFL | Sports |
NHL | Sports |
Opera | Shows |
Plays | Shows |
Pop | Concerts |
(11 rows)
```
Kueri setara menggunakan sintaks klausa FROM adalah sebagai berikut:
```
select catname, catgroup, eventid
from category left join event
on category.catid=event.catid and eventid=796;
```
Jika Anda menghapus operator kedua (\$1) dari versi klausa WHERE dari kueri ini, ia hanya mengembalikan 1 baris (baris di mana`eventid=796`).
```
select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid=796;
catname | catgroup | eventid
-----------+----------+---------
Musicals | Shows | 796
(1 row)
```
# Klausa GROUP BY
Klausa GROUP BY mengidentifikasi kolom pengelompokan untuk kueri. Ini digunakan untuk mengelompokkan baris-baris tersebut dalam tabel yang memiliki nilai yang sama di semua kolom yang terdaftar. Urutan di mana kolom terdaftar tidak masalah. Hasilnya adalah menggabungkan setiap set baris yang memiliki nilai umum menjadi satu baris grup yang mewakili semua baris dalam grup. Gunakan GROUP BY untuk menghilangkan redundansi dalam output dan untuk menghitung agregat yang berlaku untuk grup. Kolom pengelompokan harus dideklarasikan saat kueri menghitung agregat dengan fungsi standar seperti SUM, AVG, dan COUNT. Untuk informasi selengkapnya, lihat [Fungsi agregat](c_Aggregate_Functions.md).
## Sintaks
```
[ GROUP BY expression [, ...] | ALL | aggregation_extension ]
```
di mana *aggregation\$1extension* adalah salah satu dari berikut ini:
```
GROUPING SETS ( () | aggregation_extension [, ...] ) |
ROLLUP ( expr [, ...] ) |
CUBE ( expr [, ...] )
```
## Parameter
*ekspresi*
Daftar kolom atau ekspresi harus cocok dengan daftar ekspresi non-agregat dalam daftar pilih kueri. Misalnya, pertimbangkan kueri sederhana berikut.
```
select listid, eventid, sum(pricepaid) as revenue,
count(qtysold) as numtix
from sales
group by listid, eventid
order by 3, 4, 2, 1
limit 5;
listid | eventid | revenue | numtix
-------+---------+---------+--------
89397 | 47 | 20.00 | 1
106590 | 76 | 20.00 | 1
124683 | 393 | 20.00 | 1
103037 | 403 | 20.00 | 1
147685 | 429 | 20.00 | 1
(5 rows)
```
Dalam kueri ini, daftar pilih terdiri dari dua ekspresi agregat. Yang pertama menggunakan fungsi SUM dan yang kedua menggunakan fungsi COUNT. Dua kolom yang tersisa, LISTID dan EVENTID, harus dinyatakan sebagai kolom pengelompokan.
Ekspresi dalam klausa GROUP BY juga dapat mereferensikan daftar pilih dengan menggunakan nomor urut. Misalnya, contoh sebelumnya dapat disingkat sebagai berikut.
```
select listid, eventid, sum(pricepaid) as revenue,
count(qtysold) as numtix
from sales
group by 1,2
order by 3, 4, 2, 1
limit 5;
listid | eventid | revenue | numtix
-------+---------+---------+--------
89397 | 47 | 20.00 | 1
106590 | 76 | 20.00 | 1
124683 | 393 | 20.00 | 1
103037 | 403 | 20.00 | 1
147685 | 429 | 20.00 | 1
(5 rows)
```
SEMUA
ALL menunjukkan untuk mengelompokkan dengan semua kolom yang ditentukan dalam daftar SELECT kecuali yang digabungkan. Misalnya, pertimbangkan kueri berikut yang dikelompokkan oleh `col1` dan `col2` tanpa harus menentukannya secara individual dalam klausa GROUP BY. Kolom `col3` adalah argumen `SUM` fungsi dan dengan demikian tidak dikelompokkan.
```
SELECT col1, col2 sum(col3) FROM testtable GROUP BY ALL
```
Jika Anda MENGECUALIKAN kolom dalam daftar SELECT, klausa GROUP BY ALL tidak mengelompokkan hasil berdasarkan kolom tertentu.
```
SELECT * EXCLUDE col3 FROM testtable GROUP BY ALL
```
**aggregation\$1extension**
Anda dapat menggunakan ekstensi agregasi GROUPING SETS, ROLLUP, dan CUBE untuk melakukan pekerjaan beberapa operasi GROUP BY dalam satu pernyataan. Untuk informasi selengkapnya tentang ekstensi agregasi dan fungsi terkait, lihat[Ekstensi agregasi](r_GROUP_BY_aggregation-extensions.md).
## Contoh
Contoh berikut menggunakan tabel PENJUALAN yang berisi kolom: salesid, listid, sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, dan saletime. Untuk informasi selengkapnya tentang tabel PENJUALAN, lihat[Database sampel](c_sampledb.md).
Berikut contoh kelompok query oleh `salesid` dan `listid` tanpa harus menentukan mereka secara individual dalam klausa GROUP BY. Kolom `qtysold` adalah argumen `SUM` fungsi dan dengan demikian tidak dikelompokkan.
```
SELECT salesid, listid, sum(qtysold) FROM sales GROUP BY ALL;
salesid | listid | sum
--------+---------+------
33095 | 36572 | 2
88268 | 100813 | 4
110917 | 127048 | 1
...
```
Contoh query berikut mengecualikan beberapa kolom dalam daftar SELECT, sehingga GROUP BY ALL hanya grup salesid dan listid.
```
SELECT * EXCLUDE sellerid, buyerid, eventid, dateid, qtysold, pricepaid, commission, saletime
FROM sales GROUP BY ALL;
salesid | listid
--------+---------
33095 | 36572
88268 | 100813
110917 | 127048
...
```
# Ekstensi agregasi
Amazon Redshift mendukung ekstensi agregasi untuk melakukan pekerjaan beberapa operasi GROUP BY dalam satu pernyataan.
Contoh untuk ekstensi agregasi menggunakan `orders` tabel, yang menyimpan data penjualan untuk perusahaan elektronik. Anda dapat membuat `orders` dengan yang berikut ini.
```
CREATE TABLE ORDERS (
ID INT,
PRODUCT CHAR(20),
CATEGORY CHAR(20),
PRE_OWNED CHAR(1),
COST DECIMAL
);
INSERT INTO ORDERS VALUES
(0, 'laptop', 'computers', 'T', 1000),
(1, 'smartphone', 'cellphones', 'T', 800),
(2, 'smartphone', 'cellphones', 'T', 810),
(3, 'laptop', 'computers', 'F', 1050),
(4, 'mouse', 'computers', 'F', 50);
```
## *SET PENGELOMPOKAN*
Menghitung satu atau lebih kumpulan pengelompokan dalam satu pernyataan. Kumpulan pengelompokan adalah kumpulan klausa GROUP BY tunggal, satu set kolom 0 atau lebih yang dengannya Anda dapat mengelompokkan kumpulan hasil kueri. GROUP BY GROUPING SETS setara dengan menjalankan query UNION ALL pada satu set hasil yang dikelompokkan berdasarkan kolom yang berbeda. Misalnya, GROUP BY GROUPING SETS ((a), (b)) setara dengan GROUP BY a UNION ALL GROUP BY b.
Contoh berikut mengembalikan biaya produk tabel pesanan dikelompokkan sesuai dengan kategori produk dan jenis produk yang dijual.
```
SELECT category, product, sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(category, product);
category | product | total
----------------------+----------------------+-------
computers | | 2100
cellphones | | 1610
| laptop | 2050
| smartphone | 1610
| mouse | 50
(5 rows)
```
## *ROLLUP*
Mengasumsikan hierarki di mana kolom sebelumnya dianggap sebagai orang tua dari kolom berikutnya. ROLLUP mengelompokkan data berdasarkan kolom yang disediakan, mengembalikan baris subtotal tambahan yang mewakili total di semua tingkat kolom pengelompokan, selain baris yang dikelompokkan. Misalnya, Anda dapat menggunakan GROUP BY ROLLUP ((a), (b)) untuk mengembalikan kumpulan hasil yang dikelompokkan terlebih dahulu oleh a, kemudian oleh b sambil mengasumsikan bahwa b adalah ayat dari a. ROLLUP juga mengembalikan baris dengan seluruh hasil yang ditetapkan tanpa pengelompokan kolom.
GROUP BY ROLLUP ((a), (b)) setara dengan GROUP BY GROUPING SETS ((a, b), (a), ()).
Contoh berikut mengembalikan biaya produk tabel pesanan dikelompokkan pertama berdasarkan kategori dan kemudian produk, dengan produk sebagai subdivisi kategori.
```
SELECT category, product, sum(cost) as total
FROM orders
GROUP BY ROLLUP(category, product) ORDER BY 1,2;
category | product | total
----------------------+----------------------+-------
cellphones | smartphone | 1610
cellphones | | 1610
computers | laptop | 2050
computers | mouse | 50
computers | | 2100
| | 3710
(6 rows)
```
## *KUBUS*
Kelompokkan data berdasarkan kolom yang disediakan, mengembalikan baris subtotal tambahan yang mewakili total di semua tingkat kolom pengelompokan, selain baris yang dikelompokkan. CUBE mengembalikan baris yang sama dengan ROLLUP, sambil menambahkan baris subtotal tambahan untuk setiap kombinasi kolom pengelompokan yang tidak dicakup oleh ROLLUP. Misalnya, Anda dapat menggunakan GROUP BY CUBE ((a), (b)) untuk mengembalikan kumpulan hasil yang dikelompokkan terlebih dahulu oleh a, kemudian oleh b sambil mengasumsikan bahwa b adalah subbagian dari a, lalu oleh b saja. CUBE juga mengembalikan baris dengan seluruh hasil yang ditetapkan tanpa pengelompokan kolom.
GROUP BY CUBE ((a), (b)) setara dengan GROUP BY GROUPING SETS ((a, b), (a), (b), ()).
Contoh berikut mengembalikan biaya produk tabel pesanan dikelompokkan pertama berdasarkan kategori dan kemudian produk, dengan produk sebagai subdivisi kategori. Berbeda dengan contoh sebelumnya untuk ROLLUP, pernyataan mengembalikan hasil untuk setiap kombinasi kolom pengelompokan.
```
SELECT category, product, sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 1,2;
category | product | total
----------------------+----------------------+-------
cellphones | smartphone | 1610
cellphones | | 1610
computers | laptop | 2050
computers | mouse | 50
computers | | 2100
| laptop | 2050
| mouse | 50
| smartphone | 1610
| | 3710
(9 rows)
```
## *Fungsi GROUPING/GROUPING\$1ID*
ROLLUP dan CUBE menambahkan nilai NULL ke set hasil untuk menunjukkan baris subtotal. Misalnya, GROUP BY ROLLUP ((a), (b)) mengembalikan satu atau lebih baris yang memiliki nilai NULL di kolom pengelompokan b untuk menunjukkan bahwa mereka adalah subtotal bidang dalam kolom pengelompokan. Nilai-nilai NULL ini hanya berfungsi untuk memenuhi format tupel yang kembali.
Saat Anda menjalankan operasi GROUP BY dengan ROLLUP dan CUBE pada relasi yang menyimpan nilai NULL itu sendiri, ini dapat menghasilkan kumpulan hasil dengan baris yang tampaknya memiliki kolom pengelompokan yang identik. Kembali ke contoh sebelumnya, jika kolom pengelompokan b berisi nilai NULL yang disimpan, GROUP BY ROLLUP ((a), (b)) mengembalikan baris dengan nilai NULL di kolom pengelompokan b yang bukan subtotal.
Untuk membedakan antara nilai NULL yang dibuat oleh ROLLUP dan CUBE, dan nilai NULL yang disimpan dalam tabel itu sendiri, Anda dapat menggunakan fungsi GROUPING, atau alias GROUPING\$1ID. GROUPING mengambil satu set pengelompokan sebagai argumennya, dan untuk setiap baris dalam set hasil mengembalikan nilai 0 atau 1 bit yang sesuai dengan kolom pengelompokan di posisi itu, dan kemudian mengubah nilai itu menjadi bilangan bulat. Jika nilai dalam posisi itu adalah nilai NULL yang dibuat oleh ekstensi agregasi, GROUPING mengembalikan 1. Ia mengembalikan 0 untuk semua nilai lainnya, termasuk nilai NULL yang disimpan.
Misalnya, PENGELOMPOKAN (kategori, produk) dapat mengembalikan nilai berikut untuk baris tertentu, tergantung pada nilai kolom pengelompokan untuk baris tersebut. Untuk tujuan contoh ini, semua nilai NULL dalam tabel adalah nilai NULL yang dibuat oleh ekstensi agregasi.
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/id_id/redshift/latest/dg/r_GROUP_BY_aggregation-extensions.html)
Fungsi PENGELOMPOKAN muncul di bagian daftar SELECT dari kueri dalam format berikut.
```
SELECT ... [GROUPING( expr )...] ...
GROUP BY ... {CUBE | ROLLUP| GROUPING SETS} ( expr ) ...
```
Contoh berikut adalah sama dengan contoh sebelumnya untuk CUBE, tetapi dengan penambahan fungsi GROUPING untuk kumpulan pengelompokannya.
```
SELECT category, product,
GROUPING(category) as grouping0,
GROUPING(product) as grouping1,
GROUPING(category, product) as grouping2,
sum(cost) as total
FROM orders
GROUP BY CUBE(category, product) ORDER BY 3,1,2;
category | product | grouping0 | grouping1 | grouping2 | total
----------------------+----------------------+-----------+-----------+-----------+-------
cellphones | smartphone | 0 | 0 | 0 | 1610
cellphones | | 0 | 1 | 1 | 1610
computers | laptop | 0 | 0 | 0 | 2050
computers | mouse | 0 | 0 | 0 | 50
computers | | 0 | 1 | 1 | 2100
| laptop | 1 | 0 | 2 | 2050
| mouse | 1 | 0 | 2 | 50
| smartphone | 1 | 0 | 2 | 1610
| | 1 | 1 | 3 | 3710
(9 rows)
```
## *ROLLUP sebagian dan CUBE*
Anda dapat menjalankan operasi ROLLUP dan CUBE hanya dengan sebagian dari subtotal.
Sintaks untuk operasi ROLLUP dan CUBE sebagian adalah sebagai berikut.
```
GROUP BY expr1, { ROLLUP | CUBE }(expr2, [, ...])
```
Di sini, klausa GROUP BY hanya membuat baris subtotal pada level *expr2* dan seterusnya.
Contoh berikut menunjukkan sebagian operasi ROLLUP dan CUBE pada tabel pesanan, mengelompokkan terlebih dahulu berdasarkan apakah suatu produk sudah dimiliki sebelumnya dan kemudian menjalankan ROLLUP dan CUBE pada kategori dan kolom produk.
```
SELECT pre_owned, category, product,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY pre_owned, ROLLUP(category, product) ORDER BY 4,1,2,3;
pre_owned | category | product | group_id | total
-----------+----------------------+----------------------+----------+-------
F | computers | laptop | 0 | 1050
F | computers | mouse | 0 | 50
T | cellphones | smartphone | 0 | 1610
T | computers | laptop | 0 | 1000
F | computers | | 2 | 1100
T | cellphones | | 2 | 1610
T | computers | | 2 | 1000
F | | | 6 | 1100
T | | | 6 | 2610
(9 rows)
SELECT pre_owned, category, product,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY pre_owned, CUBE(category, product) ORDER BY 4,1,2,3;
pre_owned | category | product | group_id | total
-----------+----------------------+----------------------+----------+-------
F | computers | laptop | 0 | 1050
F | computers | mouse | 0 | 50
T | cellphones | smartphone | 0 | 1610
T | computers | laptop | 0 | 1000
F | computers | | 2 | 1100
T | cellphones | | 2 | 1610
T | computers | | 2 | 1000
F | | laptop | 4 | 1050
F | | mouse | 4 | 50
T | | laptop | 4 | 1000
T | | smartphone | 4 | 1610
F | | | 6 | 1100
T | | | 6 | 2610
(13 rows)
```
Karena kolom pra-dimiliki tidak termasuk dalam operasi ROLLUP dan CUBE, tidak ada total baris besar yang mencakup semua baris lainnya.
## *Pengelompokan gabungan*
Anda dapat menggabungkan beberapa SETS/ROLLUP/CUBE klausa PENGELOMPOKAN untuk menghitung tingkat subtotal yang berbeda. Pengelompokan gabungan mengembalikan produk Cartesian dari kumpulan pengelompokan yang disediakan.
Sintaks untuk menggabungkan klausa GROUPING SETS/ROLLUP/CUBE adalah sebagai berikut.
```
GROUP BY {ROLLUP|CUBE|GROUPING SETS}(expr1[, ...]),
{ROLLUP|CUBE|GROUPING SETS}(expr1[, ...])[, ...]
```
Perhatikan contoh berikut untuk melihat bagaimana pengelompokan gabungan kecil dapat menghasilkan set hasil akhir yang besar.
```
SELECT pre_owned, category, product,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY CUBE(category, product), GROUPING SETS(pre_owned, ())
ORDER BY 4,1,2,3;
pre_owned | category | product | group_id | total
-----------+----------------------+----------------------+----------+-------
F | computers | laptop | 0 | 1050
F | computers | mouse | 0 | 50
T | cellphones | smartphone | 0 | 1610
T | computers | laptop | 0 | 1000
| cellphones | smartphone | 1 | 1610
| computers | laptop | 1 | 2050
| computers | mouse | 1 | 50
F | computers | | 2 | 1100
T | cellphones | | 2 | 1610
T | computers | | 2 | 1000
| cellphones | | 3 | 1610
| computers | | 3 | 2100
F | | laptop | 4 | 1050
F | | mouse | 4 | 50
T | | laptop | 4 | 1000
T | | smartphone | 4 | 1610
| | laptop | 5 | 2050
| | mouse | 5 | 50
| | smartphone | 5 | 1610
F | | | 6 | 1100
T | | | 6 | 2610
| | | 7 | 3710
(22 rows)
```
## *Pengelompokan bersarang*
Anda dapat menggunakan SETS/ROLLUP/CUBE operasi GROUPING sebagai *expr* GROUPING SETS Anda untuk membentuk pengelompokan bersarang. Sub pengelompokan di dalam SET PENGELOMPOKAN bersarang diratakan.
Sintaks untuk pengelompokan bersarang adalah sebagai berikut.
```
GROUP BY GROUPING SETS({ROLLUP|CUBE|GROUPING SETS}(expr[, ...])[, ...])
```
Pertimbangkan contoh berikut.
```
SELECT category, product, pre_owned,
GROUPING(category, product, pre_owned) as group_id,
sum(cost) as total
FROM orders
GROUP BY GROUPING SETS(ROLLUP(category), CUBE(product, pre_owned))
ORDER BY 4,1,2,3;
category | product | pre_owned | group_id | total
----------------------+----------------------+-----------+----------+-------
cellphones | | | 3 | 1610
computers | | | 3 | 2100
| laptop | F | 4 | 1050
| laptop | T | 4 | 1000
| mouse | F | 4 | 50
| smartphone | T | 4 | 1610
| laptop | | 5 | 2050
| mouse | | 5 | 50
| smartphone | | 5 | 1610
| | F | 6 | 1100
| | T | 6 | 2610
| | | 7 | 3710
| | | 7 | 3710
(13 rows)
```
Perhatikan bahwa karena ROLLUP (kategori) dan CUBE (produk, pre\$1owned) berisi kumpulan pengelompokan (), baris yang mewakili total besar diduplikasi.
## *Catatan penggunaan*
+ Klausa GROUP BY mendukung hingga 64 set pengelompokan. Dalam kasus ROLLUP dan CUBE, atau beberapa kombinasi SET PENGELOMPOKAN, ROLLUP, dan CUBE, batasan ini berlaku untuk jumlah kumpulan pengelompokan yang tersirat. Misalnya, GROUP BY CUBE ((a), (b)) dihitung sebagai 4 set pengelompokan, bukan 2.
+ Anda tidak dapat menggunakan konstanta sebagai pengelompokan kolom saat menggunakan ekstensi agregasi.
+ Anda tidak dapat membuat kumpulan pengelompokan yang berisi kolom duplikat.
# Klausa HAVING
Klausa HAVING menerapkan kondisi untuk kumpulan hasil dikelompokkan perantara yang dikembalikan oleh kueri.
## Sintaksis
```
[ HAVING condition ]
```
Misalnya, Anda dapat membatasi hasil fungsi SUM:
```
having sum(pricepaid) >10000
```
Kondisi HAVING diterapkan setelah semua kondisi klausa WHERE diterapkan dan operasi GROUP BY selesai.
Kondisi itu sendiri mengambil bentuk yang sama dengan kondisi klausa WHERE.
## Catatan penggunaan
+ Setiap kolom yang direferensikan dalam kondisi klausa HAVING harus berupa kolom pengelompokan atau kolom yang mengacu pada hasil fungsi agregat.
+ Dalam klausa HAVING, Anda tidak dapat menentukan:
+ Nomor urut yang mengacu pada item daftar pilih. Hanya klausa GROUP BY dan ORDER BY yang menerima nomor urut.
## Contoh
Kueri berikut menghitung total penjualan tiket untuk semua acara berdasarkan nama, kemudian menghilangkan peristiwa di mana total penjualan kurang dari \$1800.000. Kondisi HAVING diterapkan pada hasil fungsi agregat dalam daftar pilih:`sum(pricepaid)`.
```
select eventname, sum(pricepaid)
from sales join event on sales.eventid = event.eventid
group by 1
having sum(pricepaid) > 800000
order by 2 desc, 1;
eventname | sum
-----------------+-----------
Mamma Mia! | 1135454.00
Spring Awakening | 972855.00
The Country Girl | 910563.00
Macbeth | 862580.00
Jersey Boys | 811877.00
Legally Blonde | 804583.00
```
Query berikut menghitung set hasil yang sama. Namun, dalam kasus ini, kondisi HAVING diterapkan ke agregat yang tidak ditentukan dalam daftar pilih:`sum(qtysold)`. Acara yang tidak menjual lebih dari 2.000 tiket dihilangkan dari hasil akhir.
```
select eventname, sum(pricepaid)
from sales join event on sales.eventid = event.eventid
group by 1
having sum(qtysold) >2000
order by 2 desc, 1;
eventname | sum
-----------------+-----------
Mamma Mia! | 1135454.00
Spring Awakening | 972855.00
The Country Girl | 910563.00
Macbeth | 862580.00
Jersey Boys | 811877.00
Legally Blonde | 804583.00
Chicago | 790993.00
Spamalot | 714307.00
```
Kueri berikut menghitung total penjualan tiket untuk semua acara berdasarkan nama, kemudian menghilangkan peristiwa di mana total penjualan kurang dari \$1800.000. Kondisi HAVING diterapkan pada hasil fungsi agregat dalam daftar pilih menggunakan alias `pp` untuk. `sum(pricepaid)`
```
select eventname, sum(pricepaid) as pp
from sales join event on sales.eventid = event.eventid
group by 1
having pp > 800000
order by 2 desc, 1;
eventname | pp
-----------------+-----------
Mamma Mia! | 1135454.00
Spring Awakening | 972855.00
The Country Girl | 910563.00
Macbeth | 862580.00
Jersey Boys | 811877.00
Legally Blonde | 804583.00
```
# Klausul KUALIFIKASI
Klausa QUALIFY memfilter hasil fungsi jendela yang dihitung sebelumnya sesuai dengan kondisi penelusuran yang ditentukan pengguna. Anda dapat menggunakan klausa untuk menerapkan kondisi penyaringan ke hasil fungsi jendela tanpa menggunakan subquery.
Ini mirip dengan [klausa HAVING](https://docs.aws.amazon.com/redshift/latest/dg/r_HAVING_clause.html), yang menerapkan kondisi untuk memfilter baris lebih lanjut dari klausa WHERE. Perbedaan antara QUALIFY dan HAVING adalah bahwa hasil yang disaring dari klausa QUALIFY dapat didasarkan pada hasil menjalankan fungsi jendela pada data. Anda dapat menggunakan klausa QUALIFY dan HAVING dalam satu kueri.
## Sintaksis
```
QUALIFY condition
```
**catatan**
Jika Anda menggunakan klausa QUALIFY secara langsung setelah klausa FROM, nama relasi FROM harus memiliki alias yang ditentukan sebelum klausa QUALIFY.
## Contoh
Contoh di bagian ini menggunakan data sampel di bawah ini.
```
create table store_sales (ss_sold_date date, ss_sold_time time,
ss_item text, ss_sales_price float);
insert into store_sales values ('2022-01-01', '09:00:00', 'Product 1', 100.0),
('2022-01-01', '11:00:00', 'Product 2', 500.0),
('2022-01-01', '15:00:00', 'Product 3', 20.0),
('2022-01-01', '17:00:00', 'Product 4', 1000.0),
('2022-01-01', '18:00:00', 'Product 5', 30.0),
('2022-01-02', '10:00:00', 'Product 6', 5000.0),
('2022-01-02', '16:00:00', 'Product 7', 5.0);
```
Contoh berikut menunjukkan bagaimana menemukan dua barang paling mahal yang dijual setelah pukul 12:00 setiap hari.
```
SELECT *
FROM store_sales ss
WHERE ss_sold_time > time '12:00:00'
QUALIFY row_number()
OVER (PARTITION BY ss_sold_date ORDER BY ss_sales_price DESC) <= 2
ss_sold_date | ss_sold_time | ss_item | ss_sales_price
--------------+--------------+-----------+----------------
2022-01-01 | 17:00:00 | Product 4 | 1000
2022-01-01 | 18:00:00 | Product 5 | 30
2022-01-02 | 16:00:00 | Product 7 | 5
```
Anda kemudian dapat menemukan barang terakhir yang dijual setiap hari.
```
SELECT *
FROM store_sales ss
QUALIFY last_value(ss_item)
OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) = ss_item;
ss_sold_date | ss_sold_time | ss_item | ss_sales_price
--------------+--------------+-----------+----------------
2022-01-01 | 18:00:00 | Product 5 | 30
2022-01-02 | 16:00:00 | Product 7 | 5
```
Contoh berikut mengembalikan catatan yang sama dengan kueri sebelumnya, item terakhir yang terjual setiap hari, tetapi tidak menggunakan klausa QUALIFY.
```
SELECT * FROM (
SELECT *,
last_value(ss_item)
OVER (PARTITION BY ss_sold_date ORDER BY ss_sold_time ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) ss_last_item
FROM store_sales ss
)
WHERE ss_last_item = ss_item;
ss_sold_date | ss_sold_time | ss_item | ss_sales_price | ss_last_item
--------------+--------------+-----------+----------------+--------------
2022-01-02 | 16:00:00 | Product 7 | 5 | Product 7
2022-01-01 | 18:00:00 | Product 5 | 30 | Product 5
```
# UNION, INTERSECT, dan KECUALI
**Topics**
+ [Sintaksis](#r_UNION-synopsis)
+ [Parameter](#r_UNION-parameters)
+ [Urutan evaluasi untuk operator yang ditetapkan](#r_UNION-order-of-evaluation-for-set-operators)
+ [Catatan penggunaan](#r_UNION-usage-notes)
+ [Contoh kueri UNION](c_example_union_query.md)
+ [Contoh UNION ALL query](c_example_unionall_query.md)
+ [Contoh pertanyaan INTERSECT](c_example_intersect_query.md)
+ [Contoh KECUALI kueri](c_Example_MINUS_query.md)
*Operator set UNION, INTERSECT, dan EXCEPT* digunakan untuk membandingkan dan menggabungkan hasil dari dua ekspresi kueri terpisah. Misalnya, jika Anda ingin mengetahui pengguna situs web mana yang merupakan pembeli dan penjual tetapi nama pengguna mereka disimpan dalam kolom atau tabel terpisah, Anda dapat menemukan *persimpangan* kedua jenis pengguna ini. Jika Anda ingin tahu pengguna situs web mana yang merupakan pembeli tetapi bukan penjual, Anda dapat menggunakan operator EXCEPT untuk menemukan *perbedaan* antara dua daftar pengguna. Jika Anda ingin membuat daftar semua pengguna, apa pun perannya, Anda dapat menggunakan operator UNION.
## Sintaksis
```
query
{ UNION [ ALL ] | INTERSECT | EXCEPT | MINUS }
query
```
## Parameter
*query*
Ekspresi kueri yang sesuai, dalam bentuk daftar pilihannya, dengan ekspresi kueri kedua yang mengikuti operator UNION, INTERSECT, atau EXCEPT. Kedua ekspresi harus berisi jumlah kolom keluaran yang sama dengan tipe data yang kompatibel; jika tidak, dua set hasil tidak dapat dibandingkan dan digabungkan. Operasi set tidak mengizinkan konversi implisit antara berbagai kategori tipe data; untuk informasi selengkapnya, lihat[Ketik kompatibilitas dan konversi](c_Supported_data_types.md#r_Type_conversion).
Anda dapat membuat kueri yang berisi ekspresi kueri dalam jumlah tak terbatas dan menautkannya dengan operator UNION, INTERSECT, dan EXCEPT dalam kombinasi apa pun. Misalnya, struktur kueri berikut ini valid, dengan asumsi bahwa tabel T1, T2, dan T3 berisi kumpulan kolom yang kompatibel:
```
select * from t1
union
select * from t2
except
select * from t3
order by c1;
```
UNION
Mengatur operasi yang mengembalikan baris dari dua ekspresi query, terlepas dari apakah baris berasal dari satu atau kedua ekspresi.
BERPOTONGAN
Mengatur operasi yang mengembalikan baris yang berasal dari dua ekspresi query. Baris yang tidak dikembalikan oleh kedua ekspresi akan dibuang.
KECUALI \$1 MINUS
Mengatur operasi yang mengembalikan baris yang berasal dari salah satu dari dua ekspresi query. Agar memenuhi syarat untuk hasil, baris harus ada di tabel hasil pertama tetapi bukan yang kedua. MINUS dan KECUALI adalah sinonim yang tepat.
SEMUA
Kata kunci ALL mempertahankan setiap baris duplikat yang dihasilkan oleh UNION. Perilaku default saat kata kunci ALL tidak digunakan adalah membuang duplikat ini. INTERSECT ALL, KECUALI ALL, dan MINUS ALL tidak didukung.
## Urutan evaluasi untuk operator yang ditetapkan
Operator set UNION dan EXCEPLE adalah asosiatif kiri. Jika tanda kurung tidak ditentukan untuk mempengaruhi urutan prioritas, kombinasi dari operator set ini dievaluasi dari kiri ke kanan. Misalnya, dalam kueri berikut, UNION T1 dan T2 dievaluasi terlebih dahulu, kemudian operasi EXCEPT dilakukan pada hasil UNION:
```
select * from t1
union
select * from t2
except
select * from t3
order by c1;
```
Operator INTERSECT lebih diutamakan daripada operator UNION dan EXCEPT ketika kombinasi operator digunakan dalam kueri yang sama. Misalnya, kueri berikut mengevaluasi persimpangan T2 dan T3, lalu menyatukan hasilnya dengan T1:
```
select * from t1
union
select * from t2
intersect
select * from t3
order by c1;
```
Dengan menambahkan tanda kurung, Anda dapat menerapkan urutan evaluasi yang berbeda. Dalam kasus berikut, hasil penyatuan T1 dan T2 berpotongan dengan T3, dan kueri kemungkinan akan menghasilkan hasil yang berbeda.
```
(select * from t1
union
select * from t2)
intersect
(select * from t3)
order by c1;
```
## Catatan penggunaan
+ Nama kolom yang dikembalikan dalam hasil kueri operasi set adalah nama kolom (atau alias) dari tabel dalam ekspresi kueri pertama. Karena nama kolom ini berpotensi menyesatkan, karena nilai dalam kolom berasal dari tabel di kedua sisi operator set, Anda mungkin ingin memberikan alias yang berarti untuk kumpulan hasil.
+ Ekspresi kueri yang mendahului operator set tidak boleh berisi klausa ORDER BY. Klausa ORDER BY menghasilkan hasil yang diurutkan bermakna hanya jika digunakan di akhir kueri yang berisi operator set. Dalam hal ini, klausa ORDER BY berlaku untuk hasil akhir dari semua operasi yang ditetapkan. Kueri terluar juga dapat berisi klausa LIMIT dan OFFSET standar.
+ Ketika kueri operator yang disetel mengembalikan hasil desimal, kolom hasil yang sesuai dipromosikan untuk mengembalikan presisi dan skala yang sama. Misalnya, dalam kueri berikut, di mana T1.REVENUE adalah kolom DECIMAL (10,2) dan T2.REVENUE adalah kolom DECIMAL (8,4), hasil desimal dipromosikan ke DECIMAL (12,4):
```
select t1.revenue union select t2.revenue;
```
Skala ini `4` karena itu adalah skala maksimum dari dua kolom. Ketepatannya adalah `12` karena T1.REVENUE membutuhkan 8 digit di sebelah kiri titik desimal (12 - 4 = 8). Promosi jenis ini memastikan bahwa semua nilai dari kedua sisi UNION sesuai dengan hasilnya. Untuk nilai 64-bit, presisi hasil maksimum adalah 19 dan skala hasil maksimum adalah 18. Untuk nilai 128-bit, presisi hasil maksimum adalah 38 dan skala hasil maksimum adalah 37.
Jika tipe data yang dihasilkan melebihi presisi Amazon Redshift dan batas skala, kueri akan menampilkan kesalahan.
+ Untuk operasi set, dua baris diperlakukan sebagai identik jika, untuk setiap pasangan kolom yang sesuai, dua nilai data *sama* atau *keduanya NULL*. Misalnya, jika tabel T1 dan T2 keduanya berisi satu kolom dan satu baris, dan baris itu adalah NULL di kedua tabel, operasi INTERSECT di atas tabel tersebut mengembalikan baris itu.
# Contoh kueri UNION
Dalam query UNION berikut, baris dalam tabel PENJUALAN digabungkan dengan baris dalam tabel LISTING. Tiga kolom yang kompatibel dipilih dari setiap tabel; dalam hal ini, kolom yang sesuai memiliki nama dan tipe data yang sama.
Set hasil akhir diurutkan oleh kolom pertama dalam tabel LISTING dan terbatas pada 5 baris dengan nilai LISTID tertinggi.
```
select listid, sellerid, eventid from listing
union select listid, sellerid, eventid from sales
order by listid, sellerid, eventid desc limit 5;
listid | sellerid | eventid
--------+----------+---------
1 | 36861 | 7872
2 | 16002 | 4806
3 | 21461 | 4256
4 | 8117 | 4337
5 | 1616 | 8647
(5 rows)
```
Contoh berikut menunjukkan bagaimana Anda dapat menambahkan nilai literal untuk output dari query UNION sehingga Anda dapat melihat ekspresi query yang dihasilkan setiap baris dalam set hasil. Kueri mengidentifikasi baris dari ekspresi kueri pertama sebagai “B” (untuk pembeli) dan baris dari ekspresi kueri kedua sebagai “S” (untuk penjual).
Kueri mengidentifikasi pembeli dan penjual untuk transaksi tiket yang harganya \$110.000 atau lebih. Satu-satunya perbedaan antara dua ekspresi kueri di kedua sisi operator UNION adalah kolom bergabung untuk tabel PENJUALAN.
```
select listid, lastname, firstname, username,
pricepaid as price, 'S' as buyorsell
from sales, users
where sales.sellerid=users.userid
and pricepaid >=10000
union
select listid, lastname, firstname, username, pricepaid,
'B' as buyorsell
from sales, users
where sales.buyerid=users.userid
and pricepaid >=10000
order by 1, 2, 3, 4, 5;
listid | lastname | firstname | username | price | buyorsell
--------+----------+-----------+----------+-----------+-----------
209658 | Lamb | Colette | VOR15LYI | 10000.00 | B
209658 | West | Kato | ELU81XAA | 10000.00 | S
212395 | Greer | Harlan | GXO71KOC | 12624.00 | S
212395 | Perry | Cora | YWR73YNZ | 12624.00 | B
215156 | Banks | Patrick | ZNQ69CLT | 10000.00 | S
215156 | Hayden | Malachi | BBG56AKU | 10000.00 | B
(6 rows)
```
Contoh berikut menggunakan operator UNION ALL karena baris duplikat, jika ditemukan, perlu dipertahankan dalam hasilnya. Untuk rangkaian acara tertentu IDs, kueri mengembalikan 0 atau lebih baris untuk setiap penjualan yang terkait dengan setiap acara, dan 0 atau 1 baris untuk setiap daftar acara tersebut. Acara IDs unik untuk setiap baris dalam tabel LISTING dan EVENT, tetapi mungkin ada beberapa penjualan untuk kombinasi acara dan daftar yang sama IDs di tabel PENJUALAN.
Kolom ketiga dalam set hasil mengidentifikasi sumber baris. Jika berasal dari tabel PENJUALAN, itu ditandai “Ya” di kolom SALESROW. (SALESROW adalah alias untuk SALES.LISTID.) Jika baris berasal dari tabel LISTING, itu ditandai “Tidak” di kolom SALESROW.
Dalam hal ini, set hasil terdiri dari tiga baris penjualan untuk daftar 500, acara 7787. Dengan kata lain, tiga transaksi berbeda terjadi untuk daftar dan kombinasi acara ini. Dua daftar lainnya, 501 dan 502, tidak menghasilkan penjualan apa pun, jadi satu-satunya baris yang dihasilkan kueri untuk daftar ini IDs berasal dari tabel LISTING (SALESROW = 'Tidak').
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union all
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
7787 | 500 | Yes
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(6 rows)
```
Jika Anda menjalankan kueri yang sama tanpa kata kunci ALL, hasilnya hanya mempertahankan satu dari transaksi penjualan.
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(4 rows)
```
# Contoh UNION ALL query
Contoh berikut menggunakan operator UNION ALL karena baris duplikat, jika ditemukan, perlu dipertahankan dalam hasilnya. Untuk rangkaian acara tertentu IDs, kueri mengembalikan 0 atau lebih baris untuk setiap penjualan yang terkait dengan setiap acara, dan 0 atau 1 baris untuk setiap daftar acara tersebut. Acara IDs unik untuk setiap baris dalam tabel LISTING dan EVENT, tetapi mungkin ada beberapa penjualan untuk kombinasi acara dan daftar yang sama IDs di tabel PENJUALAN.
Kolom ketiga dalam set hasil mengidentifikasi sumber baris. Jika berasal dari tabel PENJUALAN, itu ditandai “Ya” di kolom SALESROW. (SALESROW adalah alias untuk SALES.LISTID.) Jika baris berasal dari tabel LISTING, itu ditandai “Tidak” di kolom SALESROW.
Dalam hal ini, set hasil terdiri dari tiga baris penjualan untuk daftar 500, acara 7787. Dengan kata lain, tiga transaksi berbeda terjadi untuk daftar dan kombinasi acara ini. Dua daftar lainnya, 501 dan 502, tidak menghasilkan penjualan apa pun, jadi satu-satunya baris yang dihasilkan kueri untuk daftar ini IDs berasal dari tabel LISTING (SALESROW = 'Tidak').
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union all
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
7787 | 500 | Yes
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(6 rows)
```
Jika Anda menjalankan kueri yang sama tanpa kata kunci ALL, hasilnya hanya mempertahankan satu dari transaksi penjualan.
```
select eventid, listid, 'Yes' as salesrow
from sales
where listid in(500,501,502)
union
select eventid, listid, 'No'
from listing
where listid in(500,501,502)
order by listid asc;
eventid | listid | salesrow
---------+--------+----------
7787 | 500 | No
7787 | 500 | Yes
6473 | 501 | No
5108 | 502 | No
(4 rows)
```
# Contoh pertanyaan INTERSECT
Bandingkan contoh berikut dengan contoh UNION pertama. Satu-satunya perbedaan antara kedua contoh adalah operator set yang digunakan, tetapi hasilnya sangat berbeda. Hanya satu baris yang sama:
```
235494 | 23875 | 8771
```
Ini adalah satu-satunya baris dalam hasil terbatas dari 5 baris yang ditemukan di kedua tabel.
```
select listid, sellerid, eventid from listing
intersect
select listid, sellerid, eventid from sales
order by listid desc, sellerid, eventid
limit 5;
listid | sellerid | eventid
--------+----------+---------
235494 | 23875 | 8771
235482 | 1067 | 2667
235479 | 1589 | 7303
235476 | 15550 | 793
235475 | 22306 | 7848
(5 rows)
```
Permintaan berikut menemukan peristiwa (yang tiketnya terjual) yang terjadi di tempat-tempat di New York City dan Los Angeles pada bulan Maret. Perbedaan antara dua ekspresi kueri adalah kendala pada kolom VENUECITY.
```
select distinct eventname from event, sales, venue
where event.eventid=sales.eventid and event.venueid=venue.venueid
and date_part(month,starttime)=3 and venuecity='Los Angeles'
intersect
select distinct eventname from event, sales, venue
where event.eventid=sales.eventid and event.venueid=venue.venueid
and date_part(month,starttime)=3 and venuecity='New York City'
order by eventname asc;
eventname
----------------------------
A Streetcar Named Desire
Dirty Dancing
Electra
Running with Annalise
Hairspray
Mary Poppins
November
Oliver!
Return To Forever
Rhinoceros
South Pacific
The 39 Steps
The Bacchae
The Caucasian Chalk Circle
The Country Girl
Wicked
Woyzeck
(16 rows)
```
# Contoh KECUALI kueri
Tabel CATEGORY dalam database TICKIT berisi 11 baris berikut:
```
catid | catgroup | catname | catdesc
-------+----------+-----------+--------------------------------------------
1 | Sports | MLB | Major League Baseball
2 | Sports | NHL | National Hockey League
3 | Sports | NFL | National Football League
4 | Sports | NBA | National Basketball Association
5 | Sports | MLS | Major League Soccer
6 | Shows | Musicals | Musical theatre
7 | Shows | Plays | All non-musical theatre
8 | Shows | Opera | All opera and light opera
9 | Concerts | Pop | All rock and pop music concerts
10 | Concerts | Jazz | All jazz singers and bands
11 | Concerts | Classical | All symphony, concerto, and choir concerts
(11 rows)
```
Asumsikan bahwa tabel CATEGORY\$1STAGE (tabel pementasan) berisi satu baris tambahan:
```
catid | catgroup | catname | catdesc
-------+----------+-----------+--------------------------------------------
1 | Sports | MLB | Major League Baseball
2 | Sports | NHL | National Hockey League
3 | Sports | NFL | National Football League
4 | Sports | NBA | National Basketball Association
5 | Sports | MLS | Major League Soccer
6 | Shows | Musicals | Musical theatre
7 | Shows | Plays | All non-musical theatre
8 | Shows | Opera | All opera and light opera
9 | Concerts | Pop | All rock and pop music concerts
10 | Concerts | Jazz | All jazz singers and bands
11 | Concerts | Classical | All symphony, concerto, and choir concerts
12 | Concerts | Comedy | All stand up comedy performances
(12 rows)
```
Kembalikan perbedaan antara dua tabel. Dengan kata lain, kembalikan baris yang ada di tabel CATEGORY\$1STAGE tetapi tidak di tabel CATEGORY:
```
select * from category_stage
except
select * from category;
catid | catgroup | catname | catdesc
-------+----------+---------+----------------------------------
12 | Concerts | Comedy | All stand up comedy performances
(1 row)
```
Kueri setara berikut menggunakan sinonim MINUS.
```
select * from category_stage
minus
select * from category;
catid | catgroup | catname | catdesc
-------+----------+---------+----------------------------------
12 | Concerts | Comedy | All stand up comedy performances
(1 row)
```
Jika Anda membalikkan urutan ekspresi SELECT, kueri tidak mengembalikan baris.
# Klausa ORDER BY
**Topics**
+ [Sintaksis](#r_ORDER_BY_clause-synopsis)
+ [Parameter](#r_ORDER_BY_clause-parameters)
+ [Catatan penggunaan](#r_ORDER_BY_usage_notes)
+ [Contoh dengan ORDER BY](r_Examples_with_ORDER_BY.md)
Klausa ORDER BY mengurutkan kumpulan hasil kueri.
## Sintaksis
```
[ ORDER BY expression [ ASC | DESC ] ]
[ NULLS FIRST | NULLS LAST ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
```
## Parameter
*ekspresi*
Ekspresi yang mendefinisikan urutan urutan hasil kueri set, biasanya dengan menentukan satu atau beberapa kolom dalam daftar pilih. Hasil dikembalikan berdasarkan urutan UTF-8 biner. Anda juga dapat menentukan yang berikut:
+ Kolom yang tidak ada dalam daftar pilih
+ Ekspresi terbentuk dari satu atau lebih kolom yang ada di tabel yang direferensikan oleh kueri
+ Nomor urut yang mewakili posisi entri daftar pilih (atau posisi kolom dalam tabel jika tidak ada daftar pilih)
+ Alias yang menentukan entri daftar pilih
Ketika klausa ORDER BY berisi beberapa ekspresi, kumpulan hasil diurutkan menurut ekspresi pertama, maka ekspresi kedua diterapkan ke baris yang memiliki nilai yang cocok dari ekspresi pertama, dan seterusnya.
ASC \$1 DESC
Opsi yang mendefinisikan urutan pengurutan untuk ekspresi, sebagai berikut:
+ ASC: naik (misalnya, rendah ke tinggi untuk nilai numerik dan 'A' ke 'Z' untuk string karakter). Jika tidak ada opsi yang ditentukan, data diurutkan dalam urutan menaik secara default.
+ DESC: turun (tinggi ke rendah untuk nilai numerik; 'Z' ke 'A' untuk string).
NULLS PERTAMA \$1 NULLS TERAKHIR
Opsi yang menentukan apakah nilai NULL harus diurutkan terlebih dahulu, sebelum nilai non-null, atau terakhir, setelah nilai non-null. Secara default, nilai NULL diurutkan dan diberi peringkat terakhir dalam urutan ASC, dan diurutkan dan diberi peringkat pertama dalam urutan DESC.
BATASAN *nomor* \$1 SEMUA
Opsi yang mengontrol jumlah baris yang diurutkan yang dikembalikan kueri. Bilangan LIMIT harus berupa bilangan bulat positif; nilai maksimumnya adalah`2147483647`.
LIMIT 0 tidak mengembalikan baris. Anda dapat menggunakan sintaks ini untuk tujuan pengujian: untuk memeriksa apakah kueri berjalan (tanpa menampilkan baris apa pun) atau mengembalikan daftar kolom dari tabel. Klausa ORDER BY berlebihan jika Anda menggunakan LIMIT 0 untuk mengembalikan daftar kolom. Defaultnya adalah LIMIT ALL.
*OFFSET mulai*
Opsi yang menentukan untuk melewati jumlah baris sebelum *memulai sebelum mulai* mengembalikan baris. Nomor OFFSET harus berupa bilangan bulat positif; nilai maksimumnya adalah. `2147483647` Saat digunakan dengan opsi LIMIT, baris OFFSET dilewati sebelum mulai menghitung baris LIMIT yang dikembalikan. Jika opsi LIMIT tidak digunakan, jumlah baris dalam kumpulan hasil dikurangi dengan jumlah baris yang dilewati. Baris yang dilewati oleh klausa OFFSET masih harus dipindai, jadi mungkin tidak efisien untuk menggunakan nilai OFFSET yang besar.
## Catatan penggunaan
Perhatikan perilaku yang diharapkan berikut dengan klausa ORDER BY:
+ Nilai NULL dianggap “lebih tinggi” dari semua nilai lainnya. Dengan urutan urutan menaik default, nilai NULL mengurutkan di akhir. Untuk mengubah perilaku ini, gunakan opsi NULLS FIRST.
+ Ketika kueri tidak berisi klausa ORDER BY, sistem mengembalikan set hasil tanpa urutan baris yang dapat diprediksi. Kueri yang sama dijalankan dua kali mungkin mengembalikan set hasil dalam urutan yang berbeda.
+ Opsi LIMIT dan OFFSET dapat digunakan tanpa klausa ORDER BY; namun, untuk mengembalikan serangkaian baris yang konsisten, gunakan opsi ini bersama dengan ORDER BY.
+ Dalam sistem paralel apa pun seperti Amazon Redshift, ketika ORDER BY tidak menghasilkan urutan unik, urutan baris tidak deterministik. Artinya, jika ekspresi ORDER BY menghasilkan nilai duplikat, urutan pengembalian baris tersebut mungkin berbeda dari sistem lain atau dari satu proses Amazon Redshift ke yang berikutnya.
+ Amazon Redshift tidak mendukung literal string dalam klausa ORDER BY.
# Contoh dengan ORDER BY
Kembalikan semua 11 baris dari tabel CATEGORY, diurutkan berdasarkan kolom kedua, CATGROUP. Untuk hasil yang memiliki nilai CATGROUP yang sama, urutkan nilai kolom CATDESC dengan panjang string karakter. Kemudian urutkan berdasarkan kolom CATID dan CATNAME.
```
select * from category order by 2, length(catdesc), 1, 3;
catid | catgroup | catname | catdesc
------+----------+-----------+----------------------------------------
10 | Concerts | Jazz | All jazz singers and bands
9 | Concerts | Pop | All rock and pop music concerts
11 | Concerts | Classical | All symphony, concerto, and choir conce
6 | Shows | Musicals | Musical theatre
7 | Shows | Plays | All non-musical theatre
8 | Shows | Opera | All opera and light opera
5 | Sports | MLS | Major League Soccer
1 | Sports | MLB | Major League Baseball
2 | Sports | NHL | National Hockey League
3 | Sports | NFL | National Football League
4 | Sports | NBA | National Basketball Association
(11 rows)
```
Kembalikan kolom yang dipilih dari tabel PENJUALAN, diurutkan berdasarkan nilai QTYSOLD tertinggi. Batasi hasilnya ke 10 baris teratas:
```
select salesid, qtysold, pricepaid, commission, saletime from sales
order by qtysold, pricepaid, commission, salesid, saletime desc
limit 10;
salesid | qtysold | pricepaid | commission | saletime
--------+---------+-----------+------------+---------------------
15401 | 8 | 272.00 | 40.80 | 2008-03-18 06:54:56
61683 | 8 | 296.00 | 44.40 | 2008-11-26 04:00:23
90528 | 8 | 328.00 | 49.20 | 2008-06-11 02:38:09
74549 | 8 | 336.00 | 50.40 | 2008-01-19 12:01:21
130232 | 8 | 352.00 | 52.80 | 2008-05-02 05:52:31
55243 | 8 | 384.00 | 57.60 | 2008-07-12 02:19:53
16004 | 8 | 440.00 | 66.00 | 2008-11-04 07:22:31
489 | 8 | 496.00 | 74.40 | 2008-08-03 05:48:55
4197 | 8 | 512.00 | 76.80 | 2008-03-23 11:35:33
16929 | 8 | 568.00 | 85.20 | 2008-12-19 02:59:33
(10 rows)
```
Kembalikan daftar kolom dan tidak ada baris dengan menggunakan sintaks LIMIT 0:
```
select * from venue limit 0;
venueid | venuename | venuecity | venuestate | venueseats
---------+-----------+-----------+------------+------------
(0 rows)
```
# CONNECT BY klausa
Klausa CONNECT BY menentukan hubungan antara baris dalam hierarki. Anda dapat menggunakan CONNECT BY untuk memilih baris dalam urutan hierarkis dengan menggabungkan tabel ke dirinya sendiri dan memproses data hierarkis. Misalnya, Anda dapat menggunakannya untuk melakukan loop secara rekursif melalui bagan organisasi dan daftar data.
Proses kueri hierarkis dalam urutan sebagai berikut:
1. Jika klausa FROM memiliki gabungan, itu diproses terlebih dahulu.
1. Klausa CONNECT BY dievaluasi.
1. Klausul WHERE dievaluasi.
## Sintaksis
```
[START WITH start_with_conditions]
CONNECT BY connect_by_conditions
```
**catatan**
Meskipun START dan CONNECT bukan kata yang dicadangkan, gunakan pengidentifikasi yang dibatasi (tanda kutip ganda) atau AS jika Anda menggunakan START dan CONNECT sebagai alias tabel dalam kueri Anda untuk menghindari kegagalan saat runtime.
```
SELECT COUNT(*)
FROM Employee "start"
CONNECT BY PRIOR id = manager_id
START WITH name = 'John'
```
```
SELECT COUNT(*)
FROM Employee AS start
CONNECT BY PRIOR id = manager_id
START WITH name = 'John'
```
## Parameter
*start\$1with\$1conditions*
Kondisi yang menentukan baris root dari hierarki
*connect\$1by\$1conditions*
Kondisi yang menentukan hubungan antara baris induk dan baris anak dari hierarki. Setidaknya satu kondisi harus memenuhi syarat dengan operator ` ` unary yang digunakan untuk merujuk ke baris induk.
```
PRIOR column = expression
-- or
expression > PRIOR column
```
## Operator
Anda dapat menggunakan operator berikut dalam kueri CONNECT BY.
*TINGKAT*
Pseudocolumn yang mengembalikan tingkat baris saat ini dalam hierarki. Mengembalikan 1 untuk baris root, 2 untuk anak dari baris root, dan seterusnya.
*SEBELUMNYA*
Operator unary yang mengevaluasi ekspresi untuk baris induk dari baris saat ini dalam hierarki.
## Contoh
Contoh berikut adalah kueri CONNECT BY yang mengembalikan jumlah karyawan yang melaporkan secara langsung atau tidak langsung ke John, tidak lebih dari 4 level.
```
SELECT id, name, manager_id
FROM employee
WHERE LEVEL < 4
START WITH name = 'John'
CONNECT BY PRIOR id = manager_id;
```
Berikut ini adalah hasil dari query.
```
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
```
Definisi tabel untuk contoh ini:
```
CREATE TABLE employee (
id INT,
name VARCHAR(20),
manager_id INT
);
```
Berikut ini adalah baris yang dimasukkan ke dalam tabel.
```
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);
```
Berikut ini adalah bagan organisasi untuk departemen John.
![\[Diagram bagan organisasi untuk departemen John.\]](http://docs.aws.amazon.com/id_id/redshift/latest/dg/images/org-chart.png)
# Contoh subquery
Contoh berikut menunjukkan cara yang berbeda di mana subquery cocok dengan kueri SELECT. Lihat [JOIN contoh](r_Join_examples.md) contoh lain dari penggunaan subquery.
## PILIH daftar subquery
Contoh berikut berisi subquery dalam daftar SELECT. Subquery ini adalah *skalar*: ia mengembalikan hanya satu kolom dan satu nilai, yang diulang dalam hasil untuk setiap baris yang dikembalikan dari query luar. Kueri membandingkan nilai Q1SALES yang dihitung subquery dengan nilai penjualan untuk dua kuartal lainnya (2 dan 3) pada tahun 2008, seperti yang didefinisikan oleh kueri luar.
```
select qtr, sum(pricepaid) as qtrsales,
(select sum(pricepaid)
from sales join date on sales.dateid=date.dateid
where qtr='1' and year=2008) as q1sales
from sales join date on sales.dateid=date.dateid
where qtr in('2','3') and year=2008
group by qtr
order by qtr;
qtr | qtrsales | q1sales
-------+-------------+-------------
2 | 30560050.00 | 24742065.00
3 | 31170237.00 | 24742065.00
(2 rows)
```
## Subquery klausa WHERE
Contoh berikut berisi subquery tabel dalam klausa WHERE. Subquery ini menghasilkan beberapa baris. Dalam hal ini, baris hanya berisi satu kolom, tetapi subquery tabel dapat berisi beberapa kolom dan baris, sama seperti tabel lainnya.
Kueri menemukan 10 penjual teratas dalam hal tiket maksimum yang terjual. Daftar 10 teratas dibatasi oleh subquery, yang menghapus pengguna yang tinggal di kota di mana ada tempat tiket. Kueri ini dapat ditulis dengan cara yang berbeda; misalnya, subquery dapat ditulis ulang sebagai gabungan dalam kueri utama.
```
select firstname, lastname, city, max(qtysold) as maxsold
from users join sales on users.userid=sales.sellerid
where users.city not in(select venuecity from venue)
group by firstname, lastname, city
order by maxsold desc, city desc
limit 10;
firstname | lastname | city | maxsold
-----------+-----------+----------------+---------
Noah | Guerrero | Worcester | 8
Isadora | Moss | Winooski | 8
Kieran | Harrison | Westminster | 8
Heidi | Davis | Warwick | 8
Sara | Anthony | Waco | 8
Bree | Buck | Valdez | 8
Evangeline | Sampson | Trenton | 8
Kendall | Keith | Stillwater | 8
Bertha | Bishop | Stevens Point | 8
Patricia | Anderson | South Portland | 8
(10 rows)
```
## DENGAN subquery klausa
Lihat [DENGAN klausa](r_WITH_clause.md).
# Subquery yang berkorelasi
Contoh berikut berisi *subquery berkorelasi* dalam klausa WHERE; subquery semacam ini berisi satu atau lebih korelasi antara kolom dan kolom yang dihasilkan oleh kueri luar. Dalam hal ini, korelasinya adalah`where s.listid=l.listid`. Untuk setiap baris yang dihasilkan oleh kueri luar, subquery dijalankan untuk memenuhi syarat atau mendiskualifikasi baris.
```
select salesid, listid, sum(pricepaid) from sales s
where qtysold=
(select max(numtickets) from listing l
where s.listid=l.listid)
group by 1,2
order by 1,2
limit 5;
salesid | listid | sum
--------+--------+----------
27 | 28 | 111.00
81 | 103 | 181.00
142 | 149 | 240.00
146 | 152 | 231.00
194 | 210 | 144.00
(5 rows)
```
## Pola subquery berkorelasi yang tidak didukung
Perencana kueri menggunakan metode penulisan ulang kueri yang disebut decorrelation subquery untuk mengoptimalkan beberapa pola subquery berkorelasi untuk eksekusi di lingkungan MPP. Beberapa jenis subkueri berkorelasi mengikuti pola yang tidak dapat didekorasi dan tidak didukung oleh Amazon Redshift. Kueri yang berisi referensi korelasi berikut mengembalikan kesalahan:
+ Referensi korelasi yang melewati blok kueri, juga dikenal sebagai “referensi korelasi tingkat lewati.” Misalnya, dalam kueri berikut, blok yang berisi referensi korelasi dan blok yang dilewati dihubungkan oleh predikat NOT EXISTS:
```
select event.eventname from event
where not exists
(select * from listing
where not exists
(select * from sales where event.eventid=sales.eventid));
```
Blok yang dilewati dalam kasus ini adalah subquery terhadap tabel LISTING. Referensi korelasi menghubungkan tabel EVENT dan SALES.
+ Referensi korelasi dari subquery yang merupakan bagian dari klausa ON dalam kueri luar:
```
select * from category
left join event
on category.catid=event.catid and eventid =
(select max(eventid) from sales where sales.eventid=event.eventid);
```
Klausa ON berisi referensi korelasi dari SALES di subquery ke EVENT di kueri luar.
+ Referensi korelasi sensitif nol ke tabel sistem Amazon Redshift. Contoh:
```
select attrelid
from stv_locks sl, pg_attribute
where sl.table_id=pg_attribute.attrelid and 1 not in
(select 1 from pg_opclass where sl.lock_owner = opcowner);
```
+ Referensi korelasi dari dalam subquery yang berisi fungsi jendela.
```
select listid, qtysold
from sales s
where qtysold not in
(select sum(numtickets) over() from listing l where s.listid=l.listid);
```
+ Referensi dalam kolom GROUP BY ke hasil subquery yang berkorelasi. Contoh:
```
select listing.listid,
(select count (sales.listid) from sales where sales.listid=listing.listid) as list
from listing
group by list, listing.listid;
```
+ Referensi korelasi dari subquery dengan fungsi agregat dan klausa GROUP BY, terhubung ke kueri luar oleh predikat IN. (Pembatasan ini tidak berlaku untuk fungsi agregat MIN dan MAX.) Contoh:
```
select * from listing where listid in
(select sum(qtysold)
from sales
where numtickets>4
group by salesid);
```