4 ms·

I've noticed that ntile(n) can give unexpected results when there are fewer input rows than n, so cume_dist might be a safer choice (or percentile_disc or perce

I've noticed that ntile(n) can give unexpected results when there are fewer input rows than n, so cume_dist might be a safer choice (or percentile_disc or percentile_cont). I wrote some more details here: https://stackoverflow.com/questions/27883999/how-to-calculate-percentile-in-postgres/39535688#39535688 https://stackoverflow.com/questions/27883999/how-to-calculat... This behavior means that ntile(100) is not the same thing as percentile. But I've always wondered if I was just misunderstanding something. I wonder what others think?

da_chicken 8y ago
ntile() is one of those functions that just never seems to work the way users expect regardless of the RDBMS. I don't know if the spec's definition was just poorly thought out or what, but it's often flaky, poorly performing, or both in many RDBMSs. In PostgreSQL, you should generally use cume_dist(), percentile_cont() and percentile_disc(). Most later releases of other RDBMS have similar window functions that work more like users expect. I'm not certain what's up, but it's definitely led me to believe that ntile() was just poorly thought out at the spec level.
pfarrell 8y ago
I had a project where I had to build a tool to relocate customer data to another database (for customers who had grown large enough to prevent them from sharing db resources with other customers). This was on SQL Server. I used NTile to help create constant size batches to feed into my etl tool. Worked great for that. Haven't used it for statistical bucketing purposes.