From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Tim Uckun <timuckun(at)gmail(dot)com> |
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:13:17 |
Message-ID: | 4CBBAD3D.9010805@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 10/18/2010 08:06 AM, Tim Uckun wrote:
>> That is a bit problematic because it necessitates knowing the number
>> of rows total, and slow counting is an idiosyncrasy of postgres.
>>
>> http://wiki.postgresql.org/wiki/Slow_Counting
>>
>> To get the top 10%:
>>
>> SELECT * FROM table LIMIT(SELECT (COUNT(*) * 0.1)::integer FROM table)
>
>
> I think I wasn't making myself clear. I don't want the top 10% of the
> rows. I want the rows with the top 10% of the values in a column.
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;
See:
http://www.postgresql.org/docs/current/static/functions-window.html
Both of these seem to produce odd results with small input row counts.
Test carefully before trusting these expressions, as I'm quite new to
the use of window functions.
--
Craig Ringer
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Uckun | 2010-10-18 02:18:50 | Re: Selecting top N percent of records. |
Previous Message | Craig Ringer | 2010-10-18 01:40:15 | Re: installing from source in Windows |