Re: N-tile function in postgres

[François Beausoleil <francois@xxxxxxxxxxx>]

Le 2012-09-24 à 14:12, Rachel Owsley a écrit :

Thank you, François! This is very helpful! I’ll give this query a try. I don’t know the cross-tab function, but that’s exactly what I want to do for the column output. Regarding the sample query, I see the min (amount), but how is the upper bound defined for each decile?

ntile() splits the output in as even partitions as possible. If you have 13 rows, and you want 10 output rows, then each row will receive something like this:

# select id, ntile(10) over () from generate_series(1, 13) as t1(id);

 id | ntile 

----+-------

  1 |     1

  2 |     1

  3 |     2

  4 |     2

  5 |     3

  6 |     3

  7 |     4

  8 |     5

  9 |     6

 10 |     7

 11 |     8

 12 |     9

 13 |    10

The ntile() function isn't tied to the values at all: only to the actual number of rows. I used min(amount) to get the minimal value per group, but you can use use max(amount) to get the other end as well.

Bye!

François