Re: SELECT TOP %d PERCENT, or SELECT ... LIMIT %d PERCENT ?

2008/10/13 Grzegorz Jaskiewicz <gj@pointblue.com.pl>:
>
> On 2008-10-12, at 16:22, Hitoshi Harada wrote:
>
>> 2008/10/12 Robert Haas <robertmhaas@gmail.com>:
>>>
>>> I wonder if this could be implemented using the window-function
>>> infrastructure...
>>>
>>
>> Yeah, actually window functions I am working now has percent_rank() or
>> something he wants. That is better than WITH clauses but still we must
>> see the last row before determine the percent. Better than count(*) of
>> course, but I'm not sure it meets his needs.
>>
> excuse my ignorance, apparently I don't recall that patches discussion in
> here. If it pops out, and gets into cvs, can someone remind me of that -
> please ?

You can track pgsql news on David Fetter's blog:
http://people.planetpostgresql.org/dfetter/

> Also, will that patch also be able to speed up count(*) , or is it for
> different purpose ? Sorry if I am repeating the subject, if so - please just
> respond in private.

I guess cume_dist() will do for you:

SELECT id, cume_dist
FROM( SELECT id, cume_dist() OVER (ORDER BY id) FROM foo
)s
WHERE cume_dist < 0.3

will produce a result with id of 30% ascending order. I am working on
this window functions infrastructure to get into 8.4 but we're still
not sure if it will.

You can google "window functions" or my design docs on
http://umitanuki.net/pgsql/wfv06/design.html to see what it is for.


Regards,


-- 
Hitoshi Harada