PostgreSQL:聚合列表中名字的组合

Question

我有一个DB结构，看起来像这样:

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)

使用一些示例数据:

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

我想创建一个select语句，返回所有事件，并为所有与会者的每个昵称组合设置一行，如下所示:

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

我最初的直觉是将所有表连接在一起，按事件分组，然后使用string_agg，但这会将所有人的昵称放在列表中(当然，因为它在整个连接上聚集)。我的第二次尝试是从子查询中选择与会者姓名，但是子查询不能返回多行。我也尝试过使用数组进行聚合，但不能聚合不同维数的数组。最后，我尝试使用这里描述的一些递归魔法，但发现它很难适应我的问题，最终无法使它工作。

Answer 1

下面是一个递归查询。我创建了一个person ID数组，在递归的每个阶段，我将下一个ID与person_name表连接起来。

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;

Answer 2

我想我用"递归魔法"搞定了。之前有关。问题是我的真实数据有点复杂，每个参与者都有一个"位置"。在列表中，这并不总是适用于r.id < t.id约束。下面是一个处理问题中的样例数据的查询:

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
);

这确实为与会者的每个可能的订单返回一个额外的行，但我对此很满意(就像我说的，我的真实数据有一个"位置";字段来约束它)。如果你只需要一个排序，那么这个排序必须在某个地方的数据中指定，因此，例如添加回r.id < t.id位就可以了。