Ntile Function

From: JShrimip, Jr. <jumbo_shrimps_at_hotmail.com>
Date: Thu, 21 Dec 2000 16:51:08 -0500
Message-ID: <3A427B4C.AA949CEF@hotmail.com>

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 Received on Thu Dec 21 2000 - 15:51:08 CST