ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка (иерархический запрос)
16 сообщений из 16, страница 1 из 1
Задачка (иерархический запрос)
    #39508812
kuzea
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kuzea
Гость
Создать запрос для определения сумм окладов сотрудников от сотрудников, не имеющих менеджера (менеджеров высшего уровня), до сотрудников, не имеющих подчиненных.
Исходная таблица(частично): employee_id, last_name, salary, manager_id - где столбец manager_id хранит id менеджера данного работника

Пример результата:
MAN_LIST SUM_SALKing->Kochhar->Greenberg->Faviet 62008… …King->Hartstein->Fay 43000
Решил вот так:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
select man_list, 
    nvl(to_number(ltrim(regexp_substr(sal, ',\d*', 1, 1), ',')), 0) +
    nvl(to_number(ltrim(regexp_substr(sal, ',\d*', 1, 2), ',')), 0) +
    nvl(to_number(ltrim(regexp_substr(sal, ',\d*', 1, 3), ',')), 0) +
    nvl(to_number(ltrim(regexp_substr(sal, ',\d*', 1, 4), ',')), 0) sum_sal
from (select ltrim(sys_connect_by_path(last_name, '->'), '->') man_list, 
            sys_connect_by_path(salary, ',') sal 
        from employees
        where connect_by_isleaf = 1
        start with manager_id is null
        connect by prior employee_id = manager_id);


Но понимаю, что это решение, только для данного случая.
Хотелось бы его превратить в более универсальный вариант. По-моему, должно быть что-то вот такое (только такой запрос не хочет прекращать выполняться):
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select rn, lev, man_list, sal
from (select rn, level lev, man_list, regexp_substr(sal, '[0-9]+', 1, level) sal
      from (select rownum rn, man_list, sal
            from (select ltrim(sys_connect_by_path(last_name, '->'), '->') man_list, ltrim(sys_connect_by_path(salary, ','), ',') sal 
                  from employees
                  where connect_by_isleaf = 1
                  start with manager_id is null
                  connect by prior employee_id = manager_id))
      connect by level <= regexp_count(sal, ',') + 1)
start with lev = 1
connect by lev = prior lev + 1
and prior rn = rn;



Может кто-то что-то посоветует или конструктивно покритикует. Заранее спасибо
...
Рейтинг: 0 / 0
22.08.2017, 15:36
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508820
kuzea
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kuzea
Гость
Забыл упомянуть. Решение должно быть без использования аналитических функций
...
Рейтинг: 0 / 0
22.08.2017, 15:45
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508829
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
kuzeaопределения сумм окладов сотрудников от сотрудников, не имеющих менеджера (менеджеров высшего уровня), до сотрудников, не имеющих подчиненных.Формулировка - не ахти.
kuzea
Код: plsql
1.
where connect_by_isleaf = 1

Зачем?
...
Рейтинг: 0 / 0
22.08.2017, 15:53
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508838
kuzea
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kuzea
Гость
Elic,
нужно вытащить все последовательности работников компании, такие в которых
1)первым должен быть самый главный у которого нет начальника (т.е. manager_id is null),
2)последним в каждой последовательности должен быть такой работник, который ни для кого не является руководителем,
3)а между ними работники в соответствии с иерархией компании.

Например,
главный King
его замы - Korchhar, Ivanov
у замов в отделах - у Korchhar(Pupkin, Sidorov), у Ivanov(Petrov).

Тогда
King->Korchhar->Pupkin
King->Korchhar->Sidorov
King->Ivanov->Petrov

connect_by_isleaf = 1 нужно, чтобы оставить только те ветки иерархии, которые заканчиваются листьями(работниками не имеющими подчиненных в данном случае). Так исключаются ветки типа King->Ivanov
...
Рейтинг: 0 / 0
22.08.2017, 16:10
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508858
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
kuzea,

Я что-то не понял
если у сотрудников зарплата:
King - 1000
Kochhar -100
Greenberg - 10
Faviet- 1
Hartstein - 200
Fay - 20

то
King->Kochhar->Greenberg->Faviet = 1111
King->Hartstein->Fay = 1220

Так надо? т.е. учитываем зарплату King (например) в каждой строке где он участвует?
...
Рейтинг: 0 / 0
22.08.2017, 16:37
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508862
kuzea
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kuzea
Гость
MaximaXXL,
да
второй столбец - это сумма зарплат тех, чьи фамилии есть в цепочке в первом столбце
...
Рейтинг: 0 / 0
22.08.2017, 16:40
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508881
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
kuzea,

