0

I have the following query (PostgreSQL 9.5.19):

with userlist as (
  select a.ips, 
         array_agg(t.super_user_id) as super_users
  from tmp_table_1 t
    left join unnest(t.ips) as a(ips) on true
  group by a.ips
), shared_users as (
  select u.ips,
         array(select distinct ul.uid
               from userlist u2, unnest(u2.super_users) as ul(uid)
               where u.super_users && u2.super_users
               order by ul.uid) as super_users
  from userlist u
)
select s.super_users, array_agg(distinct s.ips) as ips, 
    (select sum(msg_count) from tmp_table_1 where super_user_id in s.super_users) as sum_msg_count
from shared_users s
group by s.super_users;

I get the following error:

ERROR:  syntax error at or near "s"
LINE 38: ...sg_count) from tmp_table_1 where super_user_id in s.super_us...
                                                              ^

It seems like I cannot access s.super_users from within the subquery.

Anyone know how I can do this?

Here is a full example: https://www.db-fiddle.com/f/geB9V6TdHEkF2mSqpd2b4g/1

Maybe I am not approaching this correctly?

Question

How can I add the additional columns (sum_msg_count, sum_user_count, max_last_date and pubs) to the result set?

Desired result

|super_users                      |  ips                                                        |sum_msg_count| sum_user_count  |   max_last_date                | pubs                        |
|---------------------------------|-------------------------------------------------------------|-------------|-----------------|--------------------------------|-----------------------------|
|{522460771,522460772,522460773}  |  {94.134.88.115,94.134.88.124,94.134.88.125,94.134.88.136}  | 30          |         9       |  "2018-06-30 21:29:48.664000"  |  {ad2,googlecooler,ads,ad1} |
|{522460774,522460775}            |  {127.0.0.1,5.5.5.5}                                        | 20          |         6       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ad3,ad1}     |
|{522460776}                      |  {8.8.8.8}                                                  | 10          |         3       |  "2018-06-30 21:29:48.664000"  |  {googlecooler,ads}         |
  • 1
    where super_user_id in s.super_users IN requires a list like where super_user_id in (select super_user_id from some_table) – McNets Oct 17 at 20:12
0

You should use ANY, not IN to search in an array

...where super_user_id = ANY (s.super_users)
  • thank you that works. Now I just need to work out how to add the pubs column. – user2569831 Oct 18 at 6:17
0
with userlist as (
  select a.ips, array_agg(t.super_user_id) as super_users
  from tmp_table_1 t
    left join unnest(t.ips) as a(ips) on true
  group by a.ips
),
shared_users as (
  select u.ips,
         array(select distinct ul.uid
               from userlist u2, unnest(u2.super_users) as ul(uid)
               where u.super_users && u2.super_users
               order by ul.uid) as super_users
  from userlist u
) --select * from shared_users
select s.super_users, array_agg(distinct s.ips) as ips, 
(select sum(msg_count) from tmp_table_1 where super_user_id = any(s.super_users)) as sum_msg_count,
(select sum(user_count) from tmp_table_1 where super_user_id = any(s.super_users)) as sum_user_count,
(select max(last_date) from tmp_table_1 where super_user_id = any(s.super_users)) as max_last_date,
(select array_agg(pubs) from (select a.pubs, array_agg(t.super_user_id) as super_users
    from tmp_table_1 t 
  left join unnest(t.pubs) as a(pubs) on true
  and t.super_user_id = any(s.super_users)
  where a.pubs is not null
  group by a.pubs) as pubsAlias) as pubs  
from shared_users s
group by s.super_users;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.