Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу. / 8 сообщений из 8, страница 1 из 1
02.12.2017, 16:17
    #39563025
gsageeh
gsageeh
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу. Буду благодарен, если кто-то сможет помочь.

DEPARTS – отделы
ID – идентификатор отдела
NAME – название отдела

MANAGERS – список менеджеров
ID – идентификатор менеджера
FIO – фамилия, имя, отчество
DEPART – отдел (ссылка на DEPARTS.ID)

SALES – таблица продаж; каждая продажа отражается в отдельной строке
ID – уникальный идентификатор
MANAGER – менеджер (ссылка на MANAGERS.ID)
SUMMA – сумма продажи


ID NAME1 Отдел продаж №12 Отдел продаж №2
ID FIO DEPART1 Иванов 12 Петров 13 Сидоров 14 Каменскайте 25 Какис Мапис 26 Панайетис 27 Кунь Лесной 18 Коровин 2

ID MANAGER SUMMA1 1 1002 2 2003 1 9004 3 4005 4 3006 5 2007 6 7008 1 4009 6 50010 3 10011 2 70012 1 50013 7 50014 8 500

Написал 2 запроса, не могу понять как их объединить и сделать ограничение внутри на вывод только 3х от каждого отдела.

Заготовка общего запроса:
Код: plsql
1.
2.
3.
select name, fio, summa
from departs, managers, sales
where departs.id=managers.depart and managers.id=sales.manager


Вложенный запрос:
Код: plsql
1.
2.
3.
4.
5.
select sum(summa), manager, depart
from sales, managers
where managers.id =sales.manager
group by manager, depart
order by sum(summa) desc;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2017, 16:59
    #39563039
gsageeh
gsageeh
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
получилось более-менее собрать, осталось только сделать так, чтобы от каждого отдела выводилось только 3 записи. Подскажите, как можно это реализовать, если у меня ограничение не на вывод кол-во итоговых строк, а на кол-во равных определённому значению на этапе поиска значений
Код: plsql
1.
2.
3.
4.
5.
select name, fio,sum(summa)
from sales, managers, departs
where managers.id = sales.manager and departs.id=managers.depart 
group by fio, name
order by sum(summa) desc;



NAME FIO SUM(SUMMA)Отдел продаж №1 Иванов 1900Отдел продаж №2 Панайетис 1200Отдел продаж №1 Петров 900Отдел продаж №2 Коровин 500Отдел продаж №1 Кунь Лесной 500Отдел продаж №1 Сидоров 500Отдел продаж №2 Каменскайте 300Отдел продаж №2 Какис Мапис 200
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2017, 17:15
    #39563045
dmdmdm
dmdmdm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
Если это задание и впрямь для студентов, то требования повысились.
Впрочем, нет. Аналитика появилась в 8-й версии, 1997 , а я как раз в это время учился в Политехе. Значит, щас сразу спрашивают.

На "3 передовика" данных не набил, только на одного передовика.

 
Код: 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.
with d as (
 select 1 id_dep, 'Отдел1' name_dep from dual union all
 select 2 id_dep, 'Отдел2' name_dep from dual union all
 select 3 id_dep, 'Отдел123' name_dep from dual
), m as
(
 select 1 id_pers, 'Манагер1' name_pers, 1 id_dep from dual union all
 select 2 id_pers, 'Манагер2' name_pers, 1 id_dep from dual union all
 select 3 id_pers, 'Манагер3' name_pers, 2 id_dep from dual union all
 select 4 id_pers, 'Манагер4' name_pers, 2 id_dep from dual union all
 select 5 id_pers, 'Манагерище' name_pers, 2 id_dep from dual union all
 select 6 id_pers, 'Главный Манагер' name_pers, 3 id_dep from dual
), s as
(
 select 1 id_pers, 100 summ from dual union all
 select 1 id_pers, 200 summ from dual union all 
 select 3 id_pers, 400 summ from dual union all 
 select 3 id_pers, 500 summ from dual union all 
 select 3 id_pers, 1000 summ from dual union all  
 select 5 id_pers, 1 summ from dual union all  
 select 5 id_pers, 2 summ from dual union all   
 select 5 id_pers, 2.5 summ from dual union all    
 select 5 id_pers, 0.5 summ from dual
)
select name_dep, name_pers, summ
  from (
    select name_dep, name_pers, summ, row_number() over (partition by name_dep order by summ desc) rn
     from (
            select d.name_dep, m.name_pers, nvl(sum(s.summ), 0) summ
              from m, d, s
             where m.id_dep = d.id_dep
               and s.id_pers (+) = m.id_pers
             group by m.name_pers, d.name_dep
           )
       )
 where rn = 1
 order by name_dep

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2017, 18:46
    #39563058
