Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: 1 2      [все]
 Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Всем привет.
Имеется следующее задание:
Для каждого сотрудника выбрать всех его начальников по иерархии.
Вывести поля: код сотрудника, имя сотрудника (фамилия + имя через пробел),
код начальника, имя начальника (фамилия + имя через пробел), кол-во промежуточных
начальников между сотрудником и начальником из данной строки выборки. Если у какого-то
сотрудника есть несколько начальников, то для данного сотрудника в выборке должно быть
несколько строк с разными начальниками. Упорядочить по коду сотрудника, затем по уровню
начальника (первый – непосредственный начальник, последний – руководитель организации).

Знаю как его выполнить с помощью рекурсивного запроса:
select  e.employee_id,
        e.last_name || ' ' || e.first_name as employee_name,
        CONNECT_BY_ROOT e.employee_id as manager_id,
        CONNECT_BY_ROOT (e.last_name || ' ' || e.first_name) as manager_name,
        level - 2 as pathlen,
        sys_connect_by_path(e.last_name || ' ' || e.first_name, '/') as path,
        level
  from  employees e
  where level > 1
  connect by e.manager_id = prior(e.employee_id)
  order by e.employee_id, 
           level
;


но не могу понять как это задание можно выполнить без рекурсивного запроса.
Подскажите пожалуйста в какую сторону копать.
2 фев 17, 21:04    [20175211]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Person2713
Знаю как его выполнить с помощью рекурсивного запроса:
но не могу понять как это задание можно выполнить без рекурсивного запроса.


Твой запрос нe рекурсивный а иерархический. Ну а без иерархического/рекурсивного запроса если нaриcовать пaру функций но внутри их все равно придется ползать от сотруника к начальнику в цикле что по-сути та-же иерархия/рекурсия.

SY.
2 фев 17, 21:38    [20175274]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4357
Person2713,

Если для получения родительских записей нельзя использовать connect by и recursive subquery factoring,
то задачу можно решить итеративной моделью (для общего случая) или pattern matching (при условии что айдишники увеличиваются с увеличением уровня).

SQL> with t(id, id_parent) as
  2  (
  3  select 1, 0 from dual
  4  union all select 11, 10 from dual
  5  union all select 2, 1 from dual
  6  union all select 3, 1 from dual
  7  union all select 4, 2 from dual
  8  )
  9  select *
 10  from t
 11  match_recognize
 12  (
 13    order by id desc
 14    measures classifier() as cls
 15    all rows per match
 16    pattern (start_ (parent_|{-dummy-})*)
 17    define
 18      start_ as start_.id = 4,
 19      parent_ as last(start_.id_parent) = parent_.id or prev(parent_.id_parent,1) = parent_.id
 20  ) mr;

        ID CLS                             ID_PARENT
---------- ------------------------------ ----------
         4 START_                                  2
         2 PARENT_                                 1
         1 PARENT_                                 0
2 фев 17, 22:42    [20175454]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
dbms_photoshop
задачу можно решить итеративной моделью


А итеративнaя модель это не рекурсия?
Через match_recognize врядли получится - не ползает он через ту-же строку дважды.

SY.
2 фев 17, 23:22    [20175558]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
SY,
помой-му иерархический и рекурсивный запрос одно и тоже. Я вроде читал, что Oracle вместо рекурсивный запрос использует иерархический запрос. Поправьте меня если ошибаюсь.
3 фев 17, 08:00    [20175847]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
dbms_photoshop,

