Penjelasan MySQL ROW_NUMBER() (MySQL 8.0): Peringkat, Query Top‑N, dan Deduplicasi

1. Introduction

MySQL versi 8.0 memperkenalkan banyak fitur baru, dan salah satu yang paling menonjol adalah dukungan untuk fungsi jendela (window functions). Pada artikel ini, kita akan fokus pada salah satu fungsi yang paling sering digunakan: ROW_NUMBER().

Fungsi ROW_NUMBER() memberikan kemampuan yang kuat untuk analisis data dan pelaporan, memudahkan penyortiran dan perankingan data berdasarkan kondisi tertentu. Artikel ini menjelaskan segala hal mulai dari penggunaan dasar dan contoh praktis hingga pendekatan alternatif untuk versi MySQL yang lebih lama.

Target readers

• Pengguna pemula hingga menengah dengan pengetahuan dasar SQL
• Insinyur dan analis data yang memproses serta menganalisis data menggunakan MySQL
• Siapa saja yang mempertimbangkan migrasi ke versi MySQL terbaru

Benefits of ROW_NUMBER()

Fungsi ini memungkinkan Anda memberi nomor unik pada setiap baris berdasarkan kondisi tertentu. Misalnya, Anda dapat dengan mudah menulis kueri seperti “buat peringkat dalam urutan menurun penjualan” atau “ekstrak dan susun data duplikat” secara singkat.

Pada versi lama, Anda sering harus menulis kueri kompleks menggunakan variabel yang didefinisikan pengguna. Dengan ROW_NUMBER(), SQL Anda menjadi lebih sederhana dan lebih mudah dibaca.

Pada artikel ini, kami akan menggunakan contoh kueri konkret dan menjelaskannya dengan cara yang ramah bagi pemula. Pada bagian berikutnya, kami akan melihat lebih dekat sintaks dasar dan perilaku fungsi ini.

2. What Is the ROW_NUMBER() Function?

Fungsi ROW_NUMBER(), yang baru ditambahkan di MySQL 8.0, adalah jenis fungsi jendela yang memberikan nomor berurutan pada baris. Ia dapat memberi nomor baris berdasarkan urutan tertentu dan/atau dalam setiap grup, yang sangat berguna untuk analisis data dan pelaporan. Di sini kami akan menjelaskan sintaks dasar secara detail dengan contoh praktis.

Basic syntax of ROW_NUMBER()

Pertama, format dasar ROW_NUMBER() adalah sebagai berikut.

SELECT
    column_name,
    ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column) AS row_num
FROM
    table_name;

Meaning of each element

• ROW_NUMBER() : Memberi nomor berurutan pada setiap baris.
• OVER : Kata kunci yang digunakan untuk mendefinisikan jendela bagi fungsi jendela.
• PARTITION BY : Mengelompokkan data berdasarkan kolom yang ditentukan. Opsional. Jika dihilangkan, penomoran diterapkan pada semua baris.
• ORDER BY : Menentukan urutan yang digunakan untuk memberi nomor, yaitu kriteria penyortiran.

Basic example

Sebagai contoh, anggap Anda memiliki tabel bernama “sales” dengan data berikut.

Untuk memberi nomor berurutan dalam setiap departemen berdasarkan penjualan menurun, gunakan kueri berikut.

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS row_num
FROM
    sales;

Result

Dari hasil ini, Anda dapat melihat bahwa peringkat berdasarkan penjualan dalam setiap departemen ditampilkan.

How to use PARTITION BY

Pada contoh di atas, data dikelompokkan berdasarkan kolom “department”. Ini memberikan urutan terpisah untuk setiap departemen.

Jika Anda menghilangkan PARTITION BY, penomoran akan diterapkan pada semua baris sebagai satu urutan tunggal.

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Result

Characteristics and caveats of ROW_NUMBER()

• Penomoran unik : Meskipun nilai sama, nomor yang diberikan tetap unik.
• Penanganan NULL : Jika ORDER BY mencakup NULL, mereka muncul pertama dalam urutan naik dan terakhir dalam urutan turun.
• Dampak kinerja : Untuk dataset besar, ORDER BY dapat menjadi mahal, sehingga indeks yang tepat sangat penting.

3. Practical Use Cases

