Referensi detail untuk semua fungsi window yang didukung - MaxCompute

Fungsi jendela digunakan untuk mengumpulkan atau menghitung data dari subset data yang ditentukan secara dinamis. Sebagai contoh, Anda dapat menggunakan fungsi jendela untuk memproses data deret waktu, menghitung peringkat data tertentu, dan menghitung rata-rata bergerak dari data tertentu. Topik ini menjelaskan sintaksis dan parameter dari fungsi jendela yang didukung oleh MaxCompute SQL serta memberikan contoh penggunaannya dalam pengembangan data.

Tabel berikut mencantumkan fungsi jendela yang didukung oleh MaxCompute SQL.

Fungsi	Deskripsi
AVG	Menghitung nilai rata-rata data dalam jendela.
CLUSTER_SAMPLE	Mensampling baris data acak. Jika true dikembalikan, baris data yang ditentukan di-sampling.
COUNT	Menghitung jumlah baris dalam jendela.
CUME_DIST	Menghitung distribusi kumulatif data dalam partisi.
DENSE_RANK	Menghitung peringkat persentil dari sebuah baris dalam sekelompok baris. Peringkatnya berurutan.
FIRST_VALUE	Mendapatkan hasil perhitungan dari baris pertama data dalam jendela tempat baris saat ini berada.
LAG	Mendapatkan hasil perhitungan dari baris ke-N yang mendahului baris saat ini pada offset tertentu dalam jendela.
LAST_VALUE	Mendapatkan hasil perhitungan dari baris terakhir data dalam jendela tempat baris saat ini berada.
LEAD	Mendapatkan hasil perhitungan dari baris ke-N yang mengikuti baris saat ini pada offset tertentu dalam jendela.
MAX	Menghitung nilai maksimum dalam jendela.
MEDIAN	Menghitung median dalam jendela.
MIN	Menghitung nilai minimum dalam jendela.
NTILE	Membagi baris data dalam partisi menjadi N kelompok dengan ukuran sama dan mengembalikan nomor kelompok tempat baris saat ini berada. Nomor kelompok berkisar dari 1 hingga N.
NTH_VALUE	Mendapatkan hasil perhitungan dari baris ke-N data dalam jendela tempat baris saat ini berada.
PERCENT_RANK	Menghitung peringkat persentil dari sebuah baris dalam sekelompok baris. Fungsi ini mengembalikan nilai persentase.
RANK	Menghitung peringkat persentil dari sebuah baris dalam sekelompok baris. Peringkat mungkin tidak berupa bilangan bulat berturut-turut.
ROW_NUMBER	Menghitung nomor urutan baris. Nomor baris dimulai dari 1.
STDDEV	Mengembalikan standar deviasi populasi dari semua nilai input. Fungsi ini juga disebut STDDEV_POP.
STDDEV_SAMP	Mengembalikan standar deviasi sampel dari semua nilai input.
SUM	Menghitung jumlah data dalam jendela.

Batasan

Sebelum menggunakan fungsi jendela, perhatikan batasan berikut:

Fungsi jendela hanya didukung dalam pernyataan SELECT.
Fungsi jendela tidak dapat berisi fungsi jendela bersarang atau fungsi agregat.
Fungsi jendela tidak dapat digunakan bersamaan dengan fungsi agregat tingkat yang sama.

Sintaksis

Sintaksis 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 bawaan, atau fungsi agregat yang didefinisikan pengguna (UDAF). Untuk informasi lebih lanjut tentang fungsi agregat bawaan, lihat Fungsi Agregat. Untuk informasi lebih lanjut tentang UDAF, lihat Ikhtisar.
expression: Format fungsi jendela, bergantung pada sintaksis fungsi tersebut.
windowing_definition: Definisi jendela. Untuk informasi lebih lanjut tentang sintaksis windowing_definition, lihat windowing_definition.
window_name: Nama jendela. Anda dapat menggunakan kata kunci window untuk mengonfigurasi jendela dan menggunakan windowing_definition untuk menentukan nama jendela. Sintaksis named_window_def:
```
window <window_name> as (<window_definition>)
```
Posisi named_window_def dalam pernyataan SQL:
```
select ... from ... [where ...] [group by ...] [having ...] named_window_def [order by ...] [limit ...]
```

windowing_definition

Sintaksis

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

Jika Anda menggunakan fungsi jendela dalam pernyataan SELECT, data dipartisi dan diurutkan berdasarkan partition by dan order by dalam windowing_definition ketika fungsi jendela dieksekusi. Jika pernyataan SELECT tidak mencakup partition by, hanya ada satu partisi yang mencakup semua data. Jika pernyataan SELECT tidak mencakup order by, data dalam partisi disusun dalam urutan acak, dan aliran data dibuat. Setelah aliran data dibuat, sekelompok baris diekstraksi dari aliran data berdasarkan frame_clause dalam windowing_definition untuk membuat jendela untuk baris saat ini. Fungsi jendela menghitung data yang termasuk dalam jendela tempat baris saat ini berada.

partition by <expression> [, ...]: Opsional. Parameter ini menentukan informasi partisi. Jika nilai kolom kunci partisi sama untuk sekelompok baris, baris-baris tersebut termasuk dalam jendela yang sama. Untuk informasi lebih lanjut tentang format partition by, lihat Operasi Tabel.
order by <expression> [asc|desc][nulls {first|last}] [, ...]: Opsional. Parameter ini menentukan cara mengurutkan baris data dalam jendela.
Catatan
Jika nilai kolom yang ditentukan dalam order by sama, hasil pengurutan mungkin tidak akurat. Untuk mengurangi pengurutan acak data, pastikan bahwa nilai kolom yang ditentukan dalam order by unik.
frame_clause: Opsional. Parameter ini digunakan untuk menentukan batas data jendela. Untuk informasi lebih lanjut tentang frame_clause, lihat frame_clause.

filter_clause

Sintaksis

FILTER (WHERE filter_condition)

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

Jika klausa FILTER disediakan, hanya baris yang memiliki nilai filter_condition bernilai true yang termasuk dalam frame jendela. Untuk fungsi jendela agregat seperti COUNT, SUM, AVG, MAX, MIN, dan WM_CONCAT, nilai tetap dikembalikan untuk setiap baris. Namun, nilai non-true seperti NULL dan false tidak termasuk dalam frame jendela dari baris mana pun. NULL diperlakukan sama seperti false.

Contoh

Menyiapkan Data

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

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

-- Meminta data dalam 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        |
+------------+------------+

Meminta 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 tidak memenuhi kondisi yang ditentukan oleh filter_condition dari hasil kueri, dan menganggap bahwa baris tersebut tidak ada selama perhitungan fungsi jendela. Jika Anda ingin menghapus baris tertentu, Anda masih perlu menentukan baris tersebut dalam klausa WHERE dalam pernyataan select ... from ... where. Nilai fungsi jendela baris ini bukan 0 atau NULL tetapi nilai fungsi jendela baris sebelumnya.
Klausa FILTER hanya dapat digunakan ketika fungsi jendela adalah fungsi agregat seperti COUNT, SUM, AVG, MAX, MIN, atau WM_CONCAT. Klausa FILTER tidak dapat digunakan ketika fungsi jendela adalah fungsi non-agregat seperti RANK, ROW_NUMBER, atau NTILE. Jika tidak, kesalahan sintaksis mungkin terjadi.
Jika Anda ingin menggunakan sintaksis FILTER dalam fungsi jendela, Anda harus menambahkan konfigurasi set odps.sql.window.function.newimpl=true;.

