Структура таблиц SQL и выборка данных

Question

Есть такая структура таблиц:

path

destination

destination_edge

destination - это как вершины графа (ориентированного), destination_edge - грани

path_destination_edge

здесь хранятся последовательности маршрутов path, при этом:

position 0 dest_from это точка отправления
position max dest_to это точка прибытия
при этом может быть всего одна запись, то есть position будет 0

Есть такой запрос

SELECT
    path.id as "Path ID",
    sum(de.distance) as "Full distance (km)",
    string_agg(concat(dFrom.name, ' - ', dTo.name), '; ') as "Path"

from path

join path_destination_edge pde on path.id = pde.path_id
join destination_edge de on de.id = pde.destination_edge_id
join destination dFrom on dFrom.id = de.destination_from_id
join destination dTo on dTo.id = de.destination_to_id

group by path.id

Его результат вот:

Хочу заменить path на destinationFrom и destinationTo, то есть из:

Path: [Vladimir - Moscow (Kievsky); Moscow (Kievsky) - Saint-Petersburg (Moskowsky)]

Нужно получить:

Destinaton From: [Vladimir]
Destination To: [Saint-Petersburg (Moskowsky)]

Если псевдокодом, то это что-то вроде
Destination From: "get destination.name from destination_edge.from where path_destination_edge.position = 0"
Destination To: "get destination.name from destination_edge.to where path_destination_edge.position = MAX(position)"

Итак, вопрос в том, можно ли сделать такое, и правильно ли сделана структура в целом, а то как-то не клеится.

Answer 1

^{(Прежде всего поддержу коллегу @Akina: набирать все ваши данные
руками было непросто.)}

Может быть, можно как-то без подзапросов, да и оптимизировать местами наверняка можно, но скорее всего копать вам в сторону чего-то такого:

WITH paths AS (
  SELECT path.id
       , FIRST_VALUE(dFrom.name) OVER path_window AS from_name
       , LAST_VALUE(dTo.name) OVER path_window AS to_name
       , de.distance
       , CONCAT(dFrom.name, ' - ', dTo.name) AS path
    FROM path
         INNER JOIN path_destination_edge AS pde
                 ON path.id = pde.path_id
         INNER JOIN destination_edge AS de
                 ON de.id = pde.destination_edge_id
         INNER JOIN destination AS dFrom
                 ON dFrom.id = de.destination_from_id
         INNER JOIN destination AS dTo
                 ON dTo.id = de.destination_to_id
  WINDOW path_window AS (
           PARTITION BY path.id
               ORDER BY pde.position
                ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
         )
   ORDER BY path.id ASC
       , pde.position ASC
)
SELECT id AS "Path ID"
     , from_name AS "From"
     , to_name AS "To"
     , SUM(distance) AS "Full distance (km)"
     , STRING_AGG(path, '; ') as "Path"
  FROM paths
 GROUP BY id
     , from_name
     , to_name
 ORDER BY id
;

DB-Fiddle.

Про оконные функции.

БЛОГ НА HUSL

Структура таблиц SQL и выборка данных

Ответы (1 шт):