Разное

Оконные функции 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):

картинка моего запроса для примера

Я с трудом могу представить более выразительный вариант это сделать. Напишите в комментах, если знаете. Однако — это же вершина айсберга знаний про window functions в sql. Опять же повторю, что все нормальные БД это поддерживают, так как это стандарт SQL.

Читайте на русском от ребят из Postgres Pro — ссылка.

И у них же про список функций — ссылка.

Вот простейший пример как получить данные с лимитом и сразу же общее количество строк в выборке без учета LIMIT / OFFSET. При это Postgres гарантирует, что сделает все это за один проход по данным.

картинка моего запроса для примера

Нарастающий итог в SQL (сумма с накоплением)

картинка моего запроса для примера

А это тоже самое, но в рамках organizationId:

картинка моего запроса для примера

Согласитесь впечатляет?

Есть только один нюанс «rows between unbounded preceding and current row» — вещь в данном запросе обязательная, так как говорит брать строку до текущей, но это такой местный PG хардкод на уровне их ядра. Про супер современные версии PG не проверял, но вроде это всё, что он пока из sql стандарта поддерживает, но даже это просто супер.

Получить разницу с минимальной зарплатой в отделе

картинка моего запроса для примера

И все за один проход по таблице!

Резюме

Читая пост, возможно вы подумали, что все то же самое можно получить на обычных группировках, join-ах и позапросах. Обычно так и есть, но мысля так, вы ограничиваете свой инструментарий, а опять повторюсь — это SQL стандарт, а не какая-то нить поделка неизвестного художника, а значит знание полезное и для вас и для вашего проекта.

Примеры запросов

Я не нашел более красивого варианта вывода запросов для копипасты. Напишите в комментах, если будут идеи как это лучше делать на Дзене.

SELECT name, salary, «organizationId»,

ROUND(salary * 100 / first_value(salary) over(PARTITION BY «organizationId» ORDER BY salary ASC) — 100, 2) as perc_diff_with_small_salary

FROM employee

SELECT «name», salary, «organizationId»,

SUM(salary) over(PARTITION BY «organizationId» rows between unbounded preceding and current row) as total

FROM employee

А на этом всё, спасибо за внимание!

Подписывайтесь на канал, ставьте лайки, оставляйте комментарии — это помогает продвижению в Дзене.

Кроме этого:

Подписывайтесь в Instagram: https://www.instagram.com/lets_goto_it/

Подписывайтесь в Telegram: https://t.me/lets_goto_it

#postgres #postgresql #sql #window functions #совет профи #dba #базы данных #database #программирование #разработка сайтов

Оконные функции – то, что должен знать каждый T-SQL программист. Часть 1.

Еще в Microsoft SQL Server 2005 появился интересный функционал – оконные функции. Это функции, которые позволяют осуществлять вычисления в заданном диапазоне строк внутри предложения Select. Для тех, кто не сталкивался с этими функциями возникает вопрос – «Что значит оконные?». Окно – значит набор строк, в рамках которого происходит вычисление. Оконная функция позволяет разбивать весь набор данных на такие окна.

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

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

Окно определяется с помощью инструкции OVER(). Давайте рассмотрим синтаксис этой инструкции:

Оконная функция (столбец для вычислений) OVER ([PARTITION BY столбец для группировки] [ORDER BY столбец для сортировки] [ROWS или RANGE выражение для ограничения строк в пределах группы])

Ассортимент функций мы рассмотрим во второй части статьи. Скажу лишь, что они разделяются на: агрегирующие, ранжирующие, смещения.

Для демонстрации работы оконных функций предлагаю на тестовой таблице:


1
2
3
4
5
6
7

CREATE TABLE ForWindowFunc (ID INT, GroupId INT, Amount INT)
GO
 
INSERT INTO ForWindowFunc (ID, GroupId, Amount)
 VALUES(1, 1, 100), (1, 1, 200), (1, 2, 150),
 (2, 1, 100), (2, 1, 300), (2, 2, 200), (2, 2, 50),
 (3, 1, 150), (3, 2, 200), (3, 2, 10);

 











ID

GroupId

Amount

1

1

100

1

1

200

1

2

150

2

1

100

2

1

300

2

2

200

2

2

50

3

1

150

3

2

200

Как видно, здесь три группы в колонке ID и две подгруппы в колонке GroupId с разным количеством элементов в группе.

Чаще всего используется функция суммирования, поэтому демонстрацию проведем именно на ней. Давайте посмотрим, как работает инструкция OVER:


1
2
3

 SELECT ID,
 Amount,
 SUM(Amount) OVER() AS SUM FROM ForWindowFunc

 











ID

Amount

Sum

1

100

1310

1

200

1310

2

100

1310

2

300

1310

2

200

1310

2

50

1310

3

150

1310

3

200

1310

3

10

1310

Мы использовали инструкцию OVER() без предложений. В таком варианте окном будет весь набор данных и никакая сортировка не применяется. Нам просто повезло, что данные вывелись в том же порядке, в котором были вставлены в таблицу, но SQL Server может поменять порядок отображения, если нет явно заданной сортировки. Поэтому инструкцию OVER() практически никогда не применяют без предложений. Но, обратим наше внимание на новый столбец SUM. Для каждой строки выводится одно и то же значение 1310. Это сквозная сумма всех значений колонки Amount.

Предложение PARTITION BY

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

Изменим наш запрос, написанный ранее, так:


1
2
3

SELECT ID, 
 Amount, 
 SUM(Amount) OVER(PARTITION BY ID) AS SUM FROM ForWindowFunc

 











ID

Amount

Sum

1

100

300

1

200

300

2

100

650

2

300

650

2

200

650

2

50

650

3

150

360

3

200

360

3

10

360

Предложение PARTITION BY сгруппировало строки по полю ID. Теперь для каждой группы рассчитывается своя сумма значений Amount. Вы можете создавать окна по нескольким полям. Тогда в PARTITION BY нужно писать поля для группировки через запятую (например, PARTITION BY ID, Amount).

Предложение ORDER BY

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


1
2
3
4

 SELECT ID,
 GroupId,
 Amount,
 SUM(Amount) OVER(PARTITION BY id ORDER BY Amount) AS SUM FROM ForWindowFunc

 












ID

GroupId

Amount

Sum

1

1

100

100

1

2

150

250

1

1

200

450

2

2

50

50

2

1

100

150

2

2

200

350

2

1

300

650

3

2

10

10

3

1

150

160

3

2

200

360

К предложению PARTITION BY добавилось ORDER BY по полю Amount. Таким образом мы указали, что хотим видеть сумму не всех значений Amount в окне, а для каждого значения Amount сумму со всеми предыдущими. Такое суммирование часто называют нарастающий итог или накопительный итог.

Вы заметили, что в выборке появилось поле GpoupId. Это поле позволит показать, как изменится нарастающий итог, в зависимости от сортировки. Изменим запрос:


1
2
3
4

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount) AS SUM FROM ForWindowFunc

 












ID

GroupId

Amount

Sum

1

1

100

100

1

1

200

300

1

2

150

450

2

1

100

100

2

1

300

400

2

2

50

450

2

2

200

650

3

1

150

150

3

2

10

160

3

2

200

360

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

Предложение ROWS/RANG

Еще два предложения ROWS и RANGE применяются в инструкции OVER.  Этот функционал появился в MS SQL Server 2012.

Предложение ROWS ограничивает строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей. Оба предложения ROWS и RANGE используются вместе с ORDER BY.

Предложение ROWS может быть задано с помощью методов:

  • CURRENT ROW – отображение текущей строки;
  • UNBOUNDED FOLLOWING – все записи после текущей;
  • UNBOUNDED PRECEDING – все предыдущие записи;
  • <целое число> PRECEDING – заданное число предыдущих строк;
  • <целое число> FOLLOWING – заданное число последующих записей.