frame_clause

Sintaksis

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

frame_clause adalah interval tertutup yang digunakan untuk menentukan batas data jendela. Batas data ditentukan berdasarkan baris yang ditentukan oleh frame_start dan frame_end.

ROWS|RANGE|GROUPS: Wajib. ROWS, RANGE, dan GROUPS menunjukkan jenis frame_clause. Aturan implementasi frame_start dan frame_end bervariasi berdasarkan jenis frame_clause.
- ROWS: Batas data jendela ditentukan berdasarkan jumlah baris.
- RANGE: Batas data jendela ditentukan berdasarkan hasil perbandingan nilai kolom yang ditentukan dalam order by. Dalam banyak kasus, order by ditentukan dalam windowing_definition. Jika order by tidak ditentukan dalam windowing_definition, nilai kolom yang ditentukan dalam order by sama untuk semua baris dalam partisi. Nilai null dianggap setara.
- Dalam partisi, baris-baris yang memiliki nilai kolom yang sama dengan yang ditentukan dalam order by membentuk grup. Jika order by tidak ditentukan, semua baris dalam partisi membentuk grup. Nilai null dianggap setara.

frame_start dan frame_end: Baris awal dan akhir jendela. frame_start wajib. frame_end opsional. Jika frame_end tidak ditentukan, nilai default CURRENT ROW digunakan.

Baris yang ditentukan oleh frame_start harus mendahului atau sama dengan baris yang ditentukan oleh frame_end. Dibandingkan dengan baris yang ditentukan oleh frame_end, baris yang ditentukan oleh frame_start lebih dekat dengan baris pertama dalam jendela setelah semua data dalam jendela diurutkan berdasarkan kolom yang ditentukan dalam order by dari windowing_definition. Tabel berikut menjelaskan nilai valid dan logika frame_start dan frame_end ketika jenis frame_clause adalah ROWS, RANGE, atau GROUPS.

Jenis frame_clause	Nilai frame_start atau frame_end	Deskripsi
ROWS, RANGE, dan GROUPS	UNBOUNDED PRECEDING	Menunjukkan baris pertama dari partisi. Baris dihitung mulai dari 1.
ROWS, RANGE, dan GROUPS	UNBOUNDED FOLLOWING	Menunjukkan baris terakhir dari partisi.
ROWS	CURRENT ROW	Menunjukkan baris saat ini. Setiap baris data sesuai dengan hasil perhitungan oleh fungsi jendela. Baris saat ini menunjukkan baris yang datanya dihitung menggunakan fungsi jendela.
	offset PRECEDING	Menunjukkan baris ke-N yang mendahului baris saat ini pada offset tertentu dalam `offset`. Sebagai contoh, jika `0 PRECEDING` menunjukkan baris saat ini, `1 PRECEDING` menunjukkan baris sebelumnya. `offset` harus berupa bilangan bulat non-negatif.
	offset FOLLOWING	Menunjukkan baris ke-N yang mengikuti baris saat ini pada offset tertentu dalam `offset`. Sebagai contoh, jika `0 FOLLOWING` menunjukkan baris saat ini, `1 FOLLOWING` menunjukkan baris berikutnya. `offset` harus berupa bilangan bulat non-negatif.
RANGE	CURRENT ROW	Jika frame_start disetel ke CURRENT ROW, itu menunjukkan baris pertama yang memiliki nilai kolom yang sama dengan baris saat ini seperti yang ditentukan dalam `order by`. Jika frame_end disetel ke CURRENT ROW, itu menunjukkan baris terakhir yang memiliki nilai kolom yang sama dengan baris saat ini seperti yang ditentukan dalam `order by`.
	offset PRECEDING	Baris-baris yang ditentukan oleh frame_start dan frame_end ditentukan berdasarkan urutan pengurutan yang ditentukan oleh `order by`. Sebagai contoh, data dalam jendela diurutkan berdasarkan kolom X, Xi menunjukkan nilai X yang sesuai dengan baris ke-i, dan Xc menunjukkan nilai X yang sesuai dengan baris saat ini. Posisi baris yang ditentukan oleh frame_start dan frame_end: `order by` disetel ke asc: frame_start menunjukkan baris pertama yang memenuhi persyaratan berikut: `Xc - Xi <= offset`. frame_end menunjukkan baris terakhir yang memenuhi persyaratan berikut: `Xc - Xi >= offset`. `order by` disetel ke desc: frame_start menunjukkan baris pertama yang memenuhi persyaratan berikut: `Xi - Xc <= offset`. frame_end menunjukkan baris terakhir yang memenuhi persyaratan berikut: `Xi - Xc >= offset`. Kolom yang ditentukan oleh `order by` dapat berupa tipe data berikut: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATETIME, DATE, dan TIMESTAMP. Sintaksis untuk `offset` tipe tanggal: `N`: menunjukkan N hari atau N detik. Harus berupa bilangan bulat non-negatif. Untuk offset tipe DATETIME atau TIMESTAMP, itu menunjukkan N detik. Untuk offset tipe DATE, itu menunjukkan N hari. `interval 'N' {YEAR\MONTH\DAY\HOUR\MINUTE\SECOND}`: menunjukkan N tahun, bulan, hari, jam, menit, atau detik. Sebagai contoh, `INTERVAL '3' YEAR` menunjukkan 3 tahun. `INTERVAL 'N-M' YEAR TO MONTH`: menunjukkan N tahun dan M bulan. Sebagai contoh, `INTERVAL '1-3' YEAR TO MONTH` menunjukkan 1 tahun dan 3 bulan. `INTERVAL 'D[ H[:M[:S[:N]]]]' DAY TO SECOND`: menunjukkan D hari, H jam, M menit, S detik, dan N nanodetik. Sebagai contoh, `INTERVAL '1 2:3:4:5' DAY TO SECOND` menunjukkan 1 hari, 2 jam, 3 menit, 4 detik, dan 5 nanodetik.
	offset FOLLOWING	Baris-baris yang ditentukan oleh frame_start dan frame_end ditentukan berdasarkan urutan pengurutan yang ditentukan oleh `order by`. Sebagai contoh, data dalam jendela diurutkan berdasarkan kolom X, Xi menunjukkan nilai X yang sesuai dengan baris ke-i, dan Xc menunjukkan nilai X yang sesuai dengan baris saat ini. Posisi baris yang ditentukan oleh frame_start dan frame_end: `order by` disetel ke asc: frame_start: menunjukkan baris pertama yang memenuhi persyaratan berikut: `Xi - Xc >= offset`. frame_end: menunjukkan baris terakhir yang memenuhi persyaratan berikut: `Xi - Xc <= offset`. `order by` disetel ke desc: frame_start: menunjukkan baris pertama yang memenuhi persyaratan berikut: `Xc - Xi >= offset`. frame_end: menunjukkan baris terakhir yang memenuhi persyaratan berikut: `Xc - Xi <= offset`.
GROUPS	CURRENT ROW	Jika frame_start disetel ke CURRENT ROW, itu menunjukkan baris pertama dari grup tempat baris saat ini berada. Jika frame_end disetel ke CURRENT ROW, itu menunjukkan baris terakhir dari grup tempat baris saat ini berada.
	offset PRECEDING	Jika frame_start disetel ke offset PRECEDING, itu menunjukkan baris pertama dari grup ke-N yang mendahului grup baris saat ini pada offset tertentu dalam `offset`. Jika frame_end disetel ke offset PRECEDING, itu menunjukkan baris terakhir dari grup ke-N yang mendahului grup baris saat ini pada offset tertentu dalam `offset`. Catatan Anda tidak dapat menyetel frame_start ke UNBOUNDED FOLLOWING, dan Anda tidak dapat menyetel frame_end ke UNBOUNED PRECEDING.
	offset FOLLOWING	Jika frame_start disetel ke offset FOLLOWING, itu menunjukkan baris pertama dari grup ke-N yang mengikuti grup baris saat ini pada offset tertentu dalam `offset`. Jika frame_end disetel ke offset FOLLOWING, baris terakhir dari grup ke-N yang mengikuti grup baris saat ini pada offset tertentu dalam `offset` digunakan. Catatan Anda tidak dapat menyetel frame_start ke UNBOUNDED FOLLOWING, dan Anda tidak dapat menyetel frame_end ke UNBOUNED PRECEDING.

frame_exclusion: Opsional. Parameter ini digunakan untuk menghapus baris tertentu dari jendela. Nilai valid:
- EXCLUDE NO OTHERS: Tidak ada baris yang dikecualikan dari jendela.
- EXCLUDE CURRENT ROW: Baris saat ini dikecualikan dari jendela.
- EXCLUDE GROUP: Seluruh grup baris dalam partisi dikecualikan dari jendela. Dalam grup tersebut, semua baris memiliki nilai kolom yang sama dengan baris saat ini seperti yang ditentukan dalam order by.
- EXCLUDE TIES: Seluruh grup baris, kecuali baris saat ini, dikecualikan dari jendela.

frame_clause Default

Jika Anda tidak menentukan frame_clause, MaxCompute menggunakan frame_clause default untuk menentukan batas data dari sebuah jendela. Nilai dari frame_clause default:

Jika odps.sql.hive.compatible diatur ke true, maka frame_clause default berikut digunakan. Aturan ini berlaku untuk sebagian besar sistem SQL.
```
RANGE between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```
Jika odps.sql.hive.compatible diatur ke false, order by ditentukan, dan salah satu dari fungsi jendela berikut digunakan, maka frame_clause default dalam mode ROWS digunakan: AVG, COUNT, MAX, MIN, STDDEV, STEDEV_POP, STDDEV_SAMP, dan SUM.
```
ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE NO OTHERS
```

Contoh Batas Data Jendela

Dalam contoh ini, tabel bernama tbl berisi tiga kolom bertipe BIGINT: pid, oid, dan rid. Tabel tbl 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 dalam Mode ROWS

windowing_definition 1

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW
-- Contoh pernyataan SQL: 
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] |
+------------+------------+------------+--------+

windowing_definition 2

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
-- Contoh pernyataan SQL: 
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] |
+------------+------------+------------+--------+

windowing_definition 3

partition by pid order by oid ROWS between 1 FOLLOWING and 3 FOLLOWING
-- Contoh pernyataan SQL: 
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   |
+------------+------------+------------+--------+

windowing_definition 4

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE CURRENT ROW
-- Contoh pernyataan SQL: 
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]    |
+------------+------------+------------+--------+

windowing_definition 5

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE GROUP
-- Contoh pernyataan SQL: 
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   |
+------------+------------+------------+--------+

windowing_definition 6

partition by pid order by oid ROWS between UNBOUNDED PRECEDING and CURRENT ROW EXCLUDE TIES
-- Contoh pernyataan SQL: 
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]   |
+------------+------------+------------+--------+

Perbedaan antara EXCLUDE CURRENT ROW dan EXCLUDE GROUP dapat diperoleh berdasarkan perbandingan antara nilai kolom window baris-baris dengan nilai kolom rid 2, 4, dan 10 dalam Sintaksis 5 dan Sintaksis 6. Jika frame_exclusion disetel ke EXCLUDE GROUP, baris-baris yang memiliki nilai kolom pid sama dalam partisi diekstraksi ketika baris-baris tersebut memiliki nilai kolom oid sama dengan baris saat ini.

Jendela dalam Mode RANGE

windowing_definition 1

partition by pid order by oid RANGE between UNBOUNDED PRECEDING and CURRENT ROW
-- Contoh pernyataan SQL: 
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] |
+------------+------------+------------+--------+

Jika frame_end disetel ke CURRENT ROW, baris terakhir yang memiliki nilai kolom oid sama dengan baris saat ini dalam order by diambil. Oleh karena itu, nilai kolom window baris yang memiliki nilai kolom rid 1 adalah [1, 2].

windowing_definition 2

partition by pid order by oid RANGE between CURRENT ROW and UNBOUNDED FOLLOWING
-- Contoh pernyataan SQL: 
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] |
+------------+------------+------------+--------+

windowing_definition 3

partition by pid order by oid RANGE between 3 PRECEDING and 1 PRECEDING
-- Contoh pernyataan SQL: 

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 yang nilai oid di dalam order by bernilai null, jika frame_start diatur ke offset PRECEDING or offset FOLLOWING, baris tersebut adalah baris pertama yang nilai oid di dalam order by bernilai null. Jika frame_end diatur ke offset PRECEDING atau offset FOLLOWING, baris tersebut adalah baris terakhir yang nilai oid di dalam order by bernilai null.

Jendela dalam Mode GROUPS

windowing_definition

partition by pid order by oid GROUPS between 2 PRECEDING and CURRENT ROW
-- Contoh pernyataan SQL: 
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

Berikut ini adalah data sampel sumber untuk membantu Anda memahami cara menggunakan fungsi-fungsi tersebut. Buat tabel bernama emp dan masukkan data sampel ke dalam tabel. Perintah contoh:

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 sampel 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 dari expr dalam jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung hasil yang dikembalikan. Nilai bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut secara implisit dikonversi menjadi nilai bertipe DOUBLE sebelum perhitungan. Jika nilai input bertipe data lain, kesalahan akan dikembalikan.
  - Jika nilai input adalah null, baris yang berisi nilai tersebut tidak digunakan untuk perhitungan.
  - Jika kata kunci distinct ditentukan, nilai rata-rata dari nilai-nilai unik dihitung.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Jika nilai input dari expr bertipe DECIMAL, nilai bertipe DECIMAL dikembalikan. Jika nilai input bertipe data lain, nilai bertipe DOUBLE dikembalikan. Jika nilai input dari expr adalah null, null dikembalikan.

Contoh

