SQL Interview Prep · Урок

Вторая по величине зарплата: пять способов

Сравнивайте решения с подзапросом, LIMIT/OFFSET и оконными функциями.

«Вторая по величине зарплата: пять способов» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 1 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Вопрос, который задают всем

«Найдите вторую по величине зарплату» — самый часто задаваемый вопрос на собеседованиях по базам данных. Интервьюеры любят его, потому что у него есть несколько правильных решений и несколько неочевидных ловушек.

Предположим, что есть таблица employee со столбцами id и salary. Ваша задача — вернуть второе по величине различное значение зарплаты.

Если зарплаты равны 300, 200, 200, 100, ответ — 200, а не вторая строка.
Если второго различного значения зарплаты нет, обычно ожидаемый ответ — NULL.

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

CREATE TABLE employee (
  id     INT PRIMARY KEY,
  salary INT
);

Способ 1: MAX значений, меньших MAX

Самое интуитивное решение: вторая по величине зарплата — это наибольшая зарплата, которая строго меньше общего максимального значения.

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

Дополнительный плюс: если второго различного значения зарплаты нет, внешний MAX агрегирует ноль строк и автоматически возвращает NULL. Именно этот бесплатный NULL и нужен интервьюерам.

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Почему подзапрос обрабатывает дубликаты

Обратите внимание: в способе 1 мы ни разу не использовали DISTINCT, однако дубликаты обрабатываются правильно.

Если три человека получают 200, а сотрудник с самой высокой зарплатой получает 300, внутренний запрос возвращает 300. Внешний фильтр оставляет все строки со значением меньше 300, а MAX среди них равен 200 независимо от количества значений 200.

Вот ключевая идея: агрегатные функции сами сворачивают дубликаты. Многие кандидаты усложняют решение, добавляя DISTINCT, хотя агрегатная функция уже делает всё правильно.

Способ 2: LIMIT с OFFSET

В MySQL и PostgreSQL можно отсортировать различные зарплаты по убыванию и пропустить первую.

OFFSET 1 пропускает максимальное значение.
LIMIT 1 оставляет только следующее значение.

DISTINCT здесь необходим, иначе повторяющиеся максимальные зарплаты приведут к тому, что OFFSET 1 укажет на повтор максимума, а не на настоящее второе по величине значение.

Ловушка: если второго различного значения нет, возвращается ноль строк, а не NULL. Мы исправим этот пограничный случай на уроке 4.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Способ 3: FETCH для SQL Server и Oracle

SQL Server и современные версии Oracle не поддерживают LIMIT ... OFFSET. Вместо этого они используют синтаксис стандарта ANSI OFFSET ... FETCH.

Логика идентична способу 2: отсортировать различные зарплаты по убыванию, пропустить одну строку и извлечь одну. Знание синтаксиса для разных диалектов показывает интервьюеру, что у вас есть опыт работы в реальных проектах.

SELECT DISTINCT salary
FROM employee
ORDER BY salary DESC
OFFSET 1 ROWS
FETCH NEXT 1 ROWS ONLY;

Способ 4: оконная функция DENSE_RANK

Современный масштабируемый подход использует оконную функцию. DENSE_RANK присваивает ранг 1 самой высокой зарплате, ранг 2 — следующей различной зарплате, а сотрудникам с одинаковой зарплатой присваивает один и тот же ранг без пропусков.

Мы вычисляем ранг в подзапросе, а затем во внешнем запросе отбираем строки с рангом 2. Помните: нельзя напрямую фильтровать по оконной функции в WHERE, поэтому оболочка подзапроса обязательна.

SELECT salary AS second_highest
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) ranked
WHERE rnk = 2;

Почему DENSE_RANK, а не RANK и ROW_NUMBER

Выбор функции ранжирования важен для семантики «различных значений»:

