| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Ntile Function
NTILE does allow you to generate percentiles, but not in the way you want from the data you have. You need to have seven occurrences of
27199203 $80.00
rather than one occurrence of
27199203 $80.00 7
Then you could query:
select id, amount, ntile(100) over (partition by id order by amount) as percentile
from normalised_table
order by id, percentile
or perhaps in your case, where you have a lower granularity:
select id, amount, 10 * ntile(10) over (partition by id order by amount) as percentile
from normalised_table
order by id, percentile
Then - if you want to print only the boundaries:
select id, percentile, max(amount)
from (
select id, amount, 10 * ntile(10) over (partition by id order by amount) as percentile
from normalised_table
order by id, percentile
BTW - Correct me if I'm wrong, but don't percentiles go from 0 to 99, or 1 to 100, rather than 0 to 100 ?
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Practical Oracle 8i: Building Efficient Databases Publishers: Addison-Wesley See a first review at: http://www.ixora.com.au/resources/index.htm#practical_8i More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html JShrimip, Jr. wrote in message <3A427B4C.AA949CEF_at_hotmail.com>...Received on Fri Dec 22 2000 - 03:28:54 CST
>Does the Ntile function create percentiles?
>I need to rank data by 10th/20th/30th, etc. percentile from a table
>containg three values:
>
>Id
>Amount
>Vol (#of identical amounts)
>Raw data starts like this:
>
> Id Amount Vol
>27199203 $35.00 1
>27199203 $70.00 1
>27199203 $76.00 2
>27199203 $80.00 7
>27199203 $81.00 10
>27199203 $88.00 2
>27199203 $98.00 10
>27199203 $100.00 1
>27199203 $101.00 7
>27199203 $105.00 1
>27199203 $106.00 2
>27199203 $108.00 2
>27199203 $109.00 4
>27199203 $111.00 1
>27199203 $112.00 3
>27199203 $117.00 16
>27199203 $121.58 1
>27199203 $130.00 1
>
>Result should look like this:
>ID Percentile Amount
>27199203 0.00 $35.00
>27199203 10.00 $80.00
>27199203 20.00 $81.00
>27199203 30.00 $88.00
>27199203 40.00 $98.00
>27199203 50.00 $101.00
>27199203 60.00 $106.00
>27199203 70.00 $112.00
>27199203 80.00 $117.00
>27199203 90.00 $117.00
>27199203 100.00 $150.00
>
>The following code does produce a ranking, but not in the right order
>(lowest amount to highest).
>Zipcpt is the ID
>Req_amt is the amount
>CPT_vol is the vol (this value "weights" the req_amt. The higher the
>volume, the greater the weight)
>
>
> column zipcpt format a12
> column "10th" format 9,999.99
> column "20th" format 9,999.99
> :<> //more of the same
> column "100th" format 9,999.99
> select distinct zipcpt,
> first_value("10th") over (partition by zipcpt order by "10th"
>nulls last) as "10th",
> first_value("20th") over (partition by zipcpt order by "20th"
>nulls last) as "20th",
> :<> //more of the same
> first_value("90th") over (partition by zipcpt order by "90th"
>nulls last) as "90th",
> first_value("100th") over (partition by zipcpt order by "100th"
>nulls last) as "100th" from
> (select cpt_vol, moving_count, zipcpt, req_amt, nNTIL,
> case when moving_count = ntil then req_amt END "10th",
> :<> //more of the same
> case when moving_count = ntil*9 then req_amt END "90th" ,
> case when moving_count = ntil*10 then req_amt END "100th"
> from
> (select
> cpt_vol,
> sum(cpt_vol) over (partition by zipcpt order by req_amt) as
>moving_count,
> zipcpt,
> req_amt,
> (sum(cpt_vol) over (partition by zipcpt)/10) as ntil,
> row_number () over (partition by zipcpt order by req_amt) as the_row,
> (sum(cpt_vol) over (partition by zipcpt)/10) * (row_number () over
>(partition by zipcpt order by req_amt)) NNTIL
> from sampledata
> order by zipcpt, moving_count)
> /
>
>27199203 $150.00 1
 |
 |