 |
 |
Archives of the TeradataForum
Message Posted: Wed, 15 Sep 2010 @ 10:27:20 GMT
| 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
| |
