Рассчитать стоимости возможных маршрутов

На информационной панели представлено несколько прямых маршрутов. У каждого своя стоимость. Есть маршруты, по которым доехать на автобусе с пересадками выгоднее, чем прямой рейс. Задача для программиста:  написать SQL-запрос, который выводит все возможные маршруты начиная с города указанного, как место отправления. Для каждого маршрута необходимо подсчитать стоимость. Если есть несколько маршрутов до одного пункта назначения, вывести самый дешёвый из них. Обратные маршруты не выводить. 

Автобусные маршруты представлены в виде таблицы:

ID	ORIGIN	DESTINATION	COST
1	MSO	SPB	500
2	MSO	KRD	200
3	MSO	SRG	400
4	KRD	SRG	100
5	KRD	RVD	200
6	RVD	VVK	1000
7	VVK	OCN	50

Результат работы запроса должен выглядеть аналогично этому:

Один из возможных SQL-запросов для решения данной задачи:

with function getpathsum(sumpath varchar2) return number is v_res number;

  begin

      with liststr as

       (select regexp_substr(sumpath, '[^/]+', 1, level) c1

          from dual

        connect by level <= regexp_count(sumpath, '/'))

      select sum(cost)

        into v_res

        from liststr

            ,busroutes

       where origin || '-' || destination = c1;

      return v_res;

  end;

  busroutes_cost as(

      select cbr

            ,path

            ,cost

            ,getpathsum(path) as routecost

       from (select connect_by_root(f.origin) || '-' || f.destination as cbr

                    ,sys_connect_by_path(f.origin || '-' || f.destination, '/') as path

                    ,f.cost

                from busroutes f

              connect by prior f.destination = f.origin)),

  busroutes_cost_sort as (

      select cbr

             ,path

             ,routecost

             ,row_number() over(partition by cbr order by routecost) as rn

         from busroutes_cost)

select cbr

      ,path

      ,routecost

  from busroutes_cost_sort

where rn = 1

Разбор запроса.

Подзапрос возвращает все возможные маршруты:

select connect_by_root(f.origin) || '-' || f.destination as cbr

      ,sys_connect_by_path(f.origin || '-' || f.destination, '/') as path

      ,f.cost

  from busroutes f

  connect by prior f.destination = f.origin

Выражение  connect_by_root(f.origin) || '-' || f.destination as cbr  представлет начало и окончание маршрута.

Выражение  sys_connect_by_path(f.origin || '-' || f.destination, '/') as path  представлет все прямые рейсы, входящие в маршрут .

Встроенная в запрос функция рассчитывает стоимость каждого маршрута. Функция принимает значение в виде /MSO-KRD/KRD-RVD, ищет стоимость каждого прямого маршрута и суммирует их. 

with function getpathsum(sumpath varchar2) return number is v_res number;

  begin

      with liststr as

       (select regexp_substr(sumpath, '[^/]+', 1, level) c1

          from dual

        connect by level <= regexp_count(sumpath, '/'))

      select sum(cost)

        into v_res

        from liststr

            ,busroutes

       where origin || '-' || destination = c1;

      return v_res;

  end;

Функция проставляет порядковый номер в пределах уникального маршрута, предварительно отсортировав их по стоимости: 1 - самому дешёвому маршруту. Далее, выбираются маршруты, у которых rn = 1 

,row_number() over(partition by cbr order by routecost) as rn