Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Аналитические функции и условия больше меньше. как?  [new]
michael063
Member

Откуда:
Сообщений: 34
Ребят, такая вот задачка,

Для всех сотрудников вывести отдел (department_id), фамилию (last_name), зарплату (salary) и количество человек,
которые, работая в этом же отделе, имеют зарплату (строго) больше, чем данный сотрудник.

обьясните как реализовать сравнение больше меньше с исп. аналитич. функций.

Спасибо.
30 май 15, 18:56    [17710625]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
mezzanine
Member

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

Думаю Вам нужен having.
30 май 15, 19:27    [17710699]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
michael063
Member

Откуда:
Сообщений: 34
Требование использовать аналитические функции.
30 май 15, 19:47    [17710755]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
eev
Member

Откуда: -
Сообщений: 1156
michael063,

типа того http://sqlfiddle.com/#!4/48bbd/15/0
]with t as
(select 100 sal from dual union all
 select 200 sal from dual union all
 select 200 sal from dual union all
 select 400 sal from dual union all
 select 300 sal from dual
)
select sal, max_in_grp - max(pos) over (partition by sal) "Сколько > ?"
from (
select sal
, count (sal) over() max_in_grp
, row_number() over(order by sal) pos
from t
)

AL Сколько > ?
100 4
200 2
200 2
300 1
400 0

partition by прикрутить для отдела
30 май 15, 21:13    [17711019]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
michael063
Member

Откуда:
Сообщений: 34
спасибо)
30 май 15, 21:37    [17711110]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
кит северных морей
Member

Откуда: krsk / nyc / krsk
Сообщений: 882
with t as
(select 1 dep, 100 sal from dual union all
 select 1, 200 sal from dual union all
 select 1, 200 sal from dual union all
 select 1, 400 sal from dual union all
 -- select 1, 300 sal from dual union all
 select 1, 300 sal from dual
)
select dep, sal,  
rank() over(partition by dep order by sal desc) - 1
how_many
from t
order by 3 desc
30 май 15, 22:21    [17711202]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
SQL*Plus
Member

Откуда: Россия, Москва
Сообщений: 8131
michael063
Требование использовать аналитические функции.
Давайте, давайте!

Побольше всяких аналитических функций, самописных PL/SQL-функций, в том числе pipelined.
Побольше промежуточных таблиц, вложенных PL/SQL-циклов FOR с курсорами...

И у нас всегда будет много работы по оптимизации систем, поставке более мощного железа и т.п ,
и у ваших заказчиков всегда будут проблемы, для решения которых нужно планировать для нас деньги.
Много денег! :-)

Так держать!

Спасибо!
30 май 15, 23:01    [17711270]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
vaneque
Member

Откуда: Тверь
Сообщений: 168
michael063
Требование использовать аналитические функции.


мне просто любопытно... требование для заказчика? для начальника/руководителя/тимлида? преподавателя в университете? и главное, ради чего, когда аналитические функции в принципе в данном случае не требуются? как насчет nonequijoin? having? что за бредовые постановки задачи??? важен результат или способ его достижения?
31 май 15, 00:27    [17711378]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
michael063
Member

Откуда:
Сообщений: 34
извращенный мозг преподавателя не дает покоя ему, видимо)
думал можно решить задачу только окном) и применяя в нем sql-expression, а оказывается нет)
31 май 15, 07:22    [17711743]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
vaneque
Member

Откуда: Тверь
Сообщений: 168
michael063
извращенный мозг преподавателя не дает покоя ему, видимо)
думал можно решить задачу только окном) и применяя в нем sql-expression, а оказывается нет)


Для такой задачи куда более правильно применять коррелированный подзапрос:

WITH employees(employee_id, department_id, salary) AS
(
  SELECT 1, 1, 100 FROM dual UNION
  SELECT 2, 1, 200 FROM dual UNION
  SELECT 3, 1, 200 FROM dual UNION
  SELECT 4, 1, 400 FROM dual UNION
  SELECT 5, 1, 300 FROM dual UNION
  SELECT 6, 2, 100 FROM dual UNION
  SELECT 7, 2, 200 FROM dual UNION
  SELECT 8, 2, 500 FROM dual UNION
  SELECT 9, 2, 500 FROM dual UNION
  SELECT 10, 2, 500 FROM dual UNION
  SELECT 11, 2, 500 FROM dual UNION
  SELECT 12, 2, 1000 FROM dual
)
SELECT 
  department_id, 
  salary, 
  (SELECT count(*) FROM employees WHERE salary>emp_outer.salary and department_id=emp_outer.department_id) "Сколько >" 
FROM employees emp_outer
ORDER BY 1,2;
31 май 15, 10:33    [17711898]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
vaneque
Member

Откуда: Тверь
Сообщений: 168
michael063
извращенный мозг преподавателя не дает покоя ему, видимо)
думал можно решить задачу только окном) и применяя в нем sql-expression, а оказывается нет)


Другой вариант - join, основанный на неравенстве(nonequijoin) с группировкой:

