Re: Planning aggregates which require sorted or distinct

Gavin,

I'm also interested in the topic, but right now I am wondering if
rank() function is a reserved name ?  We're working on built-in
tsearch2 for 8.3 release and we already have rank() function.

Oleg

On Sun, 21 Jan 2007, Gavin Sherry wrote:

> On Sat, 20 Jan 2007, Tom Lane wrote:
>
>> Gavin Sherry <swm@alcove.com.au> writes:
>>> We want to answer the following: for each employee: what is their rank in
>>> terms of salary and what is their rank in terms of age. This query
>>> answers that:
>>
>>> select empno, rank() over (order by salary) as srank,
>>>   rank() over (order by age) as arank
>>>   from employees order by empno;
>>
>> Eeek.  This seems like the worst sort of action-at-a-distance.  How does
>> rank() know what value it's supposed to report the rank of?
>
> This is a frustratingly inconsistent bit of the spec. Rank is defined as
> follows:
>
> RANK() OVER WNS is equivalent to:
>   ( COUNT (*) OVER (WNS1 RANGE UNBOUNDED PRECEDING)
>   - COUNT (*) OVER (WNS1 RANGE CURRENT ROW) + 1 )
>
> Say the salary column has the following values: {100, 200, 200, 300}. This
> would give the following output: {1, 2, 2, 4}. DENSE_RANK() would give:
> {1, 2, 2, 3}.
>
> These functions are pretty ugly (if you think about them in terms of our
> existing aggregates). However, they are by far the most heavily used
> window functions (along with ROW_NUMBER()).
>
> Thanks,
>
>
> Gavin
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
    Regards,        Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83