То что вы написали не является ли "рекурсивным запросом с подзапросом with" ([url=]http://citforum.ru/database/oracle/recursive/[/url])
3 фев 17, 08:05    [20175856]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Добрый Э - Эх
Guest
Person2713
То что вы написали не является ли "рекурсивным запросом с подзапросом with"
ну покажи нам, где там якорная часть, где рекурсивная...
3 фев 17, 08:08    [20175863]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4357
SY
dbms_photoshop
задачу можно решить итеративной моделью


А итеративнaя модель это не рекурсия?
Через match_recognize врядли получится - не ползает он через ту-же строку дважды.

SY.
Итеративная модель - это цикл while (если речь про обход дерева).
Кстати, любую рекурсию можно реализовать через не рекурсию.
Я даже устраивал троллинг на эту тему в форуме работа 13199084, но не взлетело - там любители поговорить, а не сравнивать производительность.
Ползать в две стороны не выйдет, но я написал в скобочках ограничения для решения выше.

PS. Мы своими разговорчиками только запутаем ТС. У него и так полная каша в голове.
3 фев 17, 12:14    [20176857]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
объясните пожалуйста чем отличаются иерархические запросы от рекурсивных в oracle
5 фев 17, 16:41    [20181662]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4357
Person2713
объясните пожалуйста чем отличаются иерархические запросы от рекурсивных в oracle
Открываешь синтаксис SELECT и на странице Ctr + F по словам "recursive" и "hierarchical".
5 фев 17, 16:46    [20181665]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Спасибо dbms_photoshop,
5 фев 17, 18:20    [20181776]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Переписал запрос через рекурсию, правда есть одно но, не могу понять как выводить для каждого сотрудника в отдельной строчке руководителей. Например, есть иерархия /Петров/Иванов/Козлов/Петряев. В итоге для Петряева должны получить результат:
Сотрудник Руководитель
Петряев Козлов
Петряев Иванов
Петряев Петров

Подскажите пожалуйста какие есть пути решения.
5 фев 17, 23:16    [20182344]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Person2713
Переписал запрос через рекурсию, правда есть одно но, не могу понять как выводить для каждого сотрудника в отдельной строчке руководителей. Например, есть иерархия /Петров/Иванов/Козлов/Петряев. В итоге для Петряева должны получить результат:
Сотрудник Руководитель
Петряев Козлов
Петряев Иванов
Петряев Петров

Подскажите пожалуйста какие есть пути решения.

А в целом должно быть так:
Сотрудник Руководитель
Иванов Петров
Козлов Иванов
Козлов Петров
Петряев Козлов
Петряев Иванов
Петряев Петров
5 фев 17, 23:18    [20182348]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Vint
Member

Откуда: Таган-москва
Сообщений: 4242
Person2713,
prior

dbms_photoshop,
мне кажется скоро можно будет давать ссылки на твою книгу вместо документации... по крайней мере не будет вопросов про русский язык))
6 фев 17, 09:56    [20182919]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Vint,
priop используется в иерархическом запросе. А у меня сейчас рекурсивный
6 фев 17, 20:29    [20186396]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Person2713
А в целом должно быть так


select  connect_by_root ename employee_name,
        case
          when connect_by_root ename != ename then ename
        end manager_name
  from  emp
  where connect_by_root ename != ename
     or mgr is null
  connect by empno = prior mgr
/
EMPLOYEE_N MANAGER_NA
---------- ----------
SMITH      FORD
SMITH      JONES
SMITH      KING
ALLEN      BLAKE
ALLEN      KING
WARD       BLAKE
WARD       KING
JONES      KING
MARTIN     BLAKE
MARTIN     KING
BLAKE      KING
CLARK      KING
SCOTT      JONES
SCOTT      KING
KING
TURNER     BLAKE
TURNER     KING
ADAMS      SCOTT
ADAMS      JONES
ADAMS      KING
JAMES      BLAKE
JAMES      KING
FORD       JONES
FORD       KING
MILLER     CLARK
MILLER     KING

26 rows selected.

SQL> 


Или с индентацией:

select  connect_by_root ename employee_name,
        case
          when connect_by_root ename != ename then lpad(' ',(level - 2) * 2) || ename
        end manager_name
  from  emp
  where connect_by_root ename != ename
     or mgr is null
  connect by empno = prior mgr
/
EMPLOYEE_NAME   MANAGER_NAME
--------------- ---------------
SMITH           FORD
SMITH             JONES
SMITH               KING
ALLEN           BLAKE
ALLEN             KING
WARD            BLAKE
WARD              KING
JONES           KING
MARTIN          BLAKE
MARTIN            KING
BLAKE           KING
CLARK           KING
SCOTT           JONES
SCOTT             KING
KING
TURNER          BLAKE
TURNER            KING
ADAMS           SCOTT
ADAMS             JONES
ADAMS               KING
JAMES           BLAKE
JAMES             KING
FORD            JONES
FORD              KING
MILLER          CLARK
MILLER            KING

26 rows selected.

SQL> 


SY.
6 фев 17, 21:04    [20186501]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
SY,

Спасибо за примеры, но мне надо сделать иерархический запрос
select  e.employee_id,
        e.last_name || ' ' || e.first_name as employee_name,
        CONNECT_BY_ROOT e.employee_id as manager_id,
        CONNECT_BY_ROOT (e.last_name || ' ' || e.first_name) as manager_name,
        level - 2 as pathlen,
        sys_connect_by_path(e.last_name || ' ' || e.first_name, '/') as path,
        level
  from  employees e
  where level > 1
  connect by e.manager_id = prior(e.employee_id)
  order by e.employee_id,
           manager_id desc
;


c помощью рекурсии. Сделал вот часть:
WITH
  reports_to_101 (emp_id, emp_name, mgr_id, mgr_name, ppath, lvl, pathlen) AS
  (
     SELECT e1.employee_id, 
            e1.last_name || ' ' || e1.first_name as emp_name, 
            e1.manager_id,
            null mgr_name,
            '/' || e1.last_name || ' ' || e1.first_name ppath,
            1 lvl,
            0 pathlen
     FROM employees e1
     WHERE e1.manager_id is NULL
   UNION ALL
     SELECT e.employee_id,
            e.last_name || ' ' || e.first_name as emp_name,
            e.manager_id,
            r.emp_name as mgr_name,
            ppath || '/' || e.last_name || ' ' || e.first_name,
            lvl + 1,
            r.lvl-1
     FROM reports_to_101 r, employees e
     WHERE r.emp_id = e.manager_id
  )
SELECT  emp_id, 
        emp_name, 
        mgr_id,
        mgr_name,
        ppath, 
        lvl,
        pathlen
FROM reports_to_101
where lvl > 0 
ORDER BY emp_id,
         mgr_id 
;

и вот не могу понять сотрудника выводить всех его начальников в отдельных строках
6 фев 17, 21:17    [20186528]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4357
Person2713
и вот не могу понять сотрудника выводить всех его начальников в отдельных строках
Я смотрю ты делаешь успехи и смог найти "Recursive Subquery Factoring: Examples" по ссылке, что я дал.
Теперь перейди ко второму примеру и увидь там "MGR_LIST". Неужели это так сложно?

Если так туго идет - лучше меняй специальность, потом потратишь пять лет, а работу найти невозможно.
6 фев 17, 21:43    [20186596]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Person2713
Спасибо за примеры, но мне надо c помощью рекурсии.


with r(
       ename,
       mgr,
       lvl
      ) as (
             select  ename,
                     mgr,
                     1
               from  emp e1
            union all
             select  r.ename,
                     e.mgr,
                     r.lvl + 1
               from  r,
                     emp e
               where e.empno = r.mgr
                 and e.mgr is not null
           )
search depth first by lvl set level_order
select  ename employee_name,
        (select lpad(' ',(lvl - 1)  * 2) || e.ename from emp e where e.empno = r.mgr) manager_name
  from  r
  order by level_order
/
EMPLOYEE_NAME   MANAGER_NAME
--------------- ---------------
ADAMS           SCOTT
ADAMS             JONES
ADAMS               KING
ALLEN           BLAKE
ALLEN             KING
BLAKE           KING
CLARK           KING
FORD            JONES
FORD              KING
JAMES           BLAKE
JAMES             KING
JONES           KING
KING
MARTIN          BLAKE
MARTIN            KING
MILLER          CLARK
MILLER            KING
SCOTT           JONES
SCOTT             KING
SMITH           FORD
SMITH             JONES
SMITH               KING
TURNER          BLAKE
TURNER            KING
WARD            BLAKE
WARD              KING

26 rows selected.

SQL> 


SY.
6 фев 17, 22:14    [20186691]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
dbms_photoshop,
Каюсь поленился)
7 фев 17, 06:30    [20187113]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Person2713
dbms_photoshop,
Каюсь поленился)


Второй пример не сильно помог, так как показывается как вывести целый путь. Но всё равно намёк понял буду документацию читать).
SY, спасибо за пример
7 фев 17, 06:58    [20187125]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Ребят, знаю что всем надоел. Но все-таки подскажите как решается вот эта задача:

Для каждого заказчика выбрать в виде строки через запятую даты его заказов. Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос). Для отбора «последних» строк использовать connect_by_isleaf.

В данной задаче надо вывести информацию по заказчикам с помощью иерархического запроса, правда в самой таблице иерархии никакой нет. В oracle docs нашел пример, как вывести инфу об одном заказчике, а как для всех не могу понять.
Вот все что пока сделал:
select  customer_id,
        ltrim(SYS_CONNECT_BY_PATH (order_date,', '),', ') as order_dates
  from  (
          select  rownum r,
                  order_date,
                  customer_id 
            from  orders 
            where customer_id = 101
        )
   where connect_by_isleaf = 1
   start with r = 1
   connect by r = prior r + 1
;
16 фев 17, 07:11    [20216184]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26705
Person2713
Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос).
Это устаревшее бессмысленно академическое баловство. RTFM LISTAGG
Person2713
rownum r
row_number() over (partition by customer_id order by order_date) as r 
16 фев 17, 07:40    [20216226]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Elic
Person2713
Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос).
Это устаревшее бессмысленно академическое баловство. RTFM LISTAGG
Person2713
rownum r
row_number() over (partition by customer_id order by order_date) as r 


Elic спасибо за решение, но всё же мне нужен иерархический запрос, так как это одно из заданий лабы. А ещё лучше не решение а объяснение как это можно сделать. Не могу понять как надо мыслить, чтобы такие запросы писать.
16 фев 17, 18:35    [20219012]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
ora601
Member

Откуда:
Сообщений: 708
Person2713

Для каждого заказчика выбрать в виде строки через запятую даты его заказов. Для конкатенации дат заказов использовать sys_connect_by_path (иерархический запрос). Для отбора «последних» строк использовать connect_by_isleaf.


А ещё лучше не решение а объяснение как это можно сделать.



На это можно дать только обьяснение почему так лучше не делать.
16 фев 17, 19:11    [20219114]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
ora601,

Я уже понял, что так лучше не делать, но тем не менее сделать надо.
16 фев 17, 22:15    [20219570]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Cavia porcellus
Member

Откуда:
Сообщений: 39
Интересно было бы увидеть примеры, когда рекурсивный запрос полезнее иерархического
17 фев 17, 08:33    [20219971]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26705
Cavia porcellus
Интересно было бы увидеть примеры, когда рекурсивный запрос полезнее иерархического
Иерархичное сложение, перемножение …
17 фев 17, 08:37    [20219983]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 4357
Cavia porcellus
Интересно было бы увидеть примеры, когда рекурсивный запрос полезнее иерархического
Рекурсия может быть в глубину/ширину, иерархия только в глубину.
В иерархии невозможно выполнять итеративные/накопительные действия, то есть даже задача "sys_connect_by_path наоборот" требует заметных ухищрений.
17 фев 17, 09:06    [20220035]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Так есть у кого-нибудь идею как можно решить данную задачу с помощью иерархического запроса?
17 фев 17, 15:46    [20221857]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26705
Person2713
Так есть у кого-нибудь идею как можно решить данную задачу с помощью иерархического запроса?
Зачёт нужно заслужить. Тем более, что тебе практически разжевали.
17 фев 17, 15:58    [20221921]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Elic, можно по подробнее, я не очень понял о чём вы.
17 фев 17, 18:14    [20222484]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26705
Person2713
можно по подробнее, я не очень понял о чём вы.
20216226
18 фев 17, 07:58    [20223672]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
Elic, не понимаю как аналитическая функция может помочь в данной задаче
19 фев 17, 20:27    [20226558]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Elic
Member

Откуда: 1984. Выбраковка финно-угром началась. КЯЗ
Сообщений: 26705
Person2713
не понимаю
Тут я ничем не могу помочь.
19 фев 17, 20:37    [20226587]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 8459
Person2713
Elic, не понимаю как аналитическая функция может помочь в данной задаче


Ну уж если так надо с иерархией (хотя она тут как зайцу...). Медитируй:

with t as (
           select  customer_id,
                   order_date,
                   row_number() over(partition by customer_id order by order_date) rn
             from  orders
          )
select  customer_id,
        ltrim(sys_connect_by_path(order_date,', '),', ') as order_dates
  from  t
  where connect_by_isleaf = 1
  start with rn = 1
  connect by customer_id = prior customer_id
         and rn = prior rn + 1
/


SY.
19 фев 17, 21:13    [20226679]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
SY, благодарю за решение но от этого не легче, пойду препода доставать пусть объясняет)
20 фев 17, 19:27    [20230484]     Ответить | Цитировать Сообщить модератору
 Re: Выполнение рекурсивного запроса с помощью не рекурсивных функций  [new]
Person2713
Member

Откуда:
Сообщений: 32
SY,

Еще раз спасибо, разобрался в вашем решение, теперь хоть появилось представление что и как работает)
21 фев 17, 10:49    [20232019]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: 1 2      [все]
Все форумы / Oracle Ответить