без аналитических функций

1)
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with T(lvl, empno, mgr, sal, r_sum, c_path, is_leaf)
as (
select 1 as lvl, e.empno, e.mgr, e.sal, e.sal as r_sum, '->'||e.empno as c_path   
, case When exists(Select * from scott.emp e2 Where e2.mgr = e.empno) Then 0 else 1 end as is_leaf
from scott.emp e
Where mgr is null
union all
Select T.lvl + 1 as lvl, e.empno, e.mgr, e.sal, T.r_sum + e.sal, T.c_path||'->'||e.empno
, case When exists(Select * from scott.emp e2 Where e2.mgr = e.empno) Then 0 else 1 end as is_leaf
From scott.emp e, T
Where 
e.mgr = T.empno  
)
Search Depth First By mgr Set seq
Select empno,mgr,sal,r_sum, c_path
From T
Where T.is_leaf = 1;



2)
 
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
Select empno, mgr,sal, 
 (Select sum(e2.sal)
  From scott.emp e2
  Start with e2.empno = e.empno
  Connect by prior e2.mgr = e2.empno
 ) as r_sum
, sys_connect_by_path(empno,'->') as c_path
From scott.emp e
Where connect_by_isleaf = 1 
Start with mgr is null
Connect by mgr = prior empno
;

...
Рейтинг: 0 / 0
22.08.2017, 17:10
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508895
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
booby,

Както так наверно

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
with employees as ( 
select 1 employee_id, 'King' last_name, 1000 salary, null manager_id from dual union all 
select 2 employee_id, 'Kochhar' last_name, 100 salary, 1 manager_id from dual union all 
select 3 employee_id, 'Greenberg' last_name, 10 salary, 2 manager_id from dual union all 
select 4 employee_id, 'Faviet' last_name, 1 salary, 3 manager_id from dual union all 
select 5 employee_id, 'Hartstein' last_name, 200 salary, 1 manager_id from dual union all 
select 6 employee_id, 'Fay' last_name, 20 salary, 5 manager_id from dual) 
 
select ltrim(sys_connect_by_path(last_name, '->'), '->') man_list,  
        ( 
         select  sum(e.salary) 
           from  employees e 
           start with e.employee_id = e1.employee_id 
           connect by e.employee_id = prior e.manager_id 
        ) Sal 

        from employees e1 
        where connect_by_isleaf = 1 
        start with manager_id is null 
        connect by prior employee_id = manager_id
...
Рейтинг: 0 / 0
22.08.2017, 17:28
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508900
MaximaXXL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MaximaXXL
Участник
kuzea,

Упс, это как 2-й вариант предложенный booby
...
Рейтинг: 0 / 0
22.08.2017, 17:30
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508915
kuzea
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kuzea
Гость
booby, MaximaXXL

спасибо,
через start with можно оказывается подзапрос связывать. не знал, теперь запомню
...
Рейтинг: 0 / 0
22.08.2017, 17:49
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39508945
kuzea
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kuzea
Гость
а вот если по 2-ому варианту, я правильно понимаю?

сначала отрабатывает основной запрос и вытаскивает цепочку man_list с условием connect by employee_id = prior manager_id
(так получаем первую строку (точнее ее часть, столбец man_list) результирующего множества),
а затем начиная с последнего элемента полученной цепочки отрабатывает подзапрос с условием connect by prior employee_id = manager_id и считает сумму зарплат как бы в обратном порядке (и выдает первую строку(столбец sal) результирующего множества).

и так дальше, полстроки + полстроки = строка результата, пока всё не обработается
...
Рейтинг: 0 / 0
22.08.2017, 18:27
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39509032
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
kuzea,

почти так.
да, считает сумму подзапросом в "обратом порядке".
Но для каждой строки исходного запроса.
После чего ставится фильтр на листовые строки.
Если надо, чтобы считало обратную последовательность только для листовых строк напиши например так:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
Select empno, mgr, sal 
, Case When connect_by_isleaf = 1 Then
 (Select sum(e2.sal)
  From scott.emp e2
  Start with e2.empno = e.empno
  Connect by prior e2.mgr = e2.empno 
 ) End as r_sum
, sys_connect_by_path(empno,'->') as c_path
From scott.emp e
-- Where connect_by_isleaf = 1 -- убираем условие, чтобы видеть, для каких строк идет счет
Start with mgr is null
Connect by mgr = prior empno
;
...
Рейтинг: 0 / 0
22.08.2017, 21:18
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39509033
booby
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
booby
Участник
kuzea,
автор(и выдает первую строку(столбец sal) результирующего множества).

здесь у подзапроса by design одна строка. Так работают агрегатные запросы без group by.
После первой нет следующей и быть не может.
А если внезапно, волею прокравшегося в систему таракана, вдруг образовалась бы вторая строка, то была бы получена ошибка времени выполнения.
...
Рейтинг: 0 / 0
22.08.2017, 21:23
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39509100
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
select listagg(last_name, '->') within group (order by lvl desc) as path, sum(salary) as salaries
  from
  ( select connect_by_root employee_id as employee_id, last_name, salary, level as lvl
      from hr.employees
      start with employee_id not in (select manager_id from hr.employees where manager_id is not null)
      connect by employee_id = prior manager_id
  )
  group by employee_id
;

 
Код: plaintext
PATHSALARIESKing->De Haan->Hunold->Ernst56000King->De Haan->Hunold->Austin54800King->De Haan->Hunold->Pataballa54800King->De Haan->Hunold->Lorentz54200King->Kochhar->Greenberg->Faviet62008King->Kochhar->Greenberg->Chen61208King->Kochhar->Greenberg->Sciarra60708King->Kochhar->Greenberg->Urman60808King->Kochhar->Greenberg->Popp59908King->Raphaely->Khoo38100King->Raphaely->Baida37900King->Raphaely->Tobias37800King->Raphaely->Himuro37600King->Raphaely->Colmenares37500King->Weiss->Nayer35200King->Weiss->Mikkilineni34700King->Weiss->Landry34400King->Weiss->Markle34200King->Fripp->Bissot35500King->Fripp->Atkinson35000King->Fripp->Marlow34700King->Fripp->Olson34300King->Kaufling->Mallin35200King->Kaufling->Rogers34800King->Kaufling->Gee34300King->Kaufling->Philtanker34100King->Vollman->Ladwig34100King->Vollman->Stiles33700King->Vollman->Seo33200King->Vollman->Patel33000King->Mourgos->Rajs33300King->Mourgos->Davies32900King->Mourgos->Matos32400King->Mourgos->Vargas32300King->Russell->Tucker48000King->Russell->Bernstein47500King->Russell->Hall47000King->Russell->Olsen46000King->Russell->Cambrault45500King->Russell->Tuvault45000King->Partners->King47500King->Partners->Sully47000King->Partners->McEwen46500King->Partners->Smith45500King->Partners->Doran45000King->Partners->Sewall44500King->Errazuriz->Vishney46500King->Errazuriz->Greene45500King->Errazuriz->Marvins43200King->Errazuriz->Lee42800King->Errazuriz->Ande42400King->Errazuriz->Banda42200King->Cambrault->Ozer46500King->Cambrault->Bloom45000King->Cambrault->Fox44600King->Cambrault->Smith42400King->Cambrault->Bates42300King->Cambrault->Kumar41100King->Zlotkey->Abel45500King->Zlotkey->Hutton43300King->Zlotkey->Taylor43100King->Zlotkey->Livingston42900King->Zlotkey->Grant41500King->Zlotkey->Johnson40700King->Weiss->Taylor35200King->Weiss->Fleaur35100King->Weiss->Sullivan34500King->Weiss->Geoni34800King->Fripp->Sarchand36400King->Fripp->Bull36300King->Fripp->Dellinger35600King->Fripp->Cabrio35200King->Kaufling->Chung35700King->Kaufling->Dilly35500King->Kaufling->Gates34800King->Kaufling->Perkins34400King->Vollman->Bell34500King->Vollman->Everett34400King->Vollman->McCain33700King->Vollman->Jones33300King->Mourgos->Walsh32900King->Mourgos->Feeney32800King->Mourgos->OConnell32400King->Mourgos->Grant32400King->Kochhar->Whalen45400King->Hartstein->Fay43000King->Kochhar->Mavris47500King->Kochhar->Baer51000King->Kochhar->Higgins->Gietz61308
...
Рейтинг: 0 / 0
23.08.2017, 07:55
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39509151
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Elic… Live SQL
...
Рейтинг: 0 / 0
23.08.2017, 10:34
| Ответить | Цитировать | Написать  
Задачка (иерархический запрос)
    #39509390
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
kuzea,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select  substr(sys_connect_by_path(last_name,'->'),3) path,
        xmlcast(xmlquery(sys_connect_by_path(salary,'+') returning content) as number) salary
  from  hr.employees
  where connect_by_isleaf = 1
  start with manager_id is null
  connect by manager_id = prior employee_id
  order by path
