Топ купленных товаров

Есть таблица purchase с информацией о покупках. Поля pr_name, pr_count, pr_price, pr_date означают наименование товара, количество, цену за единицу и дата покупки товара. Другие поля не рассматриваются для упрощения. Необходимо найти топ 5 товаров за каждый день, купленных за последние 7 дней. Топ означает максимальную продажу, выраженную в прибыли:  количество * цену.

Вариант запроса, решающего задачу:

select pr_date

      ,pr_name

      ,pr_sum

      ,prank

  from (select trunc(pr_date) as pr_date

              ,pr_name

              ,sum(pr_count * pr_pice) as pr_sum

              ,row_number() over(partition by trunc(pr_date) 

                            order by sum(pr_count * pr_pice) desc) as prank

          from purchase

         where trunc(pr_date) > trunc(sysdate - 7)

         group by trunc(pr_date)

                 ,pr_name)

where prank <= 5;

Одинаковый набор оценок

 В таблице marks есть поле usr  с кодом пользователя и поле mrk с оценкой. Для упрощения, другие поля не рассматриваются. Задача:  вывести пользователей, у которых такой же уникальный набор оценок, как у пользователя u2. 

Вариант запроса, решающего задачу:

with agg as

(select distinct mrk

    from marks

   where usr = 'u2')

select m.usr

  from marks m

  left join agg a on a.mrk = m.mrk

  having count(m.mrk) = count(a.mrk)

     and m.usr <> 'u2'

group by m.usr;

 

Пример данных таблицы marks:

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

На информационной панели представлено несколько прямых маршрутов. У каждого своя стоимость. Есть маршруты, по которым доехать на автобусе с пересадками выгоднее, чем прямой рейс. Задача для программиста:  написать 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