我有以下查询(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;
我收到以下错误:
ERROR: syntax error at or near "s" LINE 38: ...sg_count) from tmp_table_1 where super_user_id in s.super_us... ^
似乎我无法s.super_users
从子查询中访问。
有谁知道我该怎么做?
这是一个完整的例子: https ://www.db-fiddle.com/f/geB9V6TdHEkF2mSqpd2b4g/1
也许我没有正确地接近这个?
问题
如何将附加列(sum_msg_count
、sum_user_count
和)添加max_last_date
到pubs
结果集中?
期望的结果
|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} |
您应该使用
ANY
, 而不是在数组IN
中搜索