Выбор первых N строк из каждой группы в PostgreSQL

Быстрый ответ

Если вам надо получить первые N строк для каждой группы в PostgreSQL, примените оконную функцию ROW_NUMBER() в подзапросе, определив столбец для группировки и сортировки. После этого фильтруйте результат, используя условие rn <= N во внешнем запросе.

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY order_column) AS rn
    FROM table_name
) AS ranked
WHERE ranked.rn <= N;

Для group_column укажите имя столбца, используемого для группировки; для order_column — имя столбца, по которому производится сортировка в группах; вместо table_name подставьте имя таблицы; N замените на желаемое число записей в каждой группе.

Подробности

Функция ROW_NUMBER(), появившаяся в PostgreSQL начиная с версии 8.4, присваивает уникальный номер каждой строке в результатах запроса. Использование ключевого слова PARTITION BY вместе с указанием group_column позволяет независимо нумеровать строки в разных группах. Порядок сортировки внутри каждой группы устанавливается с помощью ORDER BY, а условие WHERE rn <= N ограничивает выборку до N записей в каждой группе.

Что нужно знать об эффективности?

Для повышения производительности можно применить correlated subqueries с клаузой ORDER BY, выполненной до применения LIMIT. Создание индексов на group_column и order_column значительно ускорит выполнение запроса. Индексирование позволяет PostgreSQL более эффективно использовать индексы, сокращая время, затрачиваемое на сортировку, и увеличивая скорость извлечения данных.

При работе с большими объемами данных ценными становятся вложенные запросы и тщательно продуманное условие WHERE, влияющее на производительность. Если запросы выполняются слишком медленными, стоит рассмотреть возможность обновления версии PostgreSQL для использования более современных оптимизаторов.

Подробнее об этом расскажет наш спикер на видео

Как работать с разреженными данными?

Если в группах имеется менее чем N записей и необходимо исключить значения null, к вашим услугам функции COALESCE или LEFT JOIN. Они помогут корректно обрабатывать пропущенные значения и обеспечат эффективное представление данных с пробелами.

Визуализация

Представьте себе школьную фотосессию. Каждый класс выстроился в ряд:

Класс A (👩‍🎓👨‍🎓🧑‍🎓): [Алиса, Боб, Чарли, ...]
Класс B (👩‍🔬👨‍🔬🧑‍🔬): [Энни, Брайан, Крис, ...]

Наша цель – сделать фотографии первых N учеников из каждого класса.

SELECT * 
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY classroom ORDER BY name) AS row
  FROM school
) photoshoot
WHERE row <= N;

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

📸 Класс A: [👩‍🎓, 👨‍🎓]
📸 Класс B: [👩‍🔬, 👨‍🔬]

Таким образом, мы видим в годовом альбоме первые N учеников каждого класса.

Когда стандартные ограничения не подходят – применяем объединения LATERAL

Для коррелированных подзапросов и сложных запросов отлично подходят соединения LATERAL. Благодаря такому мощному инструменту, можно эффективно ограничивать количество записей в группах.

SELECT *
FROM table_name t,
LATERAL (
    SELECT *
    FROM other_table
    WHERE other_table.group_column = t.group_column
    ORDER BY other_table.order_column
    LIMIT N
) AS lateral_subquery;

Без повторений, пожалуйста – только уникальные результаты

Если вам необходимы уникальные идентификаторы групп, используйте DISTINCT ON в сочетании с ORDER BY для получения первой строки каждой группы по определенному порядку.

SELECT DISTINCT ON (section_id) *
FROM your_table
ORDER BY section_id, custom_order_column;

Однако, учтите, что DISTINCT ON может быть неэффективным при работе с большими объемами данных, поэтому предпочтительней все же использовать ROW_NUMBER().

Группировка результатов по группам

Чтобы преподнести результаты удобным способом, можно сгруппировать их по соответствующим идентификаторам.

SELECT section_id, array_agg(name ORDER BY name) AS names
FROM (
    SELECT section_id, name
    FROM (
        SELECT section_id, name, ROW_NUMBER() OVER (PARTITION BY section_id ORDER BY name) AS row_num
        FROM students
    ) subquery
    WHERE row_num <= N
) AS subquery_grouped
GROUP BY section_id;

Таким образом мы организуем ежегодник, показывая первые N учеников из каждого класса, сгруппированных по section_id.

Полезные материалы

1. PostgreSQL: Документация: 16: 7.8. Запросы WITH (Общие Табличные Выражения) — главная информация о сложных SQL-запросах.
2. PostgreSQL's Powerful New Join Type: LATERAL | Heap — обзор новых возможностей PostgreSQL.
3. PostgreSQL: Документация: 16: 4.2. Выражения Значений — оконные функции как инструменты SQL.
4. PostgreSQL – Подзапросы — руководство по подзапросам в PostgreSQL.
5. Введение в массивы – PostgreSQL wiki — информация о работе с массивами в PostgreSQL.