Блог

Главная - Вопросы по программированию - Запрос схемы Oracle SCOTT – какая зарплата ближе всего к средней?

Запрос схемы Oracle SCOTT – какая зарплата ближе всего к средней?

#sql #oracle #average

#sql #Oracle #средняя

Вопрос:

Использование схемы СКОТТА. Я пытаюсь найти человека, у которого зарплата ближе всего к средней. 

 SELECT sal
      FROM (  SELECT sal
                FROM emp
            ORDER BY ABS ( (SELECT AVG (SAL) FROM EMP) - sal))
     WHERE ROWNUM = 1;

Можно ли улучшить приведенное выше решение?

Ответ №1:

Вы используете нестандартную функцию Oracle. Данные в подзапросах изначально не имеют порядка, но в Oracle вы можете его упорядочить, чтобы иметь возможность применить критерии ROWNUM позже. ROWNUM также специфичен для Oracle.

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

Вот как выбрать сотрудника (сотрудников) с зарплатой, наиболее близкой к средней в стандартном SQL:

 select *
from emp
order by abs(sal - avg(sal) over())
fetch first row with ties;

Эта последняя строка доступна только начиная с Oracle 12c.

В более старых версиях (то есть Oracle 9i, 10i или 11g) вместо этого вы бы ранжировали свои строки:

 select empno, ename, sal
from
(
  select empno, ename, sal, rank() over (order by diff) as rnk
  from
  (
    select emp.*, abs(sal - avg(sal) over()) as diff
    from emp
  ) evaluated
) ranked
where rnk = 1;

Комментарии:

1. Привет, в моей базе данных 12c ваше предложение об ограничении строк выдает ошибку. Когда я меняю ваше предложение на FETCH FIRST ROW WITH TIES , оно работает.

Ответ №2:

Извлечение данных сотрудника с помощью подзапроса:

 select empno, sal, SalDif
from 
  (
    select empno, sal, abs(sal - (select avg(sal) from emp)) as SalDif
    from emp
    order by SalDif
  )
where rownum = 1;

или как CTE:

 with CTE as
  (
    select empno, sal, abs(sal - (select avg(sal) from emp)) as SalDif
    from emp
    order by SalDif
  )
select empno, sal, SalDif
from CTE
where rownum = 1;

Ответ №3:

 with av as (select avg(sal) avgsal from scott.emp)
select emp.*, abs(emp.sal-av.avgsal) dist 
  from scott.emp, av 
  order by dist;

Для меня это выглядит лучше, но, вероятно, вы можете получить это лучше всего с помощью аналитической функции. Что-то вроде:

 select * from (
  select emp.*, abs(avg(emp.sal) over () - sal) diff 
    from scott.emp order by diff) 
where rownum = 1;