Fungsi SQL ROW_NUMBER: Cara Menampilkan Nomor Baris

Dalam SQL, dataset sering kali tidak terurut, yang dapat menyulitkan analisis. Untuk memahami bagaimana baris-baris saling berkaitan dalam sebuah dataset, kita dapat menggunakan fungsi ROW_NUMBER().

Fungsi ini memberikan nomor berurutan pada baris dalam satu set hasil, sehingga menyediakan urutan yang jelas untuk manipulasi dan analisis lebih lanjut. Ini dapat dilakukan untuk keseluruhan dataset atau untuk kelompok-kelompok data berbeda di dalam dataset.

Artikel ini mengasumsikan Anda sudah memahami dasar-dasar SQL. Kami akan membahas dasar-dasar fungsi ROW_NUMBER() yang umum digunakan dan memberikan contoh-contoh dengan tingkat kesulitan yang meningkat.

ROW_NUMBER() Syntax

Berikut sintaks dasar untuk fungsi ROW_NUMBER():

ROW_NUMBER() OVER([PARTITION BY value expression, ... ] [ORDER BY order_by_clause])

Mari kita uraikan komponen utamanya:

• ROW_NUMBER(): Ini adalah fungsi itu sendiri, yang menghasilkan nomor baris berurutan.
• OVER (...): Klausa ini wajib untuk window function seperti ROW_NUMBER(). Ini mendefinisikan konteks tempat nomor baris dihitung.
• PARTITION BY value_expression: Klausa opsional ini membagi set hasil ke dalam partisi berdasarkan kolom atau ekspresi yang ditentukan. Nomor baris kemudian dihitung secara independen dalam setiap partisi.
• ORDER BY order_by_clause: Klausa opsional ini menentukan urutan penetapan nomor baris dalam setiap partisi (atau seluruh set hasil jika tidak menggunakan PARTITION BY).

Sebagai ilustrasi, berikut cara kita menggunakan ROW_NUMBER() dalam kueri SQL yang lebih luas:

SELECT Val_1, 
    ROW_NUMBER() OVER(PARTITION BY group_1, ORDER BY number DESC) AS rn
FROM Data;

ROW_NUMBER() Examples

Dalam tiga contoh berikut, kita akan menggunakan IDE DataLab gratis. Kita akan menggunakan dataset contoh Employees (sudah tersedia di DataLab), yang memiliki empat kolom berikut:

• first_name: bidang string
• last_name: bidang string
• gender: bidang string dengan dua nilai (“M” atau “F”)
• hire_date: tanggal karyawan direkrut

Kita dapat mengkueri dataset menggunakan kode SQL berikut:

SELECT e.first_name, e.last_name, e.gender, e.hire_date
    FROM employees.employees e
LIMIT 100; -- Optionally reduce the size of the output

Memberi nomor karyawan dalam urutan alfabet

Sebelum menggunakan ROW_NUMBER(), penting untuk menentukan tujuan kita—ini akan memperjelas apakah dan bagaimana kita ingin melakukan partisi dan pengurutan. Pada contoh ini, kita ingin mengurutkan semua karyawan secara alfabet. Kita tidak memerlukan klausa PARTITION BY karena kita mengurutkan semua karyawan dalam dataset. Kita akan mengurutkan pelanggan berdasarkan nama belakang mereka (last_name). Kita akan menamai penomoran kita name_row_number.

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(ORDER BY e.last_name) AS name_row_number    
FROM employees.employees e;

Untuk menangani nilai yang sama (karyawan dengan nama belakang sama), kita dapat memperhalus pengurutan dengan menambahkan lebih banyak kolom. Pada contoh di bawah, kita mengurutkan terlebih dahulu berdasarkan last_name, lalu, jika nama belakang seorang karyawan sama dengan orang lain, kita akan mengurutkan berdasarkan nama depan mereka (first_name).

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(ORDER BY e.last_name, e.first_name) AS name_row_number    
FROM employees.employees e;

