Fungsi MySQL ROW_NUMBER

Ringkasan : dalam tutorial Fungsi MySQL ROW_NUMBER, Anda akan belajar tentang ROW_NUMBER()fungsi MySQL dan bagaimana menggunakannya untuk menghasilkan nomor urut untuk setiap baris dalam set hasil.

ROW_NUMBER()Sintaks MySQL

MySQL memperkenalkan ROW_NUMBER()fungsi tersebut sejak versi 8.0. Ini ROW_NUMBER()adalah fungsi jendela atau fungsi analitik yang menetapkan nomor urut ke setiap baris yang diterapkan dimulai dengan satu.

Perhatikan bahwa jika Anda menggunakan MySQL dengan versi kurang dari 8.0, Anda dapat meniru beberapa fungsionalitas ROW_NUMBER()fungsi tersebut menggunakan berbagai teknik.

Berikut ini adalah sintaks dari ROW_NUMBER()fungsi tersebut:

ROW_NUMBER() OVER (<partition_definition> <order_definition>)

Bahasa kode: SQL (Structured Query Language) ( sql )

partisi_definisi

Ini partition_definitionmemiliki sintaks berikut:

PARTITION BY <expression>,[{,<expression>}…]

Bahasa kode: SQL (Structured Query Language) ( sql )

The PARTITION BYklausul istirahat baris ke set yang lebih kecil. Ekspresi dapat berupa ekspresi valid apa pun yang akan digunakan dalam GROUP BYklausa. Anda dapat menggunakan beberapa ekspresi yang dipisahkan dengan koma.

The PARTITION BYklausa adalah opsional. Jika Anda menghilangkannya, seluruh rangkaian hasil dianggap sebagai partisi. Namun, saat Anda menggunakan PARTITION BYklausa, setiap partisi juga dapat dianggap sebagai jendela.

order_definition

The order_definitionsintaks terlihat seperti berikut:

ORDER BY <expression> [ASC|DESC],[{,<expression>}…]

Bahasa kode: SQL (Structured Query Language) ( sql )

Tujuan dari ORDER BYklausa adalah untuk mengatur urutan baris. Ini ORDER BYklausul independen dari ORDER BYklausul dari query.

ROW_NUMBER()Contoh fungsi MySQL

Mari gunakan productstabel dari database sampel untuk demonstrasi:

1) Menetapkan nomor urut ke baris

Pernyataan berikut menggunakan ROW_NUMBER()fungsi untuk menetapkan nomor urut ke setiap baris dari productstabel:

SELECT

ROW_NUMBER() OVER (

ORDER BY productName

) row_num,

productName,

msrp

FROM

products

ORDER BY

productName;

Bahasa kode: SQL (Structured Query Language) ( sql )

Inilah hasilnya:

2) Menemukan N baris teratas dari setiap grup

Anda dapat menggunakan ROW_NUMBER()fungsi untuk kueri yang menemukan baris N teratas untuk setiap grup, misalnya, tiga karyawan penjualan teratas dari setiap saluran penjualan, lima produk berkinerja tinggi teratas dari setiap kategori.

Pernyataan berikut menemukan tiga produk teratas yang memiliki inventaris tertinggi di setiap lini produk:

WITH inventory

AS (SELECT

productLine,

productName,

quantityInStock,

ROW_NUMBER() OVER (

PARTITION BY productLine

ORDER BY quantityInStock DESC) row_num

FROM

products

)

SELECT

productLine,

productName,

quantityInStock

FROM

inventory

WHERE

row_num <= 3;

Bahasa kode: SQL (Structured Query Language) ( sql )

Dalam contoh ini,

• Pertama, kami menggunakan ROW_NUMER()fungsi untuk memberi peringkat inventaris semua produk di setiap lini produk dengan mempartisi semua produk berdasarkan lini produk dan memesannya berdasarkan kuantitas dalam stok dalam urutan menurun. Hasilnya, setiap produk diberi peringkat berdasarkan kuantitasnya dalam stok. dan peringkat disetel ulang untuk setiap lini produk.
• Kemudian, kami hanya memilih produk yang peringkatnya kurang dari atau sama dengan tiga.

Berikut outputnya:

3) Menghapus baris duplikat

Anda dapat menggunakan ROW_NUMBER()untuk mengubah baris non-unik menjadi baris unik lalu menghapus baris duplikat . Perhatikan contoh berikut.

Pertama, buat tabel dengan beberapa nilai duplikat:

CREATE TABLE t (

id INT,

name VARCHAR(10) NOT NULL

);

INSERT INTO t(id,name)

VALUES(1,’A’),

(2,’B’),

(2,’B’),

(3,’C’),

(3,’C’),

(3,’C’),

(4,’D’);

Bahasa kode: SQL (Structured Query Language) ( sql )

Kedua, gunakan ROW_NUMBER()fungsi untuk membagi baris menjadi partisi dengan semua kolom. Nomor baris akan dimulai ulang untuk setiap kumpulan baris unik.

SELECT

id,

name,

ROW_NUMBER() OVER (PARTITION BY id, name ORDER BY id) AS row_num

FROM t;

Bahasa kode: SQL (Structured Query Language) ( sql )

Seperti yang Anda lihat dari output, baris unik adalah baris yang nomor barisnya sama dengan satu.

Ketiga, Anda dapat menggunakan ekspresi tabel umum (CTE) untuk mengembalikan baris duplikat dan menghapus pernyataan untuk dihapus:

WITH dups AS (SELECT

id,

name,

ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id) AS row_num

FROM t)

DELETE FROM t USING t JOIN dups ON t.id = dups.id

WHERE dups.row_num <> 1;

Bahasa kode: SQL (Structured Query Language) ( sql )

Perhatikan bahwa MySQL tidak mendukung penghapusan berbasis CTE, oleh karena itu, kami harus menggabungkan tabel asli dengan CTE sebagai solusinya.

4) Penomoran halaman menggunakan  ROW_NUMBER()fungsi

Karena ROW_NUMBER()menetapkan setiap baris dalam hasil menetapkan nomor unik, Anda dapat menggunakannya untuk penomoran halaman.

Misalkan, Anda perlu menampilkan daftar produk dengan 10 produk per halaman. Untuk mendapatkan produk untuk halaman kedua, Anda menggunakan query berikut:

SELECT *

FROM

(SELECT productName,

msrp,

row_number()

OVER (order by msrp) AS row_num

FROM products) t

WHERE row_num BETWEEN 11 AND 20;

Bahasa kode: SQL (Structured Query Language) ( sql )

Inilah hasilnya:

Dalam tutorial ini, Anda telah mempelajari cara menggunakan ROW_NUMBER()fungsi MySQL untuk menghasilkan nomor urut untuk setiap baris dalam set hasil.