Вы можете комбинировать эти функции для достижения желаемого результата, например:

  • ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING – в окно попадут текущая и одна следующая запись;


1
2
3
4

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) AS SUM FROM ForWindowFunc

 












ID

GroupId

Amount

Sum

1

1

100

300

1

1

200

350

1

2

150

150

2

1

100

400

2

1

300

350

2

2

50

250

2

2

200

200

3

1

150

160

3

2

10

210

3

2

200

200

Здесь, сумма рассчитывается по текущей и следующей ячейке в окне. А последняя в окне строка имеет то же значение, что и Amount. Посмотрим на первое окно, выделенное голубым цветом. Сумма 300 рассчитана сложением 100 и 200. Для следующего значения ситуация аналогичная. А последняя в окне сумма имеет значение 150, потому что текущий Amount больше не с чем складывать.

  • ROWS BETWEEN 1 PRECEDING AND CURRENT ROW – одна предыдущая и текущая запись


1
2
3
4

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId, Amount ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS SUM FROM ForWindowFunc

 












ID

GroupId

Amount

Sum

1

1

100

100

1

1

200

300

1

2

150

350

2

1

100

100

2

1

300

400

2

2

50

350

2

2

200

250

3

1

150

150

3

2

10

160

3

2

200

210

В этом запросе мы получаем сумму путем сложения текущего значения Amount и предыдущего. Первая строка имеет значение 100, т.к. предыдущего значения Amount не существует.

Предложение RANGE предназначено также для ограничения набора строк. В отличие от ROWS, оно работает не с физическими строками, а с диапазоном строк в предложении ORDER BY. Это означает, что одинаковые по рангу строки в контексте предложения ORDER BY будут считаться как одна текущая строка для функции CURRENT ROW. А в предложении ROWS текущая строка – это одна, текущая строка набора данных.

Предложение RANGE может использоваться только с опциями CURRENT ROW, UNBOUNDED PRECEDING и UNBOUNDED FOLLOWING.

Предложение RANGE может использовать опции:

  • CURRENT ROW – отображение текущей строки;
  • UNBOUNDED FOLLOWING – все записи после текущей;
  • UNBOUNDED PRECEDING – все предыдущие записи.

И не может:

  • <целое число> PRECEDING – заданное число предыдущих строк;
  • <целое число> FOLLOWING – заданное число последующих записей.

Примеры:


1
2
3
4

SELECT ID,
 GroupId,
 Amount,
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE CURRENT ROW) AS SUM FROM ForWindowFunc

 












ID

GroupId

Amount

Sum

1

1

100

300

1

1

200

300

1

2

150

150

2

1

100

400

2

1

300

400

2

2

200

250

2

2

50

250

3

1

150

150

3

2

200

210

3

2

10

210

Предложение Range настроено на текущую строку. Но, как мы помним, для Range текущая строка, это все строки, соответствующие одному значению сортировки. Сортировка в данном случае по полю GroupId. Первые две строки первого окна имеют значение GroupId равное 1 – следовательно оба эти значения удовлетворяют ограничению RANGE CURRENT ROW. Поэтому Sum для каждой из этих строк равна общей сумме Amount по ним — 300.

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW;


1
2
3
4

SELECT ID,
 GroupId,
 Amount,
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM FROM ForWindowFunc

 












ID

GroupId

Amount

Sum

1

1

100

300

1

1

200

300

1

2

150

450

2

1

100

400

2

1

300

400

2

2

200

650

2

2

50

650

3

1

150

150

3

2

200

360

3

2

10

360

В этом случае ограничение по всем предыдущим строкам и текущей. Для первой и второй строки это правило работает как предыдущее (вспоминаем CURRENT ROW), а для третьей как сумма Amount предыдущих строк с текущей.

  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING.


1
2
3
4

