ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
8 сообщений из 8, страница 1 из 1
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563025
gsageeh
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gsageeh
Гость
Вывести по 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:17
| Ответить | Цитировать | Написать  
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563039
gsageeh
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gsageeh
Гость
получилось более-менее собрать, осталось только сделать так, чтобы от каждого отдела выводилось только 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, 16:59
| Ответить | Цитировать | Написать  
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563045
dmdmdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmdmdm
Участник
Если это задание и впрямь для студентов, то требования повысились.
Впрочем, нет. Аналитика появилась в 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, 17:15
| Ответить | Цитировать | Написать  
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563058
gsageeh
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
gsageeh
Гость
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, 18:46
| Ответить | Цитировать | Написать  
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563064
dmdmdm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dmdmdm
Участник
данный селект берёт максимум из части определённой по параметру номер отдела

По-эскуэльски: сортируем по номеру строки из окна "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
02.12.2017, 19:04
| Ответить | Цитировать | Написать  
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563564
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
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, 11:43
| Ответить | Цитировать | Написать  
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563605
мдя-с
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
мдя-с
Гость
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, 12:27
| Ответить | Цитировать | Написать  
Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
    #39563672
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
мдя-с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
04.12.2017, 13:45
| Ответить | Цитировать | Написать  
8 сообщений из 8, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести по 3 лучших работника по сумме продаж из каждого отдела и упорядочить по отделу.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39563605&tid=1884806]:
 0ms
get settings:
 8ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 161ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 1ms
others: 242ms
total:  491ms
созд. / загр.: 491ms
x
x
Закрыть


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