Что такое оконные функции в sql

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

Фактически мы познакомились с этими функциями, когда рассматривали функции ранжирования. Только сейчас мы будем использовать агрегатные функции вместо функций, которые задают номер/ранг строки. Есть еще одно отличие (в реализации Майкрософт SQL Server 2005/2008) – предложение OVER () не содержит дополнительного предложения ORDER BY , поскольку значение агрегата не зависит от сортировки строк в «окне».

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

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

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

Синтаксис

function OVER < window_name | ( window_name ) | window_spec >function < ranking_function | analytic_function | aggregate_function >over_clause OVER < window_name | ( window_name ) | window_spec >window_spec ( [ PARTITION BY partition [ , . ] ] [ order_by ] [ window_frame ] )

Параметры

Функции Функция, работающая в окне. Различные классы функций поддерживают разные конфигурации спецификаций окон.
- ranking_function Любая функция окна ранжирования. Если этот window_spec должен содержать предложение ORDER BY, но не предложение window_frame.
- analytic_function Любая функция окна Аналитика.
- aggregate_function Любая из агрегатных функций. Если указано, функция не должна включать предложение FILTER.
- Раздел Одно или несколько выражений, используемых для указания группы строк, определяющих область, с которым работает функция. Если предложение PARTITION не указано, секция состоит из всех строк.
- order_by Предложение ORDER BY задает порядок строк в секции.
- window_frame Предложение рамки окна указывает скользящее подмножество строк в секции, с которой работает агрегатная или аналитическая функция.
Вы можете указать SORT BY в качестве псевдонима для ORDER BY.

Вы также можете указать DISTRIBUTE BY в качестве псевдонима для PARTITION BY. При отсутствии ORDER BY можно использовать CLUSTER BY в качестве псевдонима для PARTITION BY.

Примеры
```
> CREATE TABLE employees (name STRING, dept STRING, salary INT, age INT); > INSERT INTO employees VALUES ('Lisa', 'Sales', 10000, 35), ('Evan', 'Sales', 32000, 38), ('Fred', 'Engineering', 21000, 28), ('Alex', 'Sales', 30000, 33), ('Tom', 'Engineering', 23000, 33), ('Jane', 'Marketing', 29000, 28), ('Jeff', 'Marketing', 35000, 38), ('Paul', 'Engineering', 29000, 23), ('Chloe', 'Engineering', 23000, 25); > SELECT name, dept, salary, age FROM employees; Chloe Engineering 23000 25 Fred Engineering 21000 28 Paul Engineering 29000 23 Helen Marketing 29000 40 Tom Engineering 23000 33 Jane Marketing 29000 28 Jeff Marketing 35000 38 Evan Sales 32000 38 Lisa Sales 10000 35 Alex Sales 30000 33 > SELECT name, dept, RANK() OVER (PARTITION BY dept ORDER BY salary) AS rank FROM employees; Lisa Sales 10000 1 Alex Sales 30000 2 Evan Sales 32000 3 Fred Engineering 21000 1 Tom Engineering 23000 2 Chloe Engineering 23000 2 Paul Engineering 29000 4 Helen Marketing 29000 1 Jane Marketing 29000 1 Jeff Marketing 35000 3 > SELECT name, dept, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS dense_rank FROM employees; Lisa Sales 10000 1 Alex Sales 30000 2 Evan Sales 32000 3 Fred Engineering 21000 1 Tom Engineering 23000 2 Chloe Engineering 23000 2 Paul Engineering 29000 3 Helen Marketing 29000 1 Jane Marketing 29000 1 Jeff Marketing 35000 2 > SELECT name, dept, age, CUME_DIST() OVER (PARTITION BY dept ORDER BY age RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cume_dist FROM employees; Alex Sales 33 0.3333333333333333 Lisa Sales 35 0.6666666666666666 Evan Sales 38 1.0 Paul Engineering 23 0.25 Chloe Engineering 25 0.50 Fred Engineering 28 0.75 Tom Engineering 33 1.0 Jane Marketing 28 0.3333333333333333 Jeff Marketing 38 0.6666666666666666 Helen Marketing 40 1.0 > SELECT name, dept, salary, MIN(salary) OVER (PARTITION BY dept ORDER BY salary) AS min FROM employees; Lisa Sales 10000 10000 Alex Sales 30000 10000 Evan Sales 32000 10000 Helen Marketing 29000 29000 Jane Marketing 29000 29000 Jeff Marketing 35000 29000 Fred Engineering 21000 21000 Tom Engineering 23000 21000 Chloe Engineering 23000 21000 Paul Engineering 29000 21000 > SELECT name, salary, LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag, LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead FROM employees; Lisa Sales 10000 NULL 30000 Alex Sales 30000 10000 32000 Evan Sales 32000 30000 0 Fred Engineering 21000 NULL 23000 Chloe Engineering 23000 21000 23000 Tom Engineering 23000 23000 29000 Paul Engineering 29000 23000 0 Helen Marketing 29000 NULL 29000 Jane Marketing 29000 29000 35000 Jeff Marketing 35000 29000 0 
```
Связанные статьи
- ВЫБЕРИТЕ
- ORDER BY
- предложение window frame
- именованное окно
- Запроса
- Агрегатные функции
- Функции окна аналитики
- Функции окна ранжирования
Что такое оконные функции в sql

Скачай курс
в приложении

Перейти в приложение
Открыть мобильную версию сайта

© 2013 — 2024. Stepik

Наши условия использования и конфиденциальности

Public user contributions licensed under cc-wiki license with attribution required

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

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

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

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

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

Окей, с 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; 
```
```
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; 
```
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 и другие), но об этом поговорим в другой раз.
Похожие публикации: