Postgres: count unique array entries from subquery

Question

If my subquery foo liberates the rows:-

ID, USERS 
1   {23129}
2   {23142}
3   {23300,23300}
4   {23129,23300}

How can I get a count of unique users in a query using a window function such as:-

SELECT ... FROM ( <subquery> ) FOO

I tried this:-

array_length(array_agg(array_length(array(SELECT Distinct unnest(users))),1)) over(), 1)

But get the error that the array dimensions are not the same

NOTE WELL: I cannot change the subquery to solve this problem.

I can get the IDs in an array as follows:-

string_to_array(string_agg(array_to_string(user_ids, ','), ',') over(),',')

But they are not distinct.

Thanks for the answers, problem is the subquery cannot be changed. — CitizenFish
– CitizenFish, Commented Oct 9, 2017 at 15:31
SELECT count(distinct BAR) FROM ( <subquery> ) FOO, unnest(FOO.USERS) AS BAR — Abelisto
– Abelisto, Commented Oct 9, 2017 at 19:37

Mureinik · Accepted Answer · 2017-10-09 15:19:25Z

6

You're overcomplicating things - you can unnest the array, and then query a distinct count from it:

SELECT COUNT(DISTINCT u)
FROM   (SELECT UNNEST(users) AS u
        FROM   mytable) t

answered Oct 9, 2017 at 15:19

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CitizenFish Over a year ago

Thank but the subquery cannot be changed

klin · Accepted Answer · 2017-10-10 08:09:30Z

1

You can always use a known alghoritm in a simple SQL function:

create or replace function array_unique_elements(arr anyarray)
    returns integer
    language sql immutable
as $$
    select count(distinct a)::int
    from unnest(arr) a 
$$;

Use:

select *, array_unique_elements(users)
from (
    values
    (1, '{23129}'::int[]),
    (2, '{23142}'),
    (3, '{23300,23300}'),
    (4, '{23129,23300}')
) foo (id, users)

 id |     users     | array_unique_elements 
----+---------------+-----------------------
  1 | {23129}       |                     1
  2 | {23142}       |                     1
  3 | {23300,23300} |                     1
  4 | {23129,23300} |                     2
(4 rows)

edited Oct 10, 2017 at 8:09

answered Oct 9, 2017 at 17:07

klin

123k15 gold badges240 silver badges262 bronze badges

Comments

Vao Tsun · Accepted Answer · 2017-10-09 15:26:59Z

-1

I would as well just count distinct as Mureinik suggests.

And regarding error that you get - here's tight syntax example with array_length:

t=# with a(v) as (values('{1,2}'::int[]),('{2,3}')) 
select array_length(array_agg(distinct unnest),1) from (
  select unnest(v) from a
) a;
 array_length
--------------
            3
(1 row)

Of course it WILL NOT with window aggregation - only with GROUP BY

answered Oct 9, 2017 at 15:26

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Collectives™ on Stack Overflow

Postgres: count unique array entries from subquery

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related