Memberi nomor karyawan berdasarkan tanggal rekrut terbaru dalam masing-masing gender

Sekarang, mari urutkan karyawan dari tanggal rekrut terbaru hingga terlama dalam masing-masing gender. Kita akan kembali menggunakan klausa ORDER BY untuk mengurutkan berdasarkan hire_date, tetapi kali ini secara menurun (menggunakan DESC) agar rekrutmen terbaru diprioritaskan.

Untuk mendapatkan penomoran terpisah bagi setiap gender, kita akan menambahkan klausa PARTITION BY gender. Ini berarti nomor baris akan dimulai lagi dari 1 untuk setiap gender yang berbeda.

Berikut kueri lengkapnya:

SELECT e.first_name, e.last_name, e.gender, e.hire_date,
    ROW_NUMBER() OVER(PARTITION BY gender ORDER BY hire_date DESC) AS hire_row_number    
FROM employees.employees e;

Kita kemudian dapat mengkueri data ini menggunakan klausa WHERE untuk menemukan karyawan paling berpengalaman di setiap gender:

WITH RankedEmployees AS (
    SELECT e.first_name, e.last_name, e.gender, e.hire_date,
        ROW_NUMBER() OVER(PARTITION BY gender ORDER BY hire_date DESC) AS hire_row_number
    FROM employees.employees e
)
SELECT first_name, last_name, gender, hire_date
FROM RankedEmployees
WHERE hire_row_number = 1;

Mengurutkan karyawan berdasarkan gaji dalam masing-masing gender menggunakan JOIN

Pada contoh terakhir, kita akan memberi peringkat karyawan berdasarkan gaji, dengan mempertimbangkan gender mereka. Untuk mencapainya, kita akan melakukan join tabel employees dengan tabel salaries berdasarkan kolom emp_no:

SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary
    FROM employees.employees e
JOIN employees.salaries s ON e.emp_no = s.emp_no
LIMIT 100;

Sekarang, kita akan menggunakan PARTITION BY dan ORDER BY sekaligus. Kita akan mempartisi berdasarkan gender agar ada peringkat terpisah untuk setiap gender dan mengurutkan berdasarkan salary secara menurun untuk memeringkat peraih gaji tertinggi terlebih dahulu.

Berikut kueri lengkapnya:

SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary,
    ROW_NUMBER () OVER(PARTITION BY e.gender ORDER BY s.salary DESC) AS salary_row_number
    FROM employees.employees e
JOIN employees.salaries s ON e.emp_no = s.emp_no
LIMIT 100;

Untuk membandingkan gaji teratas untuk setiap gender, kita dapat memfilter hasil menggunakan klausa WHERE. Kueri di bawah akan menampilkan 5 penerima gaji tertinggi untuk setiap gender, diurutkan berdasarkan peringkat dalam kelompok gender masing-masing. Kueri seperti ini dapat memberikan wawasan tentang kesetaraan upah dalam dataset.

WITH RankedSalaries AS (
    SELECT e.first_name, e.last_name, e.gender, e.hire_date, s.salary,
        ROW_NUMBER() OVER(PARTITION BY e.gender ORDER BY s.salary DESC) AS salary_row_number
    FROM employees.employees e
    JOIN employees.salaries s ON e.emp_no = s.emp_no
)
SELECT first_name, last_name, gender, hire_date, salary
FROM RankedSalaries
WHERE salary_row_number <= 5
ORDER BY salary_row_number, gender;

Kesimpulan

Fungsi ROW_NUMBER() berguna saat kita memiliki dataset yang tidak terurut dan ingin memberikan penomoran baris berurutan yang jelas untuk analisis lebih lanjut. Kita menentukan urutan spesifik nomor tersebut menggunakan ORDER BY dan menentukan urutan penomoran terpisah untuk kelompok-kelompok berbeda dalam data menggunakan PARTITION BY.

Jika Anda merasa artikel ini bermanfaat dan ingin mempelajari lebih lanjut tentang SQL, lihat kursus SQL kami lainnya.