/



Или, если установлен OLAP:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
select  substr(sys_connect_by_path(last_name,'->'),3) path,
        dbms_aw.eval_number('0' || sys_connect_by_path(salary,'+')) salary
  from  hr.employees
  where connect_by_isleaf = 1
  start with manager_id is null
  connect by manager_id = prior employee_id
  order by path
/



 

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
PATH                                    SALARY
----------------------------------- ----------
King->Cambrault->Bates                   42300
King->Cambrault->Bloom                   45000
King->Cambrault->Fox                     44600
King->Cambrault->Kumar                   41100
King->Cambrault->Ozer                    46500
King->Cambrault->Smith                   42400
King->De Haan->Hunold->Austin            54800
King->De Haan->Hunold->Ernst             56000
King->De Haan->Hunold->Lorentz           54200
King->De Haan->Hunold->Pataballa         54800
King->Errazuriz->Ande                    42400
King->Errazuriz->Banda                   42200
King->Errazuriz->Greene                  45500
King->Errazuriz->Lee                     42800
King->Errazuriz->Marvins                 43200
King->Errazuriz->Vishney                 46500
King->Fripp->Atkinson                    35000
King->Fripp->Bissot                      35500
King->Fripp->Bull                        36300
King->Fripp->Cabrio                      35200
King->Fripp->Dellinger                   35600
King->Fripp->Marlow                      34700
King->Fripp->Olson                       34300
King->Fripp->Sarchand                    36400
King->Hartstein->Fay                     43000
King->Kaufling->Chung                    35700
King->Kaufling->Dilly                    35500
King->Kaufling->Gates                    34800
King->Kaufling->Gee                      34300
King->Kaufling->Mallin                   35200
King->Kaufling->Perkins                  34400
King->Kaufling->Philtanker               34100
King->Kaufling->Rogers                   34800
King->Kochhar->Baer                      51000
King->Kochhar->Greenberg->Chen           61208
King->Kochhar->Greenberg->Faviet         62008
King->Kochhar->Greenberg->Popp           59908
King->Kochhar->Greenberg->Sciarra        60708
King->Kochhar->Greenberg->Urman          60808
King->Kochhar->Higgins->Gietz            61308
King->Kochhar->Mavris                    47500
King->Kochhar->Whalen                    45400
King->Mourgos->Davies                    32900
King->Mourgos->Feeney                    32800
King->Mourgos->Grant                     32400
King->Mourgos->Matos                     32400
King->Mourgos->OConnell                  32400
King->Mourgos->Rajs                      33300
King->Mourgos->Vargas                    32300
King->Mourgos->Walsh                     32900
King->Partners->Doran                    45000
King->Partners->King                     47500
King->Partners->McEwen                   46500
King->Partners->Sewall                   44500
King->Partners->Smith                    45500
King->Partners->Sully                    47000
King->Raphaely->Baida                    37900
King->Raphaely->Colmenares               37500
King->Raphaely->Himuro                   37600
King->Raphaely->Khoo                     38100
King->Raphaely->Tobias                   37800
King->Russell->Bernstein                 47500
King->Russell->Cambrault                 45500
King->Russell->Hall                      47000
King->Russell->Olsen                     46000
King->Russell->Tucker                    48000
King->Russell->Tuvault                   45000
King->Vollman->Bell                      34500
King->Vollman->Everett                   34400
King->Vollman->Jones                     33300
King->Vollman->Ladwig                    34100
King->Vollman->McCain                    33700
King->Vollman->Patel                     33000
King->Vollman->Seo                       33200
King->Vollman->Stiles                    33700
King->Weiss->Fleaur                      35100
King->Weiss->Geoni                       34800
King->Weiss->Landry                      34400
King->Weiss->Markle                      34200
King->Weiss->Mikkilineni                 34700
King->Weiss->Nayer                       35200
King->Weiss->Sullivan                    34500
King->Weiss->Taylor                      35200
King->Zlotkey->Abel                      45500
King->Zlotkey->Grant                     41500
King->Zlotkey->Hutton                    43300
King->Zlotkey->Johnson                   40700
King->Zlotkey->Livingston                42900
King->Zlotkey->Taylor                    43100

89 rows selected.

SQL>




SY.
...
Рейтинг: 0 / 0
23.08.2017, 14:50
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Задачка (иерархический запрос)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39508820&tid=1885369]:
 0ms
get settings:
 6ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 143ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 54ms
get tp. blocked users:
 1ms
others: 196ms
total:  433ms
созд. / загр.: 433ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]