DB表如下所示:
state | city | contact
---------------------------
NY | city1 | person1;person2;person3
NY | city2 | person4;person5;person6
NY | city3 | null
CA | city1 | person7;person8;person9
CA | city2 | person10;person11;person12
我想按状态分组并将city
变成一个数组,并在分号上拆分contact
变成一个数组:
state | city. | contact
------------------------------------------------
NY | {city1, city2, city3} | {person1,person2,person3,person4,person5,person6,null}
CA | {city1, city2} | {person7,person8,person9,person10,person11,person12}
这会将每个状态的contacts
聚合为 1 行,并应处理 null 值,但它不会在分号上拆分:
select
t.state,
coalesce(nullif(array(Select x from unnest(array_agg(t.contact order by t.city)) x where x is not null, '{}', '{}') as "contacts_agg"
-- t.city, ^^ same logic as above
from table as t
group by
t.state
如何在聚合每个州的所有city
行和contact
行时将查询修改为按state
分组?
您可以取消嵌套联系人并重新聚合:
select t.state, array_agg(distinct city) as city, array_agg(distinct contact) as contacts
from t cross join
regexp_split_to_table(contacts, ';') c(contact)
group by t.state;
这是一个数据库<>小提琴。