SELECT ID, 
 GroupId, 
 Amount, 
 SUM(Amount) OVER(Partition BY id ORDER BY GroupId RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS SUM FROM ForWindowFunc

 












ID

GroupId

Amount

Sum

1

2

150

150

1

1

100

450

1

1

200

450

2

2

200

250

2

2

50

250

2

1

100

650

2

1

300

650

3

2

200

210

3

2

10

210

3

1

150

360

Это ограничение позволило нам получить сумму из текущей строки и всех предыдущих в рамках одного окна. Так как вторая и третья строка у нас в одной GroupId, то эти значения и есть Current Row. Поэтому они просуммированы сразу.

На этом закончим первую часть статьи. А напоследок — пример использования оконной функции из реальной практики.

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

Таким образом, нам нужно определить сколько же денег мы спишем со счета основного договора и сколько переведем на дочерний. Давайте посмотрим на таблицу:




ContractId

ChildContractId

PayId

CustAccount

PayAmount

1000000000

1000000002

1000000752

-200,00

800,00

1000000000

1000000003

1000000753

-1000,00

800,00

Где, ContractId – идентификатор основного договора,

ChildContractId – идентификатор дочернего договора,

PayId – идентификатор платежа,

CustAccount – баланс дочернего договора,

PayAmount – платеж.

Из таблицы видно, что для каждого дочернего договора сумма платежа 800. Это из-за того, что платеж на родительском договоре.

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

Для этого суммируем CustAccount и PayAmount. Однако, простая сумма баланса и платежа нас не устраивает. Ведь на погашение долга на втором дочернем договоре мы должны учитывать остаток от суммы баланса первого договора и платежа.

Как мы можем действовать в этой ситуации?  Мы могли бы выбрать:


1
2
3
4
5
6
7
8

SELECT
ContractId,
ChildContractId,
PayId,
CustAccount,
PayAmount,
PayAmount + (SELECT SUM(CustAccount) FROM dbo.Pays p2 WHERE p1.PayId = p2.PayId AND p2.ChildContractId <= p1.ChildContractId) AS [SUM]
FROM dbo.Pays p1

Этот запрос решает поставленную задачу, но подзапрос портит всю картину – увеличивает время выполнения запроса. Применим оконную функцию сложения:


1
2
3
4
5
6
7
8

SELECT
ContractId,
ChildContractId,
PayId,
CustAccount,
PayAmount,
PayAmount + SUM(CustAccount) OVER (ORDER BY ChildContractId) AS [SUM]
FROM dbo.Pays p1

Этот вариант работает быстрее и выглядит лаконичнее. В нашем случае мы получаем сумму по полю CustAccount в окне, которое формируется по полю ChildContractId.

Результатом этих запросов будет таблица:




ContractId

ChildContractId

PayId

CustAccount

PayAmount

Sum

1000000000

1000000002

1000000752

-200,00

800,00

600

1000000000

1000000003

1000000753

-1000,00

800,00

-400

Исходя из полученных данных в колонке Sum мы определяем сумму, которую нужно перенести с родительского договора на дочерний. Для договора 1000000002 мы погасили долг полностью, так что сумма платежа 200р. Для договора 1000000003 долг погашен частично – сумма платежа равна сумме баланса и остатка от платежа после расчета для первой записи (-1000 + 600 = -400р).

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

Примечание: Запросы в данной статье работают в PostgreSQL и могут не работать в вашей СУБД, сверяйтесь с документацией!

Недавно я открыл для себя существование оконных функций в SQL. Расскажу о них на примере ситуации, когда они мне помогли: перенумерация записей в базе.

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

Итак, ситуация: у нас в приложении есть «путевые листы», они пронумерованы по порядку, в качестве номера используется просто ID. Но теперь оказывается, что должна быть своя нумерация в рамках каждой станции, а на станциях у документов есть ещё и серии, и внутри каждой серии тоже должна быть своя нумерация.

Сперва привяжем путевые листы напрямую к станциям. Смотрим схему данных: путевые листы привязаны к машинам, машины — к станциям, добавляем в миграции колонку station_id и пишем простой запрос, который добавит ID станции в таблицу waybills:

UPDATE waybills
SET station_id = vehicles.station_id
FROM vehicles
WHERE vehicles.id = waybills.vehicle_id

Теперь надо заново перенумеровать все путевые листы в рамках станции и серии. Тут-то нам и помогут оконные функции:

UPDATE waybills
SET number = w.number
FROM (
  SELECT
    id,
    row_number() OVER (
      PARTITION BY station_id, series ORDER BY created_at
    ) AS number
  FROM waybills
) w
WHERE waybills. id = w.id;

Итак, что же здесь происходит?

Во внешнем запросе мы используем уже виденный ранее синтаксис UPDATE … FROM, специфичный для PostgreSQL, мы заполняем колонку number значением, которое нам вычисляет подзапрос для записи с таким же id, как и в подзапросе.

В подзапросе же мы выбираем id каждой строки в нашей таблице waybills и с помощью оконной функции row_number() вычисляем порядковый номер этой строки в заданном разбиении.

Обратите внимание на синтаксис вызова оконных функций: функция OVER (разбиение). Разбиение задаётся ключевым словом PARTITION BY со списком колонок, по уникальным значениям которых строки и разбиваются на отдельные группы. Отличие от GROUP BY как раз и состоит в том, что строки в группах не схлопываются в одну, а обрабатываются по отдельности. Ключевое слово ORDER BY позволяет упорядочить строки внутри каждой группы.

Таким образом мы разбили строки на группы для каждой станции и серии документа и строки в каждой группе отсортировали по старшинству — от старых к новым. (функция row_number() вернёт 1 для самой старой строки в группе).

Пробуем подзапрос на тестовых данных:

=# SELECT id, station_id, series, created_at, row_number() OVER (PARTITION BY station_id, series ORDER BY created_at) AS number FROM waybills;
 id |              station_id              | series |         created_at         | number
----+--------------------------------------+--------+----------------------------+--------
  5 | 258d39b6-7b09-49ad-94f6-33b0dcb2fa32 |        | 2015-04-07 10:53:44.736078 |      1
  1 | 69c5b783-034c-47ea-84fd-c4abd0a323d6 | 123    | 2015-03-16 09:55:16.689384 |      1
  6 | 69c5b783-034c-47ea-84fd-c4abd0a323d6 | 123    | 2015-04-28 13:47:16.397076 |      2
  7 | 69c5b783-034c-47ea-84fd-c4abd0a323d6 | 123    | 2015-04-28 13:47:40. 23337  |      3
  2 | 69c5b783-034c-47ea-84fd-c4abd0a323d6 | 23     | 2015-03-18 12:06:25.688768 |      1
  4 | 69c5b783-034c-47ea-84fd-c4abd0a323d6 | 4606   | 2015-03-24 08:10:38.143429 |      1
  3 | 69c5b783-034c-47ea-84fd-c4abd0a323d6 | 656565 | 2015-03-18 15:30:10.709491 |      1
(7 rows)

Отлично!

Миграция в полном виде будет выглядеть так:

class AddMultistationSupportForWaybills < ActiveRecord::Migration
  def change
    change_table :waybills do |t|
      t.integer :number
      t.references :station, type: :uuid
    end
    add_foreign_key :waybills, :stations

    reversible do |to|
      to.up do
        execute <<-PostgreSQL.strip_heredoc.tr("\n", ' ')
          UPDATE waybills
          SET station_id = vehicles.station_id
          FROM vehicles
          WHERE vehicles.id = waybills.vehicle_id
        PostgreSQL
        execute <<-PostgreSQL.strip_heredoc.tr("\n", ' ')
          UPDATE waybills
          SET number = w.number
          FROM (
            SELECT row_number() OVER (PARTITION BY station_id, series ORDER BY created_at) AS number, id FROM waybills
          ) w
          WHERE waybills.id = w.id;
        PostgreSQL
      end
    end
  end
end

Заключение

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

Материалы для изучения

Please enable JavaScript to view the comments powered by Disqus.

Fun with SQL: Оконные функции в Postgres

Сегодня мы продолжаем исследовать все мощные и забавные вещи, которые вы можете делать с SQL. SQL — очень выразительный язык, и когда дело доходит до анализа ваших данных, нет лучшего варианта. Доказательства мощи SQL можно увидеть во всех попытках баз данных NoSQL воссоздать возможности SQL. Так почему бы просто не начать с масштабируемой базы данных SQL? (Как и мои любимые Postgres и Citus. )

Сегодня, в последней публикации из серии «Развлечения с SQL» (предыдущие сообщения в блоге были о рекурсивных CTE, generate_series и перемещении шардов в кластере базы данных Citus), мы собираюсь посмотреть оконные функции в PostgreSQL.Оконные функции являются ключевыми в различных сценариях использования аналитики и отчетности, когда вы хотите сравнить и сопоставить данные. Оконные функции позволяют сравнивать значения между строками, которые так или иначе связаны с текущей строкой. Некоторыми практическими применениями оконных функций могут быть:

  • Определение того, когда все пользователи впервые выполнили какое-либо действие
  • Определение того, насколько увеличился или уменьшился счет каждого пользователя по сравнению с предыдущим месяцем
  • Найти, где все пользователи попали в определенную подгруппу

Базовая структура оконной функции в Postgres

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

Первый формат

  SELECT last_name,
       зарплата,
       отделение,
       ранг () ВЫШЕ (
        РАЗДЕЛЕНИЕ ПО ОТДЕЛЕНИЯМ
        ЗАКАЗАТЬ ПО ЗАПРОСУ
        DESC)
ОТ сотрудников;
  

Второй формат

  ВЫБЕРИТЕ last_name,
       зарплата,
       отделение,
       ранг () БОЛЕЕ w
ОТ сотрудников
       WINDOW w as (РАЗДЕЛЕНИЕ ПО ОТДЕЛАМ ПО УДАЛЕНИЮ зарплаты). 

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

  last_name | зарплата | отдел | классифицировать
----------- + --------- + -------------- + -------
Джонс | 45000 | Бухгалтерский учет | 1
Уильямс | 37000 | Бухгалтерский учет | 2
Смит | 55000 | Продажи | 1
Адамс | 50000 | Продажи | 2
Джонсон | 40000 | Маркетинг | 1
  

Оба они показывают фамилию сотрудников, их зарплату, их отдел, а затем занимают место, где они попадают с точки зрения заработной платы в своем отделе. Вы можете легко объединить это с CTE, чтобы затем найти только наиболее высокооплачиваемую (, где ранг = 1 ) или вторую самую высокооплачиваемую (, где ранг = 2 ) в каждом отделе.

Что можно делать с оконными функциями в Postgres?

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

  • rank — Как мы видели в предыдущем примере, ранг покажет, где строка занимает место по порядку. порядка окон.
  • percent_rank — Хотите вычислить процент попадания строки в ваш порядок окон? percent_rank даст вам процентный рейтинг на основе вашего окна, представьте его как ((ранг — 1) / (всего строк — 1))
  • отставание — Хотите выполнить свою собственную операцию между строками? Lag даст вам значение строки x строк перед вашей текущей строкой. Хотите узнать значение для будущих строк? Для этого можно использовать отведение . Отличным примером этого может быть вычисление месячного роста
  • ntile — Хотите вычислить, в какие процентили попадают значения? ntile позволяет указать процентиль для группировки сегментов.Для 4 квартилей вы должны использовать ntile (4) , для процентиля каждой строки вы должны использовать ntile (100) .

Надеюсь, вы найдете оконные функции такими же полезными, как и мы в Citus. Если у вас есть вопросы по их использованию, документация PostgreSQL — отличный ресурс или не стесняйтесь перейти на наш канал Slack

Нравится то, что вы читаете?

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

Оконных функций

Твитнуть

Какие они

Документы Postgres на самом деле отлично объясняют, что такое оконные функции: «Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Это сопоставимо с типом вычислений, которые могут быть выполнены. с совокупным
функция. Но в отличие от обычных агрегатных функций, использование оконной функции не приводит к группированию строк в одну строку вывода — строки сохраняют свои отдельные идентификаторы.За кулисами оконная функция может получить доступ не только к текущей строке
результат запроса. «ref. Однако даже при такой ясности их значение может быть не сразу понятно, поэтому, возможно, проще всего увидеть их в действии.

Оконные функции в действии

Возьмем пример таблицы:

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

Сначала мы можем ранжировать каждого человека по определенной группе:

  ВЫБЕРИТЕ last_name,
       зарплата,
       отделение,
       ранг () ВЫШЕ (
        РАЗДЕЛЕНИЕ ПО ОТДЕЛЕНИЯМ
        ЗАКАЗАТЬ ПО ЗАПРОСУ
        DESC
       )
ОТ сотрудников;

last_name звание отдела заработной платы
Джонс 45000 Бухгалтерский учет 1
Williams 37000 Бухгалтерия 2
Смит 55000 Продажи 1
Адамс 50000 продаж 2
Джонсон 40000 Маркетинг 1
  

Надеюсь, отсюда понятно, как мы можем отфильтровать и найти только самых высокооплачиваемых сотрудников в каждом отделе:

  ВЫБРАТЬ *
ИЗ (
    ВЫБРАТЬ
           фамилия,
           зарплата,
           отделение,
           ранг () ВЫШЕ (
             РАЗДЕЛЕНИЕ ПО ОТДЕЛЕНИЯМ
             ЗАКАЗАТЬ ПО ЗАПРОСУ
             DESC
            )
    ОТ сотрудников) sub_query
ГДЕ ранг = 1;

last_name звание отдела заработной платы
Джонс 45000 Бухгалтерский учет 1
Смит 55000 Продажи 1
Джонсон 40000 Маркетинг 1
  

Лучшая часть этого состоит в том, что Postgres оптимизирует запрос для вас по сравнению с синтаксическим анализом всего набора результатов, если вы делаете это самостоятельно в plpgsql или в коде ваших приложений.

оконных функций в PostgreSQL — онлайн-курс SQL

Этот онлайн-курс будет интересен аналитикам баз данных, студентам, разработчикам и многим другим. Обязательным условием для прохождения курса является знание основ SQL. Прокрутите вниз, чтобы узнать больше.

Оконные функции (также известные как аналитические функции или функции OVER) — очень полезный инструмент, особенно когда дело касается агрегирования данных. Документация PostgreSQL довольно хорошо описывает концепцию оконных функций:

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

Эти функции были введены в стандарт относительно недавно, и поэтому не так много онлайн-курсов, охватывающих эту концепцию. К счастью, этот курс умеет!

Этот онлайн-курс охватывает синтаксис и семантику оконных функций PostgreSQL и демонстрирует их невероятную мощь. После завершения курса вы узнаете типичные случаи использования оконных функций, как использовать OVER, ORDER BY и PARTITION BY для структурирования фрейма, а также разницу между предложениями ROWS и RANGE.

ДИАПАЗОН ВЫШЕ оконных функций PostgreSQL и взлома сложных агрегатов уже сегодня!

Что это для меня?

  • 218 интерактивных упражнений. Учитесь в удобном для вас темпе, в любом месте и в любое время. Взаимодействуйте с практическими упражнениями для улучшения удержания.
  • Пожизненный доступ к курсу. Приобретая курс, вы получаете мгновенный персональный доступ ко всему его содержанию.
  • Онлайн-сертификация. Успешно завершите все упражнения, и вы получите сертификат. Вы можете опубликовать сертификат в своем профиле LinkedIn (вот как)
  • 30-дневная гарантия возврата денег. Если вы не удовлетворены качеством курса, вы можете получить возмещение в течение 30 дней с момента покупки.
  • Подсказки к упражнениям. Вы можете использовать готовые подсказки для упражнений или задавать вопросы и делиться своими мыслями с другими членами нашего сообщества через вкладку «Обсудить». Вы также можете написать нам по адресу [email protected], и мы будем более чем рады ответить!

Каковы требования?

Узнайте, как:

  • Определите окно функции с помощью PostgreSQL PARTITION BY.
  • RANK строк с оконными функциями.
  • Создавайте сложные оконные рамы для оконных функций.
  • Используйте самые важные аналитические функции: LEAD, LAG, VALUE.
  • Создавайте расширенную статистику, вычисляемую независимо для различных групп строк.
  • Освойте все навыки, которые вы приобрели в нашем обширном разделе практики.
  • Глубоко разбираться в агрегатных функциях PostgreSQL.

Кому следует пройти этот курс?

  • Пользователи PostgreSQL, которые хотят улучшить свои навыки работы с запросами
  • Разработчики, которые хотят освежить то, что они уже знают
  • Начинающие аналитики баз данных, работающие с PostgreSQL
  • Студенты, занимающиеся реляционными базами данных
  • Всем, кто хочет улучшить использование оконных функций PostgreSQL

Функции окна

SQL: интуитивно понятное руководство | Автор: До Ли

Основная цель этой статьи — понять фундаментальные концепции оконных функций и применить их к рабочему процессу SQL.Оконные функции — это не что иное, как FOPO (Function Over PartitionBy OrderBy). Вот краткое описание того, о чем я расскажу в этой статье.

  • The GROUP BY
  • Оконная функция — 4 основных компонента (FOPO)
  • The func () OVER ()
  • РАЗДЕЛ BY
  • ORDER BY
  • РАЗДЕЛЕНИЕ BY & ORDER BY
  • Сводка
  • Приложение

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

1 в GROUP BY 1 представляет позицию столбца группировки под SELECT. Столбцы, перечисленные в SELECT, похожи на массив с начальной позицией индекса 1.

В этой таблице product_orders каждый order_id содержит все позиции продукта (product_name), приобретенные клиентом. В правом верхнем углу цель SQL-запроса — вывести суммарную сумму для каждого order_id. Во-первых, order_id обозначается как столбец GROUP BY. Во-вторых, все различные суммы продукта, соответствующие идентификатору order_id, суммируются в одно значение (total_amount) с помощью агрегатной функции SUM. Теперь давайте подробнее рассмотрим механизм оконных функций.

  • Во-первых, функциональный компонент определяет обработку данных.Например, мы можем ранжировать на основе столбца (например, DENSE_RANK) или создавать ячейки равного размера (например, NTILE). Ознакомьтесь с доступными оконными функциями в Postgres здесь. Агрегатные функции (например, SUM, COUNT, AVG и т. Д.) Также могут использоваться как оконные функции — они перечислены здесь.
  • Во-вторых, предложение OVER — это клей, который скрепляет все вместе. Функции, указанные выше, вызываются или активируются с использованием предложения OVER для создания оконных функций.
  • В-третьих, как и GROUP BY, PARTITION BY похож на предложение subgroup-by.Вместо того, чтобы сворачивать набор данных, PARTITION BY создает подгруппы / блоки / разделы на основе указанного столбца или столбцов.
  • Наконец, предложение ORDER BY упорядочивает данные, опять же, на основе указанного столбца или столбцов.

Другая важная концепция заключается в том, что в зависимости от уровня обработки, необходимого для преобразования данных, не обязательно использовать все компоненты, за исключением предложения OVER. ( Помните: Предложение OVER вызывает оконные функции и позволяет агрегатным функциям становиться оконными функциями.) Вот варианты использования.

  • func () OVER ()
  • func (<столбец, целочисленное значение>) OVER ()
  • func () OVER (PARTITION BY )
  • func (<столбец, целочисленное значение>) OVER (РАЗДЕЛЕНИЕ ПО <столбец (-ы)>)
  • func () OVER (РАЗДЕЛЕНИЕ ПО <столбец (-ы)> ORDER BY <столбец (-ы)>)
  • func (<столбец, целочисленное значение>) OVER (РАЗДЕЛ ПО <столбцы> ORDER BY <столбцы>)

Подход OVER () без определения PARTITION BY и / или ORDER BY применяет функцию ко всему набору данных.Этот подход хорошо сочетается с агрегатными функциями. Целостность таблицы или набора данных сохраняется, пока применяется функция для вычисления значения с использованием всех строк.

В таблице заказов каждая строка представляет собой уникальный идентификатор заказа (первичный ключ). Я добавил еще несколько заказов в эту таблицу в демонстрационных целях.

В этом примере три агрегатные функции объединены с предложением OVER для создания трех оконных функций, представленных total_amount, total_count и total_mean. Столбец total_amount суммирует все значения в столбце суммы, а общая сумма отображается в каждой строке.Та же логика применима к total_count и total_mean. Эта настройка полезна при вычислении% от общего количества и создании соотношений к общему количеству или статистике, такой как среднее значение.

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

Как я упоминал ранее, PARTITION BY похож на GROUP BY.Лучший способ думать о PARTITION BY — это подгруппа (группа внутри группы). Как и GROUP BY, начните с определения столбца или столбцов PARTITION BY. Как только подгруппы определены, предложение OVER вызовет или позволит функции выполнить свою логику для каждой подгруппы. В отличие от GROUP BY, предложение PARTITION BY не уплотняет таблицу; вместо этого он поддерживает целостность таблицы при добавлении вывода в виде столбца.

Например, при использовании таблицы product_orders столбец PARTITION BY представляет собой order_id.Более того, количество подгрупп определяется уникальным числом order_id. В данном случае это число три.

  • NTILE: Число три равно количеству ячеек, а PARTITION BY определяет подгруппы (или разделы), в которых каждая подгруппа будет разделена на три ячейки.
  • FIRST_VALUE: функция выводит первое значение или строку в определенной подгруппе.
  • COUNT: подсчитывает количество строк в каждой определенной подгруппе.
  • СУММ: суммирует значения (например,грамм. сумма) в каждой подгруппе.
  • AVG: вычисляет среднее значение для каждой определенной подгруппы.

В выводе выделенные серые блоки представляют подгруппы, определенные предложением PARTITION BY, а также создание новых столбцов оконными функциями.

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

Используя предложение ORDER BY в функциях ранжирования, он генерирует упорядоченное представление на основе значений, найденных в столбце num_product_items и amount. Разница между функциями ранжирования показана в оранжевой выделенной области ниже. Напоминаем, что в предложении ORDER BY задано упорядочение по возрастанию (ASC). Для убывания это должно быть обозначено DESC (например, ORDER BY amount DESC).

  • ROW_NUMBER (): функция ранжируется последовательно по возрастанию или убыванию независимо от повторяющихся значений в столбце.
  • RANK (): функция ранжируется последовательно по возрастанию или убыванию, но при наличии повторяющихся значений будут пробелы. Например, в num_product_items есть два вхождения числа 1, и, таким образом, функция оценивает оба как 1 (в соответствии с rank_by_items). Далее, есть два вхождения 2 в num_product_items, и функция ранжирует их как 3. Функция пропустила ранг 2, потому что второе вхождение 1 уже захватило позицию 2-го ранга. Кроме того, поскольку второе вхождение 3 уже заняло позицию 4-го ранга, следующий ранг начинается с 5.
  • DENSE_RANK (): эта функция похожа на функцию RANK (), но не пропускает и не создает пробелов. В отличие от ROW_NUMBER (), где каждой строке назначается уникальный ранг независимо от повторяющихся значений, DENSE_RANK () будет применять тот же ранг для повторяющихся значений без пробелов.

После изучения механики PARTITION BY и ORDER BY по отдельности, следующим шагом будет объединение этих двух компонентов в пару. PARTITION BY создает подгруппы (или разделы) на основе выбранного столбца или набора столбцов.ORDER BY упорядочивает данные в порядке возрастания или убывания. Я проиллюстрирую сочетание этих компонентов с помощью таблицы product_orders.

ВЫБРАТЬ * ИЗ product_orders; Эта таблица состоит из трех идентификаторов order_id со списком приобретенных товаров.

В этом примере я использую девять оконных функций, чтобы продемонстрировать сочетание PARTITION BY и ORDER BY. На этом этапе, если у вас есть твердое представление о каждом компоненте, все должно быть просто. Описание каждой оконной функции для Postgres доступно здесь.

В этом SQL-запросе все оконные функции используют order_id в предложении PARTITION BY. Количество уникальных идентификаторов order_id равно количеству подгрупп, а столбец ORDER BY упорядочивает данные для каждой подгруппы.

Один пункт, не обсуждавшийся ранее, — это использование предложения «RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING» в функциях FIRST_VALUE (), LAST_VALUE () и NTH_VALUE (). Это предложение необходимо для определения первой и последней строки подгруппы. В этом предложении нет ничего особенного, за исключением того, что он должен быть включен для установки рамки оконной функции.

Ключевой вывод заключается в том, что оконные функции представляют собой объединение существующих концепций SQL, объединенных вместе, чтобы создать другой способ нарезки и нарезки данных. Использование оконных функций дает очевидные преимущества и позволяет быстро выводить преобразованные данные вместо того, чтобы тратить время на создание собственных пользовательских функций.

Мне нравится думать об оконных функциях по принципу FOPO (функция над разделом по порядку по порядку).

  • Сначала выберите функцию, наиболее подходящую для вашего варианта использования.
  • Во-вторых, помните, что предложение OVER вызывает оконную функцию (например, DENSE_RANK (), LAG () и т. Д.) И позволяет агрегатной функции стать оконной функцией.
  • В-третьих, предложение PARTITION BY определяет подгруппы (или разделы) — группу внутри группы, в которой выбранная функция выполняется через каждый раздел.
  • Наконец, предложение ORDER BY упорядочивает данные в каждом разделе на основе столбца или набора столбцов.

В Приложении я поделился операторами SQL, используемыми для создания моих таблиц, а также данных.Лучший способ учиться — это практиковаться, поэтому не стесняйтесь погружаться глубже самостоятельно. У вас уже есть основы, и теперь вам нужно просто собрать части, исходя из вашего варианта использования. Для дальнейшего обучения и практики ознакомьтесь с этими книгами « Learning SQL: Generate, Manipulate, and Retrieve Data » и « Practical SQL: A Beginner’s Guide to Storytelling with Data ».

Спасибо за чтение и получайте удовольствие от запросов!

Если у вас уже установлен Postgres на вашем компьютере, не стесняйтесь запускать эти операторы CREATE TABLE и INSERT, а также выполнять запросы SQL, которые я поделился ранее.Для загрузки перейдите на эту страницу.

Операторы CREATE TABLE для заказов и product_orders с операторами INSERT.

Metrics Maven: оконные функции в PostgreSQL

Добро пожаловать в серию статей о метриках Maven: возможности базы данных, советы, приемы и код

В нашей серии Metrics Maven специалист по обработке данных IBM Cloud Database делится функциями базы данных, советами, приемами и кодом, который вы можете использовать для получения необходимых вам показателей из ваших данных.В этой первой статье мы рассмотрим, как использовать оконные функции в PostgreSQL.

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

Если вы используете PostgreSQL, вы, вероятно, уже знакомы со многими общими агрегатными функциями, такими как COUNT () , SUM () , MIN () , MAX () и AVG ( ) . Однако вы, возможно, не знакомы с оконными функциями, поскольку они рекламируются как расширенная функция. Хорошая новость заключается в том, что оконные функции далеко не так эзотеричны, как могут показаться.

Как следует из названия, оконные функции предоставляют «окно» в ваши данные, позволяя выполнять агрегирование для набора строк данных в соответствии с заданными критериями, которые соответствуют текущей строке. Хотя они похожи на стандартные агрегаты, существуют также дополнительные функции, которые можно использовать только через оконные функции (например, функция RANK () , которую мы продемонстрируем ниже). В некоторых ситуациях оконные функции могут минимизировать сложность вашего запроса или даже повысить производительность.

Важно отметить, что оконные функции всегда используют предложение OVER () , поэтому, если вы видите OVER () , вы смотрите на оконную функцию. Когда вы привыкнете к форматированию предложения OVER () , его месту в ваших запросах и к каким результатам вы можете получить, вы скоро начнете видеть множество способов его применения. Давайте нырнем!

ВЫШЕ ()

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

Если агрегирование, которое вы хотите запустить, должно выполняться по всем строкам, возвращаемым запросом, и вам не нужно указывать какие-либо другие условия, вы можете использовать само предложение OVER () . Вот пример простой оконной функции, запрашивающей таблицу в нашей базе данных IBM Cloud Databases for PostgreSQL, содержащую данные переписи населения США о предполагаемом населении:

 ВЫБЕРИТЕ имя КАК имя_стояния,
     popestimate2015 как state_population,
     СУММ (popestimate2015)
          ВЫШЕ () КАК national_population
ОТ населения
WHERE state> 0 - только строки уровня состояния ORDER BY name; 

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

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

 ВЫБРАТЬ СУММ (popestimate2015) как national_population
ОТ населения
WHERE state> 0 - только строки уровня состояния; 

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

 ВЫБЕРИТЕ имя КАК имя_стояния,
       popestimate2015 как state_population,
       область,
       разделение,
       СУММ (popestimate2015)
            НАД (РАЗДЕЛЕНИЕ ПО делению) КАК Divisional_population
ОТ населения
WHERE state> 0 - только строки на уровне состояния
ЗАКАЗАТЬ ПО имени; 

В сравнении с этим выглядит некрасиво, не правда ли? Используя оконные функции, наш запрос намного проще и понятнее.

Условия

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

Итак, помимо формата возвращаемых строк, который мы рассмотрели выше, другое очевидное отличие оконных функций заключается в том, как синтаксис работает в ваших запросах.Используйте предложение OVER () с агрегатной функцией (например, SUM (), или AVG () ) и / или со специализированной оконной функцией (например, RANK (), или ROW_NUMBER () ) в вашем SELECT list, чтобы указать, что вы создаете окно, и при необходимости примените дополнительные условия к предложению OVER () , например, используя PARTITION BY (вместо GROUP BY , к которой вы можете привыкнуть для агрегирования) .

Давайте рассмотрим несколько конкретных примеров.

РАЗДЕЛЕНИЕ ПО

PARTITION BY позволяет группировать агрегаты в соответствии со значениями указанных полей.

В данных переписи предполагаемого населения каждый штат классифицирован в соответствии с территорией и территорией, к которой он принадлежит. Сначала разделим по регионам:

 ВЫБЕРИТЕ имя КАК имя_стояния,
       popestimate2015 как state_population,
       область,
       СУММ (popestimate2015)
            НАД (РАЗДЕЛЕНИЕ ПО РЕГИОНАМ) AS Regional_population
ОТ населения
WHERE state> 0 - только строки на уровне состояния
ЗАКАЗАТЬ ПО ИМЕНИ
; 

Теперь мы можем видеть общую численность населения по регионам, но по-прежнему получать данные на уровне штата:

Добавим деление:

 ВЫБЕРИТЕ имя КАК имя_стояния,
       popestimate2015 как state_population,
       область,
       разделение,
       СУММ (popestimate2015)
            НАД (РАЗДЕЛЕНИЕ ПО делению) КАК Divisional_population
ОТ населения
WHERE state> 0 - только строки на уровне состояния
ЗАКАЗАТЬ ПО имени; 

Теперь мы смотрим на данные на уровне штата, с разбивкой по регионам и районам, со сводкой по населению на уровне подразделений:

ЗАКАЗАТЬ В номере

Как вы, наверное, заметили в предыдущих запросах, мы используем ORDER BY обычным способом, чтобы упорядочить результаты по имени состояния, но мы также можем использовать ORDER BY в нашем предложении OVER () чтобы повлиять на расчет оконной функции.Например, мы хотели бы использовать ORDER BY в качестве условия для оконной функции RANK () , поскольку для ранжирования необходимо установить порядок. Давайте ранжируем штаты по самой высокой численности населения:

.

 ВЫБЕРИТЕ имя КАК имя_стояния,
       popestimate2015 как state_population,
       КЛАССИФИЦИРОВАТЬ()
            ВЫШЕ (ЗАКАЗ ПО popestimate2015 desc) AS state_rank
ОТ населения
WHERE state> 0 - только строки на уровне состояния
ЗАКАЗАТЬ ПО имени; 

В этом случае мы добавили ORDER BY popestimate2015 desc в качестве условия нашего предложения OVER () , чтобы описать, как должно выполняться ранжирование.Поскольку у нас все еще есть наше предложение ORDER BY name для нашего набора результатов, наши результаты будут по-прежнему располагаться в порядке названий штатов, но мы увидим, что население ранжируется соответствующим образом, при этом Калифорния выходит на первое место в зависимости от численности населения. :

Теперь объединим наши предложения оконных функций PARTITION BY и ORDER BY , чтобы увидеть ранжирование штатов по населению в каждом регионе. Для этого мы изменим наше предложение уровня результата ORDER BY name в конце на упорядочение по регионам, чтобы было понятно, как работает наша оконная функция:

 ВЫБЕРИТЕ имя КАК имя_стояния,
       popestimate2015 как state_population,
       область,
       КЛАССИФИЦИРОВАТЬ()
           ВЫШЕ (РАЗДЕЛЕНИЕ ПО РЕГИОНАМ ЗАКАЗАТЬ ПО popestimate2015 по убыванию) AS Regional_state_rank
ОТ населения
WHERE state> 0 - только строки на уровне состояния
ЗАКАЗАТЬ ПО региону; 

Наши результаты:

Здесь мы видим, что Иллинойс занимает первое место по численности населения в регионе Среднего Запада, а Нью-Йорк занимает первое место в Северо-Восточном регионе.

Итак, мы объединили здесь некоторые условия, но что, если нам нужно более одной оконной функции?

Именованные оконные функции

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

Вот пример, в котором мы определили две функции Windows. Один, названный «rw», разбивает по регионам, а другой, названный «dw», разбивает по разделам.Мы используем каждый из них дважды: один раз для расчета общей численности населения, а второй — для расчета средней численности населения. Наши функции Windows определены и названы с помощью предложения WINDOW , которое следует после предложения WHERE в нашем запросе:

 ВЫБЕРИТЕ имя КАК имя_стояния,
       popestimate2015 как state_population,
       область,
       СУММ (popestimate2015)
            ПРЕВЫШАТЬ rw AS Regional_population,
       AVG (popestimate2015)
            ВЫШЕ rw AS avg_regional_state_population,
       разделение,
       СУММ (popestimate2015)
            НАД dw AS Divisional_population,
       AVG (popestimate2015)
            ВЫШЕ dw AS avg_divisional_state_population
ОТ населения
WHERE state> 0 - только строки на уровне состояния
WINDOW rw AS (РАЗДЕЛЕНИЕ ПО РЕГИОНАМ),
       dw AS (РАЗДЕЛЕНИЕ ПО ПОДДЕЛЕНИЮ)
ЗАКАЗАТЬ ПО имени; 

Поскольку мы еще не производили никаких манипуляций со средними значениями, цифры выглядят немного сумасшедшими; которые можно легко очистить с помощью ROUND () и CAST () , если это необходимо.Наша цель здесь — продемонстрировать, как использовать функции нескольких окон и какие результаты вы получите. Проверить это:

Теперь это информативный отчет о показателях численности населения, а оконные функции упростили задачу!

Завершение

Эта статья познакомила вас с мощью оконных функций PostgreSQL. Мы коснулись преимуществ использования оконных функций, посмотрели, чем они отличаются (и похожи) на стандартные функции агрегирования, и узнали, как использовать их с различными условными предложениями, пройдя по примерам.Теперь, когда вы можете увидеть, как работают оконные функции, начните опробовать их, заменив стандартные агрегаты оконными функциями в ваших запросах. Как только вы освоите их, вас зацепят.

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

sql — оконная функция и группа Postgres по исключению

Вы , а не , фактически используете агрегатные функции.Вы используете оконные функции . Вот почему PostgreSQL требует включения sp.payout и s.buyin в предложение GROUP BY .

Добавляя предложение OVER , агрегатная функция sum () превращается в оконную функцию, которая агрегирует значения для каждого раздела, а сохраняет все строки .

Вы можете комбинировать оконные функции и агрегатные функции . Сначала применяются агрегаты.Из вашего описания я не понял, как вы хотите обрабатывать множественные выплаты / байины за событие. Предположительно, я подсчитываю их сумму для каждого события. Теперь я могу удалить sp.payout и s.buyin из предложения GROUP BY и получить одну строку на игрока и событие :

  ВЫБРАТЬ имя
     , e.event_id
     , дата
     , sum (sum (sp.payout)) БОЛЕЕ w
     - sum (sum (s.buyin)) OVER w AS "Прибыль / Убыток"
ОТ игрока p
JOIN результат r ON r.player_id = p.player_id
ПРИСОЕДИНЯЙТЕСЬ к игре g НА g.game_id = r.game_id
JOIN event e ON e.event_id = g.event_id
ПРИСОЕДИНЯЙТЕСЬ к структурам НА s.structure_id = g.structure_id
ПРИСОЕДИНЯЙТЕСЬ
                          И sp.position = r.position
ГДЕ p.player_id = 17
ГРУППА ПО e.event_id
ОКНО с AS (ЗАКАЗАТЬ ПО e.date, e.event_id)
ЗАКАЗАТЬ ПО e.date, e.event_id;
  

В этом выражении: сумма (сумма (пр.payout)) БОЛЕЕ w внешняя sum () является оконной функцией, внутренняя sum () является агрегатной функцией.

Предположим, что p. player_id и e.event_id — это ПЕРВИЧНЫЙ КЛЮЧ в соответствующих таблицах.

Я добавил e.event_id к ORDER BY предложения WINDOW , чтобы получить детерминированный порядок сортировки. (В одну дату может быть несколько событий.) Также в результат включен event_id , чтобы различать несколько событий в день.

Хотя запрос ограничивается одним игроком ( WHERE p.player_id = 17 ), нам не нужно добавлять p.name или p.player_id к GROUP BY и ORDER BY . Если одно из объединений приведет к чрезмерному умножению строк, итоговая сумма будет неверной (частично или полностью умноженной). Тогда группировка по p.name не смогла исправить запрос.

Я также удалил e.date из пункта GROUP BY .Первичный ключ e.event_id охватывает все столбцы входной строки, начиная с PostgreSQL 9.1.

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

  ...
ГДЕ p.player_id <17 - пример - несколько игроков
GROUP BY p.name, p.player_id, e.date, e.event_id - e.date и p.name избыточны
WINDOW w AS (ЗАКАЗАТЬ ПО p.name, p.player_id, e.date, e.event_id)
ЗАКАЗАТЬ ПО p.name, p.player_id, e.date, e.event_id;
  

Если не p.имя определяется уникальным (?), группа и порядок с помощью player_id дополнительно для получения правильных результатов в детерминированном порядке сортировки.

Я оставил только e.date и p.name в GROUP BY , чтобы иметь одинаковый порядок сортировки во всех предложениях, надеясь на повышение производительности. В противном случае вы можете удалить там столбцы. (Аналогично e.date в первом запросе.)

оконных функций SQL | Advanced SQL

Начиная с этого места? Этот урок является частью полного руководства по использованию SQL для анализа данных. Проверьте начало.

В этом уроке мы рассмотрим:

В этом уроке используются данные программы Capital Bikeshare в Вашингтоне, округ Колумбия, которая публикует подробные исторические данные на уровне поездок на своем веб-сайте. Данные были загружены в феврале 2014 года, но ограничены данными, собранными в течение первого квартала 2012 года. Каждая строка представляет собой одну поездку. Большинство полей говорят сами за себя, за исключением rider_type : «Зарегистрировано» означает ежемесячное членство в программе совместного использования поездок, «Обычный» означает, что гонщик купил 3-дневный проездной.Поля start_time и end_time были очищены от своих исходных форм для соответствия форматированию даты SQL - они хранятся в этой таблице как отметки времени.

Введение в оконные функции

Документация

PostgreSQL отлично описывает концепцию оконных функций:

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

Наиболее практичным примером этого является накопительный итог:

  ВЫБРАТЬ продолжительность_секунды,
       SUM (duration_seconds) OVER (ORDER BY start_time) КАК running_total
  ИЗ tutorial.dc_bikeshare_q1_2012
  

Вы можете видеть, что приведенный выше запрос создает агрегацию ( running_total ) без использования GROUP BY .Давайте разберем синтаксис и посмотрим, как он работает.

Базовый синтаксис работы с окнами

Первая часть вышеуказанного агрегирования, SUM (duration_seconds) , очень похожа на любую другую агрегацию. Добавление НАД обозначает его как оконную функцию. Вы можете прочитать приведенную выше агрегацию как «возьмите сумму duration_seconds по всему результирующему набору в порядке start_time ».

Если вы хотите сузить диапазон от всего набора данных до отдельных групп в наборе данных, вы можете использовать PARTITION BY для этого:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       SUM (duration_seconds) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_total
  ИЗ учебника.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
  

Вышеупомянутый запрос группирует и упорядочивает запрос по start_terminal . В каждом значении start_terminal он упорядочивается по start_time , а промежуточные итоговые суммы по текущей строке и всем предыдущим строкам составляют duration_seconds . Прокрутите вниз, пока значение start_terminal не изменится, и вы заметите, что running_total начинается заново. Вот что происходит, когда вы группируете с помощью PARTITION BY .Если вы все еще в тупике, ORDER BY просто упорядочивает по указанным столбцам так же, как предложение ORDER BY , за исключением того, что он обрабатывает каждый раздел как отдельный. Он также создает промежуточную сумму - без ORDER BY каждое значение будет просто суммой всех значений duration_seconds в соответствующем start_terminal . Попробуйте выполнить вышеуказанный запрос без ORDER BY , чтобы получить представление:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       SUM (duration_seconds) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal) AS start_terminal_total
  ИЗ учебника.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
  

