Как разделить/форматировать агрегированные данные string_agg(), используя поля *_id, на которые ссылаются данные?

Этот вопрос является своего рода продолжением моего предыдущего вопроса, который можно найти здесь.

Этот SQLFiddle использует структуру базы данных и запрос, которые я описываю ниже.

База данных выглядит так:

CREATE TABLE artistnames (
    artistname_id       SERIAL  PRIMARY KEY,
    artistname          TEXT    UNIQUE NOT NULL
);

CREATE TABLE artistalias (
    artistalias_id      SERIAL  PRIMARY KEY,
    artistname_id       SERIAL  REFERENCES artistnames (artistname_id),
    artistalias         TEXT    UNIQUE NOT NULL
);

CREATE TABLE songs (
    song_id             SERIAL  PRIMARY KEY,
    song                TEXT    NOT NULL,
    artistalias_id      SERIAL  REFERENCES artistalias (artistalias_id)
);
  • один исполнитель (таблица с именами исполнителей) может иметь ноль, один или несколько псевдонимов
  • один псевдоним (таблица artistalias) принадлежит ровно одному исполнителю
  • одна песня (застольные песни) имеет одного или нескольких исполнителей (и относится к псевдониму исполнителя, с которым он исполнял песню)

Пример. Художник Фрэнсис Вейгар также использует псевдонимы Фрэнсис Фэт и Фрэнсис Файтер. Одна песня Song 1 была выпущена под именем исполнителя Francis Veigar, для другой песни Song 2 он использовал псевдоним Francis Fat. и третью песню Song 3, которую он спел под псевдонимом Francis Fighter вместе с другим артистом по имени Peeka Boo.

Использование запроса

SELECT
  string_agg(distinct(artistname), ' & ') AS artist_primary_name,
  string_agg(distinct(a1.artistalias), ' & ') AS performed_song_with_alias, 
  string_agg(a2.artistalias, ' & ') AS other_pseudonymes,
  song
FROM
  artistalias a1
  left JOIN artistalias a2 ON a2.artistname_id = a1.artistname_id
  left JOIN songs s ON s.artistalias_id = a1.artistalias_id
  left JOIN artistnames ON artistnames.artistname_id = a1.artistname_id
GROUP BY song;

отображает столбец other_pseudonymes как

+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| artist_primary_name        | performed_song_with_alias   | other_pseudonymes                                                  | song   |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Francis Veigar             | Francis Veigar              | Francis Veigar & Francis Fat & Francis Fighter                     | Song 1 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Francis Veigar             | Francis Fat                 | Francis Veigar & Francis Fat & Francis Fighter                     | Song 2 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Francis Veigar & Peeka Boo | Francis Fighter & Peeka Boo | Francis Veigar & Francis Fat & Francis Fighter & Peeka Boo & Peeka | Song 3 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Peeka Boo                  | Peeka                       | Peeka Boo & Peeka                                                  | Song 4 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+

Я хотел бы, чтобы это выглядело примерно так

+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| artist_primary_name        | performed_song_with_alias   | other_pseudonymes                                                  | song   |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Francis Veigar             | Francis Veigar              | Francis Veigar & Francis Fat & Francis Fighter                     | Song 1 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Francis Veigar             | Francis Fat                 | Francis Veigar & Francis Fat & Francis Fighter                     | Song 2 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Francis Veigar & Peeka Boo | Francis Fighter & Peeka Boo | Francis Veigar & Francis Fat & Francis Fighter / Peeka Boo & Peeka | Song 3 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+
| Peeka Boo                  | Peeka                       | Peeka Boo                                                          | Song 4 |
+----------------------------+-----------------------------+--------------------------------------------------------------------+--------+

разделение псевдонимов / псевдонимов, которые используют два разных исполнителя, с помощью «/». Что нужно изменить в запросе, чтобы добиться этого?


postgresql sql relational-database string-aggregation
person phew    schedule 06.09.2015    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Используйте подзапрос, затем SELECT агрегированные элементы и снова используйте string_agg:

SELECT
  string_agg(distinct(artistname), ' & ') AS artist_primary_name,
  string_agg(distinct(a1.artistalias), ' & ') AS performed_song_with_alias, 
  string_agg(distinct(col),' / ') AS other_pseudonymes,
  song
FROM
  artistalias a1
  left JOIN artistalias a2 ON a2.artistname_id = a1.artistname_id
  left JOIN songs s ON s.artistalias_id = a1.artistalias_id
  left JOIN artistnames ON artistnames.artistname_id = a1.artistname_id
  left join 
(SELECT 
    string_agg(a2.artistalias, ' & ') as col,
    artistname_id 
 FROM artistalias a2
 GROUP BY artistname_id)
AS aggregated_aliases ON aggregated_aliases.artistname_id = artistnames.artistname_id
GROUP BY song;
person rofls    schedule 06.09.2015