Ringkasan sintaks fungsi jendela Urutan data yang unik untuk fungsi jendela Fungsi yang didukung Contoh tabel untuk contoh fungsi jendela

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. Jendela didefinisikan menggunakan spesifikasi jendela (OVERklausa), dan didasarkan pada tiga konsep utama:

Partisi jendela, yang membentuk kelompok baris (klausa) PARTITION
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 (ROWSspesifikasi)

Fungsi jendela adalah rangkaian operasi terakhir yang dilakukan dalam kueri kecuali klausa ORDER BY akhir. Semua gabungan dan semuaWHERE, GROUP BY, dan HAVING klausa 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, sepertiCASE.

Fungsi jendela tidak dapat disarangkan. Misalnya, fungsi agregat SUM dapat muncul di dalam fungsi jendelaSUM, tetapi fungsi jendela tidak SUM dapat muncul di dalam fungsi SUM jendela lain. Berikut ini tidak didukung karena fungsi jendela bersarang di fungsi jendela lain.


SELECT SUM(SUM(selectcol) OVER (PARTITION BY ordercol)) OVER (Partition by ordercol) FROM t;

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.

OVER

Klausul yang mendefinisikan spesifikasi jendela. OVERKlausul ini wajib untuk fungsi jendela, dan membedakan fungsi jendela dari fungsi lainnyaSQL.

PARTITIONOLEH expr_list

(Opsional) Klausa PARTITION BY membagi hasil yang ditetapkan menjadi partisi, seperti klausa BY. GROUP 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 PARTITION klausa BY.

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

NULLSnilai diperlakukan sebagai kelompok mereka sendiri, diurutkan dan diberi peringkat sesuai dengan NULLS LAST opsi NULLS FIRST atau. Secara default, NULL nilai diurutkan dan diberi peringkat terakhir dalam ASC urutan, dan diurutkan dan diberi peringkat pertama dalam DESC urutan.

Amazon Redshift tidak mendukung literal string di ORDER klausa 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 FIRST | NULLS LAST

Opsi yang menentukan apakah NULLS harus diurutkan terlebih dahulu, sebelum nilai-nilai non-null, atau terakhir, setelah nilai-nilai non-null. Secara default, NULLS diurutkan dan diberi peringkat terakhir dalam ASC urutan, dan diurutkan dan peringkat pertama dalam DESC pemesanan.

frame_clause

Untuk fungsi agregat, klausa bingkai lebih lanjut menyempurnakan kumpulan baris di jendela fungsi saat menggunakan BY. ORDER Ini memungkinkan Anda untuk memasukkan atau mengecualikan set baris dalam hasil yang diurutkan. Klausa bingkai terdiri dari ROWS kata kunci dan penentu terkait.

Klausa bingkai tidak berlaku untuk fungsi peringkat. Juga, klausa bingkai tidak diperlukan ketika tidak ada klausa ORDER BY yang digunakan dalam OVER klausa 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. ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

ROWS

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 }

UNBOUNDEDPRECEDINGmenunjukkan bahwa jendela dimulai pada baris pertama partisi; offset PRECEDING menunjukkan bahwa jendela memulai sejumlah baris yang setara dengan nilai offset sebelum baris saat ini. UNBOUNDEDPRECEDINGadalah default.

CURRENTROWmenunjukkan jendela dimulai atau berakhir pada baris saat ini.

UNBOUNDEDFOLLOWINGmenunjukkan bahwa jendela berakhir pada baris terakhir partisi; offset FOLLOWING 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 FOLLOWING mengakhiri bingkai lima baris setelah baris saat ini.

BETWEENDimana tidak ditentukan, frame secara implisit dibatasi oleh baris saat ini. Misalnya, ROWS 5 PRECEDING sama denganROWS BETWEEN 5 PRECEDING AND CURRENT ROW. Juga, ROWS UNBOUNDED FOLLOWING sama 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 SUM jendela.


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:

AVGfungsi jendela
COUNTfungsi jendela
CUME_ fungsi DIST jendela
DENSE_ fungsi RANK jendela
FIRST_ fungsi VALUE jendela
LAGfungsi jendela
LAST_ fungsi VALUE jendela
LEADfungsi jendela
LISTAGGfungsi jendela
MAXfungsi jendela
MEDIANfungsi jendela
MINfungsi jendela
NTH_ fungsi VALUE jendela
PERCENTILE_ fungsi CONT jendela
PERCENTILE_ fungsi DISC jendela
RATIOFungsi jendela _TO_ REPORT
STDDEV_ SAMP dan STDDEV _ fungsi POP jendela(STDDEV_ SAMP dan STDDEV merupakan sinonim)
SUMfungsi jendela
VAR_ SAMP dan VAR _ fungsi POP jendela(VAR_ SAMP dan VARIANCE merupakan 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 bernamaWINSALES, yang berisi 11 baris, seperti yang ditunjukkan berikut.

SALESID	DATEID	SELLERID	BUYERID	QTY	QTY_SHIPPED
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 WINSALES tabel 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);

Awas Javascript dinonaktifkan atau tidak tersedia di browser Anda.

Untuk menggunakan Dokumentasi AWS, Javascript harus diaktifkan. Lihat halaman Bantuan browser Anda untuk petunjuk.

Konvensi Dokumen

TO_VARBYTE

AVG