Аналитическая функция Oracle для минимального значения в группировке

Question

Я новичок в работе с аналитическими функциями.

DEPT EMP SALARY
---- ----- ------
  10 МАРЯ 100000
  10 ДЖОН 200000
  10 СКОТТ 300000
  20 БОБ 100000
  20 Бетти 200000
  30 ALAN 100000
  30 ТОМ 200000
  30 JEFF  300000

Я хочу отдел и сотрудник с минимальной зарплатой.

Результаты должны выглядеть так:

DEPT EMP SALARY
---- ----- ------
  10 МАРЯ 100000
  20 БОБ 100000
  30 ALAN 100000

РЕДАКТИРОВАТЬ: Вот SQL у меня есть (но, конечно, он не работает, так как он хочет, чтобы персонал в группе также по предложению):

ВЫБЕРИТЕ отдел, 
  эй,
  MIN(зарплата) KEEP (DENSE_RANK ПЕРВЫЙ ЗАКАЗ по зарплате)
ИЗ mytable
ГРУППА ПО ОТДЕЛУ

Answer 1

Я думаю, что функция Rank() не подходит для этого по двум причинам.

Во-первых, он, вероятно, менее эффективен, чем метод, основанный на Min().

Причиной этого является то, что запрос должен поддерживать упорядоченный список всех зарплат по отделам при сканировании данных, и затем ранг будет присвоен позже, перечитав этот список. Очевидно, что при отсутствии индексов, которые могут быть использованы для этого, вы не можете присвоить ранг до тех пор, пока не будет прочитан последний элемент данных, и обслуживание списка стоит дорого.

Таким образом, производительность функции Rank() зависит от общего количества проверяемых элементов, и, если их достаточно для сортировки на диск, производительность падает.

Это, вероятно, более эффективно:

select dept,
       emp,
       salary
from
       (
       SELECT dept, 
              emp,
              salary,
              Min(salary) Over (Partition By dept) min_salary
       FROM   mytable
       )
where salary = min_salary
/

Этот метод требует только, чтобы в запросе содержалось одно значение на отдел из минимального значения, которое встречалось до сих пор. Если встречается новый минимум, то существующее значение изменяется, в противном случае новое значение отбрасывается. Общее количество элементов, которые должны храниться в памяти, связано с количеством отделов, а не с количеством отсканированных строк.

Возможно, у Oracle есть путь к коду, позволяющий распознать, что в данном случае Ранг на самом деле вычислять не нужно, но я бы не стал на это ставить.

Вторая причина неприязни к Rank() заключается в том, что он просто отвечает на неправильный вопрос. Вопрос не в том, "Какая запись имеет зарплату, которая является первой по рейтингу, когда зарплата по отделу упорядочена по возрастанию", а в том, "Какая запись имеет минимальную зарплату на отдел". Это имеет большое значение для меня, по крайней мере.

Answer 2

Я думаю, что вы были довольно близки с вашим первоначальным запросом. Следующее будет выполнено и соответствует вашему тестовому примеру:

SELECT dept, 
  MIN(emp) KEEP(DENSE_RANK FIRST ORDER BY salary, ROWID) AS emp,
  MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY salary, ROWID) AS salary
FROM mytable
GROUP BY dept

В отличие от решений RANK(), это гарантирует не более одного ряда на отдел. Но это намекает на проблему: что происходит в отделе, где два сотрудника получают самую низкую зарплату? Решения RANK() вернут обоих сотрудников - более одного ряда для отдела. Этот ответ выберет один произвольно и убедится, что для отдела есть только один.

Answer 3

Вы можете использовать RANK() синтаксис. Например, этот запрос скажет вам, где сотрудник занимает место в своем отделе относительно того, какова его зарплата:

SELECT
  dept,
  emp,
  salary,
  (RANK() OVER (PARTITION BY dept ORDER BY salary)) salary_rank_within_dept
FROM EMPLOYEES

Вы можете запросить здесь salary_rank_within_dept = 1:

SELECT * FROM
  (
    SELECT
      dept,
      emp,
      salary,
      (RANK() OVER (PARTITION BY dept ORDER BY salary)) salary_rank_within_dept
    FROM EMPLOYEES
  )
WHERE salary_rank_within_dept = 1

Answer 4

select e2.dept, e2.emp, e2.salary
from employee e2
where e2.salary = (select min(e1.salary) from employee e1)