In this post, I would like to explain a function that is not well known. CUME_DIST analytic function is used for calculating the cumulative distribution of a data in the group of data. It returns value greater than 0 and less than or equal to 1. Let’s create our test table.
Set Up
create table t_test
(
id number primary key,
team varchar2(50),
point number
);
insert into t_test values(1, 'Real Madrid', 85);
insert into t_test values(2, 'Barcelona', 72);
insert into t_test values(3, 'Manchester United', 75);
insert into t_test values(4, 'Arsenal', 55);
insert into t_test values(5, 'Chelsea', 67);
insert into t_test values(6, 'Milan', 69);
insert into t_test values(7, 'Juventus', 59);
insert into t_test values(8, 'Bayern Munih', 69);
commit;
select
t.*,
cume_dist() over(order by point) pct,
count(*) over(order by point) / count(*) over() pct2,
count(*) over(order by point range between unbounded preceding and current row) / count(*) over() pct3
from t_test t
order by point desc, id;
As shown above, it is easy to use CUME_DIST function in order to calculate score percentile. Equivalent solutions are also shown by PCT2 and PCT3 columns. Note that in PCT3, range between is used instead of rows between. Otherwise, percentile of Milan and Bayern Munih would have been different. In addition, this also shows that the default value of analytical functions is range between. I mentioned this in my post on Analytic functions & Windowing clause.
I hope this helps 🙂