Contoh 1: Gunakan kolom deptno untuk mendefinisikan jendela dan hitung nilai rata-rata kolom sal. Klausa order by tidak ditentukan. Fungsi ini mengembalikan nilai rata-rata kumulatif dari baris pertama hingga baris terakhir dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2916.6666666666665 |   -- Baris ini adalah baris pertama dari jendela ini. Nilai pengembalian adalah nilai rata-rata kumulatif dari baris pertama hingga keenam.
| 10         | 1300       | 2916.6666666666665 |   -- Nilai pengembalian adalah nilai rata-rata kumulatif dari baris pertama hingga keenam.
| 10         | 2450       | 2916.6666666666665 |   -- Nilai pengembalian adalah nilai rata-rata kumulatif dari baris pertama hingga keenam.
| 10         | 2450       | 2916.6666666666665 |
| 10         | 5000       | 2916.6666666666665 |
| 10         | 5000       | 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: Nonaktifkan edisi tipe data kompatibel Hive dan gunakan kolom deptno untuk mendefinisikan jendela serta hitung nilai rata-rata kolom sal. ORDER BY ditentukan. Fungsi ini mengembalikan nilai rata-rata kumulatif dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

-- Nonaktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=false;
-- Jalankan pernyataan 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 ini adalah baris pertama dari jendela ini. 
| 10         | 1300       | 1300.0     |           -- Nilai pengembalian adalah nilai rata-rata kumulatif dari nilai-nilai pada baris pertama dan kedua. 
| 10         | 2450       | 1683.3333333333333 |   -- Nilai pengembalian adalah nilai rata-rata kumulatif dari baris pertama hingga ketiga.
| 10         | 2450       | 1875.0     |           -- Nilai pengembalian adalah nilai rata-rata kumulatif dari baris pertama hingga keempat.
| 10         | 5000       | 2500.0     |           -- Nilai pengembalian adalah nilai rata-rata kumulatif dari baris pertama hingga kelima.
| 10         | 5000       | 2916.6666666666665 |   -- Nilai pengembalian adalah nilai 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: Aktifkan edisi tipe data kompatibel Hive dan gunakan kolom deptno untuk mendefinisikan jendela serta hitung nilai rata-rata kolom sal. ORDER BY ditentukan. Fungsi ini mengembalikan nilai rata-rata kumulatif dari baris pertama hingga baris yang memiliki nilai sal sama dengan baris saat ini dalam jendela saat ini. Nilai rata-rata untuk baris-baris yang memiliki nilai sal sama adalah sama. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

-- Aktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=true;
-- Jalankan pernyataan 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 ini adalah baris pertama dari jendela ini. Nilai rata-rata untuk baris pertama adalah nilai rata-rata kumulatif dari nilai-nilai pada baris pertama dan kedua karena kedua baris tersebut memiliki nilai sal yang sama.
| 10         | 1300       | 1300.0     |          -- Nilai pengembalian adalah nilai rata-rata kumulatif dari nilai-nilai pada baris pertama dan kedua.
| 10         | 2450       | 1875.0     |          -- Nilai rata-rata untuk baris ketiga adalah nilai rata-rata kumulatif dari nilai-nilai dari baris pertama hingga keempat karena baris ketiga dan keempat memiliki nilai sal yang sama.
| 10         | 2450       | 1875.0     |          -- Nilai pengembalian adalah nilai rata-rata kumulatif dari nilai-nilai 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>): Menentukan bahwa N baris data acak di-sampling.
- cluster_sample(bigint <N>, bigint <M>): Menentukan bahwa baris-baris di-sampling berdasarkan rasio tertentu (M/N). Jumlah baris yang di-sampling dihitung menggunakan rumus berikut: partition_row_count × M/N. partition_row_count menentukan jumlah baris dalam partisi.
Parameter
- N: Wajib. Konstanta bertipe BIGINT. Jika N disetel ke null, null dikembalikan.
- M: Wajib. Konstanta bertipe BIGINT. Jika M disetel ke null, null dikembalikan.
- partition_clause: Opsional. Untuk informasi lebih lanjut, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe BOOLEAN dikembalikan.

Contoh

Sample sekitar 20% entri data dalam setiap grup. Contoh pernyataan:

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

Hasil berikut dikembalikan:

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

COUNT

Sintaksis

bigint count(*) over ([partition_clause] [orderby_clause] [frame_clause])
bigint count([distinct] <expr>[,...]) over ([partition_clause] [orderby_clause] [frame_clause])

Deskripsi
- count(*): Jumlah total baris yang dikembalikan.
- count([distinct] <expr>[,...]): Saat menghitung jumlah baris, baris-baris yang memiliki nilai expr null diabaikan. Jika ada beberapa parameter expr, baris-baris yang memiliki nilai expr null juga diabaikan. Selain itu, jika kata kunci distinct ditentukan, jumlah baris setelah deduplikasi dihitung. Baris-baris yang memiliki nilai expr null diabaikan.
Parameter
- expr: Wajib. Parameter ini menentukan kolom yang nilainya ingin Anda hitung. Semua tipe data didukung. Jika nilai untuk suatu baris adalah null, baris tersebut tidak digunakan untuk perhitungan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe BIGINT dikembalikan.

Contoh Pernyataan

Contoh 1: Gunakan kolom sal untuk mendefinisikan jendela. Klausa order by tidak ditentukan. Fungsi ini mengembalikan jumlah kumulatif dari baris pertama hingga baris terakhir dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai sal yang sama. Contoh pernyataan:

select sal, count(sal) over (partition by sal) as count from emp;

Hasil berikut dikembalikan:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |   -- Baris ini adalah baris pertama dari jendela ini. Nilai COUNT untuk baris pertama adalah jumlah kumulatif untuk baris kedua karena kedua baris memiliki nilai sal yang sama.
| 1250       | 2          |   -- Nilai pengembalian adalah jumlah kumulatif dari baris pertama hingga kedua dalam jendela saat ini.
| 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: Nonaktifkan edisi tipe data kompatibel Hive dan gunakan kolom sal untuk mendefinisikan jendela. ORDER BY ditentukan. Fungsi ini mengembalikan jumlah kumulatif dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai sal yang sama. Contoh pernyataan:

-- Nonaktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=false;
-- Jalankan pernyataan berikut: 
select sal, count(sal) over (partition by sal order by sal) as count from emp;

Hasil berikut dikembalikan:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |   -- Baris ini adalah baris pertama dari jendela ini. Jumlah kumulatif untuk baris pertama adalah 1.
| 1250       | 1          |   -- Jumlah kumulatif untuk baris kedua adalah 1.
| 1250       | 2          |   -- Jumlah kumulatif untuk baris ketiga 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: Aktifkan edisi tipe data kompatibel Hive dan gunakan kolom sal untuk mendefinisikan jendela. ORDER BY ditentukan. Fungsi ini mengembalikan jumlah kumulatif dari baris pertama hingga baris terakhir dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai sal yang sama. Contoh pernyataan:

-- Aktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=true;
-- Jalankan pernyataan berikut: 
select sal, count(sal) over (partition by sal order by sal) as count from emp;

Hasil berikut dikembalikan:

+------------+------------+
| sal        | count      |
+------------+------------+
| 800        | 1          |
| 950        | 1          |
| 1100       | 1          |
| 1250       | 2          |   -- Baris ini adalah baris pertama dari jendela ini. Nilai COUNT untuk baris pertama adalah jumlah kumulatif untuk baris kedua karena kedua baris memiliki nilai sal yang sama.
| 1250       | 2          |   -- Nilai pengembalian adalah jumlah kumulatif dari baris pertama hingga kedua dalam jendela saat ini.
| 1300       | 2          |
| 1300       | 2          |
| 1500       | 1          |
| 1600       | 1          |
| 2450       | 2          |
| 2450       | 2          |
| 2850       | 1          |
| 2975       | 1          |
| 3000       | 2          |
| 3000       | 2          |
| 5000       | 2          |
| 5000       | 2          |
+------------+------------+

CUME_DIST

Sintaksis

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

Deskripsi
Menghitung distribusi kumulatif. Distribusi kumulatif menunjukkan rasio baris-baris yang nilainya lebih besar dari atau sama dengan nilai baris saat ini dibandingkan dengan semua baris dalam partisi. Rasio tersebut ditentukan berdasarkan orderby_clause.
Parameter
partition_clause dan orderby_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe DOUBLE dikembalikan. Nilai pengembalian dihitung menggunakan rumus berikut: row_number_of_last_peer/partition_row_count. row_number_of_last_peer menunjukkan nilai yang dikembalikan oleh ROW_NUMBER yang sesuai dengan baris terakhir dari grup tempat baris saat ini berada. partition_row_count menunjukkan jumlah baris dalam partisi tempat baris saat ini berada.

Contoh

Kelompokkan semua karyawan berdasarkan kolom deptno dan hitung distribusi kumulatif karyawan dalam setiap grup berdasarkan gaji. Contoh pernyataan:

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
Mengembalikan peringkat baris saat ini dalam partisi berdasarkan urutan yang ditentukan oleh orderby_clause. Peringkat dimulai dari 1. Dalam partisi, baris-baris dengan nilai kolom yang sama seperti yang ditentukan dalam order by memiliki peringkat yang sama. Peringkat meningkat sebesar 1 setiap kali nilai kolom yang ditentukan dalam order by berubah.
Parameter
partition_clause dan orderby_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe BIGINT dikembalikan. Jika orderby_clause tidak ditentukan, nilai-nilai dalam hasil yang dikembalikan semuanya 1.

Contoh

Kelompokkan semua karyawan berdasarkan kolom deptno. Dalam setiap grup, urutkan karyawan secara menurun berdasarkan nilai sal untuk mendapatkan nomor urut karyawan dalam grup masing-masing. Contoh pernyataan:

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

Hasil berikut diperoleh:

+------------+------------+------------+------------+
| 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 expr yang sesuai dengan baris pertama dalam jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung hasil yang dikembalikan.
- ignore_nulls: Opsional. Nilai bertipe BOOLEAN. Parameter ini menentukan apakah nilai null diabaikan. Nilai default: false. Jika disetel ke true, nilai non-null dari expr yang sesuai dengan baris pertama dalam jendela akan dikembalikan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe data yang sama dengan expr akan dikembalikan.

Contoh

Kelompokkan semua karyawan berdasarkan departemen dan kembalikan baris pertama data dalam setiap grup. Contoh pernyataan:

order by tidak ditentukan.

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 ini adalah baris pertama dari jendela 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 ini adalah baris pertama dari jendela ini.
| 20         | SCOTT      | 3000       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 30         | TURNER     | 1500       | 1500        |   -- Baris ini adalah baris pertama dari jendela ini.
| 30         | JAMES      | 950        | 1500        |
| 30         | ALLEN      | 1600       | 1500        |
| 30         | WARD       | 1250       | 1500        |
| 30         | MARTIN     | 1250       | 1500        |
| 30         | BLAKE      | 2850       | 1500        |
+------------+------------+------------+-------------+

order by ditentukan.

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 ini adalah baris pertama dari jendela 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 ini adalah baris pertama dari jendela ini.
| 20         | FORD       | 3000       | 3000        |
| 20         | JONES      | 2975       | 3000        |
| 20         | ADAMS      | 1100       | 3000        |
| 20         | SMITH      | 800        | 3000        |
| 30         | BLAKE      | 2850       | 2850        |   -- Baris ini adalah baris pertama dari jendela 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 dari expr yang mendahului baris saat ini pada offset tertentu. Ekspresi expr dapat berupa kolom, operasi kolom, atau operasi fungsi.
Parameter
- expr: wajib. Ekspresi yang digunakan untuk menghitung hasil yang dikembalikan.
- offset: opsional. Nilainya adalah konstanta bertipe BIGINT dan harus lebih besar dari atau sama dengan 0. Nilai 0 menunjukkan baris saat ini, sedangkan nilai 1 menunjukkan baris sebelumnya. Nilai default: 1. Jika nilai input bertipe STRING atau DOUBLE, nilai tersebut akan diubah secara implisit menjadi BIGINT sebelum perhitungan.
- default: opsional. Nilai default ketika nilai offset berada di luar rentang yang valid. Nilai parameter ini harus berupa konstanta. Nilai default parameter ini adalah null. Tipe data parameter ini harus sesuai dengan tipe data expr. Jika nilai expr bukan konstanta, nilai parameter ditentukan berdasarkan baris saat ini.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut tentang parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe data yang sama seperti expr dikembalikan.

Contoh

Kelompokkan semua karyawan berdasarkan kolom deptno dan hitung nilai sal untuk setiap karyawan pada offset tertentu. Contoh pernyataan:

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 hasil perhitungan baris terakhir dalam jendela data menggunakan ekspresi yang ditentukan oleh expr.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung hasil yang dikembalikan.
- ignore_nulls: opsional. Bertipe BOOLEAN. Menentukan apakah nilai null diabaikan. Default: false. Jika disetel ke true, nilai non-null dari expr yang sesuai dengan baris terakhir dalam suatu jendela akan dikembalikan.
- partition_clause, orderby_clause, dan frame_clause: Untuk detail lebih lanjut mengenai parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe data yang sama seperti expr akan dikembalikan.

Contoh

Kelompokkan semua karyawan berdasarkan departemen dan kembalikan baris pertama data dalam setiap grup. Contoh pernyataan:

Jika order by tidak ditentukan, semua baris dari baris pertama hingga baris terakhir termasuk dalam jendela saat ini, dan nilai dari baris terakhir di jendela tersebut dikembalikan.

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 ini adalah baris terakhir dari jendela ini. 
| 20         | FORD       | 3000       | 2975        |
| 20         | SCOTT      | 3000       | 2975        |
| 20         | SMITH      | 800        | 2975        |
| 20         | ADAMS      | 1100       | 2975        |
| 20         | JONES      | 2975       | 2975        |   -- Baris ini adalah baris terakhir dari jendela 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 ini adalah baris terakhir dari jendela saat ini. 
+------------+------------+------------+-------------+

