Creating Twentiles

Database
Fan

Creating Twentiles

I have a set of 200,000 records.

The records have a row, we'll call it 'Balance'.

I want to create twentiles...ie: carve the population up into 20 roughly equal segments.

In the data, about 50% of the records have a balance of zero or close to zero, and then it goes up from there.

When I run

select quantile(20,balance) as "Bal_Twentile" from ....

and then run a distribution on them, I end up with half the records having a decile of 0, and then 11,12,13, etc. There are no records that have been assigned a twentile of 1,2,3...up to 10.

What's going on here? This seems wrong to me.

Thanks!
1 REPLY
Junior Contributor

Re: Creating Twentiles

All rows with the same value are supposed to be in the same quantile, so this is correct behaviour.

If you don't care about that rule then just modify the QUANTILE formula
(RANK() OVER (ORDER BY balance) - 1) * 20 / COUNT(*) OVER()

to
(ROW_NUMBER() OVER (ORDER BY balance) - 1) * 20 / COUNT(*) OVER()

Dieter