ORDER и PARTITION определяют то, что называется «окном» - упорядоченное подмножество данных, по которым производятся вычисления.

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

Практическая задача

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

Попробуй это
Посмотреть ответ

Обычные подозреваемые: SUM, COUNT и AVG

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

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       SUM (duration_seconds) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal) AS running_total,
       COUNT (duration_seconds) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal) AS running_count,
       AVG (duration_seconds) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal) AS running_avg
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
  

В качестве альтернативы те же функции с ЗАКАЗАТЬ ПО :

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       SUM (duration_seconds) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_total,
       COUNT (duration_seconds) БОЛЕЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_count,
       AVG (duration_seconds) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY start_time)
         AS running_avg
  ИЗ учебника.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
  

Убедитесь, что вы включили эти два предыдущих запроса в режим и выполняете их. Эта следующая практическая задача очень похожа на примеры, поэтому попробуйте изменить приведенный выше код, а не начинать с нуля.

Практическая задача

Напишите запрос, который показывает общую продолжительность поездок на велосипеде (аналогичный последнему примеру), но сгруппированный по end_terminal , а продолжительность поездки отсортирована в порядке убывания.

Попробуй это
Посмотреть ответ

ЧИСЛО СТРОКИ ()

ROW_NUMBER () делает именно то, на что похоже - отображает номер данной строки. Он начинается с 1 и нумерует строки в соответствии с частью ORDER BY оператора окна. ROW_NUMBER () не требует указывать переменную в круглых скобках:

  ВЫБРАТЬ start_terminal,
       начальное время,
       duration_seconds,
       ROW_NUMBER () OVER (ЗАКАЗАТЬ ПО start_time)
                    AS row_number
  ИЗ учебника.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
  

