Одна задачка на SQL

В феврале 2013 года мне посчастливилось участвовать в жюри региональных финалов олимпиады Oracle ИТ-Планета

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

Итак, одно из заданий было сформулировано так:

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

Сведения о сотрудниках, для которых неизвестно к какому подразделению они приписаны, выводить не нужно.

В результат вывести 5 (пять) столбцов:
1. Идентификатор сотрудника
2. Фамилию сотрудника
3. Имя сотрудника
4. Оклад, установленный сотруднику
5. Идентификатор подразделения, к которому приписан сотрудник

Результат отсортировать:
1. По окладу, установленный сотруднику (по убыванию)
2. По фамилии сотрудника (по возрастанию)
3. По имени сотрудника (по возрастанию)
4. По идентификатору сотрудника (по возрастанию)

Решением задачи является запрос:


SELECT employee_id, last_name, first_name, salary, department_id
FROM employees E
WHERE salary > (SELECT AVG(salary) FROM employees X
WHERE E.department_id = x.department_id)
ORDER BY salary DESC, last_name, first_name, employee_id;


Вообще говоря, запрос можно сформулировать множеством способов, например, применив преобразование устранение вложенности подзапросов или используя аналитические функции. Однако интересно, сколько и какие ошибки можно допустить в решении этой набившей оскомину задачи? Я, конечно, предполагал, как можно решить эту задачу неправильно, но такого количества различных неверных решений не ожидал. 🙂

1. Один из самых популярных вариантов. Невнимательность при чтении условия задачи


SELECT employee_id, last_name, first_name, salary, department_id
FROM employees
where salary > (select avg(emp.salary)
from employees emp)
order by salary desc, last_name, first_name, employee_id


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

2. Невнимательность при написании запроса


with avg_sal as
(select department_id, avg(salary) avg_salary
from employees
group by department_id
)
select employee_id
, last_name
, first_name
, salary
, e.department_id
from employees e
inner join avg_sal
on e.department_id = avg_sal.department_id
where salary > avg_salary
order by salary, last_name, first_name, employee_id


Аналогично решению выше, это решение также выглядит весьма правдоподобно, и даже выводит верный список работников. Увы, оно неверно, т.к. во фразе ORDER BY перепутан порядок сортировки по полю salary: ASC вместо DESC. На удивление, подобного рода ошибок было очень много: перепутан порядок сортировки, поля выборки, порядок полей в сортировке, при в целом верном решении задачи. Здесь можно дать только один совет: тщательнее читать условие задачи.

3. Использование таблицы с похожими данными


SELECT e.employee_id, e.LAST_name, e.first_name, e.salary, e.department_id
FROM employees e, jobs j
WHERE e.department_id IS NOT NULL
AND j.job_id=e.job_id
AND e.salary>(j.min_salary+j.max_salary)/2
ORDER BY 4 DESC, 2,3,1 ASC;


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

4. Неверная группировка


WITH more as
(
SELECT department_id, AVG(salary) as avgr
FROM employees
GROUP BY employee_id, last_name, first_name, department_id, salary
ORDER BY department_id
)
SELECT DISTINCT e.employee_id, e.last_name, e.first_name, e.salary, e.department_id
FROM more m, employees e
WHERE e.salary > m.avgr
AND e.department_id = m.department_id
AND e.department_id IS NOT NULL
ORDER by salary desc, last_name , first_name , employee_id ;


С первого взгляда в этом запросе есть все признаки верного решения: наличие группировки, AVG, Join. Проблема в полях группировки. Их слишком много. Например, присутствует первичный ключ таблицы, а это значит, что фактически никакой группировки выполняться не будет, как и вычисления средней зарплаты по отделу. В результате выбираются все сотрудники, кроме тех, у которых минимальная зарплата по подразделению.

5. Неверный предикат сравнения


select employee_id, last_name, first_name, e.salary, e.department_id
from employees e, (select Departments.department_id ,AVG(salary) as salary
from Departments join employees
on Departments.Department_id=employees.Department_id group by Departments.department_id) q
where q.Department_id=e.Department_id and e.salary>=q.salary
order by e.salary desc, last_name, first_name, employee_id


Это решение выбирает чуть больше записей, чем требуется. Проблема в предикате сравнения: “>=” вместо “>”, что означает “не меньше средней по отделу”, а должно быть строго больше по условию задачи.

6. Неправильное использование аналитики


