Выбор максимального значения по группам (SQL)

Допустим, у нас стоит задача («фактуру» беру от фонаря, чисто чтоб под иллюстрацию решений канало): есть у нас, к примеру, таблица учеников (Students), в ней, помимо прочего, есть такие поля, как ФИО (Name), класс (ClassId) и показатель успеваемости (Performance). Нам надо (ну, скажем, для доски почёта) выбрать учеников с наилучшей успеваемостью по каждому классу.

Запущено всё это на MS SQL Server.

Так вот.

Есть решение очевидное (для нахватавшегося SQL по верхам) и есть решение правильное (для умеющего в хитрозадые особенности).

Очевидное решение (для тех, кто дальше джойнов не зашёл): скрестить таблицу учеников саму с собой, и выбрать по каждому классу тех, для кого нет никого с лучшей успеваемостью.

В переводе с русского на SQL это запишется так:

SELECT stud.Name

FROM Students stud

LEFT JOIN Students better

ON stud.ClassId = better.ClassId AND stud.Performance < better.Performance

WHERE better.Performance IS NULL

Чем это решение хорошо? Тем, что джуну оно сразу придёт в голову, и тем, что оно универсально, т.е. работать будет на любом диалекте SQL, хоть на MySQL, хоть на самопалке какой (если она правильно реализована), хоть на самых первых версиях.

Чем оно плохо? ДА ОНО ОХРЕНЕТЬ ТОРМОЗНУТОЕ!!!!! Скорость такого запроса будет О(n²), это застрелиться и не встать. Нет, то есть конкретно для отдельной школы, конечно, разницу особо и не видать, может быть, будет. Но если мы работаем с базой по всей стране (конечно, в этом случае или ClassId должен быть уникален для каждой школы, или нужно брать пару ClassId, SchoolId), мы ждать заколебнёмся.

Теперь внимание, правильный ответ для конкретной ситуации, что мы используем MS SQL Server достаточно новой версии.

Надо: разбить базу по классам, в каждом отсортировать по успеваемости и выбрать лучших.

Вот как это звучит на SQL:

SELECT TOP 1 WITH TIES Name FROM Students

ORDER BY RANK() OVER (PARTITION BY ClassId ORDER BY Performance DESC)

Работает – мгновенно.

p.S. В связи с тем, что Microsoft всё же компания американская, имеет смысл вместо их продукции использовать СУБД с открытым кодом. Чаще всего в этом качестве берётся PostgreSQL. К сожалению, прямо-прямо написать эквивалент такого решения я навскидку и без проверки на какой-нибудь плюшевой базе не берусь (пока в базах данных я не волшебник, я только лечусь). Однако помню, что требуемые конструкции имеются (вместо TOP выступает LIMIT, но нужно покорячиться для получения эффекта TOP WITH TIES – однако, начиная с PostgreSQL 13 появилось и WITH TIES; а эквивалент OVER PARTITION наизусть не помню, но тоже где-то был).

#лаборофилия