Порядковые номера строк в запросах PostgreSQL 8.4

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

Чтобы прикрепить порядковые номера к результатам запроса в PostgreSQL, можно использовать функцию ROW_NUMBER(), применяв ее вместе с предложением OVER(). Если требуется упорядочить результаты в конкретном порядке, добавьте ORDER BY в OVER().

В качестве образца запроса рассмотрим:

SELECT 
    ROW_NUMBER() OVER (ORDER BY column_to_sort) AS row_number,
    other_columns
FROM 
    table_name;

Вам следует заменить column_to_sort, other_columns и table_name на соответствующие названия столбцов и таблицы. В результате вы получите дополнительный столбец row_number, пронумерованный в заданном порядке.

Если не обязательно поддерживать порядок, и требуется только присвоить номера строк без сортировки, просто пропустите ORDER BY:

SELECT 
    ROW_NUMBER() OVER () AS row_number,
    *
FROM 
    table_name;

Такой запрос добавит столбец с номером строки row_number к неупорядоченному результату.

Работа с особыми сценариями

Справляемся с NULL значением

Если требуется расположить строки с NULL в столбце для сортировки в конце списка, примените NULLS LAST:

SELECT 
    ROW_NUMBER() OVER (ORDER BY column_to_sort NULLS LAST) AS row_number,
    *
FROM 
    table_name;

Это поместит строки с NULL в конец результатов.

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

Увеличиваем производительность при работе с большим объёмом данных

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

Сохраняем совместимость со старыми версиями PostgreSQL

В PostgreSQL до версии 8.4 функция ROW_NUMBER() отсутствовала, но можно эмулировать ее действие с использованием самосоединения (self-join):

SELECT 
    COUNT(t1.id) AS row_number,
    t2.*
FROM 
    table_name t1
JOIN 
    table_name t2 ON t1.id <= t2.id
GROUP BY 
    t2.id
ORDER BY 
    row_number;

Здесь вместо id подставьте имя столбца, который представляет собой первичный ключ.

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

В задаче найти определенное место на концерте представление выглядит следующим образом:

Каждому месту соответствует номер строки в результате запроса:

SELECT ROW_NUMBER() OVER (), * FROM concerts;

Таким образом, ROW_NUMBER() сохраняет порядок строк, аналогично порядку мест на концерте.

Обработка ситуаций, когда отсутствуют первичные ключи

Если в таблице отсутствуют первичные ключи, вы можете применить array_agg, однако стоит быть готовым к возможному снижению производительности.

WITH numbered_rows AS (
    SELECT array_agg(t.*) AS rows
    FROM your_table t
)

SELECT 
    row_number() OVER (),
    r.*
FROM 
    numbered_rows,
    LATERAL unnest(rows) as r;

Решаем особо сложные запросы

При составлении запросов с использованием сложных оконных функций оказывается полезным применение подзапросов или CTE:

WITH numbered AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY your_sort_column) AS row_number
    FROM 
        your_table
)

SELECT * FROM numbered;

Обмен информацией через SQL Fiddle

Платформа SQL Fiddle будет полезна для отладки и обмена запросами. Создавайте схемы и проверяйте SQL-код в интерактивном режиме.

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

1. Официальная документация PostgreSQL на тему оконных функций.
2. Замечания и типичные проблемы при применении нумерации строк в PostgreSQL.
3. Вводное руководство по использованию оконных функций для новичков в PostgreSQL.
4. Глубокое исследование оконных функций для всестороннего понимания и эффективной их реализации.