Terjemahan disediakan oleh mesin penerjemah. Jika konten terjemahan yang diberikan bertentangan dengan versi bahasa Inggris aslinya, utamakan versi bahasa Inggris.
Fungsi jendela
Dengan menggunakan fungsi jendela, Anda dapat membuat kueri bisnis analitik dengan lebih efisien. Fungsi jendela beroperasi pada partisi atau “jendela” dari kumpulan hasil, dan mengembalikan nilai untuk setiap baris di jendela itu. Sebaliknya, fungsi non-windowed melakukan perhitungan mereka sehubungan dengan setiap baris dalam set hasil. Tidak seperti fungsi grup yang menggabungkan baris hasil, fungsi jendela mempertahankan semua baris dalam ekspresi tabel.
Nilai yang dikembalikan dihitung dengan menggunakan nilai dari kumpulan baris di jendela itu. Untuk setiap baris dalam tabel, jendela mendefinisikan satu set baris yang digunakan untuk menghitung atribut tambahan. Sebuah jendela didefinisikan menggunakan spesifikasi jendela (klausa OVER), dan didasarkan pada tiga konsep utama:
-
Partisi jendela, yang membentuk kelompok baris (klausa PARTISI)
-
Pengurutan jendela, yang mendefinisikan urutan atau urutan baris dalam setiap partisi (klausa ORDER BY)
-
Bingkai jendela, yang didefinisikan relatif terhadap setiap baris untuk lebih membatasi set baris (spesifikasi ROWS)
Fungsi jendela adalah rangkaian operasi terakhir yang dilakukan dalam kueri kecuali klausa ORDER BY akhir. Semua bergabung dan semua klausa WHERE, GROUP BY, dan HAVING selesai sebelum fungsi jendela diproses. Oleh karena itu, fungsi jendela hanya dapat muncul di daftar pilih atau klausa ORDER BY. Anda dapat menggunakan beberapa fungsi jendela dalam satu kueri dengan klausa bingkai yang berbeda. Anda juga dapat menggunakan fungsi jendela dalam ekspresi skalar lainnya, seperti CASE.
Ringkasan sintaks fungsi jendela
Fungsi jendela mengikuti sintaks standar, yaitu sebagai berikut.
function (expression) OVER ( [ PARTITION BY expr_list ] [ ORDER BY order_list [ frame_clause ] ] )
Di sini, fungsi adalah salah satu fungsi yang dijelaskan dalam bagian ini.
Expr_list adalah sebagai berikut.
expression | column_name [, expr_list ]
Order_list adalah sebagai berikut.
expression | column_name [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, order_list ]
Frame_clause adalah sebagai berikut.
ROWS { UNBOUNDED PRECEDING | unsigned_value PRECEDING | CURRENT ROW } | { BETWEEN { UNBOUNDED PRECEDING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW} AND { UNBOUNDED FOLLOWING | unsigned_value { PRECEDING | FOLLOWING } | CURRENT ROW }}
Argumen
- fungsi
-
Fungsi jendela. Untuk detailnya, lihat deskripsi fungsi individual.
- DI ATAS
-
Klausul yang mendefinisikan spesifikasi jendela. Klausa OVER wajib untuk fungsi jendela, dan membedakan fungsi jendela dari fungsi SQL lainnya.
- PARTISI OLEH expr_list
-
(Opsional) Klausa PARTITION BY membagi hasil yang ditetapkan menjadi partisi, seperti klausa GROUP BY. Jika klausa partisi hadir, fungsi dihitung untuk baris di setiap partisi. Jika tidak ada klausa partisi yang ditentukan, partisi tunggal berisi seluruh tabel, dan fungsi dihitung untuk tabel lengkap itu.
Fungsi peringkat DENSE_RANK, NTILE, RANK, dan ROW_NUMBER memerlukan perbandingan global dari semua baris dalam kumpulan hasil. Ketika klausa PARTITION BY digunakan, pengoptimal kueri dapat menjalankan setiap agregasi secara paralel dengan menyebarkan beban kerja di beberapa irisan sesuai dengan partisi. Jika klausa PARTITION BY tidak ada, langkah agregasi harus dijalankan secara serial pada satu irisan, yang dapat memiliki dampak negatif yang signifikan pada kinerja, terutama untuk cluster besar.
Amazon Redshift tidak mendukung literal string di klausa PARTITION BY.
- PESANAN BERDASARKAN order_list
-
(Opsional) Fungsi jendela diterapkan ke baris dalam setiap partisi yang diurutkan sesuai dengan spesifikasi pesanan di ORDER BY. Klausa ORDER BY ini berbeda dari dan sama sekali tidak terkait dengan klausa ORDER BY di frame_clause. Klausa ORDER BY dapat digunakan tanpa klausa PARTITION BY.
Untuk fungsi peringkat, klausa ORDER BY mengidentifikasi ukuran untuk nilai peringkat. Untuk fungsi agregasi, baris yang dipartisi harus diurutkan sebelum fungsi agregat dihitung untuk setiap frame. Untuk selengkapnya tentang jenis fungsi jendela, lihatFungsi jendela.
Pengidentifikasi kolom atau ekspresi yang mengevaluasi ke pengidentifikasi kolom diperlukan dalam daftar urutan. Baik konstanta maupun ekspresi konstan tidak dapat digunakan sebagai pengganti nama kolom.
Nilai NULLS diperlakukan sebagai grup mereka sendiri, diurutkan dan diberi peringkat sesuai dengan opsi NULLS FIRST atau NULLS LAST. Secara default, nilai NULL diurutkan dan diberi peringkat terakhir dalam urutan ASC, dan diurutkan dan diberi peringkat pertama dalam urutan DESC.
Amazon Redshift tidak mendukung literal string dalam klausa ORDER BY.
Jika klausa ORDER BY dihilangkan, urutan baris adalah nondeterministik.
catatan
Dalam sistem paralel apa pun seperti Amazon Redshift, ketika klausa ORDER BY tidak menghasilkan urutan data yang unik dan total, urutan baris tidak deterministik. Artinya, jika ekspresi ORDER BY menghasilkan nilai duplikat (urutan sebagian), urutan pengembalian baris tersebut mungkin berbeda dari satu proses Amazon Redshift ke yang berikutnya. Pada gilirannya, fungsi jendela mungkin mengembalikan hasil yang tidak terduga atau tidak konsisten. Untuk informasi selengkapnya, lihat Urutan data yang unik untuk fungsi jendela.
- column_name
-
Nama kolom yang akan dipartisi oleh atau diurutkan oleh.
- ASC | 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 | NULLS TERAKHIR
-
Opsi yang menentukan apakah NULLS harus diurutkan terlebih dahulu, sebelum nilai non-null, atau terakhir, setelah nilai non-null. Secara default, NULLS diurutkan dan diberi peringkat terakhir dalam urutan ASC, dan diurutkan dan diberi peringkat pertama dalam urutan DESC.
- frame_clause
-
Untuk fungsi agregat, klausa bingkai lebih lanjut menyempurnakan kumpulan baris di jendela fungsi saat menggunakan ORDER BY. Ini memungkinkan Anda untuk memasukkan atau mengecualikan set baris dalam hasil yang diurutkan. Klausa bingkai terdiri dari kata kunci ROWS dan penentu terkait.
Klausa bingkai tidak berlaku untuk fungsi peringkat. Selain itu, klausa bingkai tidak diperlukan ketika tidak ada klausa ORDER BY yang digunakan dalam klausa OVER untuk fungsi agregat. Jika klausa ORDER BY digunakan untuk fungsi agregat, klausa bingkai eksplisit diperlukan.
Ketika tidak ada klausa ORDER BY yang ditentukan, bingkai tersirat tidak dibatasi, setara dengan BARIS ANTARA TIDAK TERBATAS SEBELUMNYA DAN TIDAK TERBATAS BERIKUT.
- BARIS
-
Klausa ini mendefinisikan bingkai jendela dengan menentukan offset fisik dari baris saat ini.
Klausa ini menentukan baris di jendela atau partisi saat ini yang akan digabungkan dengan nilai dalam baris saat ini. Ini menggunakan argumen yang menentukan posisi baris, yang bisa sebelum atau sesudah baris saat ini. Titik referensi untuk semua bingkai jendela adalah baris saat ini. Setiap baris menjadi baris saat ini secara bergantian saat bingkai jendela meluncur ke depan di partisi.
Bingkai dapat berupa serangkaian baris sederhana hingga dan termasuk baris saat ini.
{UNBOUNDED PRECEDING | offset PRECEDING | CURRENT ROW}Atau bisa juga satu set baris antara dua batas.
BETWEEN { UNBOUNDED PRECEDING | offset { PRECEDING | FOLLOWING } | CURRENT ROW } AND { UNBOUNDED FOLLOWING | offset { PRECEDING | FOLLOWING } | CURRENT ROW }UNBOUNDED PRECEDING menunjukkan bahwa jendela dimulai pada baris pertama partisi; offset PRECEDING menunjukkan bahwa jendela memulai sejumlah baris yang setara dengan nilai offset sebelum baris saat ini. UNBOUNDED PRECEDING adalah default.
ROW SAAT INI menunjukkan jendela dimulai atau berakhir pada baris saat ini.
BERIKUT TIDAK TERBATAS menunjukkan bahwa jendela berakhir pada baris terakhir partisi; offset BERIKUT menunjukkan bahwa jendela mengakhiri sejumlah baris yang setara dengan nilai offset setelah baris saat ini.
offset mengidentifikasi jumlah fisik baris sebelum atau sesudah baris saat ini. Dalam hal ini, offset harus berupa konstanta yang mengevaluasi nilai numerik positif. Misalnya, 5 BERIKUT mengakhiri bingkai lima baris setelah baris saat ini.
Dimana BETWEEN tidak ditentukan, frame secara implisit dibatasi oleh baris saat ini. Misalnya,
ROWS 5 PRECEDINGsama denganROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Juga,ROWS UNBOUNDED FOLLOWINGsama denganROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.catatan
Anda tidak dapat menentukan bingkai di mana batas awal lebih besar dari batas akhir. Misalnya, Anda tidak dapat menentukan salah satu frame berikut.
between 5 following and 5 preceding between current row and 2 preceding between 3 following and current row
Urutan data yang unik untuk fungsi jendela
Jika klausa ORDER BY untuk fungsi jendela tidak menghasilkan urutan data yang unik dan total, urutan baris adalah nondeterministik. Jika ekspresi ORDER BY menghasilkan nilai duplikat (urutan sebagian), urutan pengembalian baris tersebut dapat bervariasi dalam beberapa kali proses. Dalam hal ini, fungsi jendela juga dapat mengembalikan hasil yang tidak terduga atau tidak konsisten.
Misalnya, kueri berikut mengembalikan hasil yang berbeda selama beberapa proses. Hasil yang berbeda ini terjadi karena order by dateid tidak menghasilkan urutan data yang unik untuk fungsi jendela SUM.
select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 1730.00 | 1730.00 1827 | 708.00 | 2438.00 1827 | 234.00 | 2672.00 ... select dateid, pricepaid, sum(pricepaid) over(order by dateid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+------------- 1827 | 234.00 | 234.00 1827 | 472.00 | 706.00 1827 | 347.00 | 1053.00 ...
Dalam hal ini, menambahkan kolom ORDER BY kedua ke fungsi jendela dapat menyelesaikan masalah.
select dateid, pricepaid, sum(pricepaid) over(order by dateid, pricepaid rows unbounded preceding) as sumpaid from sales group by dateid, pricepaid; dateid | pricepaid | sumpaid --------+-----------+--------- 1827 | 234.00 | 234.00 1827 | 337.00 | 571.00 1827 | 347.00 | 918.00 ...
Fungsi yang didukung
Amazon Redshift mendukung dua jenis fungsi jendela: agregat dan peringkat.
Berikut ini adalah fungsi agregat yang didukung:
-
Fungsi jendela STDDEV_SAMP dan STDDEV_POP(STDDEV_SAMP dan STDDEV adalah sinonim)
-
Fungsi jendela VAR_SAMP dan VAR_POP(VAR_SAMP dan VARIANCE adalah sinonim)
Berikut ini adalah fungsi peringkat yang didukung:
Contoh tabel untuk contoh fungsi jendela
Anda dapat menemukan contoh fungsi jendela tertentu dengan setiap deskripsi fungsi. Beberapa contoh menggunakan tabel bernama WINSALES, yang berisi 11 baris, seperti yang ditunjukkan berikut.
| SALESID | DATEID | SELLERID | PEMBELI | QTY | QTY_DIKIRIM |
|---|---|---|---|---|---|
| 30001 | 8/2/2003 | 3 | B | 10 | 10 |
| 10001 | 12/24/2003 | 1 | C | 10 | 10 |
| 10005 | 12/24/2003 | 1 | A | 30 | |
| 40001 | 1/9/2004 | 4 | A | 40 | |
| 10006 | 1/18/2004 | 1 | C | 10 | |
| 20001 | 2/12/2004 | 2 | B | 20 | 20 |
| 40005 | 2/12/2004 | 4 | A | 10 | 10 |
| 20002 | 2/16/2004 | 2 | C | 20 | 20 |
| 30003 | 4/18/2004 | 3 | B | 15 | |
| 30004 | 4/18/2004 | 3 | B | 20 | |
| 30007 | 9/7/2004 | 3 | C | 30 |
Script berikut membuat dan mengisi tabel WINSALES sampel.
CREATE TABLE winsales( salesid int, dateid date, sellerid int, buyerid char(10), qty int, qty_shipped int); INSERT INTO winsales VALUES (30001, '8/2/2003', 3, 'b', 10, 10), (10001, '12/24/2003', 1, 'c', 10, 10), (10005, '12/24/2003', 1, 'a', 30, null), (40001, '1/9/2004', 4, 'a', 40, null), (10006, '1/18/2004', 1, 'c', 10, null), (20001, '2/12/2004', 2, 'b', 20, 20), (40005, '2/12/2004', 4, 'a', 10, 10), (20002, '2/16/2004', 2, 'c', 20, 20), (30003, '4/18/2004', 3, 'b', 15, null), (30004, '4/18/2004', 3, 'b', 20, null), (30007, '9/7/2004', 3, 'c', 30, null);
