Получение определенной строки среди результатов группировки

#postgresql #group_by

Необходимый результат от SQL-запроса:


Сгруппировать строки
Выделить одну из строк внутри каждой группы (например, строку с минимальным ID)
Вернуть для выбранных строк некоторые поля, не участвующие в фильтрации в предыдущем
пункте


Например, для таблицы users ( id, birthdate, department_id, ... ) получить ID самого
младшего сотрудника в каждом отделе.
В доках PostgreSQL по аггрегирующим функциям похожую проблему решают при помощи вложенных
запросов. Для приведенного выше примера выйдет что-то такое:  

select department_id, min(id) as youngest_user_id
from users users_outer
where birthdate = (
  select max(birthdate)
  from users users_inner
  where users_inner.department_id = users_outer.department_id
)
group by department_id


Вопрос: можно ли достичь того же результата, но без использования подзапросов?
    

Ответы

Ответ 1

Вообще без использования подзапросов достичь того же результата нельзя. Ведь над
данными действительно нужно проделать две различные операции, которые никак не могут
быть сведены к одной, пусть и более сложной, поскольку вторая операция (выбор записи
с максимальной birthdate) даже для того, чтобы просто начать выполняться для какого-то
конкретного department_id, уже требует полного завершения первой операции (группировки
всех данных по department_id).

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

Тем не менее, в PostgreSQL есть фича, специально предназначенная как раз для таких
трюков - Window Functions. Эта фича позволяет описывать так называемые окна, которые
по сути представляют собой группы записей без обязательного схлопывания каждой получившейся
группы в единственную запись. Исходные записи не заменяются результатом групповой агрегации,
а просто дополняются одним или несколькими столбцами, которые содержат результат той
или иной агрегирующей функции.

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

select department_id, id as youngest_user_id
from (
    select *, 
        row_number() over (partition by department_id order by birthdate desc) as num
    from users
) as s
where num = 1