Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.
Для начала хочу сразу пояснить, что оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Т.е. для простоты понимания можно считать, что postgres сначала выполняет весь запрос (кроме сортировки и limit), а потом только просчитывает оконные выражения.
Синтаксис примерно такой:
функция OVER окно
Окно — это некоторое выражение, описывающее набор строк, которые будет обрабатывать функция и порядок этой обработки.
Причем окно может быть просто задано пустыми скобками (), т.е. окном являются все строки результата запроса.
Например, в этом селекте к обычным полям id, header и score просто добавится нумерация строк.
SELECT
id,
section,
header,
score,
row_number() OVER () AS num
FROM news;
id | section | header | score | num
----+---------+-----------+-------+-----
1 | 2 | Заголовок | 23 | 1
2 | 1 | Заголовок | 6 | 2
3 | 4 | Заголовок | 79 | 3
4 | 3 | Заголовок | 36 | 4
5 | 2 | Заголовок | 34 | 5
6 | 2 | Заголовок | 95 | 6
7 | 4 | Заголовок | 26 | 7
8 | 3 | Заголовок | 36 | 8
В оконное выражение можно добавить ORDER BY, тогда можно изменить порядок обработки.
SELECT
id,
section,
header,
score,
row_number() OVER (ORDER BY score DESC) AS rating
FROM news
ORDER BY id;
id | section | header | score | rating
----+---------+-----------+-------+--------
1 | 2 | Заголовок | 23 | 7
2 | 1 | Заголовок | 6 | 8
3 | 4 | Заголовок | 79 | 2
4 | 3 | Заголовок | 36 | 4
5 | 2 | Заголовок | 34 | 5
6 | 2 | Заголовок | 95 | 1
7 | 4 | Заголовок | 26 | 6
8 | 3 | Заголовок | 36 | 3
Обратите внимание, что я добавил еще и в конце всего запоса ORDER BY id, при этом рейтинг посчитан все равно верно. Т.е. посгрес просто отсортировал результат вместе с результатом работы оконной функции, один order ничуть не мешает другому.
Дальше — больше. В оконное выражение можно добавить слово PARTITION BY [expression],
например row_number() OVER (PARTITION BY section), тогда подсчет будет идти в каждой группе отдельно:
SELECT
id,
section,
header,
score,
row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section
FROM news
ORDER BY section, rating_in_section;
id | section | header | score | rating_in_section
----+---------+-----------+-------+-------------------
2 | 1 | Заголовок | 6 | 1
6 | 2 | Заголовок | 95 | 1
5 | 2 | Заголовок | 34 | 2
1 | 2 | Заголовок | 23 | 3
4 | 3 | Заголовок | 36 | 1
8 | 3 | Заголовок | 36 | 2
3 | 4 | Заголовок | 79 | 1
7 | 4 | Заголовок | 26 | 2
Если не указывать партицию, то партицией является весь запрос.
Тут сразу надо немного сказать о функциях, которые можно использовать, так как есть очень важный нюанс.
В качестве функции можно использовать, так сказать, истинные оконные функции из мануала — это row_number(), rank(), lead() и т.д., а можно использовать функции-агрегаты, такие как: sum(), count() и т.д. Так вот, это важно, агрегатные функции работают слегка по-другому: если не задан ORDER BY в окне, идет подсчет по всей партиции один раз, и результат пишется во все строки (одинаков для всех строк партиции). Если же ORDER BY задан, то подсчет в каждой строке идет от начала партиции до этой строки.
Давайте посмотрим это на примере. Например, у нас есть некая (сферическая в вакууме) таблица пополнений балансов.
SELECT
transaction_id,
change
FROM balance_change
ORDER BY transaction_id;
transaction_id | change
----------------+--------
1 | 1.00
2 | -2.00
3 | 10.00
4 | -4.00
5 | 5.50
и мы хотим узнать заодно, как менялся остаток на балансе при этом:
SELECT
transaction_id,
change,
sum(change) OVER (ORDER BY transaction_id) as balance
FROM balance_change
ORDER BY transaction_id;
transaction_id | change | balance
----------------+--------+---------
1 | 1.00 | 1.00
2 | -2.00 | -1.00
3 | 10.00 | 9.00
4 | -4.00 | 5.00
5 | 5.50 | 10.50
Т.е. для каждой строки идет подсчет в отдельном фрейме. В данном случае фрейм — это набор строк от начала до текущей строки (если было бы PARTITION BY, то от начала партиции).
Если же мы для агрегатной фунции sum не будем использовать ORDER BY в окне, тогда мы просто посчитаем общую сумму и покажем её во всех строках. Т.е. фреймом для каждой из строк будет весь набор строк
от начала до конца партиции.
SELECT
transaction_id,
change,
sum(change) OVER () as result_balance
FROM balance_change
ORDER BY transaction_id;
transaction_id | change | result_balance
----------------+--------+----------------
1 | 1.00 | 10.50
2 | -2.00 | 10.50
3 | 10.00 | 10.50
4 | -4.00 | 10.50
5 | 5.50 | 10.50
Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.
Оконные функции можно использовать сразу по несколько штук, они друг другу ничуть не мешают, чтобы вы там в них не написали.
SELECT
transaction_id,
change,
sum(change) OVER (ORDER BY transaction_id) as balance,
sum(change) OVER () as result_balance,
round(
100.0 * sum(change) OVER (ORDER BY transaction_id) / sum(change) OVER (),
2
) AS percent_of_result,
count(*) OVER () as transactions_count
FROM balance_change
ORDER BY transaction_id;
transaction_id | change | balance | result_balance | percent_of_result | transactions_count
----------------+--------+---------+----------------+-------------------+--------------------
1 | 1.00 | 1.00 | 10.50 | 9.52 | 5
2 | -2.00 | -1.00 | 10.50 | -9.52 | 5
3 | 10.00 | 9.00 | 10.50 | 85.71 | 5
4 | -4.00 | 5.00 | 10.50 | 47.62 | 5
5 | 5.50 | 10.50 | 10.50 | 100.00 | 5
Если у вас много одинаковых выражений после OVER, то можно дать им имя и вынести отдельно с ключевым словом WINDOW, чтобы избежать дублирования кода. Вот пример из мануала:
SELECT
sum(salary) OVER w,
avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Здесь w после слова OVER идет без уже скобок.
Результат работы оконной функции невозможно отфильтровать в запросе с помощью WHERE, потому что оконные фунции выполняются после всей фильтрации и группировки, т.е. с тем, что получилось. Поэтому чтобы выбрать, например, топ 5 новостей в каждой группе, надо использовать подзапрос:
SELECT *
FROM (
SELECT
id,
section,
header,
score,
row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section
FROM news
ORDER BY section, rating_in_section
) counted_news
WHERE rating_in_section <= 5;
Еще пример для закрепления. Помимо row_number() есть несколько других функций. Например lag, которая ищет строку перед последней строкой фрейма. К примеру мы можем найти насколько очков новость отстает от предыдущей в рейтинге:
SELECT
id,
section,
header,
score,
row_number() OVER w AS rating,
lag(score) OVER w - score AS score_lag
FROM news
WINDOW w AS (ORDER BY score DESC)
ORDER BY score desc;
id | section | header | score | rating | score_lag
----+---------+-----------+-------+--------+-----------
6 | 2 | Заголовок | 95 | 1 |
3 | 4 | Заголовок | 79 | 2 | 16
8 | 3 | Заголовок | 36 | 3 | 43
4 | 3 | Заголовок | 36 | 4 | 0
5 | 2 | Заголовок | 34 | 5 | 2
7 | 4 | Заголовок | 26 | 6 | 8
1 | 2 | Заголовок | 23 | 7 | 3
2 | 1 | Заголовок | 6 | 8 | 17
Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.
Комментарии (39)
caballero
17.10.2015 00:12-6Ну, самый распространенный способ использования — для пагинации раз уж у нас номера строк под рукой
zzashpaupat
17.10.2015 10:22-1Вы бы еще OFFSET предложили.
caballero
17.10.2015 15:58-1ну, затупил. Я єтим в mssql пользуюсь а там только TOP (если в последних версиях конечно не добавили что то типа offset).
yar3333
17.10.2015 17:06+1Может я чего не понимаю, но в MySQL есть «LIMIT from,count», которым я всегда пользовался…
zzashpaupat
17.10.2015 18:23+1Основная проблема тут в том, что при «LIMIT 100000, 10» MySQL сначала посмотрит 100000 записей, а только потом вернёт 10.
JeStoneDev
17.10.2015 19:57+1если в последних версиях конечно не добавили что то типа offset
Добавили technet.microsoft.com/en-us/library/gg699618(v=sql.110).aspx
SELECT First Name + ' ' + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
evnuh
17.10.2015 03:31А можно ли использовать результат оконной функции в HAVING? Как раз сегодня решал задачу на MySQL, нужно было сделать пагинацию по дням, имея колонку с таймстемпом записей. Очень горевал, что он не умеет оконные функции, пришлось извращаться с переменными в селектах и подзапросами, но зато потом эту переменную можно в HAVING напихать и всё отфлильтруется.
varanio
17.10.2015 08:28> А можно ли использовать результат оконной функции в HAVING?
нет, нельзя. Оконные функции просчитываются уже после фильтрации по havingCasufi
19.10.2015 12:43Но ведь можно запихнуть запрос с оконной функцией в with а в результирующем запросе сделать и группировку и having нет?
Mingun
17.10.2015 09:37-1Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.
Не понял, чего тут не очевидного? Не указываемorder by— границами окна являются первая и последняя запись в выборке, указываем — первая и текущая запись в выборке.
Кстати, ваше описание оконных функций неполно — как минимум не указаны полезнейшие предложенияrange/rows, позволяющие явно указать границы окна.
varanio
17.10.2015 09:51+1> Не понял, чего тут не очевидного? Не указываем order by — границами окна являются первая и последняя запись в выборке, указываем — первая и текущая запись в выборке.
Если мы берем функцию row_number(), то она как-то все равно учитывает текущую строку, даже если не делать order by. А sum — никак не учитывает. Вот это и не очевидно, на мой взгляд.zzashpaupat
17.10.2015 10:11Об этом прямо написано в документации. sum — это агрегатная функция изначально, а row_number — оконная.
«When an aggregate function is used as a window function, it aggregates over the rows within the current row's window frame. An aggregate used with ORDER BY and the default window frame definition produces a „running sum“ type of behavior, which may or may not be what's wanted. To obtain aggregation over the whole partition, omit ORDER BY or use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Other frame specifications can be used to obtain other effects.»
rinnaatt
17.10.2015 13:02-2я довольно часто использовал оконную функцию, чтобы найти крайнее значение из набора.
Представим, что есть таблица со списком тарифов, для каждого тарифа есть цена и дата начала действия. Текущую цену можно будет узнать таким запросом.
+----+------------+--------+------------+ | id | service_id | tariff | put_into | +----+------------+--------+------------+ | 1 | 1 | 100.0 | 2013-10-01 | | 2 | 1 | 200.0 | 2014-12-01 | | 3 | 1 | 150.0 | 2015-02-01 | | 4 | 1 | 170.0 | 2016-02-15 | | 5 | 2 | 300.0 | 2013-10-01 | | 7 | 2 | 330.0 | 2015-02-01 | | 8 | 2 | 315.0 | 2016-02-15 | +----+------------+--------+------------+ select distinct first_value (t1.tariff) over (order by t1.put_into desc) as price from tariff_price t1 where t1.service_id = 1 and t1.put_into <= CURRENT_DATE; +-------+ | price | +-------+ | 150.0 | +-------+
Melkij
17.10.2015 13:14+4Эмм. А зачем тут оконная функция?
select t1.tariff from from tariff_price t1 where t1.service_id = 1 and t1.put_into <= CURRENT_DATE order by t1.put_into desc limit 1
И очевиднее и план запроса куда проще.
kiaplayer
18.10.2015 01:05Ваш запрос подойдет для предложенной задачи только при условии «t1.service_id = 1».
Если же нужно найти последние цены по всем видам услуг — то не подойдет :)varanio
18.10.2015 07:21Если бы не было этого условия, и было бы PARTITION BY, тогда да. А так — странноватый запрос.
rinnaatt
21.10.2015 09:10+1Чудак ты автор, сам же просил:
Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.
Ну я и дал пример сферического запроса в вакуме, реальный запрос намного сложнее и масштабнее, но я его здесь приводить не буду так как не интересно.
Melkij
18.10.2015 12:12Та запросто:
select distinct on (t1.service_id) t1.service_id, t1.tariff from from tariff_price t1 where t1.put_into <= CURRENT_DATE order by t1.service_id, t1.put_into desc
Santacruz
17.10.2015 14:58-3Нужна помощь зала… как красиво вытащить последний ряд партишена?
http://sqlfiddle.com/#!15/6bba4/1varanio
17.10.2015 16:16+1последний ряд партишена, если я правильно понял задачу, можно вытащить примерно так: пронумеровать row_number() over (order by… desc) as num, потом весь запрос сделать подзапросом, и отфильтровать where num = 1
FSA
17.10.2015 20:39В очередной раз жалею, что выбрал MySQL для своего проекта с несколькими десятками таблиц…
Bezk
17.10.2015 21:27+2Несколько десятков – это не тысячи, можно и смигрировать :-)
FSA
19.10.2015 00:06Можно. Осталось быдлокод подготовить. Пока медленно выходит. Плюс надо преодолеть страх отказаться от phpMyAdmin. За столько лет привык уже. А полноценной замены ему нет. Кстати, для многих это и есть причина оставаться на MySQL. Потому что «так удобнее»,
NeX
18.10.2015 01:22+1Держите. pgloader.io
NeX
18.10.2015 01:31Из плюсов — он единственный из списка wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL работает с гео полями
FSA
19.10.2015 00:00Ну у меня есть в базе координаты объектов. Только я особо не заморачивался. Главное отображаются поверх Openstreetmap.
Envek
18.10.2015 08:38Оконные функции я часто использую для миграции данных. Очень удобно. Это либо удаление дубликатов, либо штуки типа перенумерования записей, например так: envek.name/ru/blog/2015/04/28/sql-window-functions
Isopropil
18.10.2015 19:46Отличная статья, спасибо, очень радует, что материалы по этой действительно великолепной СУБД стали появляться всё чаще. Ещё очень бы хотелось материалов по масштабированию — советы, практики использования таких вещей, как pgPool-II, PostgresXL и т.д.
VolCh
19.10.2015 00:39+1Оконные функции типа first/last_value очень удобны при работе с версионированными или хронологическими данными. Особенно функции, «заглядывающие» вперёд по запросу. В том же мускуле с помощью переменных худо-бедно можно выводить результаты в текущей строке, базирующиеся на результатах предыдущих, но вот если нужны последующие, то без подзапросов с обратной сортировкой не обойтись.
ajvol
20.10.2015 10:23+2Неплохо бы ещё пару слов сказать про волшебное «UNBOUNDED FOLLOWING», и как оно влияет на выражения. Оптимизатору несложно выполнять агрегации над теми строками, которые он уже «просканировал», вероятно, поэтому по умолчанию используются границы «RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW», но если вам нужно «заглядывать вперёд» и учитывать значения будущих строк окна, то приходится использовать то самое «FOLLOWING», которое может кардинальным образом менять план и скорость выполнения запроса.
Вот простой пример расчёта среднего по умолчанию
SELECT NAME, SALARY, AVG(SALARY) OVER (ORDER BY SAL) AVG FROM EMP; NAME SALARY AVG ---------- ---------- ---------- SMITH 800 800 JAMES 950 875 ADAMS 1100 950 WARD 1250 1025
Подробнее, например, на www.dba-oracle.com/t_advanced_sql_windowing_clause.htm
zzashpaupat
«There was SQL before window functions and SQL after window functions».
Оконные функции удобно применять для всякой аналитики, отчетов, и так далее.
А еще однажды была задача добавить unique constraint на таблицу, где уже было порядка 500к записей и из них много не соответствовало условию. Запрос на удаление лишних записей был удобно сформирован с помощью оконной фукнции. Боюсь представить, сколько времени я бы удалял это вручную.
xobotyi
Да-да, помнится, делал удаление дублей до того как познакомился с окнами, а потом после. С окнами это один запрос, а без — страшно подумать, добавление столбца, создание последовательности, потом выборка по хевингу, и все это в табле с 2+млн записей, ужас! ( '/ /_ / /)