Найдите вторую по величине зарплату в каждом отделе, если у нескольких сотрудников она одинаковая
Проверила команда Сопровод · Обновлено
Короткий ответ
Пронумеруйте зарплаты внутри каждой клиники через dense_rank() OVER (PARTITION BY clinic ORDER BY salary DESC) и во внешнем запросе оставьте строки с рангом 2. DENSE_RANK дает равным зарплатам один ранг и не пропускает номера, поэтому ранг 2 — это вторая по величине сумма. Добавьте DISTINCT: вторую зарплату могут получать несколько врачей.
Почему этот вопрос задают
Классическая задача на оконные функции с подвохом в данных. По ней видно, различаете ли вы ROW_NUMBER, RANK и DENSE_RANK и знаете ли, на каком шаге запроса считается окно.
Условие задачи
Сеть ветеринарных клиник хранит врачей в таблице vets(id, clinic, name, salary), отдел здесь — клиника. Для обзора зарплат нужна вторая по величине зарплата в каждой клинике. В «Северной» и «Южной» максимум получают по два врача, в «Центральной» двое делят второе место. Выведите клинику и вторую зарплату, по одной строке на клинику, в порядке названий клиник.
Как подойти к решению
- Договоритесь, что значит «вторая»: вторая по величине сумма без учета повторов
- Посчитайте
dense_rank()по убыванию зарплаты внутри каждой клиники - Вынесите окно в подзапрос и фильтруйте ранг во внешнем запросе
- Уберите повторы через
DISTINCT, если вторую зарплату делят несколько врачей
Решение
Сортировка окна по убыванию делает максимум рангом 1. В «Северной» ранги идут 1, 1, 2, 3, и ранг 2 достается 75000. В «Центральной» ранг 2 у двух врачей, и DISTINCT сворачивает их в одну строку. Клиника, где у всех одна зарплата, в ответ не попадет: ранга 2 там нет.
SELECT DISTINCT clinic, salary AS second_salary
FROM (
SELECT clinic, salary,
dense_rank() OVER (PARTITION BY clinic ORDER BY salary DESC) AS rnk
FROM vets
) AS ranked
WHERE rnk = 2
ORDER BY clinic;clinic | second_salary -------------+--------------- Северная | 75000 Центральная | 80000 Южная | 70000 (3 rows)
Что спросят следом
Как решить задачу без оконных функций?
Как показать клинику с
NULL, если второй зарплаты там нет?А если нужны имена врачей со второй зарплатой?
Доступно по подписке
Ответы на эти вопросы
Частые ошибки
«Возьму
ROW_NUMBERи строку номер 2»ROW_NUMBERнумерует строки подряд, и при двух одинаковых максимумах вторая строка снова даст максимум. В «Северной» вы получите 90000 вместо 75000«
RANKподойдет, он учитывает равенство»RANKпосле двух первых мест ставит сразу 3, и ранга 2 в «Северной» нет вовсе. Без пропусков номера ставит толькоDENSE_RANK«Отфильтрую прямо в
WHERE dense_rank() OVER (...) = 2»Оконные функции считаются после
WHERE, и PostgreSQL выдаст ошибку. Посчитайте ранг в подзапросе или CTE и фильтруйте снаружи«Отсортирую окно по возрастанию и возьму предпоследний ранг»
Тогда придется знать число рангов в каждой клинике, и запрос усложнится. Сортируйте по убыванию: вторая сверху зарплата всегда получит ранг 2
Сигналы для интервьюера
Сильный ответ
Без подсказки обобщает запрос на N-ю зарплату: меняет одну цифру в условии и объясняет, почему этого хватает
Тревожный сигнал
Решает через SELECT DISTINCT salary … ORDER BY salary DESC LIMIT 1 OFFSET 1, что годится только для одной клиники, и не замечает, что их несколько
Красный флаг
Считает, что при одинаковых зарплатах у задачи нет ответа, и предлагает объявить такие строки ошибкой данных
Как еще могут спросить
- Как найти N-ю по величине зарплату в каждой группе?
- Напишите запрос, который вернет вторую максимальную зарплату по отделам
- Как с помощью
DENSE_RANKнайти вторую зарплату, если есть одинаковые значения? - Как получить второе по величине значение в каждой группе с учетом повторов?











