Fungsi jendela MaxCompute - sintaksis, parameter, contoh - MaxCompute

Fungsi jendela melakukan agregasi atau perhitungan lain pada subset data yang didefinisikan secara dinamis, umumnya digunakan untuk pemrosesan deret waktu, peringkat, dan rata-rata bergerak.

Catatan penggunaan

Fungsi jendela hanya dapat muncul dalam pernyataan SELECT.
Fungsi jendela tidak dapat disarangkan dengan fungsi jendela atau fungsi agregat lainnya.
Fungsi jendela tidak dapat digunakan bersamaan dengan fungsi agregat pada tingkat yang sama.

Indeks

MaxCompute SQL mendukung fungsi jendela berikut.

Fungsi	Fitur
AVG	Menghitung nilai rata-rata data dalam suatu jendela.
CLUSTER_SAMPLE	Menjalankan pengambilan sampel acak. Mengembalikan true jika baris tersebut diambil sebagai sampel.
COUNT	Menghitung jumlah catatan dalam suatu jendela.
CUME_DIST	Menghitung distribusi kumulatif.
DENSE_RANK	Menghitung peringkat. Peringkatnya berurutan.
FIRST_VALUE	Mengembalikan nilai dari baris pertama dalam kerangka jendela baris saat ini.
LAG	Mengembalikan nilai dari baris ke-N sebelum baris saat ini dalam suatu partisi.
LAST_VALUE	Mengembalikan nilai dari baris terakhir dalam kerangka jendela baris saat ini.
LEAD	Mengembalikan nilai dari baris ke-N setelah baris saat ini dalam suatu partisi.
MAX	Menghitung nilai maksimum dalam suatu jendela.
MEDIAN	Menghitung nilai median dalam suatu jendela.
MIN	Menghitung nilai minimum dalam suatu jendela.
NTILE	Membagi data yang diurutkan menjadi N kelompok berukuran sama dan mengembalikan nomor kelompok (dari 1 hingga N) untuk setiap baris.
NTH_VALUE	Mengembalikan nilai dari baris ke-N dalam kerangka jendela baris saat ini.
PERCENT_RANK	Menghitung peringkat sebagai persentase.
PERCENTILE_CONT	Menghitung persentil eksak.
PERCENTILE_DISC	Menghitung nilai persentil tertentu dengan mengurutkan kolom yang ditentukan secara ascending.
RANK	Menghitung peringkat. Peringkatnya mungkin tidak berurutan.
ROW_NUMBER	Menghitung nomor baris, dimulai dari 1.
STDDEV	Menghitung simpangan baku populasi. Ini merupakan alias untuk STDDEV_POP.
STDDEV_SAMP	Menghitung simpangan baku sampel.
SUM	Menghitung jumlah data dalam suatu jendela.

Sintaksis fungsi jendela

Sintaksis fungsi jendela:

<function_name>([distinct][<expression> [, ...]]) over (<window_definition>)
<function_name>([distinct][<expression> [, ...]]) over <window_name>

function_name: Fungsi jendela bawaan, fungsi agregat, atau user-defined aggregate function (UDAF).
expression: Format fungsi, yang harus sesuai dengan sintaksis fungsi tersebut.
windowing_definition: Definisi jendela. Untuk informasi lebih lanjut tentang sintaksisnya, lihat windowing_definition.
window_name: Nama jendela. Anda dapat menggunakan kata kunci window untuk mendefinisikan jendela kustom dan memberikan nama pada windowing_definition. Sintaksis untuk definisi jendela bernama (named_window_def) adalah sebagai berikut:
```
window <window_name> as (<window_definition>)
```
Berikut ini menjelaskan posisi pernyataan kustom dalam SQL:
```
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
```

windowing_definition

Sintaksis windowing_definition:

--partition_clause:
[partition by <expression> [, ...]]
--orderby_clause:
[order by <expression> [asc|desc][nulls {first|last}] [, ...]]
[<frame_clause>]

Ketika Anda menambahkan fungsi jendela ke pernyataan SELECT, data dipartisi dan diurutkan berdasarkan klausa partition by dan order by dalam definisi jendela. Jika Anda tidak menentukan klausa partition by, semua data dianggap sebagai satu partisi. Jika Anda tidak menentukan klausa order by, urutan data dalam partisi tidak dijamin. Untuk setiap baris—yang disebut sebagai baris saat ini—segmen data diekstraksi dari partisi berdasarkan frame_clause untuk membentuk jendela bagi baris tersebut. Fungsi jendela kemudian menghitung hasil untuk baris saat ini berdasarkan data dalam jendelanya.

partition by <expression> [, ...]: Opsional. Menentukan partisi. Baris dengan nilai kolom kunci partisi yang sama berada dalam partisi yang sama. Untuk informasi lebih lanjut tentang formatnya, lihat Operasi tabel.
order by <expression> [asc|desc][nulls {first|last}] [, ...]: Opsional. Menentukan cara data diurutkan dalam suatu partisi.

Catatan
Jika baris memiliki nilai order by yang sama, urutan pengurutannya tidak dijamin. Untuk memastikan urutan yang konsisten, pastikan nilai order by sedekat mungkin unik.
frame_clause: Opsional. Mendefinisikan batas-batas jendela. Untuk informasi lebih lanjut tentang frame_clause, lihat frame_clause.

filter_clause

Sintaksis filter_clause:

FILTER (WHERE filter_condition)

filter_condition adalah ekspresi Boolean, yang digunakan dengan cara yang sama seperti klausa WHERE dalam pernyataan select ... from ... where.

Jika Anda menyediakan klausa FILTER, hanya baris-baris yang filter_condition-nya bernilai true yang disertakan dalam kerangka jendela. Untuk fungsi jendela agregat (seperti COUNT, SUM, AVG, MAX, dan MIN), nilai tetap dikembalikan untuk setiap baris. Namun, baris-baris yang ekspresi FILTER-nya tidak bernilai true (seperti NULL atau false) tidak disertakan dalam kerangka jendela untuk perhitungan setiap baris. NULL dianggap sebagai false.

Contoh

Persiapkan data

-- Buat tabel.
CREATE TABLE IF NOT EXISTS mf_window_fun(key BIGINT,value BIGINT) STORED AS ALIORC;

-- Masukkan data.
insert into mf_window_fun values (1,100),(2,200),(1,150),(2,250),(3,300),(4,400),(5,500),(6,600),(7,700);

-- Kueri data dari tabel mf_window_fun.
select * from mf_window_fun;

-- Hasil berikut dikembalikan:
+------------+------------+
| key        | value      |
+------------+------------+
| 1          | 100        |
| 2          | 200        |
| 1          | 150        |
| 2          | 250        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

Kueri jumlah kumulatif baris yang nilainya lebih besar dari 100 dalam jendela.

select key,sum(value) filter(where value > 100) 
       over (partition by key order by key)  
       from mf_window_fun;

Hasil berikut dikembalikan:

+------------+------------+
| key        | _c1        |
+------------+------------+
| 1          | NULL       | -- Dilewati
| 1          | 150        |
| 2          | 200        |
| 2          | 450        |
| 3          | 300        |
| 4          | 400        |
| 5          | 500        |
| 6          | 600        |
| 7          | 700        |
+------------+------------+

Catatan

Klausa FILTER tidak menghapus baris yang gagal memenuhi filter_condition dari hasil kueri. Klausa ini hanya mengecualikannya dari perhitungan fungsi jendela. Untuk menghapus baris-baris tersebut dari output akhir, Anda harus menggunakan klausa select ... from ... where. Nilai fungsi jendela untuk baris yang dikecualikan bukan 0 atau NULL. Sebaliknya, nilainya mewarisi nilai dari baris sebelumnya.
Anda hanya dapat menggunakan klausa FILTER dengan fungsi jendela agregat, seperti COUNT, SUM, AVG, MAX, MIN, dan WM_CONCAT. Anda tidak dapat menggunakan klausa FILTER dengan fungsi non-agregat seperti RANK, ROW_NUMBER, atau NTILE. Jika dilanggar, akan terjadi kesalahan sintaksis.
Untuk menggunakan sintaksis FILTER dalam fungsi jendela, Anda harus mengaktifkan flag sesi berikut: set odps.sql.window.function.newimpl=true;.

frame_clause

Sintaksis frame_clause:

-- Format 1
{ROWS|RANGE|GROUPS} <frame_start> [<frame_exclusion>]
-- Format 2
{ROWS|RANGE|GROUPS} between <frame_start> and <frame_end> [<frame_exclusion>]

frame_clause adalah interval tertutup yang mendefinisikan batas-batas jendela. Interval ini mencakup baris-baris pada posisi frame_start dan frame_end.

ROWS|RANGE|GROUPS: Wajib. Jenis frame_clause. Aturan implementasi untuk frame_start dan frame_end bervariasi berdasarkan jenisnya.
- ROWS: Mendefinisikan batas jendela berdasarkan jumlah baris.
- RANGE: Mendefinisikan batas jendela dengan membandingkan nilai kolom order by. Biasanya, klausa order by ditentukan dalam definisi jendela. Jika tidak ada klausa order by yang ditentukan, semua baris dalam partisi memiliki nilai kolom order by yang sama. Nilai NULL dianggap sama.
- GROUPS: Semua baris dalam partisi dengan nilai kolom order by yang sama membentuk sebuah GROUP. Jika tidak ada klausa order by yang ditentukan, semua baris dalam partisi membentuk satu GROUP tunggal. Nilai NULL dianggap sama.

frame_start dan frame_end: Menentukan batas awal dan akhir jendela. frame_start wajib. frame_end opsional. Jika dihilangkan, nilai default-nya adalah CURRENT ROW.

Posisi yang ditentukan oleh frame_start harus mendahului posisi yang ditentukan oleh frame_end, atau sama dengan posisi frame_end. Dengan kata lain, frame_start lebih dekat ke awal partisi daripada frame_end. Awal partisi adalah posisi baris pertama setelah data diurutkan oleh pernyataan order by dalam definisi jendela. Tabel berikut menjelaskan nilai-nilai valid dan logika untuk frame_start dan frame_end ketika jenis frame_clause adalah ROWS, RANGE, atau GROUPS.

Jenis frame_clause	Nilai frame_start/frame_end	Deskripsi
ROWS, RANGE, GROUPS	UNBOUNDED PRECEDING	Baris pertama partisi. Penghitungan dimulai dari 1.
ROWS, RANGE, GROUPS	UNBOUNDED FOLLOWING	Baris terakhir partisi.
ROWS	CURRENT ROW	Posisi baris saat ini. Setiap baris data berkorespondensi dengan satu hasil fungsi jendela. Baris saat ini adalah baris yang sedang dihitung hasil fungsi jendelanya.
	offset PRECEDING	Posisi yang berada `offset` baris sebelum baris saat ini, menuju awal partisi. Misalnya, `0 PRECEDING` merujuk ke baris saat ini, dan `1 PRECEDING` merujuk ke baris sebelumnya. `offset` harus merupakan bilangan bulat non-negatif.
	offset FOLLOWING	Posisi yang berada `offset` baris setelah baris saat ini, menuju akhir partisi. Misalnya, `0 FOLLOWING` merujuk ke baris saat ini, dan `1 FOLLOWING` merujuk ke baris berikutnya. `offset` harus merupakan bilangan bulat non-negatif.
RANGE	CURRENT ROW	Sebagai frame_start, merujuk ke posisi baris pertama yang memiliki nilai kolom `order by` yang sama dengan baris saat ini. Sebagai frame_end, merujuk ke posisi baris terakhir yang memiliki nilai kolom `order by` yang sama dengan baris saat ini.
	offset PRECEDING	Posisi frame_start dan frame_end bergantung pada urutan `order by`. Asumsikan jendela diurutkan berdasarkan X. Xi merepresentasikan nilai X dari baris ke-i, dan Xc merepresentasikan nilai X dari baris saat ini. Posisinya dijelaskan sebagai berikut: Ketika `order by` ascending: frame_start: Posisi baris pertama yang memenuhi `Xc - Xi <= offset`. frame_end: Posisi baris terakhir yang memenuhi `Xc - Xi >= offset`. Ketika `order by` descending: frame_start: Posisi baris pertama yang memenuhi `Xi - Xc <= offset`. frame_end: Posisi baris terakhir yang memenuhi `Xi - Xc >= offset`. Tipe data yang didukung untuk kolom `order by` adalah: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATETIME, DATE, dan TIMESTAMP. Sintaksis untuk `offset` tipe data tanggal adalah sebagai berikut: `N`: Merepresentasikan N hari atau N detik. Harus merupakan bilangan bulat non-negatif. Untuk DATETIME dan TIMESTAMP, merepresentasikan N detik. Untuk DATE, merepresentasikan N hari. `interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}`: Merepresentasikan N tahun, bulan, hari, jam, menit, atau detik. Misalnya, `INTERVAL '3' YEAR` merepresentasikan 3 tahun. `INTERVAL 'N-M' YEAR TO MONTH`: Merepresentasikan N tahun dan M bulan. Misalnya, `INTERVAL '1-3' YEAR TO MONTH` merepresentasikan 1 tahun dan 3 bulan. `INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND`: Merepresentasikan D hari, H jam, M menit, S detik, dan N nanodetik. Misalnya, `INTERVAL '1 2:3:4:5' DAY TO SECOND` merepresentasikan 1 hari, 2 jam, 3 menit, 4 detik, dan 5 nanodetik.
	offset FOLLOWING	Posisi frame_start dan frame_end bergantung pada urutan `order by`. Asumsikan jendela diurutkan berdasarkan X. Xi merepresentasikan nilai X dari baris ke-i, dan Xc merepresentasikan nilai X dari baris saat ini. Posisinya dijelaskan sebagai berikut: Ketika `order by` ascending: frame_start: Posisi baris pertama yang memenuhi `Xi - Xc >= offset`. frame_end: Posisi baris terakhir yang memenuhi `Xi - Xc <= offset`. Ketika `order by` descending: frame_start: Posisi baris pertama yang memenuhi `Xc - Xi >= offset`. frame_end: Posisi baris terakhir yang memenuhi `Xc - Xi <= offset`.
GROUPS	CURRENT ROW	Sebagai frame_start, merujuk ke baris pertama dari GROUP tempat baris saat ini berada. Sebagai frame_end, merujuk ke baris terakhir dari GROUP tempat baris saat ini berada.
	offset PRECEDING	Sebagai frame_start, merujuk ke posisi baris pertama dalam GROUP yang berada `offset` GROUP sebelum GROUP baris saat ini, menuju awal partisi. Sebagai frame_end, merujuk ke posisi baris terakhir dalam GROUP yang berada `offset` GROUP sebelum GROUP baris saat ini, menuju awal partisi. Catatan Anda tidak dapat mengatur frame_start ke UNBOUNDED FOLLOWING atau frame_end ke UNBOUNDED PRECEDING.
	offset FOLLOWING	Sebagai frame_start, merujuk ke posisi baris pertama dalam GROUP yang berada `offset` GROUP setelah GROUP baris saat ini, menuju akhir partisi. Sebagai frame_end, merujuk ke posisi baris terakhir dalam GROUP yang berada `offset` GROUP setelah GROUP baris saat ini, menuju akhir partisi. Catatan Anda tidak dapat mengatur frame_start ke UNBOUNDED FOLLOWING atau frame_end ke UNBOUNDED PRECEDING.

frame_exclusion: Opsional. Digunakan untuk mengecualikan sebagian data dari jendela. Nilai validnya adalah:
- EXCLUDE NO OTHERS: Tidak mengecualikan data apa pun.
- EXCLUDE CURRENT ROW: Mengecualikan baris saat ini.
- EXCLUDE GROUP: Mengecualikan seluruh GROUP, artinya semua data dalam partisi yang memiliki nilai order by yang sama dengan baris saat ini.
- EXCLUDE TIES: Mengecualikan semua baris yang memiliki nilai order by yang sama dengan baris saat ini, kecuali baris saat ini sendiri.

Default frame_clause

Jika Anda tidak menentukan frame_clause, MaxCompute menggunakan frame_clause default untuk menentukan batas data yang disertakan dalam jendela. frame_clause default adalah:

Ketika mode kompatibilitas Hive diaktifkan (set odps.sql.hive.compatible=true;), frame_clause default adalah sebagai berikut, yang sama seperti di sebagian besar sistem SQL lainnya.
```
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```
Ketika mode kompatibilitas Hive dinonaktifkan (set odps.sql.hive.compatible=false;), jika klausa order by ditentukan dan fungsi jendela adalah AVG, COUNT, MAX, MIN, STDDEV, STDDEV_POP, STDDEV_SAMP, atau SUM, frame_clause default bertipe ROWS.
```
ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```

Contoh Batas Jendela

Asumsikan tabel `tbl` memiliki struktur pid: bigint, oid: bigint, rid: bigint dan berisi data berikut:

+------------+------------+------------+
| pid        | oid        | rid        |
+------------+------------+------------+
| 1          | NULL       | 1          |
| 1          | NULL       | 2          |
| 1          | 1          | 3          |
| 1          | 1          | 4          |
| 1          | 2          | 5          |
| 1          | 4          | 6          |
| 1          | 7          | 7          |
| 1          | 11         | 8          |
| 2          | NULL       | 9          |
| 2          | NULL       | 10         |
+------------+------------+------------+

Jendela tipe ROW

Definisi jendela 1

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Definisi jendela 2

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Definisi jendela 3

partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between 1 FOLLOWING and 3 FOLLOWING) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [2, 3, 4] |
| 1          | NULL       | 2          | [3, 4, 5] |
| 1          | 1          | 3          | [4, 5, 6] |
| 1          | 1          | 4          | [5, 6, 7] |
| 1          | 2          | 5          | [6, 7, 8] |
| 1          | 4          | 6          | [7, 8] |
| 1          | 7          | 7          | [8]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [10]   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

Definisi jendela 4

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
--Pernyataan SQL adalah sebagai berikut.
select pid, 
oid, 
rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | [1]    |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2, 3] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | [9]    |
+------------+------------+------------+--------+

Definisi jendela 5

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | NULL   |
| 1          | NULL       | 2          | NULL   |
| 1          | 1          | 3          | [1, 2] |
| 1          | 1          | 4          | [1, 2] |
| 1          | 2          | 5          | [1, 2, 3, 4] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7] |
| 2          | NULL       | 9          | NULL   |
| 2          | NULL       | 10         | NULL   |
+------------+------------+------------+--------+

Definisi jendela 6

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1]    |
| 1          | NULL       | 2          | [2]    |
| 1          | 1          | 3          | [1, 2, 3] |
| 1          | 1          | 4          | [1, 2, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9]    |
| 2          | NULL       | 10         | [10]   |
+------------+------------+------------+--------+

Dengan membandingkan hasil window untuk baris-baris yang rid-nya 2, 4, dan 10 dalam contoh ini dan contoh sebelumnya, Anda dapat mengamati perbedaan antara EXCLUDE CURRENT ROW dan EXCLUDE GROUP. Untuk EXCLUDE GROUP, dalam partisi yang sama (di mana pid sama), semua data dengan oid yang sama dengan baris saat ini dikecualikan.

Jendela tipe RANGE

Definisi jendela 1

partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [1, 2, 3, 4, 5, 6] |
| 1          | 7          | 7          | [1, 2, 3, 4, 5, 6, 7] |
| 1          | 11         | 8          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Ketika CURRENT ROW digunakan sebagai frame_end, ini mencakup semua baris hingga baris terakhir yang memiliki nilai order by oid yang sama dengan baris saat ini. Oleh karena itu, hasil window untuk catatan yang rid-nya 1 adalah [1, 2].

Definisi jendela 2

partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | NULL       | 2          | [1, 2, 3, 4, 5, 6, 7, 8] |
| 1          | 1          | 3          | [3, 4, 5, 6, 7, 8] |
| 1          | 1          | 4          | [3, 4, 5, 6, 7, 8] |
| 1          | 2          | 5          | [5, 6, 7, 8] |
| 1          | 4          | 6          | [6, 7, 8] |
| 1          | 7          | 7          | [7, 8] |
| 1          | 11         | 8          | [8]    |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Definisi jendela 3

partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
--Pernyataan SQL adalah sebagai berikut.

select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid RANGE between 3 PRECEDING and 1 PRECEDING) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | NULL   |
| 1          | 1          | 4          | NULL   |
| 1          | 2          | 5          | [3, 4] |
| 1          | 4          | 6          | [3, 4, 5] |
| 1          | 7          | 7          | [6]    |
| 1          | 11         | 8          | NULL   |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Untuk baris-baris yang nilai order by-nya oid adalah NULL, jika Anda menggunakan offset {PRECEDING|FOLLOWING} dan offset bukan UNBOUNDED, batasnya ditentukan sebagai berikut: Ketika digunakan sebagai frame_start, ini menunjuk ke baris pertama dengan nilai order by NULL dalam partisi. Ketika digunakan sebagai frame_end, ini menunjuk ke baris terakhir dengan nilai order by NULL.

Jendela tipe GROUPS

Definisi jendela adalah sebagai berikut:

partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
--Pernyataan SQL adalah sebagai berikut.
select pid, 
       oid, 
       rid, 
collect_list(rid) over(partition by pid order by 
oid GROUPS between 2 PRECEDING and CURRENT ROW) as window from tbl;

Hasil berikut dikembalikan:

+------------+------------+------------+--------+
| pid        | oid        | rid        | window |
+------------+------------+------------+--------+
| 1          | NULL       | 1          | [1, 2] |
| 1          | NULL       | 2          | [1, 2] |
| 1          | 1          | 3          | [1, 2, 3, 4] |
| 1          | 1          | 4          | [1, 2, 3, 4] |
| 1          | 2          | 5          | [1, 2, 3, 4, 5] |
| 1          | 4          | 6          | [3, 4, 5, 6] |
| 1          | 7          | 7          | [5, 6, 7] |
| 1          | 11         | 8          | [6, 7, 8] |
| 2          | NULL       | 9          | [9, 10] |
| 2          | NULL       | 10         | [9, 10] |
+------------+------------+------------+--------+

Data sampel

Contoh-contoh berikut menggunakan data sampel ini. Buat dan isi tabel emp:

create table if not exists emp
   (empno bigint,
    ename string,
    job string,
    mgr bigint,
    hiredate datetime,
    sal bigint,
    comm bigint,
    deptno bigint);
tunnel upload emp.txt emp;

File emp.txt berisi data berikut:

7369,SMITH,CLERK,7902,1980-12-17 00:00:00,800,,20
7499,ALLEN,SALESMAN,7698,1981-02-20 00:00:00,1600,300,30
7521,WARD,SALESMAN,7698,1981-02-22 00:00:00,1250,500,30
7566,JONES,MANAGER,7839,1981-04-02 00:00:00,2975,,20
7654,MARTIN,SALESMAN,7698,1981-09-28 00:00:00,1250,1400,30
7698,BLAKE,MANAGER,7839,1981-05-01 00:00:00,2850,,30
7782,CLARK,MANAGER,7839,1981-06-09 00:00:00,2450,,10
7788,SCOTT,ANALYST,7566,1987-04-19 00:00:00,3000,,20
7839,KING,PRESIDENT,,1981-11-17 00:00:00,5000,,10
7844,TURNER,SALESMAN,7698,1981-09-08 00:00:00,1500,0,30
7876,ADAMS,CLERK,7788,1987-05-23 00:00:00,1100,,20
7900,JAMES,CLERK,7698,1981-12-03 00:00:00,950,,30
7902,FORD,ANALYST,7566,1981-12-03 00:00:00,3000,,20
7934,MILLER,CLERK,7782,1982-01-23 00:00:00,1300,,10
7948,JACCKA,CLERK,7782,1981-04-12 00:00:00,5000,,10
7956,WELAN,CLERK,7649,1982-07-20 00:00:00,2450,,10
7956,TEBAGE,CLERK,7748,1982-12-30 00:00:00,1300,,10

AVG

Sintaksis