gsageeh
gsageeh
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
dmdmdm, если не сложно можете ответить на возникшие вопросы?
Код: plsql
1.
select name_dep, name_pers, summ, row_number() over (partition by name_dep order by summ desc) rn


я так понимаю данный селект берёт максимум из части определённой по параметру номер отдела, за что отвечает rn, почему он именно 1? Несколько максимальных подобным образом можно получить или только если по одному получать и потом убирать из выборки?

Код: plsql
1.
 and s.id_pers (+) = m.id_pers


чем это условие отличается от прописанного через "="?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.12.2017, 19:04
    #39563064
dmdmdm
dmdmdm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
данный селект берёт максимум из части определённой по параметру номер отдела

По-эскуэльски: сортируем по номеру строки из окна "partition by name_dep order by summ desc".
По-русски: в каждой группе по имени отдела отсортируем по убыванию суммы, возьмем только 1-ю строку из отсортированных, т.е. строку с максимальной суммой по данному отделу.

Несколько максимальных

Код: plsql
1.
where rn < 3



чем это условие отличается от прописанного через "="?

Это Oracle native syntax.

ANSI syntax:

 
Код: 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.
with d as (
 select 1 id_dep, 'Отдел1' name_dep from dual union all
 select 2 id_dep, 'Отдел2' name_dep from dual union all
 select 3 id_dep, 'Отдел123' name_dep from dual
), m as
(
 select 1 id_pers, 'Манагер1' name_pers, 1 id_dep from dual union all
 select 2 id_pers, 'Манагер2' name_pers, 1 id_dep from dual union all
 select 3 id_pers, 'Манагер3' name_pers, 2 id_dep from dual union all
 select 4 id_pers, 'Манагер4' name_pers, 2 id_dep from dual union all
 select 5 id_pers, 'Манагерище' name_pers, 2 id_dep from dual union all
 select 6 id_pers, 'Главный Манагер' name_pers, 3 id_dep from dual
), s as
(
 select 1 id_pers, 100 summ from dual union all
 select 1 id_pers, 200 summ from dual union all 
 select 3 id_pers, 400 summ from dual union all 
 select 3 id_pers, 500 summ from dual union all 
 select 3 id_pers, 1000 summ from dual union all  
 select 5 id_pers, 1 summ from dual union all  
 select 5 id_pers, 2 summ from dual union all   
 select 5 id_pers, 2.5 summ from dual union all    
 select 5 id_pers, 0.5 summ from dual
)
select name_dep, name_pers, summ
  from (
    select name_dep, name_pers, summ, row_number() over (partition by name_dep order by summ desc) rn
     from (
            select d.name_dep, m.name_pers, nvl(sum(s.summ), 0) summ
              from m join d on m.id_dep = d.id_dep
                  left outer join s on s.id_pers = m.id_pers
             group by m.name_pers, d.name_dep
           )
       )
 where rn = 1
 order by name_dep

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2017, 11:43
    #39563564
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
dmdmdm,

на один уровень вложенности можно уменьшить

Код: 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.
 25  select name_dep, name_pers, sum_m
 26    from (
 27      select name_dep
 28          , name_pers, nvl(sum(s.summ), 0) sum_m
 29          , row_number() over (partition by name_dep order by nvl(sum(s.summ), 0) desc,m.name_pers) rn
 30  --     from (
 31  --            select d.name_dep, m.name_pers, nvl(sum(s.summ), 0) summ
 32                from m, d, s
 33               where m.id_dep = d.id_dep
 34                 and s.id_pers (+) = m.id_pers
 35               group by d.name_dep,m.name_pers
 36  --           )
 37         )
 38   where rn <= 3
 39*  order by name_dep
SQL> /

NAME_DEP NAME_PERS            SUM_M
-------- --------------- ----------
Отдел1   Манагер1               300
Отдел1   Манагер2                 0
Отдел123 Главный Манагер          0
Отдел2   Манагер3              1900
Отдел2   Манагерище               6
Отдел2   Манагер4                 0

6 rows selected.



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2017, 12:27
    #39563605
мдя-с
мдя-с
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
Stax,

мдя , а лейтералы в оракель раззи ещё не подвезли ?

http://www.sql.ru/forum/actualsearch.aspx?search=lateral&sin=0&bid=3&a=&ma=0&dt=-1&s=1&so=1

или подвозят "медленно и печально" ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.12.2017, 13:45
    #39563672
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
мдя-сStax,

мдя , а лейтералы в оракель раззи ещё не подвезли ?

http://www.sql.ru/forum/actualsearch.aspx?search=lateral&sin=0&bid=3&a=&ma=0&dt=-1&s=1&so=1

или подвозят "медленно и печально" ?

подвезли, но не всем

кому завезли, то у них и топ н есть

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу. / 8 сообщений из 8, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 459ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1884806]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 160ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 29ms
get tp. blocked users:
 1ms
others: 234ms
total:  459ms
x
x
Закрыть


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