PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to aggregate only parts of members of each group in one query statement in PG?

I have a table people(gid int, name varchar) with data:

gid    name                 
1      Bill
2      Will
2      Musk
2      Jack
1      Martin
1      Jorge

now I want to get a result set like below:

  gid   representative(varchar)
   1     Bill, Martin
   2     Will, Musk

I mean to list out each group with two of people’s names of that group as representative. Here the order doesn’t matter, the count matters. How to do it in one query statement, i.e. in PG?

After multiple trying for a long while, I can achieve the goal by this statement:

with indexed as(
    select gid,name,row_number() over (partition by gid) as index
    from people
),filtered as(
    select gid, name 
    from indexed where index<3
)
select gid,string_agg(name,',')
from filtered
group by gid;

However, the actual table has huge number rows and more complicated schema. It consumes long time to just create the first CTE table "indexed" because of traversing the whole table.
Is there better resolution to direct database engine to select out just parts of rows for each group at first, without full traversing?

Tags:

2

Answers


  1. 0

    Do you have a Table with id as primary key?
    Then you could give a double subselect a try:

    I don’t have a clue what the optimizer will make out of it

    select id, (select name from (select array_agg(foo.name) name 
    from (select name from people where people.id = a.id LIMIT 2) as  foo) as bar)
from xxx_table a
    Login or Signup to reply.
  2. 0

    One option is to use Row_Number() analytic function and STRING_AGG()

    --    S a m p l e    D a t a :
Create Table People As
( Select 1 as gid, 'Bill' as name Union All
  Select 2,        'Will' Union All
  Select 2,        'Musk' Union All
  Select 2,        'Jack' Union All
  Select 1,        'Martin' Union All
  Select 1,        'Jorge' )

    --    S Q L : 
SELECT gid, STRING_AGG(name, ', ') as names
FROM   ( Select gid, name, Row_Number() Over(Partition By gid Order By gid) as rn
         From   People )
WHERE  rn <= 2
GROUP BY gid

    /*
gid names
--- ---------------
  1 Bill, Martin
  2 Will, Musk      */

    See the fiddle here.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search