如何汇总和分组为两列?

   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