Re: N-tile function in postgres - Mailing list pgsql-general

From François Beausoleil
Subject Re: N-tile function in postgres
Date
Msg-id 8EEEEE28-A89B-4D4D-B8BC-EE898A8F82EB@teksol.info
Whole thread Raw
In response to Re: N-tile function in postgres  (Rachel Owsley <Rachel.Owsley@edointeractive.com>)
Responses Re: N-tile function in postgres
List pgsql-general

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

pgsql-general by date:

Previous
From: Oleg Bartunov
Date:
Subject: Re: How to do a full-text search words within some proximity of each other?
Next
From: Rachel Owsley
Date:
Subject: Re: N-tile function in postgres