Jika order by ditentukan, baris dari baris pertama hingga baris saat ini termasuk dalam jendela saat ini, dan nilai baris saat ini di jendela tersebut dikembalikan.

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 ini adalah baris saat ini dari jendela ini. 
| 10         | KING       | 5000       | 5000        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 10         | CLARK      | 2450       | 2450        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 10         | WELAN      | 2450       | 2450        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 10         | TEBAGE     | 1300       | 1300        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 10         | MILLER     | 1300       | 1300        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 20         | SCOTT      | 3000       | 3000        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 20         | FORD       | 3000       | 3000        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 20         | JONES      | 2975       | 2975        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 20         | ADAMS      | 1100       | 1100        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 20         | SMITH      | 800        | 800         |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 30         | BLAKE      | 2850       | 2850        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 30         | ALLEN      | 1600       | 1600        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 30         | TURNER     | 1500       | 1500        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 30         | MARTIN     | 1250       | 1250        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 30         | WARD       | 1250       | 1250        |   -- Baris ini adalah baris saat ini dari jendela ini. 
| 30         | JAMES      | 950        | 950         |   -- Baris ini adalah baris saat ini dari jendela ini. 
+------------+------------+------------+-------------+

LEAD

Sintaksis

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

Deskripsi
Mengembalikan nilai dari expr yang sesuai dengan baris ke-N setelah baris saat ini pada offset tertentu. Ekspresi expr dapat berupa kolom, operasi kolom, atau operasi fungsi.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung hasil yang dikembalikan.
- offset: Opsional. Nilainya adalah konstanta bertipe BIGINT dan harus lebih besar dari atau sama dengan 0. Nilai 0 menunjukkan baris saat ini, dan nilai 1 menunjukkan baris berikutnya. Nilai default: 1. Jika nilai masukan bertipe STRING atau DOUBLE, nilai tersebut akan diubah secara implisit menjadi nilai bertipe BIGINT sebelum perhitungan.
- default: Opsional. Nilai default ketika nilai dari offset berada di luar rentang yang valid. Nilai parameter ini harus berupa konstanta. Nilai default dari parameter ini adalah null. Nilai parameter ini harus memiliki tipe data yang sama dengan nilai dari expr. Jika nilai dari expr bukan konstanta, nilai parameter ditentukan berdasarkan baris saat ini.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut tentang parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe data yang sama dengan expr dikembalikan.

Contoh

Kelompokkan semua karyawan berdasarkan kolom deptno dan hitung nilai sal untuk setiap karyawan pada offset tertentu. Contoh pernyataan:

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 dari expr dalam jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung nilai maksimum. Nilai input dapat berupa tipe data apa pun selain BOOLEAN. Jika nilai untuk suatu baris adalah null, baris tersebut tidak digunakan untuk perhitungan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe yang sama seperti expr dikembalikan.

Contoh

Contoh 1: Gunakan kolom deptno untuk mendefinisikan jendela dan dapatkan nilai maksimum dari kolom sal. Klausa order by tidak ditentukan. Fungsi ini mengembalikan nilai maksimum dari jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 5000       |   -- Baris ini adalah baris pertama dari jendela ini. Nilai pengembalian adalah nilai maksimum di antara nilai-nilai dari baris pertama hingga keenam.
| 10         | 2450       | 5000       |   -- Nilai pengembalian adalah nilai maksimum di antara nilai-nilai dari baris pertama hingga keenam.
| 10         | 5000       | 5000       |   -- Nilai pengembalian adalah nilai maksimum di antara nilai-nilai 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: Gunakan kolom deptno untuk mendefinisikan jendela dan dapatkan nilai maksimum dari kolom sal. Klausa ORDER BY ditentukan. Fungsi ini mengembalikan nilai maksimum di antara nilai-nilai dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- Baris ini adalah baris pertama dari jendela ini.
| 10         | 1300       | 1300       |   -- Nilai pengembalian adalah nilai maksimum di antara nilai-nilai pada baris pertama dan kedua.
| 10         | 2450       | 2450       |   -- Nilai pengembalian adalah nilai maksimum di antara nilai-nilai dari baris pertama hingga ketiga.
| 10         | 2450       | 2450       |   -- Nilai pengembalian adalah nilai maksimum di antara nilai-nilai 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
Mengembalikan median dari expr dalam jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung median. Nilai bertipe DOUBLE atau DECIMAL. Nilai parameter ini harus memiliki panjang 1 hingga 255 digit.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut secara implisit dikonversi menjadi nilai bertipe DOUBLE sebelum perhitungan. Jika bertipe data lain, kesalahan akan dikembalikan.
  - Jika nilai input adalah null, null dikembalikan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe DOUBLE atau DECIMAL dikembalikan. Jika semua ekspresi yang ditentukan oleh expr bernilai null, null dikembalikan.

Contoh

Gunakan kolom deptno untuk mendefinisikan jendela dan hitung nilai median dari kolom sal. Fungsi ini mengembalikan nilai median dari jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 2450.0     |   -- Baris ini adalah baris pertama dari jendela ini. Nilai pengembalian adalah nilai median dari nilai-nilai 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 dari expr dalam jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung nilai minimum. Nilai input dapat berupa tipe data apa pun selain BOOLEAN. Jika nilai untuk suatu baris adalah null, baris tersebut tidak digunakan untuk perhitungan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe yang sama seperti expr dikembalikan.

Contoh

Contoh 1: Gunakan kolom deptno untuk mendefinisikan jendela dan dapatkan nilai minimum dari kolom sal. Klausa order by tidak ditentukan. Fungsi ini mengembalikan nilai minimum dari jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1300       |   -- Baris ini adalah baris pertama dari jendela ini. Nilai pengembalian adalah nilai minimum di antara nilai-nilai dari baris pertama hingga keenam.
| 10         | 2450       | 1300       |   -- Nilai pengembalian adalah nilai minimum di antara nilai-nilai dari baris pertama hingga keenam.
| 10         | 5000       | 1300       |   -- Nilai pengembalian adalah nilai minimum di antara nilai-nilai 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: Gunakan kolom deptno untuk mendefinisikan jendela dan dapatkan nilai minimum dari kolom sal. Klausa ORDER BY ditentukan. Fungsi ini mengembalikan nilai minimum di antara nilai-nilai dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

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

NTILE

Sintaksis

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

Deskripsi
Membagi baris-baris data dalam partisi menjadi N grup dengan ukuran sama dan mengembalikan nomor grup tempat baris tertentu berada. Jika data dalam partisi tidak dapat dibagi menjadi N grup dengan ukuran sama, satu baris tambahan dialokasikan ke M grup pertama.
Parameter
- N: Wajib. Parameter ini menentukan jumlah pembagian. Nilai bertipe BIGINT.
- partition_clause dan orderby_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe BIGINT dikembalikan.

Contoh