Использование предложения PARTITION BY позволит вам снова начать отсчет 1 в каждом разделе. Следующий запрос снова запускает счет для каждого терминала:

  ВЫБРАТЬ start_terminal,
       начальное время,
       duration_seconds,
       ROW_NUMBER () ВЫШЕ (РАЗДЕЛ НА start_terminal
                          ЗАКАЗАТЬ ПО start_time)
                    AS row_number
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
  

RANK () и DENSE_RANK ()

RANK () немного отличается от ROW_NUMBER () .Если вы заказываете до start_time , например, может случиться так, что на некоторых терминалах есть поездки с двумя одинаковыми временами начала. В этом случае им присваивается один и тот же ранг, тогда как ROW_NUMBER () дает им разные номера. В следующем запросе вы заметили 4-е и 5-е наблюдения для start_terminal 31000 - им обоим присвоен ранг 4, а следующий результат получает ранг 6:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       RANK () OVER (РАЗДЕЛЕНИЕ ПО start_terminal
                    ЗАКАЗАТЬ ПО start_time)
              Рейтинг AS
  ИЗ учебника. dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
  

Вы также можете использовать DENSE_RANK () вместо RANK () в зависимости от вашего приложения. Представьте себе ситуацию, в которой три записи имеют одинаковое значение. Используя любую команду, все они получат одинаковый ранг. Для этого примера предположим, что это «2». Вот как две команды будут по-разному оценивать следующие результаты:

  • RANK () присвоит идентичным строкам ранг 2, затем пропустит ранги 3 и 4, поэтому следующий результат будет 5
  • DENSE_RANK () все равно присвоит всем идентичным строкам ранг 2, но следующая строка будет иметь ранг 3 - никакие ранги не будут пропущены.

