Fungsi jendela DENSE_RANK

PDFRSS

Fungsi jendela DENSE_RANK menentukan peringkat nilai dalam sekelompok nilai, berdasarkan ekspresi ORDER BY dalam klausa OVER. Jika klausa PARTITION BY opsional ada, peringkat diatur ulang untuk setiap kelompok baris. Baris dengan nilai yang sama untuk kriteria peringkat menerima peringkat yang sama. Fungsi DENSE_RANK berbeda dari RANK dalam satu hal: jika dua atau lebih baris terikat, tidak ada celah dalam urutan nilai peringkat. Misalnya, jika dua baris diberi peringkat1, peringkat berikutnya adalah2.

Anda dapat memiliki fungsi peringkat dengan klausa PARTITION BY dan ORDER BY yang berbeda dalam kueri yang sama.

Sintaks

DENSE_RANK() OVER
(
[ PARTITION BY expr_list ]
[ ORDER BY order_list ]
) 

Argumen

( )

Fungsi ini tidak mengambil argumen, tetapi tanda kurung kosong diperlukan.

DI ATAS

Klausa jendela untuk fungsi DENSE_RANK.

PARTISI OLEH expr_list

(Opsional) Satu atau lebih ekspresi yang menentukan jendela.

PESANAN BERDASARKAN order_list

(Opsional) Ekspresi yang menjadi dasar nilai peringkat. Jika tidak ada PARTITION BY yang ditentukan, ORDER BY menggunakan seluruh tabel. Jika ORDER BY dihilangkan, nilai kembalinya adalah 1 untuk semua baris.

Jika ORDER BY tidak menghasilkan urutan unik, urutan baris adalah nondeterministik. Untuk informasi selengkapnya, lihat Urutan data yang unik untuk fungsi jendela.

Jenis pengembalian

BIGINT

Contoh

Contoh berikut menggunakan tabel sampel untuk fungsi jendela. Untuk informasi selengkapnya, lihat Contoh tabel untuk contoh fungsi jendela.

Contoh berikut memesan tabel dengan jumlah yang terjual dan memberikan peringkat padat dan peringkat reguler untuk setiap baris. Hasilnya diurutkan setelah hasil fungsi jendela diterapkan.

SELECT salesid, qty,
DENSE_RANK() OVER(ORDER BY qty DESC) AS d_rnk,
RANK() OVER(ORDER BY qty DESC) AS rnk
FROM winsales
ORDER BY 2,1;

+---------+-----+-------+-----+
| salesid | qty | d_rnk | rnk |
+---------+-----+-------+-----+
|   10001 |  10 |     5 |   8 |
|   10006 |  10 |     5 |   8 |
|   30001 |  10 |     5 |   8 |
|   40005 |  10 |     5 |   8 |
|   30003 |  15 |     4 |   7 |
|   20001 |  20 |     3 |   4 |
|   20002 |  20 |     3 |   4 |
|   30004 |  20 |     3 |   4 |
|   10005 |  30 |     2 |   2 |
|   30007 |  30 |     2 |   2 |
|   40001 |  40 |     1 |   1 |
+---------+-----+-------+-----+

Perhatikan perbedaan peringkat yang ditetapkan ke kumpulan baris yang sama saat fungsi DENSE_RANK dan RANK digunakan berdampingan dalam kueri yang sama.

Contoh berikut mempartisi tabel dengan sellerid, mengurutkan setiap partisi dengan kuantitas, dan memberikan peringkat padat untuk setiap baris. Hasilnya diurutkan setelah hasil fungsi jendela diterapkan.

SELECT salesid, sellerid, qty,
DENSE_RANK() OVER(PARTITION BY sellerid ORDER BY qty DESC) AS d_rnk
FROM winsales
ORDER BY 2,3,1;

+---------+----------+-----+-------+
| salesid | sellerid | qty | d_rnk |
+---------+----------+-----+-------+
|   10001 |        1 |  10 |     2 |
|   10006 |        1 |  10 |     2 |
|   10005 |        1 |  30 |     1 |
|   20001 |        2 |  20 |     1 |
|   20002 |        2 |  20 |     1 |
|   30001 |        3 |  10 |     4 |
|   30003 |        3 |  15 |     3 |
|   30004 |        3 |  20 |     2 |
|   30007 |        3 |  30 |     1 |
|   40005 |        4 |  10 |     2 |
|   40001 |        4 |  40 |     1 |
+---------+----------+-----+-------+

Untuk berhasil menggunakan contoh terakhir, gunakan perintah berikut untuk menyisipkan baris ke dalam tabel WINSALES. Baris ini memiliki buyerid, sellerid, dan qtysold yang sama dengan baris lainnya. Ini akan menyebabkan dua baris terikat pada contoh terakhir dan dengan demikian akan menunjukkan perbedaan antara fungsi DENSE_RANK dan RANK.

INSERT INTO winsales VALUES(30009, '2/2/2003', 3, 'b', 20, NULL);

Contoh berikut mempartisi tabel dengan buyerid dan sellerid, mengurutkan setiap partisi berdasarkan kuantitas, dan menetapkan peringkat padat dan peringkat reguler untuk setiap baris. Hasilnya diurutkan setelah fungsi jendela diterapkan.

SELECT salesid, sellerid, qty, buyerid,
DENSE_RANK() OVER(PARTITION BY buyerid, sellerid ORDER BY qty DESC) AS d_rnk,
RANK() OVER (PARTITION BY buyerid, sellerid ORDER BY qty DESC) AS rnk
FROM winsales
ORDER BY rnk;

+---------+----------+-----+---------+-------+-----+
| salesid | sellerid | qty | buyerid | d_rnk | rnk |
+---------+----------+-----+---------+-------+-----+
|   20001 |        2 |  20 | b       |     1 |   1 |
|   30007 |        3 |  30 | c       |     1 |   1 |
|   10006 |        1 |  10 | c       |     1 |   1 |
|   10005 |        1 |  30 | a       |     1 |   1 |
|   20002 |        2 |  20 | c       |     1 |   1 |
|   30009 |        3 |  20 | b       |     1 |   1 |
|   40001 |        4 |  40 | a       |     1 |   1 |
|   30004 |        3 |  20 | b       |     1 |   1 |
|   10001 |        1 |  10 | c       |     1 |   1 |
|   40005 |        4 |  10 | a       |     2 |   2 |
|   30003 |        3 |  15 | b       |     2 |   3 |
|   30001 |        3 |  10 | b       |     3 |   4 |
+---------+----------+-----+---------+-------+-----+