Berikut adalah skenario praktis yang menggunakan fungsi ROW_NUMBER() MySQL. Fungsi ini berguna dalam banyak kasus dunia nyata, seperti perankingan data dan penanganan duplikat.

3-1. Ranking within each group

Sebagai contoh, pertimbangkan kasus di mana Anda ingin “mengurutkan karyawan berdasarkan penjualan dalam setiap departemen” menggunakan data penjualan. Gunakan dataset berikut sebagai contoh.

Contoh Kueri: Peringkat penjualan per departemen

SELECT
    employee,
    department,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Hasil:

Dengan cara ini, setiap departemen mendapatkan urutan masing‑masing dalam urutan menurun penjualan, memudahkan pembuatan peringkat.

3-2. Mengambil Baris Top N

Selanjutnya, mari lihat kasus di mana Anda ingin “mengambil 3 karyawan teratas berdasarkan penjualan dalam setiap departemen.”

Contoh Kueri: Mengambil Baris Top N

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT
    employee,
    department,
    sale
FROM
    RankedSales
WHERE
    rank <= 3;

Hasil:

Contoh ini mengambil hanya 3 baris teratas berdasarkan penjualan dalam setiap departemen. Seperti yang Anda lihat, ROW_NUMBER() cocok tidak hanya untuk peringkat tetapi juga untuk menyaring hasil teratas.

3-3. Menemukan dan menghapus data duplikat

Basis data kadang‑kadang berisi catatan duplikat. Dalam kasus seperti itu, Anda juga dapat menanganinya dengan mudah menggunakan ROW_NUMBER().

Contoh Kueri: Mendeteksi duplikat

SELECT *
FROM (
    SELECT
        employee,
        sale,
        ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
    FROM
        sales
) tmp
WHERE rank > 1;

Kueri ini mendeteksi duplikat ketika ada beberapa catatan untuk nama karyawan yang sama.

Contoh Kueri: Menghapus duplikat

DELETE FROM sales
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            ROW_NUMBER() OVER (PARTITION BY employee ORDER BY sale DESC) AS rank
        FROM
            sales
    ) tmp
    WHERE rank > 1
);

Ringkasan

ROW_NUMBER() berguna dalam berbagai skenario, seperti:

1. Peringkat dalam setiap grup
2. Mengambil Baris Top N
3. Mendeteksi dan menghapus duplikat

Hal ini membuat pemrosesan dan analisis data yang kompleks menjadi lebih sederhana dan lebih efisien.

4. Perbandingan dengan Fungsi Jendela Lain

Di MySQL 8.0, selain ROW_NUMBER(), terdapat fungsi jendela seperti RANK() dan DENSE_RANK() yang dapat digunakan untuk peringkat dan perhitungan posisi. Meskipun mereka memiliki peran serupa, perilaku dan hasilnya berbeda. Di sini kami akan membandingkan setiap fungsi dan menjelaskan kapan menggunakannya.

4-1. Fungsi RANK()

Fungsi RANK() memberikan peringkat, memberikan peringkat yang sama untuk nilai yang sama dan melewatkan nomor peringkat berikutnya.

Sintaks dasar

SELECT
    column_name,
    RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS rank
FROM
    table_name;

Contoh

Dengan data berikut, hitung peringkat penjualan.

Contoh Kueri: Menggunakan RANK()

SELECT
    employee,
    sale,
    RANK() OVER (ORDER BY sale DESC) AS rank
FROM
    sales;

Hasil:

Poin penting:

• A dan B dengan jumlah penjualan yang sama (800) keduanya diperlakukan sebagai peringkat “1”.
• Peringkat berikutnya “2” dilewatkan, sehingga C menjadi peringkat “3”.

4-2. Fungsi DENSE_RANK()

Fungsi DENSE_RANK() juga memberikan peringkat yang sama untuk nilai yang sama, tetapi tidak melewatkan nomor peringkat berikutnya.

Sintaks dasar

SELECT
    column_name,
    DENSE_RANK() OVER (PARTITION BY group_column ORDER BY sort_column) AS dense_rank
FROM
    table_name;

Contoh

Dengan data yang sama seperti di atas, coba fungsi DENSE_RANK().

Contoh Kueri: Menggunakan DENSE_RANK()

SELECT
    employee,
    sale,
    DENSE_RANK() OVER (ORDER BY sale DESC) AS dense_rank
