Fun with SQL: Window Functions in Postgres

Re:Fun with SQL: Window Functions in Postgres by pjungwir on Monday June 04, 2018 @05:58PM (#17232982)

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-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?

[ Parent | on HN ]

Re:Fun with SQL: Window Functions in Postgres by da_chicken on Monday June 04, 2018 @07:35PM (#17233640)

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.

[ Parent | on HN ]

Re:Fun with SQL: Window Functions in Postgres by pfarrell on Tuesday June 05, 2018 @02:19AM (#17235381)

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.

[ Parent | on HN ]