Window

PERCENT_RANK

PostgreSQL

Menghitung relative rank sebagai persentase (0 sampai 1). Formula: (rank - 1) / (total_rows - 1). Baris pertama selalu 0, baris terakhir selalu 1.

Tipe hasil: double precision

Syntax

SQL

PERCENT_RANK() OVER (
  [PARTITION BY partition_expression]
  ORDER BY sort_expression
)

Parameter

ORDER BYexpressionwajib

Menentukan urutan ranking

PARTITION BYexpressionopsional

Membagi data menjadi grup terpisah

Contoh Penggunaan

Percentile Rank

SQL

1 SELECT 
2   name,
3   salary,
4   PERCENT_RANK() OVER (ORDER BY salary) AS percentile
5 FROM employees;

Menghitung posisi relatif gaji setiap karyawan.

Hasil

name	salary	percentile
Andi	5000000	0
Budi	7000000	0.25
Citra	9000000	0.5
Dewi	12000000	0.75
... 1 baris lainnya

Filter Top 10%

SQL

1 SELECT *
2 FROM (
3   SELECT 
4     product_name,
5     sales,
6     PERCENT_RANK() OVER (ORDER BY sales DESC) AS pct_rank
7   FROM products
8 ) ranked
9 WHERE pct_rank <= 0.1;

Mencari produk di top 10% penjualan.

Perbandingan dengan CUME_DIST

SQL

1 SELECT 
2   score,
3   PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
4   CUME_DIST() OVER (ORDER BY score) AS cume_dist
5 FROM test_results;

PERCENT_RANK dimulai dari 0, CUME_DIST tidak pernah 0.

Fungsi Equivalen di Database Lain

PERCENT_RANK

MySQL

PERCENT_RANK

Snowflake

PERCENT_RANK

BigQuery

PERCENT_RANK

DuckDB

Fungsi Terkait

ROW_NUMBERRANKDENSE_RANKCUME_DISTNTILE

Kembali ke PostgreSQL Functions

1	SELECT
2	name,
3	salary,
4	PERCENT_RANK() OVER (ORDER BY salary) AS percentile
5	FROM employees;

1	SELECT *
2	FROM (
3	SELECT
4	product_name,
5	sales,
6	PERCENT_RANK() OVER (ORDER BY sales DESC) AS pct_rank
7	FROM products
8	) ranked
9	WHERE pct_rank <= 0.1;

1	SELECT
2	score,
3	PERCENT_RANK() OVER (ORDER BY score) AS pct_rank,
4	CUME_DIST() OVER (ORDER BY score) AS cume_dist
5	FROM test_results;