FROM
    sales;

Hasil:

Poin penting:

• A dan B dengan jumlah penjualan yang sama (800) keduanya diperlakukan sebagai peringkat “1”.
• Tidak seperti RANK(), peringkat berikutnya dimulai pada “2”, sehingga kontinuitas peringkat terjaga.

4-3. Bagaimana ROW_NUMBER() berbeda

Fungsi ROW_NUMBER() berbeda dari dua fungsi lainnya karena menetapkan nomor unik bahkan ketika nilai-nilainya sama.

Contoh

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (ORDER BY sale DESC) AS row_num
FROM
    sales;

Hasil:

Poin penting:

• Bahkan jika nilai-nilai sama, setiap baris mendapatkan nomor unik, sehingga tidak ada peringkat duplikat.
• Ini berguna ketika Anda memerlukan kontrol urutan yang ketat atau keunikan per baris.

4-4. Ringkasan kasus penggunaan cepat

Ringkasan

ROW_NUMBER(), RANK(), dan DENSE_RANK() harus digunakan secara tepat tergantung pada skenario.

1. ROW_NUMBER() paling cocok ketika Anda membutuhkan nomor unik per baris.
2. RANK() berguna ketika Anda ingin nilai yang sama berbagi peringkat dan menekankan celah peringkat.
3. DENSE_RANK() cocok ketika Anda menginginkan peringkat berkelanjutan tanpa celah.

5. Alternatif untuk Versi MySQL di Bawah 8.0

Pada versi sebelum MySQL 8.0, ROW_NUMBER() dan fungsi jendela lainnya tidak didukung. Namun, Anda dapat mencapai perilaku serupa menggunakan variabel yang didefinisikan pengguna. Bagian ini menjelaskan alternatif praktis untuk versi MySQL di bawah 8.0.

5-1. Penomoran berurutan menggunakan variabel yang didefinisikan pengguna

Pada MySQL 5.7 dan sebelumnya, Anda dapat menggunakan variabel yang didefinisikan pengguna untuk menetapkan nomor berurutan per baris. Mari kita lihat contoh berikut.

Contoh: Peringkat penjualan per departemen

Data contoh:

Query:

SET @row_num = 0;
SET @dept = '';

SELECT
    employee,
    department,
    sale,
    @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
    @dept := department
