如何汇总和分组为两列?

   Name          age   gender
competition1      3      0
competition1      3      1
competition1      2      0
competition1      2      1
competition2      3      0
competition2      3      1
competition3      1      0

我想查询在下表中转换的该表。

     Name          Array of ages   Array of gender
'competition1',      {'3','2'}       {'0','1'}
'competition2',      {'3'}       {'0','1'}
'competition3',      {'1'}           {'0'}
评论
wquod
wquod

You can aggregate by name, and use array_agg(distinct ...) to generate arrays without duplicates. Note that you can also order the values in the arrays as needed:

select 
    name, 
    array_agg(distinct age order by age desc) ages, 
    array_agg(distinct gender order by gender) genders
from mytable
group by name
点赞
评论
bdicta
bdicta

您可以使用array_agg

 SELECT name, array_agg(age), array_agg(gender)
 FROM data_table
 GROUP BY name
点赞
评论