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(at)alcove(dot)com(dot)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(at)postgresql(dot)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(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83