FROM
    (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales;

Hasil:

5-2. Mengambil Baris Top N

Untuk mengambil Baris Top N, Anda dapat menggunakan variabel yang didefinisikan pengguna dengan cara serupa.

Query:

SET @row_num = 0;
SET @dept = '';

SELECT *
FROM (
    SELECT
        employee,
        department,
        sale,
        @row_num := IF(@dept = department, @row_num + 1, 1) AS rank,
        @dept := department
    FROM
        (SELECT * FROM sales ORDER BY department, sale DESC) AS sorted_sales
) AS ranked_sales
WHERE rank <= 3;

Hasil:

Query ini menetapkan peringkat per departemen dan kemudian mengekstrak hanya baris dalam tiga teratas.

5-3. Mendeteksi dan menghapus duplikat

Anda juga dapat menangani data duplikat menggunakan variabel yang didefinisikan pengguna.

Contoh query: Deteksi duplikat

SET @row_num = 0;
SET @id_check = '';

SELECT *
FROM (
    SELECT
        id,
        name,
        @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
        @id_check := name
    FROM
        (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
) AS tmp
WHERE rank > 1;

Contoh query: Hapus duplikat

DELETE FROM customers
WHERE id IN (
    SELECT id
    FROM (
        SELECT
            id,
            @row_num := IF(@id_check = name, @row_num + 1, 1) AS rank,
            @id_check := name
        FROM
            (SELECT * FROM customers ORDER BY name, id) AS sorted_customers
    ) AS tmp
    WHERE rank > 1
);

5-4. Catatan penting saat menggunakan variabel yang didefinisikan pengguna

1. Ketergantungan sesi

• Variabel yang didefinisikan pengguna hanya berlaku dalam sesi saat ini. Mereka tidak dapat digunakan kembali di query atau sesi yang berbeda.

2. Ketergantungan pada urutan pemrosesan

• Variabel yang didefinisikan pengguna bergantung pada urutan eksekusi, sehingga penetapan ORDER BY yang tepat sangat penting.

3. Keterbacaan dan pemeliharaan SQL

• Query dapat menjadi kompleks, jadi pada MySQL 8.0 dan yang lebih baru, disarankan menggunakan fungsi jendela.

Ringkasan

In MySQL versi di bawah 8.0, Anda dapat menggunakan variabel yang didefinisikan pengguna untuk menerapkan penomoran berurutan dan peringkat sebagai pengganti fungsi jendela. Namun, karena kueri cenderung menjadi lebih kompleks, sebaiknya pertimbangkan untuk migrasi ke versi yang lebih baru bila memungkinkan.

6. Peringatan dan Praktik Terbaik

MySQL’s ROW_NUMBER() function dan alternatif berbasis variabel sangat nyaman, tetapi ada poin penting yang perlu diingat agar dapat dijalankan dengan akurat dan efisien. Bagian ini menjelaskan peringatan praktis dan praktik terbaik untuk optimasi kinerja.

6-1. Pertimbangan Kinerja

1. Biaya ORDER BY

ROW_NUMBER() selalu digunakan bersama ORDER BY. Karena memerlukan pengurutan, waktu pemrosesan dapat meningkat secara signifikan untuk dataset yang besar.

Mitigasi:

• Gunakan indeks: Tambahkan indeks pada kolom yang digunakan dalam ORDER BY untuk mempercepat pengurutan.
• Gunakan LIMIT: Ambil hanya jumlah baris yang benar‑benar Anda butuhkan untuk mengurangi jumlah data yang diproses.

Contoh:

SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales
LIMIT 1000;

2. Peningkatan penggunaan memori dan I/O disk

Fungsi jendela diproses menggunakan tabel temporer dan memori. Seiring pertumbuhan volume data, konsumsi memori dan I/O disk dapat meningkat.

Mitigasi:

• Bagi kueri: Pecah pemrosesan menjadi kueri yang lebih kecil dan ekstrak data langkah demi langkah untuk mengurangi beban.
• Gunakan tabel temporer: Simpan data yang diekstrak dalam tabel temporer dan jalankan agregasi dari sana untuk mendistribusikan beban kerja.

6-2. Tips Penyetelan Kueri

1. Periksa rencana eksekusi

Di MySQL, Anda dapat menggunakan EXPLAIN untuk memeriksa rencana eksekusi kueri. Ini membantu Anda memverifikasi apakah indeks digunakan dengan benar.

Contoh:

EXPLAIN
SELECT
    employee,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Contoh output:

Jika Anda melihat Using index, itu menunjukkan bahwa indeks digunakan secara tepat.

2. Optimalkan indeks

Pastikan menambahkan indeks pada kolom yang digunakan dalam ORDER BY dan WHERE. Perhatikan khusus hal‑hal berikut.

• Indeks satu kolom: Baik untuk kondisi pengurutan sederhana
• Indeks komposit: Efektif ketika beberapa kolom terlibat dalam kondisi

Contoh:

CREATE INDEX idx_department_sale ON sales(department, sale DESC);

3. Gunakan pemrosesan batch

Alih‑alih memproses seluruh dataset yang besar sekaligus, Anda dapat mengurangi beban dengan memproses data secara batch.

Contoh:

SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 0;
SELECT * FROM sales WHERE department = 'Sales Department' LIMIT 1000 OFFSET 1000;

6-3. Mempertahankan konsistensi data

1. Pembaruan dan perhitungan ulang

Ketika baris disisipkan atau dihapus, penomoran dapat berubah. Bangun mekanisme untuk menghitung ulang nomor sesuai kebutuhan.

Contoh:

CREATE VIEW ranked_sales AS
SELECT
    employee,
    sale,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
FROM
    sales;

Menggunakan view membantu Anda menjaga peringkat tetap terkini berdasarkan data terbaru.

6-4. Contoh kueri praktik terbaik

Berikut adalah contoh praktik terbaik yang mempertimbangkan kinerja dan kemudahan pemeliharaan.

Contoh: Ekstrak N baris Teratas

WITH RankedSales AS (
    SELECT
        employee,
        department,
        sale,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY sale DESC) AS rank
    FROM
        sales
)
SELECT *
FROM RankedSales
WHERE rank <= 3;

Struktur ini menggunakan common table expression (CTE) untuk meningkatkan keterbacaan dan kegunaan kembali.

Ringkasan

Saat menggunakan ROW_NUMBER() atau alternatifnya, ingat poin‑poin berikut:

1. Tingkatkan kecepatan melalui optimasi indeks .
2. Identifikasi bottleneck dengan memeriksa rencana eksekusi .
3. Rencanakan pembaruan data dan pertahankan konsistensi.
4. Gunakan pemrosesan batch dan CTE untuk mendistribusikan beban.

Menerapkan praktik terbaik ini akan memungkinkan pemrosesan yang efisien untuk analisis dan pelaporan data berskala besar.

7. Kesimpulan

Dalam artikel ini, kami fokus pada fungsi ROW_NUMBER() MySQL, menjelaskan segala hal mulai dari penggunaan dasar dan contoh praktis hingga alternatif untuk versi lama, serta catatan penting dan praktik terbaik. Pada bagian ini, kami akan merangkum poin-poin utama dan menyimpulkan hal-hal praktis yang dapat diambil.

7-1. Mengapa ROW_NUMBER() berguna

Fungsi ROW_NUMBER() sangat berguna untuk analisis data dan pelaporan dengan cara berikut:

1. Penomoran berurutan dalam grup: Dengan mudah membuat peringkat penjualan berdasarkan departemen atau kategori.
2. Mengambil N baris teratas: Secara efisien menyaring dan mengekstrak data berdasarkan kondisi tertentu.
3. Mendeteksi dan menghapus duplikat: Berguna untuk pembersihan dan pengorganisasian data.

Karena menyederhanakan kueri kompleks, fungsi ini secara signifikan meningkatkan keterbacaan dan pemeliharaan SQL.

7-2. Perbandingan dengan fungsi jendela lainnya

Dibandingkan dengan fungsi jendela seperti RANK() dan DENSE_RANK(), ROW_NUMBER() berbeda karena memberikan nomor unik bahkan untuk nilai yang identik.

Memilih fungsi yang tepat:
Memilih fungsi terbaik untuk tujuan Anda memungkinkan pemrosesan data yang efisien.

7-3. Menangani versi MySQL yang lebih lama

Untuk lingkungan di bawah MySQL 8.0, kami juga memperkenalkan pendekatan menggunakan variabel buatan pengguna. Namun, Anda harus mempertimbangkan catatan penting berikut:

• Keterbacaan berkurang karena SQL yang lebih kompleks
• Optimasi kueri dapat menjadi lebih sulit dalam beberapa kasus
• Penanganan tambahan mungkin diperlukan untuk mempertahankan konsistensi data

Jika memungkinkan, pertimbangkan dengan kuat untuk migrasi ke MySQL 8.0 atau yang lebih baru dan menggunakan fungsi jendela.

7-4. Poin penting untuk optimasi kinerja

1. Gunakan indeks: Tambahkan indeks pada kolom yang digunakan dalam ORDER BY untuk meningkatkan kecepatan.
2. Periksa rencana eksekusi: Validasi kinerja sebelumnya dengan EXPLAIN.
3. Adopsi pemrosesan batch: Proses dataset besar dalam potongan lebih kecil untuk mendistribusikan beban.
4. Gunakan view dan CTE: Tingkatkan kegunaan kembali dan sederhanakan kueri kompleks.

Dengan menerapkan teknik-teknik ini, Anda dapat mencapai pemrosesan data yang efisien dan stabil.

7-5. Catatan akhir

ROW_NUMBER() adalah alat yang kuat yang dapat secara signifikan meningkatkan efisiensi analisis data.
Dalam artikel ini, kami membahas segala hal mulai dari sintaks dasar dan contoh praktis hingga catatan penting dan alternatif.

Kami mendorong Anda untuk menjalankan kueri sendiri sambil mengikuti artikel ini. Meningkatkan keterampilan SQL Anda akan membantu Anda menangani analisis data dan pelaporan yang lebih kompleks dengan percaya diri.

Lampiran: Sumber referensi

• Dokumentasi resmi: MySQL Window Functions
• Lingkungan SQL daring: SQL Fiddle (alat yang memungkinkan Anda menjalankan dan menguji SQL secara online)