PostgreSQL:基于另一列和列表创建一个新列

Question

表1

动物栖息地狮子动物园、森林、家庭、海洋鱼类洋、动物园、水族馆狗家，动物园，野生鸟海，森林，家

Answer 1

首先对数据库进行规范化，然后对结果使用string_agg()：

select Animal,string_agg(Habitat,',') as Habitat
from (
select animal, trim(unnest( string_to_array(Habitat,','))) as Habitat 
from table1
) x
where Habitat in (select trim(unnest( string_to_array('Zoo,Home',','))))
group by Animal;

请参阅：DBFIDDLE

Answer 2

假设table1定义如下：

create table table1 as 
select animal, habitat from (values 
('Lion',Array ['Zoo','Forest','Home','Sea']),
('Fish',Array['Sea', 'Zoo', 'Aquarium']),
('Dog',Array['Home', 'Zoo', 'Wild']),
('Bird',Array['Sea', 'Forest', 'Home'])) as a(animal, habitat)

阵列的交叉可以像一样完成

select animal,array_agg(one_habitat) from (
select animal, unnest(habitat) one_habitat from table1
) a 
where one_habitat = ANY(Array['Zoo','Home'])
group  by animal

家，动物园

动物	array_agg
狮子
鱼类	Zoo
狗	Home，Zoo
Bird	主页