Практическая задача

Напишите запрос, который показывает 5 самых длинных поездок из каждого стартового терминала, упорядоченных по терминалам, и самые длинные поездки в пределах каждого терминала. Ограничены поездками, совершенными до 8 января 2012 года.

Попробуй это
Посмотреть ответ

NTILE

Вы можете использовать оконные функции, чтобы определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Синтаксис: NTILE (* # сегментов *) .В этом случае ORDER BY определяет, какой столбец использовать для определения квартилей (или любого указанного вами количества плиток). Например:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       NTILE (4) НАД
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
          Квартиль AS,
       NTILE (5) НАД
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Квинтиль AS,
       NTILE (100) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Процентиль AS
  ИЗ учебника.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
 ЗАКАЗАТЬ ПО start_terminal, duration_seconds
  

Глядя на результаты вышеприведенного запроса, можно увидеть, что столбец процентиль рассчитывается не так, как вы могли ожидать. Если бы у вас было только две записи и вы измеряли процентили, можно было бы ожидать, что одна запись будет определять 1-й процентиль, а другая - 100-й процентиль. Используя функцию NTILE , вы фактически увидите одну запись в 1-м процентиле и одну во 2-м процентиле.Вы можете увидеть это в результатах для start_terminal 31000 - столбец процентиль выглядит как числовое ранжирование. Если вы прокрутите вниз до start_terminal 31007, вы увидите, что процентили вычисляются правильно, поскольку для этого start_terminal имеется более 100 записей. Если вы работаете с очень маленькими окнами, помните об этом и подумайте об использовании квартилей или аналогичных небольших полос.

Практическая задача

Напишите запрос, который показывает только продолжительность поездки и процентиль, в который она попадает (по всему набору данных, а не по терминалам).

Попробуй это
Посмотреть ответ

LAG и LEAD

Часто бывает полезно сравнить строки с предыдущими или последующими строками, особенно если у вас есть данные в разумном порядке. Вы можете использовать LAG или LEAD для создания столбцов, которые извлекают значения из других строк - все, что вам нужно сделать, это указать, из какого столбца извлекать данные и сколько строк вы хотите извлекать. LAG тянет из предыдущих рядов и LEAD тянет из следующих рядов:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       LAG (duration_seconds; 1) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) КАК задержка,
       LEAD (duration_seconds; 1) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds) КАК ведущий
  ИЗ учебника.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
 ЗАКАЗАТЬ ПО start_terminal, duration_seconds
  

