Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести строки с максимальным значением параметра / 25 сообщений из 32, страница 1 из 2
  1  все
21.03.2017, 19:58
    #39424373
invisiblesereja
invisiblesereja
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
Добрый вечер.
Меня не нужно ругать и закидывать ссаными тряпками.
Я уже на протяжении долгого времени ломаю голову и так и не смог прийти к правильному ответу.
У меня было тестовое задание, на результат которого я уже никак не повлияю, но мне инетересно.

Задача простая:
Есть стандартная таблица Employees (Employee_ID, Department_ID, Salary).
Нужно вывести сотрудников с максимальной в своем отделе зарплатой, избежав подзапросов .
Таким образом, вариант

Код: plsql
1.
2.
3.
4.
select employee_id, department_id, salary from
(select employee_id, department_id, salary, row_number() over(partition by department_id order by salary desc) rn
from employees)
where rn = 1



отпадает, а других у меня и нет.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 20:14
    #39424380
ma1tus
ma1tus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
invisiblesereja,

DENSE_RANK LAST - не?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 20:29
    #39424388
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
ma1tus,

Не. Немного ошибся) - аналитический dense_rank.

Код: plsql
1.
2.
3.
4.
5.
6.
SELECT * FROM (
SELECT e.department_name, e.last_name, e.salary, DENSE_RANK() 
   OVER (PARTITION BY e.department_id ORDER BY e.salary DESC NULLS LAST) AS drank
   FROM employees e
) 
WHERE drank=1
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 21:01
    #39424393
ma1tus
ma1tus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
ora601, и точно ведь..)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 21:14
    #39424399
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
ora601,

Аналитика не удовлетворяет условиям.

ТСу надо группировка + dense rank.

Еще задача решается для любителей экзотики через pattern matching.
Или для любителей извращений через model.

Без подзапросов (или inline view как у тебя).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 22:13
    #39424419
ora601
ora601
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
dbms_photoshopora601,

Аналитика не удовлетворяет условиям.

ТСу надо группировка + dense rank.

Еще задача решается для любителей экзотики через pattern matching.
Или для любителей извращений через model.

Без подзапросов (или inline view как у тебя).

У тебя слишком академическое восприятие) Без инлайн вью это возможно наверное только на model наверное.

Обычно без подзапроса в таких задачах имеется ввиду без

Код: plsql
1.
WHERE (dept_id, salary) IN (SELECT dept_id, max(salary) FROM emp GROUP BY dept_id)



Ну и keep dense rank выведет только одного из лидеров, что вообщем-то не верно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 22:30
    #39424422
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
invisiblesereja,
какая версія оракля?
через модельку я б сделал так
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
  1  select * from emp e
  2  model
  3  return updated rows
  4  partition by (deptno)
  5  dimension by (empno e,DENSE_RANK() OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) d)
  6  measures (ename, sal,-1 sall)
  7  rules upsert  all
  8  (sall[any,d=1]=sal[cv(),cv()]
  9* )
SQL> /

    DEPTNO          E          D ENAME             SAL       SALL
---------- ---------- ---------- ---------- ---------- ----------
        20       7902          1 FORD             3000       3000
        30       7698          1 BLAKE            2850       2850
        10       7839          1 KING             5000       5000
        20       7788          1 SCOTT            3000       3000



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 22:35
    #39424424
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
ora601dbms_photoshopora601,

Аналитика не удовлетворяет условиям.

ТСу надо группировка + dense rank.

Еще задача решается для любителей экзотики через pattern matching.
Или для любителей извращений через model.

Без подзапросов (или inline view как у тебя).

У тебя слишком академическое восприятие) Без инлайн вью это возможно наверное только на model наверное.

Обычно без подзапроса в таких задачах имеется ввиду без

Код: plsql
1.
WHERE (dept_id, salary) IN (SELECT dept_id, max(salary) FROM emp GROUP BY dept_id)



Ну и keep dense rank выведет только одного из лидеров, что вообщем-то не верно.


нет, не так, у Вас тоже подзапрос (инлине вю)
я так понимаю, автор привел пример с подзапросом

.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:19
    #39424442
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
ma1tusora601, и точно ведь..)

А точно ли точно? Не смущaет что aggregate KEEP DENSE_RANK и анaлитический DENSE_RANK не эквивалентны.
Посему TC неплoхo бы определиться что делaть если в oтделе несколько сотрудников с максимальной в своем отделе зарплатой.
Ну и match_recognize:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
select  deptno,
        ename,
        sal
  from  emp
  match_recognize(
                  partition by deptno
                  order by sal desc
                  all rows per match
                  pattern(top+)
                  define
                     top as sal = first(sal) and match_number() = 1
                 )