Bagi semua karyawan menjadi tiga grup berdasarkan kolom sal dalam urutan menurun dan dapatkan nomor grup tempat setiap karyawan berada. Contoh pernyataan:

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 dari expr yang sesuai dengan baris ke-N dalam sebuah jendela.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung hasil yang dikembalikan.
- number: Wajib. Nilai bertipe BIGINT. Nilai tersebut harus bilangan bulat lebih besar dari atau sama dengan 1. Jika nilai input adalah 1, fungsi ini setara dengan FIRST_VALUE.
- ignore_nulls: Opsional. Nilai bertipe BOOLEAN. Parameter ini menentukan apakah akan mengabaikan nilai null. Nilai default: false. Jika parameter ini disetel ke true, nilai non-null dari expr yang sesuai dengan baris ke-N dari jendela dikembalikan.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe data yang sama seperti expr dikembalikan.

Contoh

Kelompokkan semua karyawan berdasarkan departemen dan kembalikan baris keenam data dalam setiap grup. Contoh pernyataan:

Jika order by tidak ditentukan, baris dari baris pertama hingga terakhir termasuk dalam jendela saat ini. Nilai baris keenam dalam jendela saat ini dikembalikan. Contoh pernyataan:

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 ini adalah baris keenam dari jendela ini.
| 20         | FORD       | 3000       | NULL       |
| 20         | SCOTT      | 3000       | NULL       |
| 20         | SMITH      | 800        | NULL       |
| 20         | ADAMS      | 1100       | NULL       |
| 20         | JONES      | 2975       | NULL       |   -- Jendela saat ini memiliki kurang dari enam baris, dan 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 ini adalah baris keenam dari jendela saat ini.
+------------+------------+------------+------------+

Jika order by ditentukan, baris dari baris pertama hingga baris saat ini termasuk dalam jendela saat ini. Nilai baris keenam dalam jendela saat ini dikembalikan.

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 ini hanya memiliki dua baris, dan null dikembalikan. 
| 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 partisi berdasarkan orderby_clause.
Parameter
partition_clause dan orderby_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe DOUBLE dikembalikan. Rentang nilai valid adalah [0.0, 1.0]. Nilai pengembalian dihitung menggunakan rumus berikut: "(rank - 1)/(partition_row_count - 1)". rank menunjukkan nilai yang dikembalikan oleh fungsi RANK yang sesuai dengan baris saat ini. partition_row_count menunjukkan jumlah baris dalam partisi tempat baris saat ini berada. Jika partisi hanya berisi satu baris data, 0.0 dikembalikan.

Contoh

Hitung peringkat persentil setiap karyawan dalam grup berdasarkan kolom sal. Contoh pernyataan:

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

RANK

Sintaksis

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

Deskripsi
Mengembalikan peringkat baris saat ini dalam partisi berdasarkan urutan yang ditentukan oleh orderby_clause. Peringkat dimulai dari 1.
Parameter
partition_clause dan orderby_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai bertipe BIGINT dikembalikan. Nilai pengembalian mungkin duplikat dan tidak berurutan. Nilai pengembalian adalah nomor urut baris pertama dalam grup tempat baris saat ini berada. Nomor urut baris pertama dihitung menggunakan fungsi ROW_NUMBER(). Jika orderby_clause tidak ditentukan, nilai-nilai dalam hasil yang dikembalikan semuanya 1.

Contoh

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
Mengembalikan nomor urut baris saat ini dalam partisi. Nomor urut dimulai dari 1.
Parameter
Untuk informasi lebih lanjut, lihat windowing_definition. frame_clause tidak didukung.
Nilai Pengembalian
Nilai bertipe BIGINT dikembalikan.

Contoh

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
Mengembalikan standar deviasi populasi dari semua nilai input. Fungsi ini juga disebut STDDEV_POP.
Parameter
- expr: Wajib. Ekspresi yang digunakan untuk menghitung standar deviasi populasi. Nilai bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut secara implisit dikonversi menjadi nilai bertipe DOUBLE sebelum perhitungan. Jika bertipe data lain, kesalahan akan dikembalikan.
  - Jika nilai input adalah null, baris yang berisi nilai tersebut tidak digunakan untuk perhitungan.
  - Jika kata kunci distinct ditentukan, standar deviasi populasi dari nilai unik dihitung.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe data yang sama seperti expr dikembalikan. Jika semua ekspresi yang ditentukan oleh expr bernilai null, null dikembalikan.

Contoh

Contoh 1: Gunakan kolom deptno untuk mendefinisikan jendela dan hitung standar deviasi populasi dari kolom sal. Klausa order by tidak ditentukan. Fungsi ini mengembalikan standar deviasi populasi kumulatif dari jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1546.1421524412158 |   -- Baris ini adalah baris pertama dari jendela ini. Nilai pengembalian adalah standar deviasi populasi kumulatif dari nilai-nilai dari baris pertama hingga keenam.
| 10         | 2450       | 1546.1421524412158 |   -- Nilai pengembalian adalah standar deviasi populasi kumulatif dari nilai-nilai 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: Nonaktifkan edisi tipe data kompatibel Hive dan gunakan kolom deptno untuk mendefinisikan jendela serta hitung standar deviasi populasi dari kolom sal. Klausa ORDER BY ditentukan. Fungsi ini mengembalikan standar deviasi populasi kumulatif dari nilai-nilai dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

-- Nonaktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=false;
-- Jalankan pernyataan 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 ini adalah baris pertama dari jendela ini.
| 10         | 1300       | 0.0        |           -- Nilai pengembalian adalah standar deviasi populasi kumulatif dari nilai-nilai pada baris pertama dan kedua.
| 10         | 2450       | 542.1151989096865 |    -- Nilai pengembalian adalah standar deviasi populasi kumulatif dari nilai-nilai dari baris pertama hingga ketiga.
| 10         | 2450       | 575.0      |           -- Nilai pengembalian adalah standar deviasi populasi kumulatif dari nilai-nilai 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: Aktifkan edisi tipe data kompatibel Hive dan gunakan kolom deptno untuk mendefinisikan jendela serta hitung standar deviasi populasi dari kolom sal. Klausa ORDER BY ditentukan. Fungsi ini mengembalikan standar deviasi populasi kumulatif dari nilai-nilai dari baris pertama hingga baris yang memiliki nilai sal sama dengan baris saat ini dalam jendela saat ini. Standar deviasi populasi untuk baris-baris yang memiliki nilai sal sama adalah sama. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

-- Aktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=true;
-- Jalankan pernyataan 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 ini adalah baris pertama dari jendela ini. Standar deviasi populasi untuk baris pertama adalah standar deviasi populasi kumulatif dari nilai-nilai pada baris pertama dan kedua karena kedua baris tersebut memiliki nilai sal yang sama.
| 10         | 1300       | 0.0        |           -- Nilai pengembalian adalah standar deviasi populasi kumulatif dari nilai-nilai pada baris pertama dan kedua.
| 10         | 2450       | 575.0      |           -- Standar deviasi populasi untuk baris ketiga adalah standar deviasi populasi kumulatif dari nilai-nilai dari baris pertama hingga keempat karena baris ketiga dan keempat memiliki nilai sal yang sama.
| 10         | 2450       | 575.0      |           -- Nilai pengembalian adalah standar deviasi populasi kumulatif dari nilai-nilai 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
Mengembalikan standar deviasi sampel dari semua nilai input.
Parameter
- expr: Wajib. Parameter ini menentukan ekspresi yang digunakan untuk menghitung standar deviasi sampel. Nilai bertipe DOUBLE atau DECIMAL.
  - Jika nilai input bertipe STRING atau BIGINT, nilai tersebut secara implisit dikonversi menjadi nilai bertipe DOUBLE sebelum perhitungan. Jika bertipe data lain, kesalahan akan dikembalikan.
  - Jika nilai input adalah null, baris yang berisi nilai tersebut tidak digunakan untuk perhitungan.
  - Jika kata kunci distinct ditentukan, standar deviasi sampel dari nilai unik dihitung.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
