Re: count (DISTINCT field) OVER () - Mailing list pgsql-general

On Thu, 10 Nov 2011 10:02:36 +0100
Thomas Kellerer <spam_eater@gmx.net> wrote:

>Tarlika Elisabeth Schmitz, 10.11.2011 00:52:
>> I would like to implement the equivalent of "count (DISTINCT id)
>> OVER ()":
>>
>>[...]
>>
>> produces result:
>> id, name, delta, cnt
>> 1787    Toomyvara    0.5    4
>> 1787    Toomevara    0.4    4
>> 1700    Ardcroney    0.105    4
>> 1788    Townsfield    0.1    4
>>
>
>This should do it:
>
>SELECT id,
>        name,
>        delta,
>        sum(case when rn = 1 then rn else null end)  over() as
> distinct_id_count
>FROM (
>     SELECT
>       id, name, similarity(name, 'Tooneyvara') as delta,
>       row_number() OVER(partition by id) AS rn
>       FROM vtown
>       WHERE
>           similarity(name, 'Tooneyvara') > 0.1
>) t
>ORDER BY delta DESC
>


I like you suggestion, Thomas. It is not that dissimilar from but
cleaner than my original SELECT ... FROM (SELECT DISTINCT ON(id)
attempt.
It's also very slightly faster.

Here's another, slightly shorter, variation of your suggestion:

SELECT id, name, delta,
       max(rank)  OVER() as cnt
FROM (
     SELECT
       id, name, similarity(name, 'Tooneyvara') as delta,
       dense_rank() OVER(ORDER BY id) AS rank
       FROM vtown
       WHERE
           similarity(name, 'Tooneyvara') > 0.1
) t
ORDER BY delta DESC