有效地从多个表中获取不同的值

Question

我在Postgres数据库中有以下表:

表folders

| id  | name      |
|-----|-----------|
| 1   | folder A  |
| 2   | folder B  |

Tablefiles——表示文件夹(大表)中的文件

| id  | folder_id |    
|-----|-----------|
| 1   | 1         |
| 2   | 1         |
| 3   | 2         |

表metadata_one——表示与文件(大表)有关的一些信息

| id  | file_id | label |    
|-----|---------|-------|
| 1   | 1       | abc   |
| 2   | 1       | def   |
| 3   | 2       | abc   |

表metadata_two——表示与文件(大表)有关的一些其他信息

| id  | file_id | label |    
|-----|---------|-------|
| 1   | 1       | abc   |
| 2   | 1       | def   |
| 3   | 2       | abc   |

如何在文件夹级别上获得不同标签值的列表?

预期的结果

只有不同的标签值，在两个metadata表

| name         | labels    |
|--------------|-----------|
| folder A     | abc,def   |
| folder B     | abc       |

目前我是这样做的:

SELECT
folders.name,
string_agg(m1.label, ',') AS m1_labels,
string_agg(m2.label, ',') AS m2_labels
FROM
folders
JOIN files ON
files.folder_id = folders.id
JOIN metadata_one m1 ON
m1.file_id = files.id
JOIN metadata_two m2 ON
m2.file_id = files.id
GROUP BY
folder.name

但这给了我以下内容:

| name         | m1_labels | m2_labels |    
|--------------|-----------|-----------|
| folder A     | abc,def   | abc,def   |
| folder B     | abc       | abc       |

我正在寻找一个优化的解决方案，因为files和metadata表可以非常大。

Answer 1

您可以在临时表中生成metadata_one和metadata_two的UNION，然后像这样进行字符串聚合

WITH metadata_by_folder AS (
SELECT
folders.name,
m1.label AS label
FROM
folders
JOIN files ON
files.folder_id = folders.id
JOIN metadata_one m1 ON
m1.file_id = files.id
UNION SELECT
folders.name,
m2.label AS label
FROM
folders
JOIN files ON
files.folder_id = folders.id
JOIN metadata_two m2 ON
m2.file_id = files.id
)
SELECT
metadata_by_folder.name,
string_agg(metadata_by_folder.label, ',') AS labels
FROM
metadata_by_folder
GROUP BY
metadata_by_folder.name;