Using PERCENT_RANK in Oracle SQL-CodePudding

Can someone explain the mathematics behind Percent_Rank() in Oracle SQL.

SELECT ID, principal_disbursed, DUE_DATE, CREDIT_OFFICER, 
PERCENT_RANK() OVER (ORDER BY principal_disbursed)
FROM TABLE1

I need to understand the calculation running in the background.

id	principal_disbursed	principal_repaid	due_date	repayment_date	credit_officer
1	$334	$334	12/10/2017	17/10/2017	John
2	$184	$184	10/11/2018	20/10/2018	Maria
3	$417	$417	18/08/2017	24/08/2017	Maria
4	$500	$500	04/08/2017	14/07/2017	Maria
5	$250	$250	04/08/2017	06/08/2017	John

CodePudding user response：

Here is an example that might give you a hint. I have removed the dates.

with data(id,   principal_disbursed,    principal_repaid,   credit_officer) as (
select 1,   334,    334,'John' from dual union all
select 2,   184,    184,'Maria' from dual union all
select 3,   417,    417,'Maria' from dual union all
select 4,   500,    500,'Maria' from dual union all
select 5,   250,    250,'John' from dual
)
,cnt as (
select count(*)-1 cnt
from data
)
,one as (
SELECT ID, principal_disbursed, CREDIT_OFFICER
from data 
order by principal_disbursed
)
select one.*, case when rownum=1 then 0 else (rownum-1)/(select cnt from cnt) end
from one 
;

That is the same as :

with data(id,   principal_disbursed,    principal_repaid,   credit_officer) as (
select 1,   334,    334,'John' from dual union all
select 2,   184,    184,'Maria' from dual union all
select 3,   417,    417,'Maria' from dual union all
select 4,   500,    500,'Maria' from dual union all
select 5,   250,    250,'John' from dual
)
SELECT ID, principal_disbursed, CREDIT_OFFICER, 
PERCENT_RANK() OVER (ORDER BY principal_disbursed)
FROM data