CODEWITHSUNDEEP
sqlvertica
El Dj
El Dj

Reputation: 385

Calculate percentile in Vertica database

I've been trying, in vain, to return rows from a specific percentile but I keep getting wrong values. The values of SCORE in the database is where the percentile should be calculated from.

This is my query. I'm trying to get all values that are in the top 10%.

select x.count, MBR_NAME, MBR_ID, SCORE,
PERCENTILE_CONT(0.9) WITHIN GROUP(ORDER BY SCORE) OVER (PARTITION BY x.count) AS PERCENTILE
from tbSCORES_INFO, (select count(*) as count FROM tbSCORES_INFO) as x

When I run this query, I get a fixed value as percentile and it's the same for all rows.

The desired output should be to return all rows that falls in the xth percentile.

Upvotes: 2

Views: 3845

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

I think you want ntile():

select i.*, ntile(10) over (order by x.score) as decile
from tblscores_info i;

If you want the top 10%, then use a subquery:

select i.*
from (select i.*, ntile(10) over (order by x.score) as decile
      from tblscores_info i
     ) i
where decile = 10;

The PERCENTILE() functions return the value that is the breaking point. NTILE() actually assigns the "percentile".

Upvotes: 3

Related Questions