Как выбрать первые n строк из каждой категории в PostgreSQL?

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

Это был особый тип запроса, потому что в данном случае мы не можем использовать limit. Первое, что приходит на ум при запросе этого типа, - это использовать оператор UNION, который является наивным решением и наименее эффективным, поскольку мы должны написать запрос, определяющий каждый идентификатор задания. Запрос будет выглядеть так:

select * from score_data where job_id=1 order by score desc limit 3
UNION
select * from score_data where job_id=2 order by score desc limit 3
UNION
select * from score_data where job_id=3 order by score desc limit 3
.
.
.
select * from score_data where job_id=n order by score desc limit 3

После некоторого исследования я пришел к Оконным функциям, которые, согласно определению:

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

Одна из функций - rank(), с помощью которой мы можем добавить новый столбец, который содержит ранг текущей строки в каждой группе.

Мы генерируем рейтинг, используя следующий запрос:

SELECT score_data.*, 
  rank() OVER (PARTITION BY job_id ORDER BY score DESC) 
  FROM score_data

В таблице score_data есть столбцы: id, job_id, scheme_id, score.
Давайте посмотрим, что происходит в приведенном выше запросе. OVER указывает, как разделить или окномить строки, внутри которых мы указали правило разделения. PARTITION BY разбивает таблицу на разделы на основе job_id и ORDER BY упорядочивает строки на основе score внутри раздела. Это добавляет новый столбец с именем rank в набор результатов. rank сохраняет ранг строки в своей группе.

Результат вышеуказанного запроса выглядит так:

Теперь нам нужно получить трех лучших кандидатов для каждого job_id, для которого мы запустим дополнительный запрос на выборку для этого выбора, где ранг будет ≤3.

select ranked_scores.* from 
(SELECT score_data.*,
  rank() OVER (PARTITION BY job_id ORDER BY score DESC)
  FROM score_data) ranked_scores 
where rank <=3

Что дает наш требуемый результат.

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

Спасибо!