Это особенно полезно, если вы хотите вычислить разницу между строками:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Как разница
  ИЗ tutorial. dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
 ЗАКАЗАТЬ ПО start_terminal, duration_seconds
  

Первая строка столбца разница пуста, потому что нет предыдущей строки, из которой можно было бы извлечь.Аналогично, использование LEAD создаст нули в конце набора данных. Если вы хотите сделать результаты немного чище, вы можете заключить его во внешний запрос, чтобы удалить нули:

  ВЫБРАТЬ *
  ИЗ (
    ВЫБЕРИТЕ start_terminal,
           duration_seconds,
           duration_seconds -LAG (duration_seconds, 1) ВЫШЕ
             (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
             Как разница
      ИЗ tutorial.dc_bikeshare_q1_2012
     ГДЕ start_time <'2012-01-08'
     ЗАКАЗАТЬ ПО start_terminal, duration_seconds
       ) sub
 ГДЕ под.разница НЕ НУЛЬ
  

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

Если вы планируете написать несколько оконных функций в одном запросе, используя одно и то же окно, вы можете создать псевдоним. Возьмите пример NTILE выше:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       NTILE (4) НАД
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Квартиль AS,
       NTILE (5) НАД
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Квинтиль AS,
       NTILE (100) ВЫШЕ
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
         Процентиль AS
  ИЗ учебника.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
 ЗАКАЗАТЬ ПО start_terminal, duration_seconds
  

Это можно переписать как:

  ВЫБРАТЬ start_terminal,
       duration_seconds,
       NTILE (4) OVER ntile_window AS,
       NTILE (5) OVER ntile_window AS, квинтиль,
       NTILE (100) OVER ntile_window AS процентиль
  ИЗ tutorial.dc_bikeshare_q1_2012
 ГДЕ start_time <'2012-01-08'
ОКНО ntile_window AS
         (РАЗДЕЛЕНИЕ ПО start_terminal ORDER BY duration_seconds)
 ЗАКАЗАТЬ ПО start_terminal, duration_seconds
  

Предложение WINDOW , если оно есть, всегда должно стоять после предложения WHERE .

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *