Pengenalan fungsi jendela MaxCompute - MaxCompute - Alibaba Cloud Documentation Center

Fungsi jendela melakukan agregasi atau perhitungan lain pada subset data yang didefinisikan secara dinamis. Fungsi ini sering digunakan untuk tugas seperti pemrosesan data time series, peringkat, dan perhitungan rata-rata bergerak. Topik ini menjelaskan sintaksis, parameter, dan contoh fungsi jendela yang didukung oleh MaxCompute SQL.

Penerapan

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 level 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. Peringkat tersebut berurutan.
FIRST_VALUE	Mengembalikan nilai dari baris pertama dalam frame 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 frame 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 terurut 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 frame 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. Peringkat tersebut 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.

Sintaks fungsi jendela

Sintaksis untuk fungsi jendela adalah sebagai berikut.

<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 untuk windowing_definition adalah sebagai berikut.

--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 pengurutan data 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 sedapat mungkin unik.
frame_clause: Opsional. Mendefinisikan batas-batas jendela. Untuk informasi lebih lanjut tentang frame_clause, lihat frame_clause.

filter_clause

Sintaksis untuk filter_clause adalah sebagai berikut.

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 membuat filter_condition bernilai true yang dimasukkan ke dalam frame jendela. Untuk fungsi jendela agregat (seperti COUNT, SUM, AVG, MAX, dan MIN), nilai tetap dikembalikan untuk setiap baris. Namun, baris-baris di mana ekspresi FILTER tidak bernilai true (seperti NULL atau false) tidak dimasukkan ke dalam frame 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 dilakukan, 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 untuk frame_clause adalah sebagai berikut.

-- 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 tergantung 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 satu 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 berdasarkan pernyataan order by dalam definisi window. Tabel berikut menjelaskan nilai yang 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 pada baris saat ini, dan `1 PRECEDING` merujuk pada baris sebelumnya. `offset` harus berupa bilangan bulat non-negatif.
	offset FOLLOWING	Posisi yang berada `offset` baris setelah baris saat ini, menuju akhir partisi. Misalnya, `0 FOLLOWING` merujuk pada baris saat ini, dan `1 FOLLOWING` merujuk pada baris berikutnya. `offset` harus berupa bilangan bulat non-negatif.
RANGE	CURRENT ROW	Sebagai frame_start, merujuk pada posisi baris pertama yang memiliki nilai kolom `order by` yang sama dengan baris saat ini. Sebagai frame_end, merujuk pada 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 pada baris ke-i, dan Xc merepresentasikan nilai X pada baris saat ini. Posisi-posisi tersebut dijelaskan sebagai berikut: Ketika `order by` bersifat menaik: 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. Sintaks untuk `offset` tipe tanggal adalah sebagai berikut: `N`: Merepresentasikan N hari atau N detik. Harus berupa 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. Contohnya, `INTERVAL '3' YEAR` merepresentasikan 3 tahun. `INTERVAL 'N-M' YEAR TO MONTH`: Merepresentasikan N tahun dan M bulan. Contohnya, `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. Contohnya, `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 pada baris ke-i, dan Xc merepresentasikan nilai X pada baris saat ini. Posisi-posisi tersebut 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 pada baris pertama dari GROUP tempat baris saat ini berada. Sebagai frame_end, merujuk pada baris terakhir dari GROUP tempat baris saat ini berada.
	offset PRECEDING	Sebagai frame_start, merujuk pada posisi baris pertama dalam GROUP yang berada `offset` GROUP sebelum GROUP baris saat ini, menuju awal partisi. Sebagai frame_end, merujuk pada 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 pada posisi baris pertama dalam GROUP yang berada `offset` GROUP setelah GROUP baris saat ini, menuju akhir partisi. Sebagai frame_end, merujuk pada 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 itu sendiri.

Default frame_clause

Jika Anda tidak menentukan frame_clause, MaxCompute menggunakan frame_clause default untuk menentukan batas data yang dimasukkan ke 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 dengan kebanyakan 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 di mana rid adalah 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 di mana rid adalah 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 di mana nilai order by 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

Untuk membantu Anda memahami cara menggunakan setiap fungsi, topik ini menyediakan data sumber dan contoh fungsi berdasarkan data tersebut. Perintah contoh berikut membuat tabel bernama emp dan menambahkan data ke dalamnya:

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 untuk menghitung hasilnya. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut dikonversi secara implisit ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak dimasukkan ke 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. Jika tidak, 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 sekitar 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

Sintaks

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