Nilai dengan tipe yang sama seperti expr dikembalikan. Jika semua ekspresi yang ditentukan oleh expr bernilai null, null dikembalikan. Jika jendela hanya memiliki satu baris data yang nilai expr-nya bukan null, 0 dikembalikan.

Contoh

Contoh 1: Gunakan kolom deptno untuk mendefinisikan jendela dan hitung standar deviasi sampel dari kolom sal. Klausa order by tidak ditentukan. Fungsi ini mengembalikan standar deviasi sampel kumulatif dari jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 1693.7138680032904 |   -- Baris ini adalah baris pertama dari jendela ini. Nilai pengembalian adalah standar deviasi sampel kumulatif dari nilai-nilai dari baris pertama hingga keenam.
| 10         | 2450       | 1693.7138680032904 |   -- Nilai pengembalian adalah standar deviasi sampel kumulatif dari nilai-nilai dari baris pertama hingga keenam.
| 10         | 5000       | 1693.7138680032904 |   -- Nilai pengembalian adalah standar deviasi sampel kumulatif dari nilai-nilai 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: Gunakan kolom deptno untuk mendefinisikan jendela dan hitung standar deviasi sampel dari kolom sal. Klausa ORDER BY ditentukan. Fungsi ini mengembalikan standar deviasi sampel kumulatif dari nilai-nilai dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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 ini adalah baris pertama dari jendela ini.
| 10         | 1300       | 0.0        |          -- Nilai pengembalian adalah standar deviasi sampel kumulatif dari nilai-nilai pada baris pertama dan kedua.
| 10         | 2450       | 663.9528095680697 |   -- Nilai pengembalian adalah standar deviasi sampel kumulatif dari nilai-nilai 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 dari expr dalam jendela.
Parameter
- expr: Wajib. Parameter ini menentukan kolom yang ingin Anda jumlahkan. Kolom bertipe DOUBLE, DECIMAL, atau BIGINT.
  - Jika nilai input bertipe STRING, nilai tersebut secara implisit dikonversi menjadi nilai bertipe DOUBLE sebelum perhitungan. Jika bertipe data lain, kesalahan akan dikembalikan.
  - Jika nilai input adalah null, baris yang berisi nilai tersebut tidak digunakan untuk perhitungan.
  - Jika kata kunci distinct ditentukan, jumlah dari nilai unik dihitung.
- partition_clause, orderby_clause, dan frame_clause: Untuk informasi lebih lanjut tentang parameter-parameter ini, lihat windowing_definition.
Nilai Pengembalian
- Jika nilai input bertipe BIGINT, nilai bertipe BIGINT dikembalikan.
- Jika nilai input bertipe DECIMAL, nilai bertipe DECIMAL dikembalikan.
- Jika nilai input bertipe DOUBLE atau STRING, nilai bertipe DOUBLE dikembalikan.
- Jika nilai input adalah null, null dikembalikan.

Contoh

Contoh 1: Gunakan kolom deptno untuk mendefinisikan jendela dan hitung jumlah dari kolom sal. Klausa order by tidak ditentukan. Fungsi ini mengembalikan jumlah kumulatif dari jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

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

Hasil berikut dikembalikan:

+------------+------------+------------+
| deptno     | sal        | _c2        |
+------------+------------+------------+
| 10         | 1300       | 17500      |   -- Baris ini adalah baris pertama dari jendela ini. Nilai pengembalian adalah jumlah kumulatif dari nilai-nilai dari baris pertama hingga keenam.
| 10         | 2450       | 17500      |   -- Nilai pengembalian adalah jumlah kumulatif dari nilai-nilai dari baris pertama hingga keenam.
| 10         | 5000       | 17500      |   -- Nilai pengembalian adalah jumlah kumulatif dari nilai-nilai 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: Nonaktifkan edisi tipe data kompatibel Hive dan gunakan kolom deptno untuk mendefinisikan jendela serta hitung jumlah dari kolom sal. Klausa ORDER BY ditentukan. Fungsi ini mengembalikan jumlah kumulatif dari nilai-nilai dari baris pertama hingga baris saat ini dalam jendela saat ini. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

-- Nonaktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=false;
-- Jalankan pernyataan 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 ini adalah baris pertama dari jendela ini.
| 10         | 1300       | 2600       |   -- Nilai pengembalian adalah jumlah kumulatif dari nilai-nilai pada baris pertama dan kedua.
| 10         | 2450       | 5050       |   -- Nilai pengembalian adalah jumlah kumulatif dari nilai-nilai 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: Aktifkan edisi tipe data kompatibel Hive dan gunakan kolom deptno untuk mendefinisikan jendela serta hitung jumlah dari kolom sal. Klausa ORDER BY ditentukan. Fungsi ini mengembalikan jumlah kumulatif dari nilai-nilai dari baris pertama hingga baris yang memiliki nilai sal sama dengan baris saat ini dalam jendela saat ini. Nilai jumlah untuk baris-baris yang memiliki nilai sal sama adalah sama. Jendela saat ini mencakup baris-baris yang memiliki nilai deptno yang sama. Contoh pernyataan:

-- Aktifkan edisi tipe data kompatibel Hive. 
set odps.sql.hive.compatible=true;
-- Jalankan pernyataan 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 ini adalah baris pertama dari jendela ini. Jumlah untuk baris pertama adalah jumlah kumulatif dari nilai-nilai pada baris pertama dan kedua karena kedua baris tersebut memiliki nilai sal yang sama.
| 10         | 1300       | 2600       |   -- Nilai pengembalian adalah jumlah kumulatif dari nilai-nilai pada baris pertama dan kedua.
| 10         | 2450       | 7500       |   -- Jumlah untuk baris ketiga adalah jumlah kumulatif dari nilai-nilai dari baris pertama hingga keempat karena baris ketiga dan keempat memiliki nilai sal yang sama.
| 10         | 2450       | 7500       |   -- Nilai pengembalian adalah jumlah kumulatif dari nilai-nilai dari baris pertama hingga keempat.
| 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 sebelumnya tidak memenuhi kebutuhan bisnis Anda, MaxCompute juga menyediakan fungsi kustom. Untuk informasi lebih lanjut tentang UDF, lihat Ikhtisar.
Untuk informasi lebih lanjut tentang FAQ tentang SQL MaxCompute, lihat topik-topik berikut:
Untuk informasi lebih lanjut tentang kesalahan umum dan FAQ tentang fungsi bawaan MaxCompute, lihat topik-topik berikut:
- Kesalahan Umum untuk Fungsi Bawaan
- FAQ tentang Fungsi Bawaan