Fungsi jendela: pemeringkatan dan penghitungan per grup

Fungsi jendela adalah fungsi yang beroperasi pada "grup baris" (jendela_) tetapi — tidak seperti GROUP BY — tidak mengurangi jumlah baris: nilai yang dihitung melalui jendela dilampirkan ke setiap baris hasil akhir.

Ini adalah alat yang tepat untuk menentukan peringkat, penomoran progresif, persentase atas total grup, dan banyak operasi lain yang canggung atau tidak mungkin dengan GROUP BY.

Sintaksnya: function() OVER (…)

ROW_NUMBER() OVER (PARTITION BY colonna ORDER BY colonna2)

Komponen:

• PARTITION BY — mendefinisikan "grup" (seperti GROUP BY, tetapi barisnya tetap terpisah);
• ORDER BY di dalam OVER(…) — menentukan urutan dalam partisi.

OVER () (tanda kurung kosong) berarti "seluruh tabel sebagai satu jendela". Berguna untuk penghitungan seperti "persentase total".

Fungsi peringkat

ROW_NUMBER() OVER (PARTITION BY p ORDER BY o)
RANK()       OVER (PARTITION BY p ORDER BY o)
DENSE_RANK() OVER (PARTITION BY p ORDER BY o)

Perbedaan (dengan nilai 100, 100, 90, 80):

Contoh: peringkat produk per kategori

SELECT name,
       category_id,
       price,
       RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS rank
FROM   products
ORDER BY category_id, rank;

Untuk setiap category_id RANK dimulai ulang dari 1 dan diurutkan secara menurun harga: Anda mendapatkan peringkat produk dalam setiap kategori, dalam satu kategori ditembak.

Penomoran progresif

-- Progressive order number for each customer:
SELECT id,
       customer_id,
       ordered_at,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS row_num
FROM   orders
ORDER BY customer_id, row_num;

Setiap pesanan pertama pelanggan akan memiliki row_num = 1, pesanan kedua 2, dst.

Cobalah

Tinjau latihan