WITH employees(employee_id, department_id, salary) AS
(
  SELECT 1, 1, 100 FROM dual UNION
  SELECT 2, 1, 200 FROM dual UNION
  SELECT 3, 1, 200 FROM dual UNION
  SELECT 4, 1, 400 FROM dual UNION
  SELECT 5, 1, 300 FROM dual UNION
  SELECT 6, 2, 100 FROM dual UNION
  SELECT 7, 2, 200 FROM dual UNION
  SELECT 8, 2, 500 FROM dual UNION
  SELECT 9, 2, 500 FROM dual UNION
  SELECT 10, 2, 500 FROM dual UNION
  SELECT 11, 2, 500 FROM dual UNION
  SELECT 12, 2, 1000 FROM dual
)
SELECT 
  t1.employee_id, t1.department_id, t1.salary, count(t2.salary)
FROM employees t1
LEFT JOIN employees t2 ON t2.salary>t1.salary AND t1.department_id=t2.department_id
GROUP BY t1.employee_id, t1.department_id, t1.salary
ORDER BY 1,2,3;


покажите преподу оба варианта ;)
31 май 15, 10:41    [17711922]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
кит северных морей
Member

Откуда: krsk / nyc / krsk
Сообщений: 882
vaneque
куда более правильно применять
почему?
31 май 15, 17:25    [17712658]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
vaneque
Member

Откуда: Тверь
Сообщений: 168
кит северных морей
vaneque
куда более правильно применять
почему?


да по определению) в данной задаче требуется рассчитать на одну группу (сотрудник+подразделение+зарплата) одно скалярное значение (кол-во случаев, когда в этом же подразделении другие сотрудники зарабатывают больше). аналитические функции требуются, когда на одну группу возвращается множество значений. они не для таких задач предназначены.
31 май 15, 17:44    [17712714]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
кит северных морей
Member

Откуда: krsk / nyc / krsk
Сообщений: 882
vaneque,

интересная точка зрения, только вот непредназначенный для решения задачи инструмент почему-то отрабатывает вдвое быстрее, чем предназначенный, не долбясь при этом в базу со страшной силой.

+ ddl
create table dropme$emp(emp primary key, dept, sal) as
select rownum, mod(rownum, 1e5), rownum
from dual
connect by rownum <= 1e6
/

create index ix_emp_dept on dropme$emp(dept)
/
exec dbms_stats.gather_table_stats(user, 'dropme$emp');


  1  WITH employees(employee_id, department_id, salary) AS
  2    ( SELECT * FROM dropme$emp
  3    ),
  4    t(emp, dep, sal)  as
  5  (select * from dropme$emp )
  6  SELECT department_id,
  7    salary,
  8    (SELECT COUNT(*)
  9    FROM employees
 10    WHERE salary     >emp_outer.salary
 11    AND department_id=emp_outer.department_id
 12    ) "??????? >"
 13  FROM employees emp_outer
 14  intersect
 15  select dep, sal,
 16  rank() over(partition by dep order by sal desc) - 1
 17  how_many
 18* from t
12:16:58 SQL> /

1000000 rows selected. 

Elapsed: 00:02:03.27


12:22:07 SQL> l
  1  with t(emp, dep, sal)  as
  2  (select * from dropme$emp )
  3  select dep, sal,
  4  rank() over(partition by dep order by sal desc) - 1
  5  how_many
  6* from t
12:22:08 SQL> /

1000000 rows selected.

Elapsed: 00:00:56.44

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        682  consistent gets
          0  physical reads
          0  redo size
   13462583  bytes sent via SQL*Net to client
      16245  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

12:26:23 SQL> l
  1  WITH employees(employee_id, department_id, salary) AS
  2    ( SELECT * FROM dropme$emp )
  3  SELECT department_id,
  4    salary,
  5    (SELECT COUNT(*)
  6    FROM employees
  7    WHERE salary     >emp_outer.salary
  8    AND department_id=emp_outer.department_id
  9    ) cnt
 10* FROM employees emp_outer
12:26:25 SQL> /

1000000 rows selected.

Elapsed: 00:01:57.06

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   10044614  consistent gets
          0  physical reads
          0  redo size
   14962839  bytes sent via SQL*Net to client
      16245  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed


self-join ожидаемо лучше, но всё равно медленнее, да и запрос громоздкий.

12:31:57 SQL> l
  1  WITH employees(employee_id, department_id, salary) AS
  2  (select * from dropme$emp )
  3  SELECT
  4    t1.employee_id, t1.department_id, t1.salary, count(t2.salary)
  5  FROM employees t1
  6  LEFT JOIN employees t2 ON t2.salary>t1.salary AND t1.department_id=t2.department_id
  7* GROUP BY t1.employee_id, t1.department_id, t1.salary
12:31:58 SQL> /

1000000 rows selected.

Elapsed: 00:01:10.16

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1364  consistent gets
          0  physical reads
          0  redo size
   19944514  bytes sent via SQL*Net to client
      16245  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed
31 май 15, 19:42    [17712955]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
vaneque
Member

Откуда: Тверь
Сообщений: 168
спасибо за разбор предложенных вариантов, действительно, получается аналитическая функция более шустрая

