CODEWITHSUNDEEP
mysqloracle-databaseplsqloracle11gplsqldeveloper
Monica
Monica

Reputation: 169

SQL Query / find percentile based on rank

I have created the following tables with ranks for a data set:

Position  Index IndexL IndexH Amount Rank
1          2.5    2      3     2000   1     
1          2.5    2      3     3000   2
1          2.5    2      3     4000   3
1          2.5    2      3     5000   4
1          2.5    2      3     6000   5

2          1.5    1      2     2500   1     
2          1.5    1      2     4500   2
2          1.5    1      2     6700   3
2          1.5    1      2     8900   4
2          1.5    1      2     9900   5

Now I want to find the percentile based on the ranks created using the indices such that I get the following output : 

Position Amount 
1         3000+(4000-3000)*(2.5-2)
2         2500+(4500-2500)*(1.5-1)

Can someone help me with this. I am kinda new to SQL world.

Thanks, Monica

Upvotes: 1

Views: 1278

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26353

You can have Oracle assign a percentile for you using the NTILE analytic function:

SELECT
  position,
  amount,
  NTILE(100) OVER (PARTITION BY POSITION ORDER BY amount)
FROM myTable

I'm not sure if the result will match your calculations (I'm a bit hazy on some of my statistics). If not, please try the PERCENTILE_CONT solution proposed by @GordonLinoff, or else you can try the PERCENT_RANK analytic function - just replace NTILE(100) in the query above with PERCENT_RANK().

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

I think you can do what you want with the percentile_cont() aggregation function. It looks like you want the median:

SELECT position,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Amount) as Median
from t
group by position;

You can read more about it here.

Upvotes: 1

Related Questions