/

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 SCOTT            3000
        20 FORD             3000
        30 BLAKE            2850

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:21
    #39424445
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
ora601У тебя слишком академическое восприятиеОтнюдь. Я заядлый практик-эникейщик.
ora601Без инлайн вью это возможно наверное только на model наверное.Как я уже сказал решается через model/pattern matching.

Один из вариантов модели уже написал наш коллега из Львова.

Через pattern matching
Код: 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.
SQL> with emp (deptno, empno, sal) as
  2  (
  3  select 1, 1, 1 from dual
  4  union all select 1, 2, 100 from dual
  5  union all select 1, 3, 100 from dual
  6  union all select 1, 4, 10 from dual
  7  union all select 2, 1, 1 from dual
  8  union all select 2, 2, 10 from dual
  9  )
 10  select *
 11  from emp
 12  match_recognize
 13  (
 14    partition by deptno
 15    order by sal desc
 16    all rows per match
 17    pattern ((max_sal|{-dummy-})+)
 18    define max_sal as sal = first(sal)
 19  ) mr
 20  order by deptno, empno;
 
    DEPTNO        SAL      EMPNO
---------- ---------- ----------
         1        100          2
         1        100          3
         2         10          2


ora601Обычно без подзапроса в таких задачах имеется ввиду
...
Ну и keep dense rank выведет только одного из лидеров, что вообщем-то не верно.Обычно предполагается, что уникальный один или испытуемый задаст вопрос про уникальность.
Если их таки не один, то keep не проканает, да.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:23
    #39424448
12це
12це
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
invisiblesereja,

fetch first ... with ties
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:25
    #39424450
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
Ну а еcли нужно только одного (например с бoльшим стажем):

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
select  deptno,
        ename,
        sal
  from  emp
  match_recognize(
                  partition by deptno
                  order by sal desc,hiredate desc
                  measures first(ename) as ename,
                           first(sal) as sal
                  one row per match
                  pattern(dummy+)
                  define
                    dummy as 1 = 1
                 )
/

    DEPTNO ENAME             SAL
---------- ---------- ----------
        10 KING             5000
        20 SCOTT            3000
        30 BLAKE            2850

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:33
    #39424453
кросс-банальность
кросс-банальность
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
А банальный having = max уже не катит?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:36
    #39424455
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
invisibleserejaНужно вывести сотрудников с максимальной в своем отделе зарплатой, избежав подзапросов .Все условия соблюдены.
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SQL> with emp (deptno, empno, sal) as
  2  (
  3  select 1, 1, 1 from dual
  4  union all select 1, 2, 100 from dual
  5  union all select 1, 3, 100 from dual
  6  union all select 1, 4, 10 from dual
  7  union all select 2, 1, 1 from dual
  8  union all select 2, 2, 10 from dual
  9  )
 10  select deptno,
 11         max(sal) sal,
 12         regexp_count(listagg(sal, ',') within group(order by sal desc) || ',', max(sal) || ',') max_emp_cnt,
 13         rtrim(regexp_substr(listagg(empno, ',') within group(order by sal desc, empno), '(\d+,){' ||
 14                             regexp_count(listagg(sal, ',') within group(order by sal desc) || ',', max(sal) || ',') || '}'),
 15               ',') max_emp_list
 16    from emp
 17   group by deptno
 18   order by deptno;
 
    DEPTNO        SAL MAX_EMP_CNT MAX_EMP_LIST
---------- ---------- ----------- --------------------------------------------------------------------------------
         1        100           2 2,3
         2         10           1 2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:38
    #39424456
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
кросс-банальностьА банальный having = max уже не катит?Ну покажи мастерство группировки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:39
    #39424457
Б.анальный
Б.анальный
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
dbms_photoshopкросс-банальностьА банальный having = max уже не катит?Ну покажи мастерство группировки.
Код: plsql
1.
2.
3.
4.
5.
select e1.deptno, e1.empno, e1.sal
from emp e1
join emp e2 on e2.deptno=e1.deptno
group by e1.deptno, e1.empno, e1.sal  
having e1.sal = max(e2.sal)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:42
    #39424459
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
Б.анальный,

Таки да, как-то я забыл, что есть джойны.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:43
    #39424460
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
12цеinvisiblesereja,

fetch first ... with ties

А "в своем отделе" куда воткнуть? Да и за фасадом подзапросов нeмepянo:

Код: 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.
SQL> variable c clob
SQL> begin
  2      dbms_utility.expand_sql_text('select  deptno,
  3          ename,
  4          sal
  5    from  emp
  6    order by sal desc
  7    fetch first 1 row
  8    with ties',:c);
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------------------------------------------------------
SELECT  "A1"."DEPTNO" "DEPTNO",
        "A1"."ENAME" "ENAME",
        "A1"."SAL" "SAL"
  FROM  (
         SELECT  "A2"."DEPTNO" "DEPTNO",
                 "A2"."ENAME" "ENAME",
                 "A2"."SAL" "SAL",
                 "A2"."SAL" "rowlimit_$_0",
                 RANK() OVER ( ORDER BY "A2"."SAL" DESC ) "rowlimit_$$_rank"
           FROM  (
                  SELECT  "A3"."EMPNO" "EMPNO",
                          "A3"."ENAME" "ENAME",
                          "A3"."JOB" "JOB",
                          "A3"."MGR" "MGR",
                          "A3"."HIREDATE" "HIREDATE",
                          CASE
                            WHEN USER <>'U1' THEN "A3"."SAL"
                            ELSE NULL
                          END  "SAL",
                          "A3"."COMM" "COMM",
                          "A3"."DEPTNO" "DEPTNO"
                    FROM  "SCOTT"."EMP" "A3"
                 ) "A2"
        ) "A1"
  WHERE "A1"."rowlimit_$$_rank"<=1
  ORDER BY "A1"."rowlimit_$_0" DESC

SQL>



SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.03.2017, 23:48
    #39424464
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
Насчет:

Код: plsql
1.
2.
3.
4.
                          CASE
                            WHEN USER <>'U1' THEN "A3"."SAL"
                            ELSE NULL
                          END  "SAL",



это у меня висит DBMS_RLS policy запрещающая U1 глядеть на SAL.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.03.2017, 00:01
    #39424467
Ещё на.фан.тазировал
Ещё на.фан.тазировал
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
Код: plsql
1.
2.
3.
4.
5.
6.
7.
select distinct
   deptno,
   case when sal = first_value(sal) over(partition by deptno order by sal desc, empno) 
     then  empno 
     else first_value( empno) over(partition by deptno order by sal desc, empno) end empno,
   first_value(sal) over(partition by deptno order by sal desc, empno) sal
from emp;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.03.2017, 07:46
    #39424528
ma1tus
ma1tus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
SYma1tusora601, и точно ведь..)А точно ли точно? Не смущaет что aggregate KEEP DENSE_RANK и анaлитический DENSE_RANK не эквивалентны.
SY.Ну, решение в отсутствиe group by, мне приглянулось больше, чем
 
предположенное мною)
Код: plsql
1.
2.
3.
4.
5.
6.
select * from (
  select employee_id, department_id, salary, 
         max(salary) keep (dense_rank last order by salary) over (partition by department_id) as maxsalary
    from employees
   group by employee_id, department_id, salary 
) where salary = maxsalary

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.03.2017, 08:46
    #39424548
ma1tus
ma1tus
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
ma1tusпредположенное мноюСорри, group by-то зачем взгромоздил...
 
Код: plsql
1.
2.
3.
4.
5.
select * from (
  select employee_id, department_id, salary, 
         max(salary) keep (dense_rank last order by salary) over (partition by department_id) as maxsalary
    from employees
) where salary = maxsalary

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.03.2017, 16:13
    #39425034
stax..
stax..
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
Б.анальныйdbms_photoshopпропущено...
Ну покажи мастерство группировки.
Код: plsql
1.
2.
3.
4.
5.
select e1.deptno, e1.empno, e1.sal
from emp e1
join emp e2 on e2.deptno=e1.deptno
group by e1.deptno, e1.empno, e1.sal  
having e1.sal = max(e2.sal)


понравилолсь решение, к сожеленью, не со Львова :)

......
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.03.2017, 16:49
    #39425073
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
invisiblesereja,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
with employees as (
select 1 employee_id, 1 department_id, 1 salary from dual union all
select 4, 1, 2 from dual union all
select 2, 2, 5 from dual union all
select 5, 2, 4 from dual union all
select 3, 3, 6 from dual union all
select 6, 3, 3 from dual
)
select max(employee_id) keep (dense_rank last order by salary) as employee_id,
department_id,
max(salary) as salary
from employees
group by department_id
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
22.03.2017, 16:53
    #39425082
--Eugene--
--Eugene--
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Вывести строки с максимальным значением параметра
будем надеяться, что уровень дохода уникально распределен среди сотрудников )))
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Вывести строки с максимальным значением параметра / 25 сообщений из 32, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 541ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1886241]:
 0ms
get settings:
 8ms
get forum list:
 13ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 212ms
get topic data:
 7ms
get forum data:
 2ms
get page messages:
 68ms
get tp. blocked users:
 1ms
others: 224ms
total:  541ms
x
x
Закрыть


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