кит северных морей
vaneque,

интересная точка зрения, только вот непредназначенный для решения задачи инструмент почему-то отрабатывает вдвое быстрее, чем предназначенный, не долбясь при этом в базу со страшной силой.

+ ddl
create table dropme$emp(emp primary key, dept, sal) as
select rownum, mod(rownum, 1e5), rownum
from dual
connect by rownum <= 1e6
/

create index ix_emp_dept on dropme$emp(dept)
/
exec dbms_stats.gather_table_stats(user, 'dropme$emp');


  1  WITH employees(employee_id, department_id, salary) AS
  2    ( SELECT * FROM dropme$emp
  3    ),
  4    t(emp, dep, sal)  as
  5  (select * from dropme$emp )
  6  SELECT department_id,
  7    salary,
  8    (SELECT COUNT(*)
  9    FROM employees
 10    WHERE salary     >emp_outer.salary
 11    AND department_id=emp_outer.department_id
 12    ) "??????? >"
 13  FROM employees emp_outer
 14  intersect
 15  select dep, sal,
 16  rank() over(partition by dep order by sal desc) - 1
 17  how_many
 18* from t
12:16:58 SQL> /

1000000 rows selected. 

Elapsed: 00:02:03.27


12:22:07 SQL> l
  1  with t(emp, dep, sal)  as
  2  (select * from dropme$emp )
  3  select dep, sal,
  4  rank() over(partition by dep order by sal desc) - 1
  5  how_many
  6* from t
12:22:08 SQL> /

1000000 rows selected.

Elapsed: 00:00:56.44

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        682  consistent gets
          0  physical reads
          0  redo size
   13462583  bytes sent via SQL*Net to client
      16245  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

12:26:23 SQL> l
  1  WITH employees(employee_id, department_id, salary) AS
  2    ( SELECT * FROM dropme$emp )
  3  SELECT department_id,
  4    salary,
  5    (SELECT COUNT(*)
  6    FROM employees
  7    WHERE salary     >emp_outer.salary
  8    AND department_id=emp_outer.department_id
  9    ) cnt
 10* FROM employees emp_outer
12:26:25 SQL> /

1000000 rows selected.

Elapsed: 00:01:57.06

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
   10044614  consistent gets
          0  physical reads
          0  redo size
   14962839  bytes sent via SQL*Net to client
      16245  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed


self-join ожидаемо лучше, но всё равно медленнее, да и запрос громоздкий.

12:31:57 SQL> l
  1  WITH employees(employee_id, department_id, salary) AS
  2  (select * from dropme$emp )
  3  SELECT
  4    t1.employee_id, t1.department_id, t1.salary, count(t2.salary)
  5  FROM employees t1
  6  LEFT JOIN employees t2 ON t2.salary>t1.salary AND t1.department_id=t2.department_id
  7* GROUP BY t1.employee_id, t1.department_id, t1.salary
12:31:58 SQL> /

1000000 rows selected.

Elapsed: 00:01:10.16

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1364  consistent gets
          0  physical reads
          0  redo size
   19944514  bytes sent via SQL*Net to client
      16245  bytes received via SQL*Net from client
       2001  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1000000  rows processed
31 май 15, 19:59    [17712994]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
vaneque
да по определению) в данной задаче требуется рассчитать на одну группу (сотрудник+подразделение+зарплата) одно скалярное значение (кол-во случаев, когда в этом же подразделении другие сотрудники зарабатывают больше). аналитические функции требуются, когда на одну группу возвращается множество значений. они не для таких задач предназначены.


Бред. Именно для таких задач они и предназначены. Сравни планы своего "правильного" решения и аналитического ршения oт кита северных морей. И вообще, абсолютно неважно что именно используется в решении если оно, readable, scalable, maintainable, performing, cost effective. Пропорцию выбирать по обстоятельствам.

SY.
31 май 15, 20:09    [17713013]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
ArtNick
Member

Откуда:
Сообщений: 1227
vaneque
спасибо за разбор предложенных вариантов, действительно, получается аналитическая функция более шустрая

наверное дело не в шустрости функции.
1 июн 15, 09:40    [17713902]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
vaneque
Member

Откуда: Тверь
Сообщений: 168
ага...оптимальная по совокупности критериев...

ArtNick
vaneque
спасибо за разбор предложенных вариантов, действительно, получается аналитическая функция более шустрая

наверное дело не в шустрости функции.
1 июн 15, 09:45    [17713913]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
michael063
Member

Откуда:
Сообщений: 34
прям тема разрослась) так приятно видеть умы тут)
спасибо ребята)
1 июн 15, 19:45    [17717228]     Ответить | Цитировать Сообщить модератору
 Re: Аналитические функции и условия больше меньше. как?  [new]
kaldorey
Member

Откуда:
Сообщений: 600
Если подумать, то любую аналитическую функцию можно переписать длинно с более простым синтаксисом. Аналитические как раз таки и призваны решить задачи слишком большого количества обращений к одним и тем же ресурсам. Это их прямое назначение
2 июн 15, 07:08    [17718252]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить