Выбор первых 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.