SELECT EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
SALARY,
DEPARTMENT_ID FROM
(SELECT EMPLOYEE_ID,
LAST_NAME,
FIRST_NAME,
SALARY,
DEPARTMENT_ID,
SUM(salary) OVER (PARTITION BY department_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) lcsum,
COUNT(salary) OVER (PARTITION BY department_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) lcount
FROM employees
ORDER BY manager_id, last_name, salary)
WHERE salary > lcsum/lcount
AND DEPARTMENT_ID IS NOT NULL
ORDER BY SALARY DESC,
LAST_NAME,
FIRST_NAME,
EMPLOYEE_ID;


В этом решении неправильно записано условие вычисления суммы аналитической функцией. При такой записи получается нарастающий итог. Т.е. SUM(salary) OVER (PARTITION BY department_id) возвращает сумму по всему департаменту, а SUM(salary) OVER (PARTITION BY department_id ORDER BY salary ) возвращает нарастающий итог по сумме зарплаты для каждого департамента. В итоге запрос выводит неизвестно что.

7. Неожиданная сортировка


select employee_id,last_name,first_name,salary,department_id
from employees,(
select sum(salary) as s1, count(salary) as s2
from employees)
where salary>s1/s2
order by - salary,+ last_name,+ first_name,+ employee_id;


Удивительно, но этот запрос работает 🙂 Т.е. понятно, что запрос написан неправильно и его создатель, скорее всего, слабо знаком с синтаксисом и функциями языка. Однако секция сортировки выглядит оригинально. И в чем-то даже логично. Хотя order by – last_name написать уже нельзя, last_name – строковый тип данных.
Здесь дело в парсере SQL. Официального подтверждения этому нет, но похоже, что парсер Oracle просто выкидывает лидирующие плюсы в выражениях. Так что их можно безболезненно писать 🙂

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

В заключение хотелось бы привести пару правильных решений. Которые в нормальной ситуации также являются плохим решением задачи.

8. Плохой стиль


with q1 as (select department_id, avg(salary) s1 from employees where department_id is not null group by department_id)
select e.employee_id, e.last_name, e.first_name, e.salary, e.department_id from employees e, q1 where e.department_id=q1.department_id and e.salary>q1.s1 order by 4 desc, 2 asc, 3 asc, 1 asc


Поддерживать это решение невозможно. Дело не только в том, что очень сложно понять, что же конкретно делает этот запрос. Еще сложнее проверить, что он делает именно то, что нужно. Это очень наглядно видно во время подобного рода соревнований. Были еще несколько похожих, но неправильных вариантов. Этому решению просто повезло. Скорее всего, этот запрос не тестировали и просто случайно удалось написать его с первого раза правильно. Остальным же решениям не повезло. В нескольких похожих запросах были опечатки (это нормально), но стиль оформления запроса не позволил их увидеть перед отправкой на проверку.

Впрочем, это тема для отдельного разговора.

9. Лишние соединения в запросе


with sal as
(select d.department_id did, avg(salary) avgs from
departments d join employees e on d.department_id=e.department_id
group by e.department_id)
select e.employee_id, e.last_name, e.first_name, e.salary, d.department_id
from departments d join employees e on d.department_id=e.department_id
JOIN sal on sal.did=d.department_id
where e.salary>sal.avgs
order by salary desc, last_name, first_name, employee_id


В этом простом запросе умудрились сделать 2 лишних соединения. На эту тему уже было сказано достаточно много и в будущем будет сказано не меньше, но от базы данных требуется не только получить правильный результат, но и сделать это достаточно оперативно. Не стоит нагружать БД бесполезной работой и таким образом писать запросы. Иначе придется покупать Exadata 🙂

P.S. Эталонное решение также не идеально: таблица сотрудников сканируется дважды. Его можно улучшить, используя аналитику. Например, так:


SELECT employee_id, last_name, first_name, salary, department_id
FROM (SELECT employee_id, last_name, first_name, salary, department_id,
(e.salary - avg(e.salary)
OVER(PARTITION BY e.department_id)) sal_dif
FROM hr.employees e)
WHERE sal_dif > 0
ORDER BY salary DESC, last_name, first_name, employee_id


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

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 107 | 8346 | 5 (40)| 00:00:01 |
| 1 | SORT ORDER BY | | 107 | 8346 | 5 (40)| 00:00:01 |
|* 2 | VIEW | | 107 | 8346 | 4 (25)| 00:00:01 |
| 3 | WINDOW SORT | | 107 | 2782 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2782 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
—————————————————

2 – filter(“SAL_DIF”>0)