Оконные функции

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

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

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

Важно знать момент вычисления оконных функций в выполнении запроса. Они вычисляются после соединения таблиц во 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
#max_price
11321800000.00

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

SELECT pp.*,
       max(pp.price) OVER () AS max_price
  FROM product_price pp
#product_idstore_idpricemax_price
1130010500.001321800000.00
21040075600.001321800000.00
3840037000.001321800000.00
...............

При использовании агрегатной функции получили одну единственную строку с максимальным значением. При использовании оконной функции получили столько же строк, сколько в таблице 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_numname
11Аудиотехника
22Бытовая техника
33Игровые консоли
44Ноутбуки
55Ноутбуки и аксессуары
66Рюкзаки
77Сотовые телефоны
88Товары для дома
99Фотоаппараты
1010Цифровая техника

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
#row_numname
11Аудиотехника
22Бытовая техника
33Игровые консоли
44Ноутбуки
55Ноутбуки и аксессуары
66Рюкзаки
77Сотовые телефоны
88Товары для дома
99Фотоаппараты
1010Цифровая техника

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

SELECT row_number() OVER (ORDER BY c.name) as row_num,
       c.name
  FROM category c
 ORDER BY c.name DESC
#row_numname
110Цифровая техника
29Фотоаппараты
38Товары для дома
47Сотовые телефоны
56Рюкзаки
65Ноутбуки и аксессуары
74Ноутбуки
83Игровые консоли
92Бытовая техника
101Аудиотехника
ПредыдущаяСледующая