使用[Postgres]分组时，请选择常用值

Question

我有三个主表会议、人物、爱好和两个关系表。

Table meetings
+---------------+
| id | subject  |
+----+----------+
|  1 | Kickoff  |
|  2 | Relaunch |
|  3 | Party    |
+----+----------+

Table persons
+------------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Anna  |
|  3 | Linda |
+----+-------+

Table hobbies
+---------------+
| id | name     |
+----+----------+
|  1 | Soccer   |
|  2 | Tennis   |
|  3 | Swimming |
+----+----------+

Relation Table meeting_person
+-----------------+-----------+
| id | meeting_id | person_id |
+----+------------+-----------+
|  1 |          1 |         1 |
|  2 |          1 |         2 |
|  3 |          1 |         3 |
|  4 |          2 |         1 |
|  5 |          2 |         2 |
|  6 |          3 |         1 |
+----+------------+-----------+

Relation Table person_hobby
+----------------+----------+
| id | person_id | hobby_id |
+----+-----------+----------+
|  1 |         1 |        1 |
|  2 |         1 |        2 |
|  3 |         1 |        3 |
|  4 |         2 |        1 |
|  5 |         2 |        2 |
|  6 |         3 |        1 |
+----+-----------+----------+

现在我想找出每个参加会议的人的共同爱好。因此，期望的结果是：

+------------+-----------------+------------------------+
| meeting_id | persons         | common_hobbies         |
|            | (Aggregated)    | (Aggregated)           |
+------------+-----------------+------------------------+
|          1 | John,Anna,Linda | Soccer                 |
|          2 | John,Anna       | Soccer,Tennis          |
|          3 | John            | Soccer,Tennis,Swimming |
+------------+-----------------+------------------------+

我目前正在进行的工作是：

select
m.id as "meeting_id", 
(
select string_agg(distinct p.name, ',')
from meeting_person mp
inner join persons p on mp.person_id = p.id
where m.id = mp.meeting_id
) as "persons",
string_agg(distinct h2.name , ',') as "common_hobbies"
from meetings m
inner join meeting_person mp2 on m.id = mp2.meeting_id 
inner join persons p2 on mp2.person_id = p2.id
inner join person_hobby ph2 on p2.id = ph2.person_id 
inner join hobbies h2 on ph2.hobby_id = h2.id 
group by m.id

但这个查询列出的不是常见的爱好，而是至少被提及过的所有爱好。

+------------+-----------------+------------------------+
| meeting_id | persons         | common_hobbies         |
+------------+-----------------+------------------------+
|          1 | John,Anna,Linda | Soccer,Tennis,Swimming |
|          2 | John,Anna       | Soccer,Tennis,Swimming |
|          3 | John            | Soccer,Tennis,Swimming |
+------------+-----------------+------------------------+

有人给我什么提示吗，关于我如何解决这个问题？

干杯

Answer 1

这个问题可以通过实现自定义聚合函数(在这里找到(来解决：

create or replace function array_intersect(anyarray, anyarray)
returns anyarray language sql
as $$
select 
case 
when $1 is null then $2
when $2 is null then $1
else
array(
select unnest($1)
intersect
select unnest($2))
end;
$$;
create aggregate array_intersect_agg (anyarray)
(
sfunc = array_intersect,
stype = anyarray
);

因此，解决方案可以是下一个：

select 
meeting_id, 
array_agg(ph.name) persons, 
array_intersect_agg(hobby) common_hobbies
from meeting_person mp
join (
select p.id, p.name, array_agg(h.name) hobby
from person_hobby ph
join persons p on ph.person_id = p.id
join hobbies h on h.id = ph.hobby_id
group by p.id, p.name
) ph on ph.id = mp.person_id
group by meeting_id;

看看这个例子小提琴

结果：

meeting_id |    persons            | common_hobbies
-----------+-----------------------+--------------------------
1          |    {John,Anna,Linda}  | {Soccer}
3          |    {John}             | {Soccer,Tennis,Swimming}
2          |    {John,Anna}        | {Soccer,Tennis}