DISTINCT|ALL: Opsional. Menentukan apakah akan menghapus catatan duplikat sebelum menghitung. Nilai default-nya adalah ALL, yang menghitung semua catatan. Jika Anda menentukan DISTINCT, fungsi hanya menghitung catatan unik.
colname: Wajib. Kolom yang nilainya ingin Anda hitung. Parameter ini dapat bertipe data apa pun. Anda dapat menentukan * untuk colname, seperti dalam COUNT(*). Dalam kasus ini, fungsi mengembalikan jumlah total baris. Baris di mana nilai colname adalah NULL tidak dimasukkan ke dalam perhitungan.
expr: Wajib. Ekspresi. Parameter ini dapat bertipe data apa pun. Baris di mana ekspresi bernilai NULL tidak dimasukkan ke dalam perhitungan. Jika Anda menentukan kata kunci DISTINCT, fungsi mengembalikan jumlah nilai unik.
COUNT([DISTINCT] <expr>[,...]): Menghitung jumlah baris dalam jendela yang ditentukan di mana nilai semua ekspresi yang ditentukan bukan NULL. Jika Anda menentukan kata kunci DISTINCT, fungsi menghitung baris setelah menghapus baris duplikat.
partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut, lihat windowing_definition.

Nilai kembali

Fungsi mengembalikan nilai BIGINT. Baris di mana colname adalah NULL tidak dimasukkan ke dalam perhitungan.

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 FILE_PATH emp;   
```

Contoh 1: Tentukan kolom untuk jendela dan kembalikan jumlah kumulatif tanpa pengurutan

Contoh ini mempartisi jendela berdasarkan kolom gaji (sal). Data tidak diurutkan. Fungsi mengembalikan jumlah kumulatif semua baris dalam jendela saat ini. Jendela berisi semua baris yang memiliki nilai sal yang sama.

Perintah contoh

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

Hasil

+------------+------------+
| sal        | count      | 
+------------+------------+
| 800        | 1          | 
| 950        | 1          | 
| 1100       | 1          | 
| 1250       | 2          |  -- Partisi berisi dua baris di mana sal adalah 1250. Fungsi mengembalikan 2 untuk kedua baris.
| 1250       | 2          |  -- Jumlahnya juga 2 untuk baris kedua dalam partisi.
| 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: Dalam mode non-kompatibel Hive, tentukan kolom untuk jendela dan kembalikan jumlah kumulatif setelah pengurutan

Dalam mode non-kompatibel Hive, contoh ini mempartisi jendela berdasarkan kolom gaji (sal) dan mengurutkan data. Fungsi mengembalikan jumlah kumulatif dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela berisi semua baris yang memiliki nilai sal yang sama.

Perintah contoh

-- 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;

Nilai kembali

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 1          |   -- Partisi dimulai. Jumlah berjalan untuk baris pertama adalah 1.
| 1250       | 2          |   -- Jumlah berjalan untuk baris kedua adalah 2.
| 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: Dalam mode kompatibel Hive, tentukan kolom untuk jendela dan kembalikan jumlah kumulatif setelah pengurutan

Dalam mode kompatibel Hive, contoh ini mempartisi jendela berdasarkan kolom gaji (sal) dan mengurutkan data. Fungsi mengembalikan jumlah kumulatif dari baris pertama hingga baris terakhir dalam jendela saat ini. Jendela berisi semua baris yang memiliki nilai sal yang sama.

Perintah contoh

-- 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          |   -- Partisi berisi dua baris di mana sal adalah 1250. Fungsi mengembalikan 2 untuk kedua baris.
| 1250       | 2          |   -- Jumlahnya juga 2 untuk baris kedua dalam partisi.
| 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: Kembalikan jumlah total baris

Hitung jumlah total karyawan di semua departemen.

Perintah contoh
```
SELECT COUNT(*) FROM emp;
```

Hasil

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

Contoh 5: Kelompokkan data dan hitung total untuk setiap kelompok

Contoh ini menggunakan fungsi COUNT dengan GROUP BY untuk mengelompokkan semua karyawan berdasarkan departemen (deptno) dan menghitung jumlah karyawan di setiap departemen.

Perintah contoh

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

Hasil

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

Contoh 6: Hitung nilai unik

Contoh ini menggunakan DISTINCT untuk menghapus nilai duplikat dan menghitung jumlah departemen.

Perintah contoh
```
SELECT COUNT(DISTINCT deptno) FROM emp;
```

Nilai kembali

+------------+
| _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 (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 frame jendela.
Parameter
- expr: Wajib. Ekspresi untuk menghitung hasilnya.
- ignore_nulls: Opsional. Nilai BOOLEAN yang menentukan apakah akan mengabaikan nilai NULL. Nilai default-nya adalah False. Jika parameter ini diatur ke True, fungsi mengembalikan nilai non-NULL pertama dari expr dalam frame 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 untuk menghitung hasilnya.
- offset: Opsional. Offset, yang merupakan konstanta BIGINT lebih besar atau sama dengan 1. Nilai 1 menunjukkan baris sebelumnya. Nilai default-nya adalah 1. Jika nilai input bertipe STRING atau DOUBLE, nilai tersebut dikonversi secara implisit ke tipe BIGINT untuk perhitungan.
- default: Opsional. Menentukan nilai default yang dikembalikan ketika offset di luar batas. Jika parameter ini tidak ditentukan, nilai default-nya adalah NULL. Nilai ini harus berupa 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 frame jendela.
Parameter
- expr: Wajib. Ekspresi untuk menghitung hasilnya.
- ignore_nulls: Opsional. Nilai BOOLEAN yang menentukan apakah akan mengabaikan nilai NULL. Nilai default-nya adalah False. Jika parameter ini diatur ke True, fungsi mengembalikan nilai non-NULL terakhir dari expr dalam frame 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, frame 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, frame 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 untuk menghitung hasilnya.
- offset: Opsional. Offset, yang merupakan konstanta BIGINT lebih besar atau sama dengan 0. Nilai 0 menunjukkan baris saat ini, dan nilai 1 menunjukkan baris berikutnya. Nilai default-nya adalah 1. Jika nilai input bertipe STRING atau DOUBLE, nilai tersebut dikonversi secara implisit ke tipe BIGINT untuk perhitungan.
- default: Opsional. Nilai yang dikembalikan jika offset di luar batas. Nilai ini harus berupa konstanta dengan tipe data yang sama dengan expr. Nilai 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 dimasukkan ke 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 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 untuk menghitung median. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut dikonversi secara implisit 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 dimasukkan ke 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 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 terurut dalam suatu partisi menjadi N kelompok, seukuran mungkin, dan mengembalikan nomor kelompok untuk setiap baris. Jika jumlah baris tidak habis dibagi N, 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

Beri nomor kelompok 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 frame jendela.
Parameter
- expr: Wajib. Ekspresi untuk menghitung hasilnya.
- number: Wajib. Nilai BIGINT. Bilangan bulat lebih besar atau sama dengan 1. Jika nilainya 1, fungsi ini setara dengan FIRST_VALUE.
- ignore_nulls: Opsional. Nilai BOOLEAN yang menentukan apakah akan mengabaikan nilai NULL. Nilai default-nya adalah False. Jika parameter ini diatur ke True, fungsi mengembalikan nilai non-NULL ke-N dari expr dalam frame 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, frame 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, frame jendela default adalah `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. Fungsi mengembalikan nilai baris ke-6 dalam frame 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, outputnya 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. Fungsi ini 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 akan mengabaikan nilai NULL. Konstanta BOOLEAN. Nilai default-nya adalah TRUE. Jika diatur ke FALSE, nilai NULL diperlakukan sebagai nilai minimum selama pengurutan.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat Fungsi jendela.
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. Fungsi ini 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 akan mengabaikan nilai NULL. Konstanta BOOLEAN. Nilai default-nya adalah TRUE. Jika diatur ke FALSE, nilai NULL diperlakukan sebagai nilai minimum selama pengurutan.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut, lihat Fungsi jendela.
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 mungkin diduplikasi 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 (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 diizinkan.
Nilai kembali
Mengembalikan nilai BIGINT.

Contoh

Partisi berdasarkan departemen (deptno) dan berikan nomor unik berurutan kepada 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 untuk menghitung simpangan baku populasi. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut dikonversi secara implisit ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak dimasukkan ke 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 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 untuk menghitung simpangan baku sampel. Harus bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut dikonversi secara implisit ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak dimasukkan ke 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 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 untuk menghitung jumlahnya. Harus bertipe DOUBLE, DECIMAL, atau BIGINT.
  - Jika nilai input bertipe STRING, nilai tersebut dikonversi secara implisit ke tipe DOUBLE untuk perhitungan. Kesalahan dikembalikan untuk tipe data lainnya.
  - Jika nilai input adalah NULL, baris tersebut tidak dimasukkan ke 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 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, MaxCompute juga mendukung user-defined function. Untuk informasi lebih lanjut tentang user-defined function, lihat Ikhtisar UDF MaxCompute.
Untuk informasi lebih lanjut tentang masalah umum dengan MaxCompute SQL, lihat:
Untuk informasi lebih lanjut tentang kode kesalahan umum dan masalah dengan fungsi bawaan MaxCompute, lihat:
- Kode kesalahan umum untuk fungsi bawaan
- FAQ tentang fungsi bawaan