Re: count (DISTINCT field) OVER ()

On Thu, 10 Nov 2011 10:02:36 +0100
Thomas Kellerer <spam_eater(at)gmx(dot)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