Оконные функции возвращают одно значение по всем строкам или по группе строк.
Оконные функции очень похожи на агрегатные функции, только они не изменяют итоговое количество строк, а вычисляют результат для каждой строки.
Важно знать момент вычисления оконных функций в выполнении запроса. Они вычисляются после соединения таблиц во 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 |
---|---|
1 | 1321800000.00 |
Оконная функция:
SELECT pp.*,
max(pp.price) OVER () AS max_price
FROM product_price pp
# | product_id | store_id | price | max_price |
---|---|---|---|---|
1 | 1 | 300 | 10500.00 | 1321800000.00 |
2 | 10 | 400 | 75600.00 | 1321800000.00 |
3 | 8 | 400 | 37000.00 | 1321800000.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_num | name |
---|---|---|
1 | 1 | Аудиотехника |
2 | 2 | Бытовая техника |
3 | 3 | Игровые консоли |
4 | 4 | Ноутбуки |
5 | 5 | Ноутбуки и аксессуары |
6 | 6 | Рюкзаки |
7 | 7 | Сотовые телефоны |
8 | 8 | Товары для дома |
9 | 9 | Фотоаппараты |
10 | 10 | Цифровая техника |
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_num | name |
---|---|---|
1 | 1 | Аудиотехника |
2 | 2 | Бытовая техника |
3 | 3 | Игровые консоли |
4 | 4 | Ноутбуки |
5 | 5 | Ноутбуки и аксессуары |
6 | 6 | Рюкзаки |
7 | 7 | Сотовые телефоны |
8 | 8 | Товары для дома |
9 | 9 | Фотоаппараты |
10 | 10 | Цифровая техника |
А можно отсортировать в другом порядке:
SELECT row_number() OVER (ORDER BY c.name) as row_num,
c.name
FROM category c
ORDER BY c.name DESC
# | row_num | name |
---|---|---|
1 | 10 | Цифровая техника |
2 | 9 | Фотоаппараты |
3 | 8 | Товары для дома |
4 | 7 | Сотовые телефоны |
5 | 6 | Рюкзаки |
6 | 5 | Ноутбуки и аксессуары |
7 | 4 | Ноутбуки |
8 | 3 | Игровые консоли |
9 | 2 | Бытовая техника |
10 | 1 | Аудиотехника |