ROW_NUMBER присваивает каждой строке уникальный номер, поэтому два человека с зарплатой 300 окажутся в строках 1 и 2, а ранг 2 будет повтором максимальной зарплаты. Неверно.
RANK оставляет пропуски после одинаковых значений: две зарплаты 300 получают ранг 1, а следующая зарплата — ранг 3. На ранге 2 вы её пропустите. Неверно.
DENSE_RANK присваивает одинаковым значениям один и тот же ранг и не оставляет пропусков, поэтому ранг 2 всегда соответствует второй различной зарплате. Верно.

Способ 5: подсчёт в коррелированном подзапросе

Классический приём, существовавший до появления оконных функций: зарплата является N-й по величине, если строго выше неё находятся ровно N минус 1 различных зарплат.

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

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

SELECT salary AS second_highest
FROM employee e
WHERE 1 = (
  SELECT COUNT(DISTINCT e2.salary)
  FROM employee e2
  WHERE e2.salary > e.salary
);

Разобранный пример от начала до конца

Возьмём зарплаты: 500, 500, 350, 350, 100.

Способ 1: MAX равен 500, наибольшее значение меньше 500 — 350. Ответ: 350.
Способ 4 (DENSE_RANK): 500 -> ранг 1, 350 -> ранг 2, 100 -> ранг 3. Ранг 2 — это 350.
Способ 5: для зарплаты 350 ровно одна различная зарплата (500) больше неё. Условие выполнено. Ответ: 350.

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

Какой способ выбрать

Рекомендации для собеседования:

Сначала сформулируйте вопрос: «Вам нужны различные зарплаты и NULL, если таких нет?» Уточняющий вопрос принесёт вам дополнительные баллы.
DENSE_RANK — лучший вариант по умолчанию; он легко обобщается на N-ю позицию и на группы.
MAX ниже MAX — лучшее однострочное решение, которое автоматически возвращает NULL.
LIMIT/OFFSET — краткий вариант, но он зависит от диалекта и в пограничном случае не возвращает строк.

Именно умение вслух объяснить компромиссы отличает ответ специалиста среднего уровня от ответа начинающего специалиста.

Распространённые ошибки, которых следует избегать

Обратите внимание на ловушки, которые интервьюеры намеренно расставляют:

Использовать ROW_NUMBER вместо DENSE_RANK и получить максимальную зарплату дважды.
Забыть DISTINCT в варианте с LIMIT/OFFSET, если максимальная зарплата встречается несколько раз.
Предположить, что ORDER BY salary DESC LIMIT 1,1 возвращает различное значение (это не так).
Вернуть вторую строку, а не второе значение.

Быстрая проверка

Проверьте, насколько хорошо вы поняли выбор функции ранжирования.

Итоги

Теперь вы знаете пять способов найти вторую по величине зарплату:

MAX ниже MAX — переносимый вариант, который автоматически возвращает NULL.
LIMIT/OFFSET и OFFSET/FETCH — краткие варианты, зависящие от диалекта.
DENSE_RANK — масштабируемый вариант по умолчанию, который правильно обрабатывает равные значения.
Коррелированный подсчёт — элегантный подход, обобщаемый на N-ю позицию.

Главные выводы: выясняйте, нужны ли вам различные значения, предпочитайте DENSE_RANK для обработки равных значений и помните, какие методы возвращают NULL, а какие — не возвращают строк, если второго значения нет.

Часто задаваемые вопросы

Урок «Вторая по величине зарплата: пять способов» бесплатный?

Да — полный текст урока «Вторая по величине зарплата: пять способов» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Вторая по величине зарплата: пять способов»?

Сравнивайте решения с подзапросом, LIMIT/OFFSET и оконными функциями. Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 1 из 4.

Сколько времени занимает урок «Вторая по величине зарплата: пять способов»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Вторая по величине зарплата: пять способов
N-е по величине значение с DENSE_RANK
Самый высокооплачиваемый сотрудник отдела
Возврат NULL при отсутствии N-го значения

← Назад к SQL Interview Prep