Оконные функции postgresql: Postgres Pro Standard : Документация: 9.5: 3.5. Оконные функции : Компания Postgres Professional

Содержание

Postgres Pro Standard : Документация: 9.6: 9.21. Оконные функции : Компания Postgres Professional

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

Оконные функции дают возможность выполнять вычисления с набором строк, каким-либо образом связанным с текущей строкой запроса. Вводную информацию об этом можно получить в Разделе 3.5, а подробнее узнать о синтаксисе можно в Подразделе 4.2.8.

Встроенные оконные функции перечислены в Таблице 9.56. Заметьте, что эти функции должны вызываться именно как оконные, т. е. при вызове необходимо использовать предложение OVER.

В дополнение к этим функциям в качестве оконных можно использовать любые встроенные или пользовательские обычные (но не сортирующие и не гипотезирующие) агрегатные функции (встроенные функции перечислены в Разделе 9.20). Агрегатные функции работают как оконные, только когда за их вызовом следует предложение OVER; в противном случае они останутся обычными агрегатными.

Таблица 9.56. Оконные функции общего назначения

Функция	Тип результата	Описание
`row_number()`	`bigint`	номер текущей строки в её разделе, начиная с 1
`rank()`	`bigint`	ранг текущей строки с пропусками; то же, что и `row_number` для первой родственной ей строки
`dense_rank()`	`bigint`	ранг текущей строки без пропусков; эта функция считает группы родственных строк
`percent_rank()`	`double precision`	относительный ранг текущей строки: (`rank` — 1) / (общее число строк — 1)
`cume_dist()`	`double precision`	относительный ранг текущей строки: (число строк, предшествующих или родственных текущей) / (общее число строк)
`ntile(число_групп integer)`	`integer`	ранжирование по целым числам от 1 до значения аргумента так, чтобы размеры групп были максимально близки
`lag(значение anyelement [, смещение integer [, по_умолчанию anyelement ]])`	`тип аргумента значение`	возвращает `значение` для строки, положение которой задаётся `смещением` от текущей строки к началу раздела; если такой строки нет, возвращается значение `по_умолчанию` (оно должно иметь тот же тип, что и `значение`). Оба параметра `смещение` и `по_умолчанию` вычисляются для текущей строки. Если они не указываются, то `смещение` считается равным 1, а `по_умолчанию` — NULL
`lead(значение anyelement [, смещение integer [, по_умолчанию anyelement ]])`	`тип аргумента значение`	возвращает `значение` для строки, положение которой задаётся `смещением` от текущей строки к концу раздела; если такой строки нет, возвращается значение `по_умолчанию` (оно должно иметь тот же тип, что и `значение`). Оба параметра `смещение` и `по_умолчанию` вычисляются для текущей строки. Если они не указываются, то `смещение` считается равным 1, а `по_умолчанию` — NULL
`first_value(значение any)`	`тип аргумента значение`	возвращает `значение`, вычисленное для первой строки в рамке окна
`last_value(значение any)`	`тип аргумента значение`	возвращает `значение`, вычисленное для последней строки в рамке окна
`nth_value(значение any, n integer)`	`тип аргумента значение`	возвращает `значение`, вычисленное в `н-ой` строке в рамке окна (считая с 1), или NULL, если такой строки нет

Результат всех функций, перечисленных в Таблице 9. 56, зависит от порядка сортировки, заданного предложением ORDER BY в определении соответствующего окна. Строки, которые являются одинаковыми с точки зрения сортировки ORDER BY, считаются родственными; четыре функции, вычисляющие ранг, реализованы так, что их результат будет одинаковым для любых двух родственных строк.

Заметьте, что функции first_value, last_value и nth_value рассматривают только строки в «рамке окна», которая по умолчанию содержит строки от начала раздела до последней родственной строки для текущей. Поэтому результаты last_value и иногда nth_value могут быть не очень полезны. В таких случаях можно переопределить рамку, добавив в предложение OVER подходящее указание (RANGE или ROWS). Подробнее эти указания описаны в Подразделе 4.2.8.

Когда в качестве оконной функции используется агрегатная, она обрабатывает строки в рамке текущей строки. Агрегатная функция с ORDER BY и определением рамки окна по умолчанию будет вычисляться как «бегущая сумма», что может не соответствовать желаемому результату. Чтобы агрегатная функция работала со всем разделом, следует опустить ORDER BY или использовать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Используя другие указания в определении рамки, можно получить и другие эффекты.

Примечание

В стандарте SQL определены параметры RESPECT NULLS или IGNORE NULLS для функций lead, lag, first_value, last_value и nth_value. В Postgres Pro такие параметры не реализованы: эти функции ведут себя так, как положено в стандарте по умолчанию (или с подразумеваемым параметром RESPECT NULLS). Также функция nth_value не поддерживает предусмотренные стандартом параметры FROM FIRST и FROM LAST: реализовано только поведение по умолчанию (с подразумеваемым параметром FROM FIRST). (Получить эффект параметра FROM LAST можно, изменив порядок ORDER BY на обратный.)

Оконные функции в SQL — что это и зачем они нужны

Многие разработчики, даже давно знакомые с SQL, не понимают оконные функции, считая их какой-то особой магией для избранных. И, хотя реализация оконных функций поддерживается с SQL Server 2005, кто-то до сих пор «копипастит» их со StackOverflow, не вдаваясь в детали. Этой статьёй мы попытаемся развенчать миф о неприступности этой функциональности SQL и покажем несколько примеров работы оконных функций на реальном датасете.

Почему не GROUP BY и не JOIN

Сразу проясним, что оконные функции — это не то же самое, что GROUP BY. Они не уменьшают количество строк, а возвращают столько же значений, сколько получили на вход. Во-вторых, в отличие от GROUP BY, OVER может обращаться к другим строкам. И в-третьих, они могут считать скользящие средние и кумулятивные суммы.

Примечание Оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Для простоты понимания можно считать, что SQL сначала выполняет весь запрос (кроме сортировки и limit), а уже потом считает значения окна.

Окей, с GROUP BY разобрались. Но в SQL практически всегда можно пойти несколькими путями. К примеру, может возникнуть желание использовать подзапросы или JOIN. Конечно, JOIN по производительности предпочтительнее подзапросов, а производительность конструкций JOIN и OVER окажется одинаковой. Но OVER даёт больше свободы, чем жёсткий JOIN. Да и объём кода в итоге окажется гораздо меньше.

Для начала

Оконные функции начинаются с оператора OVER и настраиваются с помощью трёх других операторов: PARTITION BY, ORDER BY и ROWS. Про ORDER BY, PARTITION BY и его вспомогательные операторы LAG, LEAD, RANK мы расскажем подробнее.
Все примеры будут основаны на датасете олимпийских медалистов от Datacamp. Таблица называется summer_medals и содержит результаты Олимпиад с 1896 по 2010:

ROW_NUMBER и ORDER BY

Как уже говорилось выше, оператор OVER создаёт оконную функцию. Начнём с простой функции ROW_NUMBER, которая присваивает номер каждой выбранной записи:

SELECT
athlete,
event,
ROW_NUMBER() OVER() AS row_number
FROM Summer_Medals
ORDER BY row_number ASC;

Каждая пара «спортсмен — вид спорта» получила номер, причём к этим номерам можно обращаться по имени row_number.
ROW_NUMBER можно объединить с ORDER BY, чтобы определить, в каком порядке строки будут нумероваться. Выберем с помощью DISTINCT все имеющиеся виды спорта и пронумеруем их в алфавитном порядке:

SELECT
sport,
ROW_NUMBER() OVER(ORDER BY sport ASC) AS Row_N
FROM (
SELECT DISTINCT sport
FROM Summer_Medals
) AS sports
ORDER BY sport ASC;

PARTITION BY и LAG, LEAD и RANK

PARTITION BY позволяет сгруппировать строки по значению определённого столбца. Это полезно, если данные логически делятся на какие-то категории и нужно что-то сделать с данной строкой с учётом других строк той же группы (скажем, сравнить теннисиста с остальными теннисистами, но не с бегунами или пловцами). Этот оператор работает только с оконными функциями типа LAG, LEAD, RANK и т. д.

LAG

Функция LAG берёт строку и возвращает ту, которая шла перед ней. Например, мы хотим найти всех олимпийских чемпионов по теннису (мужчин и женщин отдельно), начиная с 2004 года, и для каждого из них выяснить, кто был предыдущим чемпионом.
Решение этой задачи требует нескольких шагов. Сначала надо создать табличное выражение, которое сохранит результат запроса «чемпионы по теннису с 2004 года» как временную именованную структуру для дальнейшего анализа. А затем разделить их по полу и выбрать предыдущего чемпиона с помощью LAG:

– Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = 'Tennis' AND
event = 'Singles' AND
Medal = 'Gold')

– Оконная функция разделяет по полу и берёт чемпиона из предыдущей строки
SELECT
Athlete as Champion,
Gender,
Year,
LAG(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Last_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;

Функция PARTITION BY в таблице вернула сначала всех мужчин, потом всех женщин. Для победителей 2008 и 2012 года приведён предыдущий чемпион; так как данные есть только за 3 олимпиады, у чемпионов 2004 года нет предшественников, поэтому в соответствующих полях стоит null.

LEAD

Функция LEAD похожа на LAG, но вместо предыдущей строки возвращает следующую. Можно узнать, кто стал следующим чемпионом после того или иного спортсмена:

– Табличное выражение ищет теннисных чемпионов и выбирает нужные столбцы
WITH Tennis_Gold AS (
SELECT
Athlete,
Gender,
Year,
Country
FROM
Summer_Medals
WHERE
Year >= 2004 AND
Sport = 'Tennis' AND
event = 'Singles' AND
Medal = 'Gold')

– Оконная функция разделяет по полу и берёт чемпиона из следующей строки
SELECT
Athlete as Champion,
Gender,
Year,
LEAD(Athlete) OVER (PARTITION BY gender
ORDER BY Year ASC) AS Future_Champion
FROM Tennis_Gold
ORDER BY Gender ASC, Year ASC;

RANK

Оператор RANK похож на ROW_NUMBER, но присваивает одинаковые номера строкам с одинаковыми значениями, а «лишние» номера пропускает. Есть также DENSE_RANK, который не пропускает номеров. Звучит запутанно, так что проще показать на примере. Вот ранжирование стран по числу олимпиад, в которых они участвовали, разными операторами:

Row_number — ничего интересного, строки просто пронумерованы по возрастанию.
Rank_number — строки ранжированы по возрастанию, но нет номера 3. Вместо этого, 2 строки делят номер 2, а за ними сразу идёт номер 4.
Dense_rank — то же самое, что и rank_number, но номер 3 не пропущен. Номера идут подряд, но зато никто не оказался пятым из пяти.

Вот код:

-- Табличное выражение выбирает страны и считает годы
WITH countries AS (
SELECT
Country,
COUNT(DISTINCT year) AS participated
FROM
Summer_Medals
WHERE
Country in ('GBR', 'DEN', 'FRA', 'ITA','AUT')
GROUP BY
Country)

-- Разные оконные функции ранжируют страны
SELECT
Country,
participated,
ROW_NUMBER()
OVER(ORDER BY participated DESC) AS Row_Number,
RANK()
OVER(ORDER BY participated DESC) AS Rank_Number,
DENSE_RANK()
OVER(ORDER BY participated DESC) AS Dense_Rank
FROM countries
ORDER BY participated DESC;

Напоследок

Вот так мы и разложили этот датасет по полочкам при помощи оконных функций. На этом наше введение в оконные функции заканчивается. Надеемся, это было интересно и не так сложно, как могло показаться.

Конечно, это далеко не все возможности оконных функций. Для них есть много других полезных вещей, например ROWS, NTILE и агрегирующие функции (SUM, MAX, MIN и другие), но об этом поговорим в другой раз.

Адаптированный перевод статьи «Intro to Window Functions in SQL»

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

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

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

Таким образом, если запрос использует какие-либо агрегаты, GROUP BY или HAVING, то строки, которые будут предоставлены оконным функциям будут уже сгруппированными, а не первоначальными строками таблиц, которые указаны в предложениях FROM/WHERE.

Каждая строчка возвращаемая запросом, ничего не знает про другую. Поэтому разработчики, начинают использовать группировки в подзапросах, чтобы получить уже посчитаннае данные, на основании набора строк. Из-за этого запрос становиться слишком «тяжелым» и сопровождать его, остается очень мало удовольствия.

Все эти проблемы, решаются с помощью оконных функций. Давайте на примере, создадим и наполним данными таблицу «Сотрудники»:

Нам необходимо вытащить из таблицы «Сотрудники», следующие данные:

Название отдела,

Номер сотрудника,

Средняя зарплата по отделу сотрудника,

Общая сумма зарплат отдела,

Количество сотрудников, в котором работает сотрудник

Так будет выглядеть запрос, без использования оконных функций:

Время выполнения ~ 4ms.

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

Время выполнения ~ 2ms.

Таким образом, на самом простом запросе, выигрыш по времени составил более чем, в 2 раза. Ну и код намного более читабельным стал.

А здесь вы можете узнать, как можно удалить все дублирующие строки в БД, с помощью оконных функций.

Если вам помогла статья, пожалуйста перейдите по одному из рекламных блоков, расположенных на сайте. Таким образом вы поддержите проект. Спасибо

Пользовательские агрегатные и оконные функции в PostgreSQL и Oracle | by Postgres Professional | Postgres Professional

Новая статья Егора Рогова в нашем техническом блоге на www.habrahabr.ru, посвящена сравнению функционала двух популярных СУБД — PostgreSQL и Oracle.

В этой статье мы посмотрим, как в двух системах создавать пользовательские агрегатные и оконные (в терминологии Oracle — аналитические) функции. Несмотря на различия в синтаксисе и в целом в подходе к расширяемости, механизм этих функций очень похож. Но и различия тоже имеются.
Надо признать, что собственные агрегатные и оконные функции встречается довольно редко. Оконные функции вообще по каким-то причинам традиционно относят к разряду «продвинутого» SQL и считают сложными для понимания и освоения. Тут бы разобраться с теми функциями, которые уже имеются в СУБД! Зачем тогда вообще вникать в этот вопрос? Могу назвать несколько причин:

Хотя оконные функции объективно сложнее обычных агрегатных, но ничего запредельного в них нет; это абсолютно необходимый инструмент для SQL-разработчика. А создание собственной оконной функции, даже совсем простой, позволяет лучше разобраться с тем, как работают стандартные.
Оконные и агрегатные функции — прекрасный способ совместить процедурную обработку с декларативной логикой. В некоторых ситуациях получается выполнить сложные действия, оставаясь в рамках парадигмы решения задачи одним SQL-запросом.
Да и просто интересная тема, а уж тем более интересно сравнить две системы.

Пример, на котором будем тренироваться — подсчет среднего, аналог стандартной функции avg для типа numeric (number в Oracle). Мы напишем такую функцию и посмотрим, как она работает в агрегатном и оконном режимах и может ли она вычисляться несколькими параллельными процессами. А в заключение поглядим на пример из реальной жизни.

Читать статью полностью в нашем блоге на www.habrahabr.ru:

Оконные функции (window functions) в PostgreSQL | Уйти в IT!

Здравствуйте, уважаемые подписчики и гости канала!

Продолжаю цикл статей по одной из моих любимых баз данных — PostgreSQL. Сори, за картинки, но дзен пока не позволяет нормально форматировать код. В самом конце статьи будут запросы для копипасты не картинкой.

Работаю я с этой БД уже достаточно давно — лет 8 наверно. Переходили с MySQL по заданию от моего руководителя, который почитал статейку и поверил в PG.

Тогда, да и долго еще после мы использовали БД как обычно, без особых изысков, разве, что секция RETURNING нам очень зашла — это особенная радость после mysql во всяком случае тех годов.

В общем ближе к сути — кто не знает, в PostgreSQL как и в любых нормальных БД, есть поддержка оконных функций. Речь не о OS Windows если что. Оконные функции — это такой особый вид функций, расширяющих аналитическую часть СУБД. Они не уменьшают и не увеличивают количество строк, как GROUP BY или JOIN, а добавляют действительно новую функциональность, например:

— Посчитать сумму с накоплением (нарастающий итог)
— Вывести список сотрудников с колонкой — процент отклонения ЗП сотрудника от средней в отделе. При этом в таблице нужны все сотрудники всех отделов
— Посчитать общее кол-во записей без учета limit, offset
— Получить номер строки выборки. А если показалось легко, то усложните до — получить номер строки сотрудника, среди его отдела с обратной сортировкой по имени. И все в одной таблице

К слову сказать, для Google BigQuery (которую я тоже сильно люблю) так же применимы знания, полученные вами в этой статье, разве что синтаксис может незначительно отличаться.

Самое важное, что надо понимать, что пишется это примерно так:

ФУНКЦИЯ(ВАШЕ_ПОЛЕ) OVER(ТУТ МНОГО НАСТРОЕК ИМЕННО ОКОННЫХ ФУНКЦИЙ) as ИМЯ_НОВОГО_ПОЛЯ.

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

Кстати, у меня есть отличная статья про использование json в Postgres, почитай, ведь json не мешает использовать оконные функции, возможно ты найдешь там полезное для себя.

Это оптимальнее подзапросов (как правило)

В SQL всегда можно разными путями решить задачу и этот не исключение — в mysql до относительно недавнего времени тоже не было поддержи оконных функций и жили же люди. Жили, да, но неудобно раз поддержку добавили.

В общем, вы можете наколхозить четырехэтажных запросов, но зачем, если можно этого не делать и написать элегантный код. При этом он не является просто синтаксическим сахаром — это нормальная полноценная функциональность.

Тут считаем процентное отклонение зарплаты сотрудника относительно его отдела/компании (organizationId):