From: | Tim Uckun <timuckun(at)gmail(dot)com> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | Peter Geoghegan <peter(dot)geoghegan86(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Selecting top N percent of records. |
Date: | 2010-10-18 02:18:50 |
Message-ID: | AANLkTimo_q_LqCuFm9+k3gYs9VOW4s0uwYBRt0q_rmDM@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> OK, so you want a median-style "sort them in descending order and count down
> until you've selected the first 10% of rows" approach? In other words,
> values in the 90th percentile of the distribution?
>
> Try this. Given table "x" with single integer column "y", obtain rows of x
> in the 90th percentile of y:
>
> select ranked.y FROM (select percent_rank() over (order by y desc) as pc, y
> from x) AS ranked WHERE pc <= 0.1;
>
> or:
>
> select ranked.y from (select ntile(10) over (order by y desc) as pc, y from
> x) AS ranked WHERE pc = 1;
>
Thanks I will look into the window functions. I haven't used them
before so thanks for the tip.
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Smith | 2010-10-18 02:46:39 | Re: installing from source in Windows |
Previous Message | Craig Ringer | 2010-10-18 02:13:17 | Re: Selecting top N percent of records. |