Kursus MariaDB / Membandingkan ROW_NUMBER, RANK, dan DENSE_RANK
Fungsi jendela menambahkan perhitungan pada kumpulan baris sambil mempertahankan baris masukan. ROW_NUMBER memberi nomor berbeda untuk setiap baris, sedangkan RANK dan DENSE_RANK dapat memberi peringkat sama untuk nilai yang sama; tentukan kebijakan nilai seri sebelum memilih fungsi.
Bedakan nomor baris dari peringkat bersama
Total pembelian pelanggan yang sama dapat memerlukan nomor tampilan berbeda tetapi peringkat pembelian yang sama. Ringkas dahulu satu total per pelanggan, lalu beri peringkat pada baris pelanggan tersebut. Mengganti nama fungsi saja tidak memperbaiki tingkat data masukan atau definisi seri yang berbeda. Panduan WITH dan CTE membantu memisahkan agregasi dari pemberian peringkat.
| Fungsi | Penanganan nilai seri | Contoh urutan |
|---|---|---|
| ROW_NUMBER | Setiap baris mendapat nomor berbeda | 1, 2, 3, 4 |
| RANK | Nilai urutan sama mendapat peringkat sama; ada lompatan berikutnya | 1, 1, 3, 3 |
| DENSE_RANK | Nilai sama mendapat peringkat sama tanpa lompatan | 1, 1, 2, 2 |
Fungsi jendela atau window function menggunakan OVER untuk menentukan kumpulan dan urutan perhitungan. Referensi ROW_NUMBER MariaDB dan gambaran umum fungsi jendela menjelaskan perbedaan dari agregasi yang menggabungkan baris.
Siapkan pelanggan dengan total yang sengaja sama
Hubungkan ke server belajar sebagai course_user dan pilih shopcourse. Panduan klien dan berkas SQL UTF-8 menjelaskan penyimpanan serta pelaksanaan dengan SOURCE C:/mariadb-lab/lesson.sql;. Ganti jalur dengan berkas sebenarnya dan jalankan SQL di dalam klien MariaDB.
Sebelum membuat c32_customers, c32_products, c32_orders, dan c32_items, periksa SELECT VERSION(), DATABASE(); serta SHOW TABLES;. Jika objek sudah ada, periksa definisi dan barisnya dahulu. Gunakan hanya objek latihan yang boleh dibuang dan jangan mengganti tabel aplikasi. Blok berikut menyediakan persiapan lengkap.
USE shopcourse;
CREATE TABLE c32_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c32_order_customer FOREIGN KEY (customer_id)
REFERENCES c32_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c32_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c32_item_order FOREIGN KEY (order_id)
REFERENCES c32_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c32_item_product FOREIGN KEY (product_id)
REFERENCES c32_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c32_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c32_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c32_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c32_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
SQL mempertahankan nama dan kota Korea dari sumber. Produk 1–4 adalah buku catatan, pena, cangkir, dan lampu. Pelanggan 1 serta 3 berada di Seoul, pelanggan 2 di Busan, dan pelanggan 4 di Daejeon. Simpan teks dengan UTF-8 dan gunakan ID untuk membandingkan hasil.
Pelanggan 1 dan 2 masing-masing mempunyai nilai barang dibayar 19500.00. Pelanggan 3 hanya mempunyai pesanan dibatalkan dan pelanggan 4 tidak mempunyai pesanan; laporan ini memberi total dibayar nol untuk keduanya. Memasukkan pelanggan yang belum membeli serta mengecualikan pesanan dibatalkan merupakan bagian dari definisi laporan.
Ringkas total pelanggan dan bandingkan tiga fungsi
WITH customer_totals AS (
SELECT c.customer_id,c.customer_name,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c LEFT JOIN c32_orders o
ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name
)
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER(ORDER BY goods_total DESC,customer_id ASC) AS row_no,
RANK() OVER(ORDER BY goods_total DESC) AS amount_rank,
DENSE_RANK() OVER(ORDER BY goods_total DESC) AS dense_amount_rank
FROM customer_totals ORDER BY goods_total DESC,customer_id ASC;
| Pelanggan | Total | row_no | amount_rank | dense_amount_rank |
|---|---|---|---|---|
| 1 | 19500.00 | 1 | 1 | 1 |
| 2 | 19500.00 | 2 | 1 | 1 |
| 3 | 0.00 | 3 | 3 | 2 |
| 4 | 0.00 | 4 | 3 | 2 |
ROW_NUMBER memakai customer_id sebagai pemecah seri yang unik sehingga urutan nomor dapat direproduksi. Dua fungsi peringkat hanya mengurutkan berdasarkan jumlah agar total yang sama tetap mendapat peringkat sama. Menambahkan ID pelanggan unik ke definisi RANK dan DENSE_RANK akan membedakan kombinasi urutan dan menghilangkan seri yang dimaksud.
RANK melompat ke 3 sesudah dua pelanggan pada peringkat 1. DENSE_RANK berpindah ke nilai peringkat berbeda berikutnya, yaitu 2. Angka tersebut menjawab pertanyaan berbeda; pilih sesuai aturan laporan, bukan karena satu urutan terlihat lebih rapi.
Bedakan urutan jendela, urutan tampilan, dan partisi
ORDER BY di dalam OVER menentukan urutan perhitungan fungsi, sedangkan ORDER BY pada SELECT akhir menentukan urutan tampilan. Nyatakan keduanya jika diperlukan. Urutan jendela saja tidak menjamin urutan akhir baris.
PARTITION BY membagi baris menjadi kelompok tempat perhitungan dimulai kembali. Peringkat per kota memerlukan kolom city pada masukan hasil agregasi dan PARTITION BY city yang sesuai. Berbeda dari GROUP BY, partisi tidak menyatukan baris pelanggan menjadi satu baris per kota.
Kueri lengkap berikut menggunakan data c32 yang sama, memulai nomor kembali untuk setiap kota, dan menampilkan hasil berdasarkan ID pelanggan.
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
)
SELECT customer_id,city,goods_total,
ROW_NUMBER() OVER (PARTITION BY city
ORDER BY goods_total DESC,customer_id ASC) AS regional_row_no
FROM customer_totals ORDER BY customer_id;
| customer_id | goods_total | regional_row_no |
|---|---|---|
| 1 | 19500.00 | 1 |
| 2 | 19500.00 | 1 |
| 3 | 0.00 | 2 |
| 4 | 0.00 | 1 |
Pelanggan 1 dan 3 berada pada partisi Seoul dan mendapat nomor 1 serta 2. Pelanggan 2 dan 4 masing-masing memulai partisi lain dari 1. Hapus hanya PARTITION BY city untuk membandingkan dengan urutan global 1, 2, 3, 4, lalu pulihkan partisi kota.
Saring hasil peringkat melalui kueri luar
WHERE pada SELECT yang sama tidak dapat langsung memakai row_no yang baru dibuat. Bentuk dahulu hasil peringkat melalui CTE atau tabel turunan, kemudian saring pada kueri luar. Permintaan dua baris berbeda dari semua pelanggan yang seri pada peringkat pertama, sehingga kolom yang disaring perlu dipilih dengan sengaja.
Memberi peringkat langsung pada rincian barang akan memberi peringkat pada baris rincian, bukan pelanggan. Pertahankan tahap agregasi pelanggan dan tahap fungsi jendela secara terpisah agar satuan laporan jelas.
WITH customer_totals AS (
SELECT c.customer_id, c.customer_name, c.city,
COALESCE(SUM(CASE WHEN o.status='paid'
THEN i.quantity*i.unit_price ELSE 0 END),0) AS goods_total
FROM c32_customers c
LEFT JOIN c32_orders o ON o.customer_id=c.customer_id
LEFT JOIN c32_items i ON i.order_id=o.order_id
GROUP BY c.customer_id,c.customer_name,c.city
), ranked AS (
SELECT customer_id,customer_name,goods_total,
ROW_NUMBER() OVER (ORDER BY goods_total DESC,customer_id) AS row_no,
RANK() OVER (ORDER BY goods_total DESC) AS amount_rank
FROM customer_totals
)
SELECT customer_id,goods_total,row_no,amount_rank
FROM ranked WHERE row_no <= 2 ORDER BY row_no;
Hasilnya pelanggan 1 dan 2 dengan row_no 1 serta 2 dan amount_rank 1 untuk keduanya. Ganti hanya kondisi luar menjadi row_no <= 1 untuk satu baris, atau amount_rank = 1 untuk semua yang seri pada posisi pertama. Pulihkan row_no <= 2 setelah perbandingan.
Periksa seri dan kelompok yang memenuhi syarat
Gunakan nilai seri yang disengaja dan amati peringkat berikutnya. Balik hanya pemecah seri customer_id pada ROW_NUMBER untuk membandingkan urutan di dalam seri tanpa mengubah peringkat berdasarkan jumlah, lalu pulihkan arah semula. Pastikan kebijakan pesanan dibatalkan dan pelanggan tanpa pembelian tetap sesuai. Peringkat yang dihitung merupakan hasil laporan, bukan pengganti ID pelanggan yang stabil.
Sebelum mengulang latihan, periksa basis data yang dipilih, SHOW CREATE TABLE, serta baris keempat tabel dan cadangkan data yang perlu dipertahankan. Hapus hanya objek c32_ milik artikel ini, dimulai dari tabel anak lalu induk. DROP TABLE menghapus struktur dan data sekaligus serta tidak dibalik dengan ROLLBACK biasa; pisahkan pembersihan dari percobaan transaksi.
DROP TABLE shopcourse.c32_items;
DROP TABLE shopcourse.c32_orders;
DROP TABLE shopcourse.c32_products;
DROP TABLE shopcourse.c32_customers;




