Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 15 Sep 2010 @ 10:27:20 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


Subj:   Re: Excluding Top and Bottom 5 % of records
 
From:   Maca & Dot

Dieter Noeth wrote:

  Instead of quantile (which is non-Standard and therefore deprecated) better use percent_rank, which is quite similar to your calculation:  


  qualify percent_rank() over (Partition by Team Order By Results desc) between 0.05 and 0.95  


  There's a slight difference between quantile and percent_rank, quantile is based on "(rank - 1) / count" vs. "(rank - 1) / (count - 1)"  


Hi all,

Initially I thought Rank_Percent() would be the answer. However, in looking to exclude top/bottom 5% of records, the problem was that if 10 % of the records were 0 and the last recored is negative then all the 0 values would be equally ranked at around 0.9 which would mean that the top 5% would be removed but only 1 record form the bottom would be removed. Hence the Row_Number() approach



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023