2014-11-08

TSQL - Integer Percentiles

select
       T1.Field1

       , PERCENT_RANK()
              over( order by Field1 asc ) as PercentRank

       , cast(
              round(
                     PERCENT_RANK()
                           over( order by Field1 asc )
                     * 100
                     , 0
              )
              as int
       ) as PercentRankInt
from
       (
                             select 1 as Field1
              union all select 1
              union all select 1
              union all select 2
              union all select 2
              union all select 3
              union all select 4
              --union all select 8
       ) as T1
order by
       Field1
; 



PercentRankInt calculates the percentile each value occupies in relation to all other values in the column, as an integer number, ready to be used for quick filters, thus enabling the exclusion of, for example, top and bottom deciles, quintiles, etc.

No comments:

Post a Comment