PostgreSQL: Aggregating combinations of names in list

user_408060 0
Viewed 51

I have a DB structure that looks something like this:

               Table "public.person"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 

                 Table "public.person_name"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 person | integer           |           | not null | 
 name   | character varying |           |          | 
Foreign-key constraints:
    "person_name_person_fkey" FOREIGN KEY (person) REFERENCES person(id)

                    Table "public.event"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 id     | integer           |           | not null | 
 name   | character varying |           |          | 

              Table "public.attendee"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 event  | integer |           |          | 
 person | integer |           |          | 
Foreign-key constraints:
    "attendee_event_fkey" FOREIGN KEY (event) REFERENCES public.event(id)
    "attendee_person_fkey" FOREIGN KEY (person) REFERENCES person(id)

With some sample data:

person:
 id 
----
  0
  1
  2
  3

person_name:
 person |   name    
--------+-----------
      0 | Alex
      0 | Alexander
      1 | Barbara
      1 | Barb
      2 | Cecilia
      3 | Dave
      3 | David

event:
 id |    name    
----+------------
  0 | Wedding
  1 | Party
  2 | Funeral

attendee:
 event | person 
-------+--------
     0 |      0
     0 |      1
     0 |      2
     1 |      1
     1 |      2
     2 |      2
     2 |      3

I'd like to make a select statement that returns all events, with a row for every combination of nicknames that all attendees have, like this:

 event_id | event_name | attendee_list
----------+------------+---------------
        0 | Wedding    | Alex, Barbara, Cecilia
        0 | Wedding    | Alexander, Barbara, Cecilia
        0 | Wedding    | Alex, Barb, Cecilia
        0 | Wedding    | Alexander, Barb, Cecilia
        1 | Party      | Barbara, Cecilia
        1 | Party      | Barb, Cecilia
        2 | Funeral    | Cecilia, Dave
        2 | Funeral    | Cecilia, David

My initial intuition was to join all of the tables together, group by event, and then use string_agg, but that puts all of everybody's nicknames in the list (of course, since it's aggregating over the whole join). My second attempt was to select the attendee names from a subquery, but subqueries can't return multiple rows. I also tried aggregating using arrays instead, as described here, but you can't aggregate arrays of differing dimensionality. Finally, I tried using some recursive magic as described here, but found it difficult to adapt to my problem, and ultimately couldn't get it to work.

aggregate-functions join postgresql sql database
2 Answers

Here's a recursive query that does it. I made a array of the person IDs, and in each stage of the recursion I joined the next ID with the person_name table.

WITH RECURSIVE recur AS (
    SELECT 
        event as event_id, 
        event.name as event_name, 
        array_agg(person) as person_id_list, 
        ARRAY[]::text[] as person_name_list,
        1 as index
    FROM attendee, event 
    WHERE attendee.event = event.id 
    GROUP BY event, event.name
UNION ALL
    SELECT 
        event_id, 
        event_name, 
        person_id_list,
        person_name_list || person_name.name,
        index + 1
    FROM recur
    JOIN person_name on (person_name.person = recur.person_id_list[recur.index])
    WHERE cardinality(recur.person_id_list) >= recur.index
)
SELECT event_id, event_name, array_to_string(person_name_list, ', ') as attendee_list
FROM recur
WHERE cardinality(recur.person_id_list) < recur.index
ORDER BY event_id;
user_68978070

I think I got it figured out with the "recursive magic" linked before. The issue was that my real data was a little more complicated and each attendee had a "position" in the list, which didn't always work with the r.id < t.id constraint. Here's a query that works with the sample data in the question:

with recursive recur as (
    select
        array[person_name.person] as persons,
        array[name] as names,
        attendee.event
    from person_name
    join attendee
    on person_name.person=attendee.person
union all
    select
        persons || t.person,
        names || t.name,
        attendee.event
    from person_name t
    join recur r
    on t.person != all(r.persons)
    join attendee
    on t.person=attendee.person
    and attendee.event=r.event
)
select event, names
from recur
where cardinality(names)=(
    select count(*)
    from attendee
    where attendee.event=recur.event
);

This does return an additional row for every possible order of attendees as well, but I'm fine with that (and like I said, my real data has a "position" field that would constrain that). If you needed only one ordering, the ordering has to be specified in the data somewhere, so for example adding back that r.id < t.id bit would work.

user_4080600
Related