Получение номера строки

Оконные функции возвращают одно значение по всем строкам или по группе строк.

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

Важно знать момент вычисления оконных функций в выполнении запроса. Они вычисляются после соединения таблиц во FROM, отсечения строк в WHERE и группировки GROUP BY и HAVING, но перед сортировкой результата запроса через ORDER BY. Поэтому оконные функции можно использовать в списке выборки (сразу после SELECT) и ORDER BY.

SELECT можно_здесь
    FROM ...
 WHERE ...
 GROUP BY ...
HAVING ...
 ORDER BY и_здесь 

Вычисление максимального значения

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

Агрегатная функция:

SELECT max(pp.price) AS max_price
  FROM product_price pp

Оконная функция:

SELECT pp.*,
       max(pp.price) OVER () AS max_price
  FROM product_price pp

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

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

Синтаксис

Общий синтаксис вызова оконных функций:

функция(...) OVER (условия для группировки строк и сортировки)

Оконную функцию можно отличить от агрегатной по ключевому слову OVER.

Определение номера строки

Для определение номера строки воспользуемся оконной функцией row_number()

SELECT row_number() OVER (ORDER BY c.name) as row_num,
       c.name
  FROM category c

row_number - оконная функция, которая возвращает номер строки в порядке, указанном в ORDER BY внутри OVER.

ORDER BY внутри OVER не связан с сортировкой строк в результате запроса. Результат запроса нужно сортировать явно через ORDER BY. То, что в результате мы получили отсортированные в алфавитном порядке строки, можно считать случайностью (конечно, они не случайно в таком порядке вернулись, но порядок может измениться в любой момент).

Отсортируем результат:

SELECT row_number() OVER (ORDER BY c.name) as row_num,
       c.name
  FROM category c
 ORDER BY row_num

А можно отсортировать в другом порядке:

SELECT row_number() OVER (ORDER BY c.name) as row_num,
       c.name
  FROM category c
 ORDER BY c.name DESC