double avg([distinct] double <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal avg([distinct] decimal <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Mengembalikan nilai rata-rata expr dalam suatu jendela.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung hasilnya. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, secara implisit dikonversi ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak disertakan dalam perhitungan.
  - Jika Anda menentukan kata kunci distinct, fungsi menghitung rata-rata dari nilai-nilai unik.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Jika expr bertipe DECIMAL, nilai DECIMAL dikembalikan. Selain itu, nilai DOUBLE dikembalikan. Jika semua nilai expr adalah NULL, NULL dikembalikan.

Contoh

Contoh 1: Partisi berdasarkan departemen (deptno) dan hitung gaji rata-rata (sal) tanpa pengurutan. Fungsi menghitung gaji rata-rata untuk seluruh partisi (semua baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, avg(sal) over (partition by deptno) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2916.6666666666665 |   -- Ini adalah baris pertama jendela. Nilainya adalah rata-rata kumulatif dari baris pertama hingga keenam.
| 10         | 2450       | 2916.6666666666665 |   -- Nilainya adalah rata-rata kumulatif dari baris pertama hingga keenam.
| 10         | 5000       | 2916.6666666666665 |   -- Nilainya adalah rata-rata kumulatif dari baris pertama hingga keenam.
| 10         | 1300       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 10         | 2450       | 2916.6666666666665 |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 20         | 800        | 2175.0     |
| 20         | 1100       | 2175.0     |
| 20         | 2975       | 2175.0     |
| 30         | 1500       | 1566.6666666666667 |
| 30         | 950        | 1566.6666666666667 |
| 30         | 1600       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 1250       | 1566.6666666666667 |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Contoh 2: Dalam mode non-kompatibel Hive, partisi berdasarkan departemen (deptno), urutkan berdasarkan gaji (sal), dan hitung gaji rata-rata. Fungsi menghitung rata-rata berjalan dari baris pertama partisi hingga baris saat ini. Perintahnya adalah sebagai berikut:

-- Nonaktifkan mode kompatibel Hive.
set odps.sql.hive.compatible=false;
-- Jalankan perintah SQL berikut.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |           -- Baris pertama jendela.
| 10         | 1300       | 1300.0     |           -- Rata-rata kumulatif dari baris pertama hingga kedua.
| 10         | 2450       | 1683.3333333333333 |   -- Rata-rata kumulatif dari baris pertama hingga ketiga.
| 10         | 2450       | 1875.0     |           -- Rata-rata kumulatif dari baris pertama hingga keempat.
| 10         | 5000       | 2500.0     |           -- Rata-rata kumulatif dari baris pertama hingga kelima.
| 10         | 5000       | 2916.6666666666665 |   -- Rata-rata kumulatif dari baris pertama hingga keenam.
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 1968.75    |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1100.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

Contoh 3: Dalam mode kompatibel Hive, partisi berdasarkan departemen (deptno), urutkan berdasarkan gaji (sal), dan hitung gaji rata-rata. Fungsi menghitung rata-rata berjalan dari baris pertama partisi hingga peer terakhir baris saat ini (baris dengan sal yang sama memiliki rata-rata yang sama). Perintahnya adalah sebagai berikut:

-- Aktifkan mode kompatibel Hive.
set odps.sql.hive.compatible=true;
-- Jalankan perintah SQL berikut.
select deptno, sal, avg(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300.0     |          -- Baris pertama jendela. Karena sal baris pertama dan kedua sama, rata-rata untuk baris pertama adalah rata-rata kumulatif dua baris pertama.
| 10         | 1300       | 1300.0     |          -- Rata-rata kumulatif dari baris pertama hingga kedua.
| 10         | 2450       | 1875.0     |          -- Karena sal baris ketiga dan keempat sama, rata-rata untuk baris ketiga adalah rata-rata kumulatif empat baris pertama.
| 10         | 2450       | 1875.0     |          -- Rata-rata kumulatif dari baris pertama hingga keempat.
| 10         | 5000       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 20         | 800        | 800.0      |
| 20         | 1100       | 950.0      |
| 20         | 2975       | 1625.0     |
| 20         | 3000       | 2175.0     |
| 20         | 3000       | 2175.0     |
| 30         | 950        | 950.0      |
| 30         | 1250       | 1150.0     |
| 30         | 1250       | 1150.0     |
| 30         | 1500       | 1237.5     |
| 30         | 1600       | 1310.0     |
| 30         | 2850       | 1566.6666666666667 |
+------------+------------+------------+

CLUSTER_SAMPLE

Sintaksis

boolean cluster_sample(bigint <N>) OVER ([partition_clause])
boolean cluster_sample(bigint <N>, bigint <M>) OVER ([partition_clause])

Deskripsi
- cluster_sample(bigint <N>): Mengambil sampel acak N baris dari partisi.
- cluster_sample(bigint <N>, bigint <M>): Mengambil sampel pecahan baris (M/N) dari partisi. Jumlah baris yang diambil kira-kira partition_row_count × M / N, di mana partition_row_count adalah jumlah baris dalam partisi.
Parameter
- N: Wajib. Konstanta BIGINT. Jika N adalah NULL, nilai kembalinya adalah NULL.
- M: Wajib. Konstanta BIGINT. Jika M adalah NULL, nilai kembalinya adalah NULL.
- partition_clause: Opsional. Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai BOOLEAN.

Contoh

Untuk mengambil sampel kira-kira 20% baris dari setiap kelompok, gunakan perintah berikut:

select deptno, sal
    from (
        select deptno, sal, cluster_sample(5, 1) over (partition by deptno) as flag
        from emp
        ) sub
    where flag = true;

Hasil berikut dikembalikan:

+------------+------------+
| deptno     | sal        |
+------------+------------+
| 10         | 1300       |
| 20         | 3000       |
| 30         | 950        |
+------------+------------+

COUNT

Sintaksis

-- Hitung jumlah catatan.
BIGINT COUNT([DISTINCT|ALL] <colname>)

-- Hitung jumlah catatan dalam jendela.
BIGINT COUNT(*) OVER ([partition_clause] [orderby_clause] [frame_clause])
BIGINT COUNT([DISTINCT] <expr>[,...]) OVER ([partition_clause] [orderby_clause] [frame_clause])

Parameter

Parameter	Wajib	Deskripsi
`DISTINCT \| ALL`	Tidak	Mengontrol penanganan duplikat. `ALL` (default) menghitung semua baris non-NULL. `DISTINCT` hanya menghitung nilai non-NULL yang unik.
`colname`	Ya	Kolom yang akan dihitung. Menerima tipe data apa pun. Gunakan `*` untuk menghitung semua baris, termasuk baris yang nilainya NULL.
`expr`	Ya	Ekspresi dengan tipe data apa pun. Baris NULL dikecualikan. Dengan `DISTINCT`, hanya nilai non-NULL unik yang dihitung. `COUNT([DISTINCT] <expr>[,...]) OVER` menghitung baris di mana semua ekspresi yang ditentukan bernilai non-NULL.
`partition_clause`, `orderby_clause`, `frame_clause`	Tidak	Klausa definisi jendela. Lihat Ikhtisar Fungsi Jendela.

Nilai kembali

Mengembalikan BIGINT. Baris NULL dikecualikan kecuali Anda menggunakan COUNT(*).

Contoh

Persiapkan data uji

Jika Anda sudah memiliki data, Anda dapat melewati langkah ini.

Unduh data uji test_data.txt.

Buat tabel uji.

CREATE TABLE IF NOT EXISTS emp(
  empno BIGINT,
  ename STRING,
  job STRING,
  mgr BIGINT,
  hiredate DATETIME,
  sal BIGINT,
  comm BIGINT,
  deptno BIGINT
);

Muat data.

Ganti FILE_PATH dengan path dan nama file data Anda yang sebenarnya.
```
TUNNEL UPLOAD {{FILEPATH}} emp;
```

Contoh 1: Partisi jendela tanpa pengurutan

Partisi jendela berdasarkan sal. Tanpa ORDER BY, setiap baris mengembalikan jumlah total baris dalam partisinya.

SELECT sal, COUNT(sal) OVER (PARTITION BY sal) AS count
FROM emp;

Hasil:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Dua baris memiliki sal=1250; keduanya mengembalikan 2.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Contoh 2: Partisi jendela dengan pengurutan (mode non-kompatibel Hive)

Dalam mode non-kompatibel Hive, menambahkan ORDER BY menghasilkan jumlah berjalan. Setiap baris mengembalikan jumlah kumulatif dari baris pertama hingga baris saat ini dalam partisinya.

-- Nonaktifkan mode kompatibel Hive.
SET odps.sql.hive.compatible=false;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Hasil:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |  -- Jumlah berjalan dimulai dari 1 untuk baris pertama dalam partisi.
| 1250       | 2          |  -- Bertambah menjadi 2 untuk baris kedua.
| 1300       | 1          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 1          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 1          |
| 3000       | 2          |
| 5000       | 1          |
| 5000       | 2          |
+------------+------------+

Contoh 3: Partisi jendela dengan pengurutan (mode kompatibel Hive)

Dalam mode kompatibel Hive, ORDER BY tidak menghasilkan jumlah berjalan. Setiap baris dalam partisi mengembalikan jumlah total partisi, sama seperti menghilangkan ORDER BY.

-- Aktifkan mode kompatibel Hive.
SET odps.sql.hive.compatible=true;

SELECT sal, COUNT(sal) OVER (PARTITION BY sal ORDER BY sal) AS count
FROM emp;

Hasil:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |  -- Kedua baris dalam partisi mengembalikan jumlah penuh partisi.
| 1250       | 2          |
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

Contoh 4: Hitung semua baris dalam tabel

SELECT COUNT(*) FROM emp;

Hasil:

+------------+
| _c0        |
+------------+
| 17         |
+------------+

Contoh 5: Hitung baris per kelompok

Gunakan COUNT dengan GROUP BY untuk mendapatkan jumlah karyawan per departemen.

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno;

Hasil:

+------------+------------+
| deptno     | _c1        |
+------------+------------+
| 20         | 5          |
| 30         | 6          |
| 10         | 6          |
+------------+------------+

Contoh 6: Hitung nilai unik

Gunakan DISTINCT untuk menghitung jumlah departemen berbeda.

SELECT COUNT(DISTINCT deptno) FROM emp;

Hasil:

+------------+
| _c0        |
+------------+
| 3          |
+------------+

CUME_DIST

Sintaksis

double cume_dist() over([partition_clause] [orderby_clause])

Deskripsi

Menghitung distribusi kumulatif suatu nilai dalam sekelompok nilai. Hasilnya adalah jumlah baris dengan nilai kurang dari atau sama dengan nilai baris saat ini, dibagi dengan jumlah total baris dalam partisi. Perbandingan ditentukan oleh orderby_clause.
Parameter

partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai DOUBLE. Nilai kembali spesifik sama dengan row_number_of_last_peer / partition_row_count, di mana row_number_of_last_peer adalah nilai kembali fungsi jendela ROW_NUMBER untuk baris terakhir dari GROUP baris saat ini, dan partition_row_count adalah jumlah baris dalam partisi tempat baris tersebut berada.

Contoh

Partisi berdasarkan departemen (deptno) dan hitung distribusi kumulatif gaji (sal) dalam setiap departemen. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, concat(round(cume_dist() over (partition by deptno order by sal desc)*100,2),'%') as cume_dist from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | cume_dist  |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 33.33%     |
| 10         | KING       | 5000       | 33.33%     |
| 10         | CLARK      | 2450       | 66.67%     |
| 10         | WELAN      | 2450       | 66.67%     |
| 10         | TEBAGE     | 1300       | 100.0%     |
| 10         | MILLER     | 1300       | 100.0%     |
| 20         | SCOTT      | 3000       | 40.0%      |
| 20         | FORD       | 3000       | 40.0%      |
| 20         | JONES      | 2975       | 60.0%      |
| 20         | ADAMS      | 1100       | 80.0%      |
| 20         | SMITH      | 800        | 100.0%     |
| 30         | BLAKE      | 2850       | 16.67%     |
| 30         | ALLEN      | 1600       | 33.33%     |
| 30         | TURNER     | 1500       | 50.0%      |
| 30         | MARTIN     | 1250       | 83.33%     |
| 30         | WARD       | 1250       | 83.33%     |
| 30         | JAMES      | 950        | 100.0%     |
+------------+------------+------------+------------+

DENSE_RANK

Sintaksis

bigint dense_rank() over ([partition_clause] [orderby_clause])

Deskripsi

Menghitung peringkat baris saat ini dalam partisinya berdasarkan urutan pengurutan yang ditentukan dalam orderby_clause. Peringkat dimulai dari 1. Baris dengan nilai order by yang sama dalam suatu partisi diberi peringkat yang sama. Peringkat bertambah 1 setiap kali nilai order by berubah.
Parameter

partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai BIGINT. Jika orderby_clause tidak ditentukan, semua baris menerima peringkat 1.

Contoh

Partisi berdasarkan departemen (deptno) dan beri peringkat karyawan dalam setiap departemen berdasarkan gaji mereka (sal) secara descending. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, dense_rank() over (partition by deptno order by sal desc) as nums from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 3          |
| 20         | SMITH      | 800        | 4          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 5          |
+------------+------------+------------+------------+

FIRST_VALUE

Sintaksis

first_value(<expr>[, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Mengembalikan nilai ekspresi expr dari baris pertama dalam kerangka jendela.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung hasilnya.
- ignore_nulls: Opsional. Nilai BOOLEAN yang menentukan apakah nilai NULL diabaikan. Nilai default adalah False. Jika parameter ini diatur ke True, fungsi mengembalikan nilai non-NULL pertama dari expr dalam kerangka jendela.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr.

Contoh

Perintah berikut mengelompokkan semua karyawan berdasarkan departemen dan mengembalikan baris data pertama dari setiap kelompok:

Tanpa menentukan order by:

select deptno, ename, sal, first_value(sal) over (partition by deptno) as first_value from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 1300        |   -- Baris pertama jendela saat ini.
| 10         | CLARK      | 2450       | 1300        |
| 10         | KING       | 5000       | 1300        |
| 10         | MILLER     | 1300       | 1300        |
| 10         | JACCKA     | 5000       | 1300        |
| 10         | WELAN      | 2450       | 1300        |
| 20         | FORD       | 3000       | 3000        |   -- Baris pertama jendela saat ini.
| 20         | SCOTT      | 3000       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 30         | TURNER     | 1500       | 1500        |   -- Baris pertama jendela saat ini.
| 30         | JAMES      | 950        | 1500        |
| 30         | ALLEN      | 1600       | 1500        |
| 30         | WARD       | 1250       | 1500        |
| 30         | MARTIN     | 1250       | 1500        |
| 30         | BLAKE      | 2850       | 1500        |
+------------+------------+------------+-------------+

Menentukan order by:

select deptno, ename, sal, first_value(sal) over (partition by deptno order by sal desc) as first_value from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | first_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   -- Baris pertama jendela saat ini.
| 10         | KING       | 5000       | 5000        |
| 10         | CLARK      | 2450       | 5000        |
| 10         | WELAN      | 2450       | 5000        |
| 10         | TEBAGE     | 1300       | 5000        |
| 10         | MILLER     | 1300       | 5000        |
| 20         | SCOTT      | 3000       | 3000        |   -- Baris pertama jendela saat ini.
| 20         | FORD       | 3000       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 30         | BLAKE      | 2850       | 2850        |   -- Baris pertama jendela saat ini.
| 30         | ALLEN      | 1600       | 2850        |
| 30         | TURNER     | 1500       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | JAMES      | 950        | 2850        |
+------------+------------+------------+-------------+

LAG

Sintaksis

lag(<expr>[,bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

Deskripsi

Mengembalikan nilai ekspresi expr dari baris yang berada offset baris sebelum baris saat ini (menuju awal partisi). Ekspresi expr dapat berupa kolom, operasi kolom, atau operasi fungsi.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung.
- offset: Opsional. Offset, yaitu konstanta BIGINT yang lebih besar atau sama dengan 1. Nilai 1 menunjukkan baris sebelumnya. Nilai default adalah 1. Jika nilai input bertipe STRING atau DOUBLE, secara implisit dikonversi ke tipe BIGINT untuk perhitungan.
- default: Opsional. Menentukan nilai default yang dikembalikan ketika offset berada di luar batas. Jika parameter ini tidak ditentukan, default-nya adalah NULL. Nilainya harus merupakan konstanta dengan tipe data yang sama dengan expr. Jika expr bukan konstanta, nilai ini dievaluasi berdasarkan baris saat ini.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr.

Contoh

Partisi berdasarkan departemen (deptno) dan ambil gaji (sal) dari baris sebelumnya untuk setiap karyawan. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, lag(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |
| 10         | MILLER     | 1300       | 1300       |
| 10         | CLARK      | 2450       | 1300       |
| 10         | WELAN      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | 800        |
| 20         | JONES      | 2975       | 1100       |
| 20         | SCOTT      | 3000       | 2975       |
| 20         | FORD       | 3000       | 3000       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | 950        |
| 30         | WARD       | 1250       | 1250       |
| 30         | TURNER     | 1500       | 1250       |
| 30         | ALLEN      | 1600       | 1500       |
| 30         | BLAKE      | 2850       | 1600       |
+------------+------------+------------+------------+

LAST_VALUE

Sintaksis

last_value(<expr>[, <ignore_nulls>]) over([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Mengembalikan nilai ekspresi expr dari baris terakhir dalam kerangka jendela.

Parameter

expr: Wajib. Ekspresi yang akan dihitung.
ignore_nulls: Opsional. Nilai BOOLEAN yang menentukan apakah nilai NULL diabaikan. Nilai default adalah False. Jika parameter ini diatur ke True, fungsi mengembalikan nilai non-NULL terakhir dari expr dalam kerangka jendela.
partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr.

Contoh

Perintah berikut mengelompokkan semua karyawan berdasarkan departemen dan mengembalikan baris data terakhir dari setiap kelompok:

Tanpa klausa order by, kerangka jendela mencakup semua baris dalam partisi. Fungsi mengembalikan nilai dari baris terakhir dalam partisi.

select deptno, ename, sal, last_value(sal) over (partition by deptno) as last_value from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | TEBAGE     | 1300       | 2450        |
| 10         | CLARK      | 2450       | 2450        |
| 10         | KING       | 5000       | 2450        |
| 10         | MILLER     | 1300       | 2450        |
| 10         | JACCKA     | 5000       | 2450        |   
| 10         | WELAN      | 2450       | 2450        |   -- Baris terakhir jendela saat ini.
| 20         | FORD       | 3000       | 2975        |
| 20         | SCOTT      | 3000       | 2975        |
| 20         | SMITH      | 800        | 2975        |
| 20         | ADAMS      | 1100       | 2975        |
| 20         | JONES      | 2975       | 2975        |   -- Baris terakhir jendela saat ini.
| 30         | TURNER     | 1500       | 2850        |
| 30         | JAMES      | 950        | 2850        |
| 30         | ALLEN      | 1600       | 2850        |
| 30         | WARD       | 1250       | 2850        |
| 30         | MARTIN     | 1250       | 2850        |
| 30         | BLAKE      | 2850       | 2850        |   -- Baris terakhir jendela saat ini.
+------------+------------+------------+-------------+

Dengan klausa order by, kerangka jendela default adalah `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Fungsi mengembalikan nilai dari baris saat ini.

select deptno, ename, sal, last_value(sal) over (partition by deptno order by sal desc) as last_value from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+-------------+
| deptno     | ename      | sal        | last_value |
+------------+------------+------------+-------------+
| 10         | JACCKA     | 5000       | 5000        |   -- Baris saat ini jendela saat ini.
| 10         | KING       | 5000       | 5000        |   -- Baris saat ini jendela saat ini.
| 10         | CLARK      | 2450       | 2450        |   -- Baris saat ini jendela saat ini.
| 10         | WELAN      | 2450       | 2450        |   -- Baris saat ini jendela saat ini.
| 10         | TEBAGE     | 1300       | 1300        |   -- Baris saat ini jendela saat ini.
| 10         | MILLER     | 1300       | 1300        |   -- Baris saat ini jendela saat ini.
| 20         | SCOTT      | 3000       | 3000        |   -- Baris saat ini jendela saat ini.
| 20         | FORD       | 3000       | 3000        |   -- Baris saat ini jendela saat ini.
| 20         | JONES      | 2975       | 2975        |   -- Baris saat ini jendela saat ini.
| 20         | ADAMS      | 1100       | 1100        |   -- Baris saat ini jendela saat ini.
| 20         | SMITH      | 800        | 800         |   -- Baris saat ini jendela saat ini.
| 30         | BLAKE      | 2850       | 2850        |   -- Baris saat ini jendela saat ini.
| 30         | ALLEN      | 1600       | 1600        |   -- Baris saat ini jendela saat ini.
| 30         | TURNER     | 1500       | 1500        |   -- Baris saat ini jendela saat ini.
| 30         | MARTIN     | 1250       | 1250        |   -- Baris saat ini jendela saat ini.
| 30         | WARD       | 1250       | 1250        |   -- Baris saat ini jendela saat ini.
| 30         | JAMES      | 950        | 950         |   -- Baris saat ini jendela saat ini.
+------------+------------+------------+-------------+

LEAD

Sintaksis

lead(<expr>[, bigint <offset>[, <default>]]) over([partition_clause] orderby_clause)

Deskripsi

Mengembalikan nilai ekspresi expr dari baris yang berada offset baris setelah baris saat ini (menuju akhir partisi). Ekspresi expr dapat berupa kolom, operasi kolom, atau operasi fungsi.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung.
- offset: Opsional. Offset, yaitu konstanta BIGINT yang lebih besar atau sama dengan 0. Nilai 0 menunjukkan baris saat ini, dan nilai 1 menunjukkan baris berikutnya. Nilai default adalah 1. Jika nilai input bertipe STRING atau DOUBLE, secara implisit dikonversi ke tipe BIGINT untuk perhitungan.
- default: Opsional. Nilai yang dikembalikan jika offset berada di luar batas. Nilai ini harus merupakan konstanta dengan tipe data yang sama dengan expr. Default-nya adalah NULL. Jika expr bukan konstanta, nilai ini dievaluasi berdasarkan baris saat ini.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr.

Contoh

Partisi berdasarkan departemen (deptno) dan ambil gaji (sal) dari baris berikutnya untuk setiap karyawan. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, lead(sal, 1) over (partition by deptno order by sal) as sal_new from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 1300       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | WELAN      | 2450       | 5000       |
| 10         | KING       | 5000       | 5000       |
| 10         | JACCKA     | 5000       | NULL       |
| 20         | SMITH      | 800        | 1100       |
| 20         | ADAMS      | 1100       | 2975       |
| 20         | JONES      | 2975       | 3000       |
| 20         | SCOTT      | 3000       | 3000       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | 1250       |
| 30         | MARTIN     | 1250       | 1250       |
| 30         | WARD       | 1250       | 1500       |
| 30         | TURNER     | 1500       | 1600       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | BLAKE      | 2850       | NULL       |
+------------+------------+------------+------------+

MAX

Sintaksis

max(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Mengembalikan nilai maksimum expr dalam suatu jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung nilai maksimum. Dapat bertipe data apa pun kecuali BOOLEAN. Jika nilainya NULL, baris tersebut tidak disertakan dalam perhitungan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr.

Contoh

Contoh 1: Partisi berdasarkan departemen (deptno), hitung gaji maksimum (sal), dan jangan urutkan. Fungsi mengembalikan nilai maksimum partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, max(sal) over (partition by deptno) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 5000       |   -- Baris pertama jendela. Nilainya adalah maksimum dari baris pertama hingga keenam.
| 10         | 2450       | 5000       |   -- Nilainya adalah maksimum dari baris pertama hingga keenam.
| 10         | 5000       | 5000       |   -- Nilainya adalah maksimum dari baris pertama hingga keenam.
| 10         | 1300       | 5000       |
| 10         | 5000       | 5000       |
| 10         | 2450       | 5000       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 20         | 800        | 3000       |
| 20         | 1100       | 3000       |
| 20         | 2975       | 3000       |
| 30         | 1500       | 2850       |
| 30         | 950        | 2850       |
| 30         | 1600       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 1250       | 2850       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

Contoh 2: Partisi berdasarkan departemen (deptno), hitung gaji maksimum (sal), dan urutkan hasilnya. Fungsi mengembalikan nilai maksimum dari baris pertama hingga baris saat ini dalam partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, max(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- Baris pertama jendela.
| 10         | 1300       | 1300       |   -- Nilai maksimum dari baris pertama hingga kedua.
| 10         | 2450       | 2450       |   -- Nilai maksimum dari baris pertama hingga ketiga.
| 10         | 2450       | 2450       |   -- Nilai maksimum dari baris pertama hingga keempat.
| 10         | 5000       | 5000       |
| 10         | 5000       | 5000       |
| 20         | 800        | 800        |
| 20         | 1100       | 1100       |
| 20         | 2975       | 2975       |
| 20         | 3000       | 3000       |
| 20         | 3000       | 3000       |
| 30         | 950        | 950        |
| 30         | 1250       | 1250       |
| 30         | 1250       | 1250       |
| 30         | 1500       | 1500       |
| 30         | 1600       | 1600       |
| 30         | 2850       | 2850       |
+------------+------------+------------+

MEDIAN

Sintaksis

median(<expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Menghitung median expr dalam suatu jendela.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung mediannya. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, secara implisit dikonversi ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika input adalah NULL, nilai kembalinya adalah NULL.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai DOUBLE atau DECIMAL. Jika semua nilai expr adalah NULL, NULL dikembalikan.

Contoh

Partisi berdasarkan departemen (deptno) dan hitung gaji median (sal). Fungsi mengembalikan median untuk seluruh partisi (semua baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, median(sal) over (partition by deptno) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2450.0     |   -- Baris pertama jendela. Nilainya adalah median dari baris pertama hingga keenam.
| 10         | 2450       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 1300       | 2450.0     |
| 10         | 5000       | 2450.0     |
| 10         | 2450       | 2450.0     |
| 20         | 3000       | 2975.0     |
| 20         | 3000       | 2975.0     |
| 20         | 800        | 2975.0     |
| 20         | 1100       | 2975.0     |
| 20         | 2975       | 2975.0     |
| 30         | 1500       | 1375.0     |
| 30         | 950        | 1375.0     |
| 30         | 1600       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 1250       | 1375.0     |
| 30         | 2850       | 1375.0     |
+------------+------------+------------+

MIN

Sintaksis

min(<expr>) over([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Mengembalikan nilai minimum expr dalam suatu jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung nilai minimum. Dapat bertipe data apa pun kecuali BOOLEAN. Jika nilainya NULL, baris tersebut tidak disertakan dalam perhitungan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr.

Contoh

Contoh 1: Partisi berdasarkan departemen (deptno), hitung gaji minimum (sal), dan jangan urutkan. Fungsi mengembalikan nilai minimum partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, min(sal) over (partition by deptno) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- Baris pertama jendela. Nilainya adalah minimum dari baris pertama hingga keenam.
| 10         | 2450       | 1300       |   -- Nilainya adalah minimum dari baris pertama hingga keenam.
| 10         | 5000       | 1300       |   -- Nilainya adalah minimum dari baris pertama hingga keenam.
| 10         | 1300       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 2450       | 1300       |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 30         | 1500       | 950        |
| 30         | 950        | 950        |
| 30         | 1600       | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

Contoh 2: Partisi berdasarkan departemen (deptno), hitung gaji minimum (sal), dan urutkan hasilnya. Fungsi mengembalikan nilai minimum dari baris pertama hingga baris saat ini dalam partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, min(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- Baris pertama jendela.
| 10         | 1300       | 1300       |   -- Nilai minimum dari baris pertama hingga kedua.
| 10         | 2450       | 1300       |   -- Nilai minimum dari baris pertama hingga ketiga.
| 10         | 2450       | 1300       |
| 10         | 5000       | 1300       |
| 10         | 5000       | 1300       |
| 20         | 800        | 800        |
| 20         | 1100       | 800        |
| 20         | 2975       | 800        |
| 20         | 3000       | 800        |
| 20         | 3000       | 800        |
| 30         | 950        | 950        |
| 30         | 1250       | 950        |
| 30         | 1250       | 950        |
| 30         | 1500       | 950        |
| 30         | 1600       | 950        |
| 30         | 2850       | 950        |
+------------+------------+------------+

NTILE

Sintaksis

bigint ntile(bigint <N>) over ([partition_clause] [orderby_clause])

Deskripsi

Membagi baris-baris yang diurutkan dalam suatu partisi menjadi N kelompok, seukuran mungkin, dan mengembalikan nomor kelompok untuk setiap baris. Jika jumlah baris tidak habis dibagi N, kelompok-kelompok pertama (yang memiliki nomor kelompok lebih kecil) akan memiliki satu baris tambahan.
Parameter
- N: Wajib. Jumlah kelompok. Nilai BIGINT.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai BIGINT.

Contoh

Bagi semua karyawan menjadi 3 kelompok dalam setiap departemen berdasarkan gaji (sal) secara descending, dan kembalikan nomor kelompok untuk setiap karyawan. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, ntile(3) over (partition by deptno order by sal desc) as nt3 from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nt3        |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 2          |
| 10         | WELAN      | 2450       | 2          |
| 10         | TEBAGE     | 1300       | 3          |
| 10         | MILLER     | 1300       | 3          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 2          |
| 20         | ADAMS      | 1100       | 2          |
| 20         | SMITH      | 800        | 3          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 1          |
| 30         | TURNER     | 1500       | 2          |
| 30         | MARTIN     | 1250       | 2          |
| 30         | WARD       | 1250       | 3          |
| 30         | JAMES      | 950        | 3          |
+------------+------------+------------+------------+

NTH_VALUE

Sintaksis

nth_value(<expr>, <number> [, <ignore_nulls>]) over ([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Mengembalikan nilai ekspresi expr dari baris ke-N dalam kerangka jendela.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung.
- number: Wajib. Nilai BIGINT. Bilangan bulat yang lebih besar atau sama dengan 1. Jika nilainya 1, fungsi ini setara dengan FIRST_VALUE.
- ignore_nulls: Opsional. Nilai BOOLEAN yang menentukan apakah nilai NULL diabaikan. Nilai default adalah False. Jika parameter ini diatur ke True, fungsi mengembalikan nilai non-NULL ke-N dari expr dalam kerangka jendela.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr.

Contoh

Perintah berikut mengelompokkan semua karyawan berdasarkan departemen dan mengembalikan baris data ke-6 dari setiap kelompok:

Tanpa klausa order by, kerangka jendela mencakup semua baris dalam partisi. Fungsi mengembalikan nilai baris ke-6 dalam partisi.

select deptno, ename, sal, nth_value(sal,6) over (partition by deptno) as nth_value from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | 2450       |
| 10         | CLARK      | 2450       | 2450       |
| 10         | KING       | 5000       | 2450       |
| 10         | MILLER     | 1300       | 2450       |
| 10         | JACCKA     | 5000       | 2450       |
| 10         | WELAN      | 2450       | 2450       |   -- Baris ke-6 jendela saat ini.
| 20         | FORD       | 3000       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |   -- Jendela saat ini tidak memiliki baris ke-6, sehingga NULL dikembalikan.
| 30         | TURNER     | 1500       | 2850       |
| 30         | JAMES      | 950        | 2850       |
| 30         | ALLEN      | 1600       | 2850       |
| 30         | WARD       | 1250       | 2850       |
| 30         | MARTIN     | 1250       | 2850       |
| 30         | BLAKE      | 2850       | 2850       |   -- Baris ke-6 jendela saat ini.
+------------+------------+------------+------------+

Dengan klausa order by, kerangka jendela default adalah `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Fungsi mengembalikan nilai baris ke-6 dalam kerangka jendela.

select deptno, ename, sal, nth_value(sal,6) over (partition by deptno order by sal) as nth_value from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nth_value  |
+------------+------------+------------+------------+
| 10         | TEBAGE     | 1300       | NULL       |   
| 10         | MILLER     | 1300       | NULL       |   -- Jendela saat ini hanya memiliki 2 baris, sehingga baris ke-6 melebihi panjang jendela.
| 10         | CLARK      | 2450       | NULL       |
| 10         | WELAN      | 2450       | NULL       |
| 10         | KING       | 5000       | 5000       |  
| 10         | JACCKA     | 5000       | 5000       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | FORD       | 3000       | NULL       |
| 30         | JAMES      | 950        | NULL       |
| 30         | MARTIN     | 1250       | NULL       |
| 30         | WARD       | 1250       | NULL       |
| 30         | TURNER     | 1500       | NULL       |
| 30         | ALLEN      | 1600       | NULL       |
| 30         | BLAKE      | 2850       | 2850       |
+------------+------------+------------+------------+

PERCENT_RANK

Sintaksis

double percent_rank() over([partition_clause] [orderby_clause])

Deskripsi

Menghitung peringkat persentil baris saat ini dalam partisinya, berdasarkan urutan pengurutan yang ditentukan oleh orderby_clause.
Parameter

partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai DOUBLE dalam rentang [0.0, 1.0]. Nilai kembali spesifik sama dengan “(rank - 1) / (partition_row_count - 1)”, di mana rank adalah hasil fungsi jendela RANK untuk baris tersebut, dan partition_row_count adalah jumlah baris dalam partisi tempat baris tersebut berada. Jika partisi hanya berisi satu baris, output-nya adalah 0.0.

Contoh

Hitung peringkat persentil gaji setiap karyawan dalam departemennya. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, percent_rank() over (partition by deptno order by sal desc) as sal_new from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | sal_new    |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 0.0        |
| 10         | KING       | 5000       | 0.0        |
| 10         | CLARK      | 2450       | 0.4        |
| 10         | WELAN      | 2450       | 0.4        |
| 10         | TEBAGE     | 1300       | 0.8        |
| 10         | MILLER     | 1300       | 0.8        |
| 20         | SCOTT      | 3000       | 0.0        |
| 20         | FORD       | 3000       | 0.0        |
| 20         | JONES      | 2975       | 0.5        |
| 20         | ADAMS      | 1100       | 0.75       |
| 20         | SMITH      | 800        | 1.0        |
| 30         | BLAKE      | 2850       | 0.0        |
| 30         | ALLEN      | 1600       | 0.2        |
| 30         | TURNER     | 1500       | 0.4        |
| 30         | MARTIN     | 1250       | 0.6        |
| 30         | WARD       | 1250       | 0.6        |
| 30         | JAMES      | 950        | 1.0        |
+------------+------------+------------+------------+

PERCENTILE_CONT

Sintaksis

-- Hitung persentil eksak
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Hitung persentil eksak dalam jendela
PERCENTILE_CONT(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Deskripsi

Menghitung persentil eksak. Menggunakan algoritma interpolasi linear, mengurutkan kolom yang ditentukan secara ascending, dan mengembalikan nilai eksak pada persentil yang ditentukan.
Parameter
- col_name: Wajib. Kolom bertipe DOUBLE atau DECIMAL.
- percentile: Wajib. Persentil yang akan dihitung. Konstanta DOUBLE dalam rentang [0, 1].
- isIgnoreNull: Opsional. Menentukan apakah nilai NULL diabaikan. Konstanta BOOLEAN. Nilai default adalah TRUE. Jika diatur ke FALSE, nilai NULL diperlakukan sebagai nilai minimum selama pengurutan.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition..
Nilai Kembali

Mengembalikan nilai persentil yang dihitung sebagai DOUBLE.

Contoh

Contoh 1: Abaikan nilai NULL dan hitung persentil eksak dalam jendela.

SELECT
  PERCENTILE_CONT(x, 0) OVER() AS min,
  PERCENTILE_CONT(x, 0.01) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5) OVER() AS median,
  PERCENTILE_CONT(x, 0.9) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Hasil kembalian
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| 0.0        | 0.03        | 1.5        | 2.7          | 3.0        | 
+------------+-------------+------------+--------------+------------+

Contoh 2: Jangan abaikan nilai NULL. Nilai NULL diperlakukan sebagai nilai minimum selama pengurutan. Hitung persentil eksak dalam jendela.

SELECT
  PERCENTILE_CONT(x, 0, false) OVER() AS min,
  PERCENTILE_CONT(x, 0.01, false) OVER() AS percentile1,
  PERCENTILE_CONT(x, 0.5, false) OVER() AS median,
  PERCENTILE_CONT(x, 0.9, false) OVER() AS percentile90,
  PERCENTILE_CONT(x, 1, false) OVER() AS max
FROM VALUES(0D),(3D),(NULL),(1D),(2D) AS tbl(x) LIMIT 1;

-- Hasil kembalian
+------------+-------------+------------+--------------+------------+
| min        | percentile1 | median     | percentile90 | max        | 
+------------+-------------+------------+--------------+------------+
| NULL       | 0.0         | 1.0        | 2.6          | 3.0        | 
+------------+-------------+------------+--------------+------------+

PERCENTILE_DISC

Sintaksis

-- Hitung nilai persentil tertentu
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>])

-- Hitung nilai persentil dalam jendela
PERCENTILE_DISC(<col_name>, DOUBLE <percentile>[, BOOLEAN <isIgnoreNull>]) OVER ([partition_clause] [orderby_clause])

Deskripsi

Menghitung nilai persentil tertentu. Pertama-tama mengurutkan kolom yang ditentukan secara ascending, lalu mengembalikan nilai pertama yang distribusi kumulatifnya lebih besar atau sama dengan persentil yang ditentukan.
Parameter
- col_name: Wajib. Kolom dengan tipe data yang dapat diurutkan.
- percentile: Wajib. Persentil yang akan dihitung. Konstanta DOUBLE dalam rentang [0, 1].
- isIgnoreNull: Opsional. Menentukan apakah nilai NULL diabaikan. Konstanta BOOLEAN. Nilai default adalah TRUE. Jika diatur ke FALSE, nilai NULL diperlakukan sebagai nilai minimum selama pengurutan.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai persentil yang dihitung. Tipe datanya sama dengan kolom input col_name.

Contoh

Contoh 1: Abaikan nilai NULL dan hitung nilai persentil dalam jendela.

SELECT
  x,
  PERCENTILE_DISC(x, 0) OVER() AS min,
  PERCENTILE_DISC(x, 0.5) OVER() AS median,
  PERCENTILE_DISC(x, 1) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Hasil kembalian
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | a          | b          | c          | 
| NULL       | a          | b          | c          | 
| b          | a          | b          | c          | 
| a          | a          | b          | c          | 
+------------+------------+------------+------------+

Contoh 2: Jangan abaikan nilai NULL. Nilai NULL diperlakukan sebagai nilai minimum selama pengurutan. Hitung nilai persentil dalam jendela.

SELECT
  x,
  PERCENTILE_DISC(x, 0, false) OVER() AS min,
  PERCENTILE_DISC(x, 0.5, false) OVER() AS median,
  PERCENTILE_DISC(x, 1, false) OVER() AS max
FROM VALUES('c'),(NULL),('b'),('a') AS tbl(x);

-- Hasil kembalian
+------------+------------+------------+------------+
| x          | min        | median     | max        | 
+------------+------------+------------+------------+
| c          | NULL       | a          | c          | 
| NULL       | NULL       | a          | c          | 
| b          | NULL       | a          | c          | 
| a          | NULL       | a          | c          | 
+------------+------------+------------+------------+

RANK

Sintaksis

bigint rank() over ([partition_clause] [orderby_clause])

Deskripsi

Menghitung peringkat baris saat ini dalam partisinya, berdasarkan urutan pengurutan yang ditentukan oleh orderby_clause. Penghitungan dimulai dari 1.
Parameter

partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Mengembalikan nilai BIGINT. Nilai kembali bisa duplikat dan tidak berurutan. Nilai kembali spesifik adalah nilai ROW_NUMBER() dari baris pertama dalam GROUP tempat baris data tersebut berada. Jika orderby_clause tidak ditentukan, semua baris menerima peringkat 1.

Contoh

Partisi berdasarkan departemen (deptno) dan beri peringkat karyawan dalam setiap departemen berdasarkan gaji mereka (sal) secara descending. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, rank() over (partition by deptno order by sal desc) as nums from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 1          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 3          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 5          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 1          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 4          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

ROW_NUMBER

Sintaksis

row_number() over([partition_clause] [orderby_clause])

Deskripsi

Menghitung nomor baris baris saat ini dalam partisinya, dimulai dari 1.
Parameter

Untuk informasi lebih lanjut, lihat windowing_definition. frame_clause tidak diperbolehkan.
Nilai Kembali

Mengembalikan nilai BIGINT.

Contoh

Partisi berdasarkan departemen (deptno) dan berikan nomor unik berurutan untuk setiap karyawan dalam departemennya, berdasarkan gaji (sal) secara descending. Perintahnya adalah sebagai berikut:

select deptno, ename, sal, row_number() over (partition by deptno order by sal desc) as nums from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+------------+
| deptno     | ename      | sal        | nums       |
+------------+------------+------------+------------+
| 10         | JACCKA     | 5000       | 1          |
| 10         | KING       | 5000       | 2          |
| 10         | CLARK      | 2450       | 3          |
| 10         | WELAN      | 2450       | 4          |
| 10         | TEBAGE     | 1300       | 5          |
| 10         | MILLER     | 1300       | 6          |
| 20         | SCOTT      | 3000       | 1          |
| 20         | FORD       | 3000       | 2          |
| 20         | JONES      | 2975       | 3          |
| 20         | ADAMS      | 1100       | 4          |
| 20         | SMITH      | 800        | 5          |
| 30         | BLAKE      | 2850       | 1          |
| 30         | ALLEN      | 1600       | 2          |
| 30         | TURNER     | 1500       | 3          |
| 30         | MARTIN     | 1250       | 4          |
| 30         | WARD       | 1250       | 5          |
| 30         | JAMES      | 950        | 6          |
+------------+------------+------------+------------+

STDDEV

Sintaksis

double stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])
decimal stddev|stddev_pop([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Menghitung simpangan baku populasi. Ini merupakan alias untuk fungsi STDDEV_POP.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung simpangan baku populasinya. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, secara implisit dikonversi ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak disertakan dalam perhitungan.
  - Jika Anda menentukan kata kunci distinct, fungsi menghitung simpangan baku populasi dari nilai-nilai unik.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr. Jika semua nilai expr adalah NULL, NULL dikembalikan.

Contoh

Contoh 1: Partisi berdasarkan departemen (deptno), hitung simpangan baku populasi gaji (sal), dan jangan urutkan. Fungsi mengembalikan simpangan baku populasi kumulatif partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, stddev(sal) over (partition by deptno) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1546.1421524412158 |   -- Baris pertama jendela. Nilainya adalah simpangan baku populasi kumulatif dari baris pertama hingga keenam.
| 10         | 2450       | 1546.1421524412158 |   -- Nilainya adalah simpangan baku populasi kumulatif dari baris pertama hingga keenam.
| 10         | 5000       | 1546.1421524412158 |
| 10         | 1300       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 10         | 2450       | 1546.1421524412158 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 800        | 1004.7387720198718 |
| 20         | 1100       | 1004.7387720198718 |
| 20         | 2975       | 1004.7387720198718 |
| 30         | 1500       | 610.1001739241042 |
| 30         | 950        | 610.1001739241042 |
| 30         | 1600       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 1250       | 610.1001739241042 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Contoh 2: Dalam mode non-kompatibel Hive, partisi berdasarkan departemen (deptno), hitung simpangan baku populasi gaji (sal), dan urutkan hasilnya. Fungsi mengembalikan simpangan baku populasi kumulatif dari baris pertama hingga baris saat ini dalam partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

-- Nonaktifkan mode kompatibel Hive.
set odps.sql.hive.compatible=false;
-- Jalankan perintah SQL berikut.
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- Baris pertama jendela.
| 10         | 1300       | 0.0        |           -- Simpangan baku populasi kumulatif dari baris pertama hingga kedua.
| 10         | 2450       | 542.1151989096865 |    -- Simpangan baku populasi kumulatif dari baris pertama hingga ketiga.
| 10         | 2450       | 575.0      |           -- Simpangan baku populasi kumulatif dari baris pertama hingga keempat.
| 10         | 5000       | 1351.6656391282572 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1024.2947268730811 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 150.0      |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

Contoh 3: Dalam mode kompatibel Hive, partisi berdasarkan departemen (deptno), hitung simpangan baku populasi gaji (sal), dan urutkan hasilnya. Fungsi mengembalikan simpangan baku populasi kumulatif dari baris pertama hingga baris dengan nilai yang sama dengan baris saat ini (baris dengan sal yang sama memiliki simpangan baku populasi yang sama). Perintahnya adalah sebagai berikut:

-- Aktifkan mode kompatibel Hive.
set odps.sql.hive.compatible=true;
-- Jalankan perintah SQL berikut.
select deptno, sal, stddev(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |           -- Baris pertama jendela. Karena sal baris pertama dan kedua sama, simpangan baku populasi untuk baris pertama adalah simpangan baku populasi kumulatif dua baris pertama.
| 10         | 1300       | 0.0        |           -- Simpangan baku populasi kumulatif dari baris pertama hingga kedua.
| 10         | 2450       | 575.0      |           -- Karena sal baris ketiga dan keempat sama, simpangan baku populasi untuk baris ketiga adalah simpangan baku populasi kumulatif empat baris pertama.
| 10         | 2450       | 575.0      |           -- Simpangan baku populasi kumulatif dari baris pertama hingga keempat.
| 10         | 5000       | 1546.1421524412158 |
| 10         | 5000       | 1546.1421524412158 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 150.0      |
| 20         | 2975       | 962.4188277460079 |
| 20         | 3000       | 1004.7387720198718 |
| 20         | 3000       | 1004.7387720198718 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1250       | 141.4213562373095 |
| 30         | 1500       | 194.8557158514987 |
| 30         | 1600       | 226.71568097509268 |
| 30         | 2850       | 610.1001739241042 |
+------------+------------+------------+

STDDEV_SAMP

Sintaksis

double stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])
decimal stddev_samp([distinct] <expr>) over([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Menghitung simpangan baku sampel.
Parameter
- expr: Wajib. Ekspresi yang akan dihitung simpangan baku sampelnya. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, secara implisit dikonversi ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak disertakan dalam perhitungan.
  - Jika Anda menentukan kata kunci distinct, fungsi menghitung simpangan baku sampel dari nilai-nilai unik.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali

Nilai kembali memiliki tipe data yang sama dengan expr. Jika semua nilai expr adalah NULL, NULL dikembalikan. Jika jendela hanya berisi satu nilai non-NULL untuk expr, hasilnya adalah 0.

Contoh

Contoh 1: Partisi berdasarkan departemen (deptno), hitung simpangan baku sampel gaji (sal), dan jangan urutkan. Fungsi mengembalikan simpangan baku sampel kumulatif partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, stddev_samp(sal) over (partition by deptno) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1693.7138680032904 |   -- Baris pertama jendela. Nilainya adalah simpangan baku sampel kumulatif dari baris pertama hingga keenam.
| 10         | 2450       | 1693.7138680032904 |   -- Nilainya adalah simpangan baku sampel kumulatif dari baris pertama hingga keenam.
| 10         | 5000       | 1693.7138680032904 |   -- Nilainya adalah simpangan baku sampel kumulatif dari baris pertama hingga keenam.
| 10         | 1300       | 1693.7138680032904 |     
| 10         | 5000       | 1693.7138680032904 |
| 10         | 2450       | 1693.7138680032904 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 3000       | 1123.3320969330487 |
| 20         | 800        | 1123.3320969330487 |
| 20         | 1100       | 1123.3320969330487 |
| 20         | 2975       | 1123.3320969330487 |
| 30         | 1500       | 668.331255192114 |
| 30         | 950        | 668.331255192114 |
| 30         | 1600       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 1250       | 668.331255192114 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

Contoh 2: Partisi berdasarkan departemen (deptno), hitung simpangan baku sampel gaji (sal), dan urutkan hasilnya. Fungsi mengembalikan simpangan baku sampel kumulatif dari baris pertama hingga baris saat ini dalam partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, stddev_samp(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 0.0        |          -- Baris pertama jendela.
| 10         | 1300       | 0.0        |          -- Simpangan baku sampel kumulatif dari baris pertama hingga kedua.
| 10         | 2450       | 663.9528095680697 |   -- Simpangan baku sampel kumulatif dari baris pertama hingga ketiga.
| 10         | 2450       | 663.9528095680696 |
| 10         | 5000       | 1511.2081259707413 |
| 10         | 5000       | 1693.7138680032904 |
| 20         | 800        | 0.0        |
| 20         | 1100       | 212.13203435596427 |
| 20         | 2975       | 1178.7175234126282 |
| 20         | 3000       | 1182.7536725793752 |
| 20         | 3000       | 1123.3320969330487 |
| 30         | 950        | 0.0        |
| 30         | 1250       | 212.13203435596427 |
| 30         | 1250       | 173.20508075688772 |
| 30         | 1500       | 225.0      |
| 30         | 1600       | 253.4758371127315 |
| 30         | 2850       | 668.331255192114 |
+------------+------------+------------+

SUM

Sintaksis

sum([distinct] <expr>) over ([partition_clause] [orderby_clause] [frame_clause])

Deskripsi

Mengembalikan jumlah expr dalam suatu jendela.
Parameter
- expr: Wajib. Kolom yang akan dihitung jumlahnya. Harus bertipe DOUBLE, DECIMAL, atau BIGINT.
  - Jika nilai input bertipe STRING, secara implisit dikonversi ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak disertakan dalam perhitungan.
  - Jika Anda menentukan kata kunci distinct, fungsi menghitung jumlah nilai-nilai unik.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Kembali
- Jika nilai input bertipe BIGINT, nilai BIGINT dikembalikan.
- Jika nilai input bertipe DECIMAL, nilai DECIMAL dikembalikan.
- Jika nilai input bertipe DOUBLE atau STRING, nilai DOUBLE dikembalikan.
- Jika semua nilai input adalah NULL, NULL dikembalikan.

Contoh

Contoh 1: Partisi berdasarkan departemen (deptno), hitung jumlah gaji (sal), dan jangan urutkan. Fungsi mengembalikan jumlah kumulatif partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

select deptno, sal, sum(sal) over (partition by deptno) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 17500      |   -- Baris pertama jendela. Nilainya adalah jumlah kumulatif dari baris pertama hingga keenam.
| 10         | 2450       | 17500      |   -- Nilainya adalah jumlah kumulatif dari baris pertama hingga keenam.
| 10         | 5000       | 17500      |   -- Nilainya adalah jumlah kumulatif dari baris pertama hingga keenam.
| 10         | 1300       | 17500      |
| 10         | 5000       | 17500      |
| 10         | 2450       | 17500      |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 20         | 800        | 10875      |
| 20         | 1100       | 10875      |
| 20         | 2975       | 10875      |
| 30         | 1500       | 9400       |
| 30         | 950        | 9400       |
| 30         | 1600       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 1250       | 9400       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Contoh 2: Dalam mode non-kompatibel Hive, partisi berdasarkan departemen (deptno), hitung jumlah gaji (sal), dan urutkan hasilnya. Fungsi mengembalikan jumlah kumulatif dari baris pertama hingga baris saat ini dalam partisi saat ini (baris dengan deptno yang sama). Perintahnya adalah sebagai berikut:

-- Nonaktifkan mode kompatibel Hive.
set odps.sql.hive.compatible=false;
-- Jalankan perintah SQL berikut.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- Baris pertama jendela.
| 10         | 1300       | 2600       |   -- Jumlah kumulatif dari baris pertama hingga kedua.
| 10         | 2450       | 5050       |   -- Jumlah kumulatif dari baris pertama hingga ketiga.
| 10         | 2450       | 7500       |
| 10         | 5000       | 12500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 7875       |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 2200       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Contoh 3: Dalam mode kompatibel Hive, partisi berdasarkan departemen (deptno), hitung jumlah gaji (sal), dan urutkan hasilnya. Fungsi mengembalikan jumlah kumulatif dari baris pertama hingga baris dengan nilai yang sama dengan baris saat ini (baris dengan sal yang sama memiliki jumlah yang sama). Perintahnya adalah sebagai berikut:

-- Aktifkan mode kompatibel Hive.
set odps.sql.hive.compatible=true;
-- Jalankan perintah SQL berikut.
select deptno, sal, sum(sal) over (partition by deptno order by sal) from emp;

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2600       |   -- Baris pertama jendela. Karena sal baris pertama dan kedua sama, jumlah untuk baris pertama adalah jumlah kumulatif dua baris pertama.
| 10         | 1300       | 2600       |   -- Jumlah kumulatif dari baris pertama hingga kedua.
| 10         | 2450       | 7500       |   -- Karena sal baris ketiga dan keempat sama, jumlah untuk baris ketiga adalah jumlah kumulatif empat baris pertama.
| 10         | 5000       | 17500      |
| 10         | 5000       | 17500      |
| 20         | 800        | 800        |
| 20         | 1100       | 1900       |
| 20         | 2975       | 4875       |
| 20         | 3000       | 10875      |
| 20         | 3000       | 10875      |
| 30         | 950        | 950        |
| 30         | 1250       | 3450       |
| 30         | 1250       | 3450       |
| 30         | 1500       | 4950       |
| 30         | 1600       | 6550       |
| 30         | 2850       | 9400       |
+------------+------------+------------+

Referensi

Jika fungsi bawaan tidak memenuhi kebutuhan Anda, Anda dapat membuat fungsi yang didefinisikan pengguna. Ikhtisar UDF MaxCompute.
Isu umum SQL MaxCompute:
Kode kesalahan umum untuk fungsi bawaan:
- Kode kesalahan umum untuk fungsi bawaan
- FAQ tentang fungsi bawaan