From: | Daniel Cristian Cruz <danielcristian(at)gmail(dot)com> |
---|---|
To: | pgsql-admin <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: COUNT(DISTINCT field) OVER (PARTITION BY another_field) |
Date: | 2009-07-21 12:32:01 |
Message-ID: | 48d0cacb0907210532p7941a9f2i4f7b6bc7407b1131@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Auto-answer:
WITH uniques AS (
SELECT group_key, COUNT(DISTINCT reference) AS unique_references
FROM record_data
GROUP BY group_key
)
SELECT
group_key,
record_data.*,
uniques.unique_references
FROM record_data
JOIN uniques
USING (group_key)
ORDER BY
group_key;
Super-PostgreSQL is my hero...
2009/7/21 Daniel Cristian Cruz <danielcristian(at)gmail(dot)com>
> Hi list,
>
> Does someone knows why this is not a supported feature?
>
> I was happily SQL querying when I woke up from my dreams with this message:
>
> ERROR: DISTINCT is not implemented for window functions
> LINE 6: COUNT(DISTINCT handle) OVER (PARTITION BY pk_pessoas) AS ha...
>
> It could be very useful to find unique references within groups of clone
> records. Or there is another way to write this kind of query?
>
> Example (reference is a column of record_data):
>
> SELECT
> group_key,
> record_data.*,
> COUNT(DISTINCT reference) OVER (PARTITION BY group_key) AS
> unique_references
> FROM record_data
> ORDER BY
> group_key;
>
> Regards,
> --
> Daniel Cristian Cruz
> クルズ クリスチアン ダニエル
>
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル
From | Date | Subject | |
---|---|---|---|
Next Message | Carol Walter | 2009-07-21 16:35:46 | Cannot connect to postgresql |
Previous Message | Daniel Cristian Cruz | 2009-07-21 11:59:58 | COUNT(DISTINCT field) OVER (PARTITION BY another_field) |