Что такое оконная функция

Руководство по оконным функциям

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

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

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)

Первые три выходных столбца взяты непосредственно из таблицы empsalary, и для каждой строки в таблице есть одна выходная строка. Четвертый столбец представляет среднее значение по всем строкам таблицы, которые имеют то же значение depname что и текущая строка. (На самом деле это та же функция, что и обычный агрегат avg, но предложение OVER приводит к тому, что она обрабатывается как оконная функция и вычисляет результат в рамках тех значений, что выделены для расчета в окне).

Вызов оконной функции всегда содержит предложение OVER непосредственно после имени и аргумента(ов) оконной функции. Это то, что синтаксически отличает её от обычной функции или обычного агрегата. Предложение OVER точно определяет, как строки запроса разделяются для обработки оконной функцией. Предложение PARTITION BY в OVER делит строки на группы или разделы, которые имеют одинаковые выражения значений PARTITION BY. Для каждой строки оконная функция вычисляется по строкам, которые попадают в тот же раздел, что и текущая строка.

Вы также можете контролировать порядок, в котором строки обрабатываются оконными функциями, используя ORDER BY в OVER. (Окно ORDER BY даже не должно соответствовать порядку, в котором выводятся строки). Вот пример:

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

depname | empno | salary | rank -----------+-------+--------+------ develop | 8 | 6000 | 1 develop | 10 | 5200 | 2 develop | 11 | 5200 | 2 develop | 9 | 4500 | 4 develop | 7 | 4200 | 5 personnel | 2 | 3900 | 1 personnel | 5 | 3500 | 2 sales | 1 | 5000 | 1 sales | 4 | 4800 | 2 sales | 3 | 4800 | 2 (10 rows)

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

Строки, рассматриваемые оконной функцией, являются строками «виртуальной таблицы», созданной предложением FROM запроса, отфильтрованным по его WHERE, GROUP BY и HAVING если таковые имеются. Например, строка, удаленная из резултата из-за несоответствия условию WHERE не видна ни одной оконной функции. Запрос может содержать несколько оконных функций, которые по-разному разбивают данные на части с использованием разных предложений OVER, но все они действуют на одну и ту же коллекцию строк, определенных этой виртуальной таблицей.

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

Есть еще одна важная концепция, связанная с оконными функциями: для каждой строки в ее разделе есть набор строк, называемый кадром окна. Некоторые оконные функции действуют только на строки кадра окна, а не на весь раздел. По умолчанию, если задано ORDER BY, то кадр состоит из всех строк от начала раздела до текущей строки, а также любых последующих строк, которые равны текущей строке в соответствии с предложением ORDER BY. Если ORDER BY опущен, кадр по умолчанию состоит из всех строк в разделе 1 . Вот пример использования sum:

SELECT salary, sum(salary) OVER () FROM empsalary;

salary | sum --------+------- 5200 | 47100 5000 | 47100 3500 | 47100 4800 | 47100 3900 | 47100 4200 | 47100 4500 | 47100 4800 | 47100 6000 | 47100 5200 | 47100 (10 rows)

Мы видим что, поскольку в предложении OVER нет ORDER BY, кадр окна совпадает с разделом, который в отсутствие PARTITION BY и представляет собой всю таблицу; другими словами, каждая сумма берется по всей таблице, и поэтому мы получаем одинаковый результат для каждой выходной строки. Но если мы добавим предложение ORDER BY, мы получим совсем другие результаты:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

salary | sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 25700 4800 | 25700 5000 | 30700 5200 | 41100 5200 | 41100 6000 | 47100 (10 rows)

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

Оконные функции разрешены только в списке SELECT и предложении ORDER BY запроса. Они запрещены в других местах, например в предложениях GROUP BY, HAVING и WHERE. Это потому, что они логически выполняются после обработки этих предложений. Кроме того, оконные функции выполняются после обычных агрегатных функций. Это означает, что допустимо включать вызов агрегатной функции в аргументы оконной функции, но не наоборот.

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

SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary ) AS ss WHERE pos < 3;

Приведенный выше запрос показывает только строки из внутреннего запроса, имеющие ранг менее 3.

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

SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

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

SQL-Ex blog

Эта статья является руководством по использованию оконных функций SQL в приложениях, для которых требуется выполнять тяжелые вычислительные запросы. Данные множатся с поразительной скоростью. В 2022 в мире произведено и потреблено 94 зетабайтов данных. Сегодня у нас есть множество инструментов типа Hive и Spark для обработки Big Data. Несмотря на то, что эти инструменты различаются по типам проблем, для решения которых они спроектированы, они используют базовый SQL, что облегчает работу с большими данными. Оконные функции являются примером одной из таких концепций SQL. Это необходимо знать инженерам-программистам и специалистам по данным.

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

Документация PostgreSQL дает хорошее введение в эту концепцию:

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

Сравнение оконных и агрегатных функций

AVG() - возвращает среднее значений указанного столбца.
SUM() - возвращает сумму всех значений.
MAX(), MIN() - возвращают максимальное и минимальное значения.
COUNT() - возвращает общее число значений

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

SELECT date, city, AVG(amount) AS avg_transaction_amount_for_city 
FROM transactions 
GROUP BY date, city;

Результат этого запроса показан ниже.

Агрегатная функция AVG() и GROUP BY дают нам среднее значение, сгруппированное по дате и городу. Если посмотреть на строки за 2-е ноября, то мы имеем две транзакции в New York, а 3-го ноября - две транзакции в San Francisco. В результирующем наборе отдельные строки свернуты в единственную строку, представляющую агрегатные значения для каждой группы.

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

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

SELECT id, date, city, amount, 
AVG(amount) OVER (PARTITION BY date, city) AS avg_daily_transaction_amount_for_city 
FROM transactions 
ORDER BY id;

Результат:

Обратите внимание, что строки не сворачиваются. Присутствует по одной строке на каждую транзакцию и вычисленные средние значения в avg_daily_transaction_amount_for_city.

На диаграмме ниже показана разница между агрегатными и оконными функциями.

Сходство и различие между оконными и агрегатными функциями

Работают с множеством строк
Вычисляют агрегатные величины
Группируют или секционируют данные по одному или нескольким столбцам

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

Использование OVER() вместо GROUP BY для определения множества строк
Использование большего числа функций в дополнение к агрегатным, например: RANK(), LAG(), LEAD()
Могут группировать строки по их рангу, процентилю и т.д. в дополнение к значениям столбца
Не сворачивают строки в единственную строку на группу
Могут использовать скользящую рамку окна на основе текущей строки

Зачем использовать оконные функции?

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

Синтаксис оконной функции

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

Мы хотим вычислить накопительные итоги транзакций за каждый день в каждом городе. Запрос ниже делает это.

SELECT id, city, 
date, 
SUM(amount) OVER 
(PARTITION BY city ORDER BY date) 
AS running_total 
FROM transactions

Первая часть агрегата выше, SUM(amount), выглядит подобно любой другой агрегации. Добавление OVER означает, что это оконная функция. PARTITION BY сужает окно со всего набора данных до отдельных групп в рамках этого набора данных. Вышеприведенный запрос группирует данные по городу (city) и упорядочивает их по дате (date). Внутри каждой группы города данные упорядочиваются по дате и накопительные итоги суммируются от текущей строки и всех предыдущих строк группы. При изменении значения города можно заметить, что значение накопительных итогов (running_total) начинается заново для этого города. Вот результаты этого запроса:

ORDER BY и PARTITION BY определяют то, что является окном - упорядоченный набор данных над которым выполняются вычисления.

Типы оконных функций

Агрегатные функции: Эти функции вычисляют единственное значение для множества строк
- SUM(), MAX(), MIN(), AVG(), COUNT()
- RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
- LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()
- FIRST_VALUE() and LAST_VALUE().
Еще примеры использования оконных функций

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

Рассмотрим несколько примеров. Таблица ниже, которая называется train_schedule содержит train_id, станцию (station) и время (time) прибытия поездов в районе залива Сан-Франциско. Нам нужно вычислить время до следующей станции для каждого поезда в расписании.

Это можно вычислить как разность времен прибытия для каждой пары соседних станций для каждого поезда. Вычисление без использования оконных функций может оказаться более сложным. Большинство разработчиков считывали бы таблицу в память и использовали логику приложения для вычисления значений. Оконная функция LEAD здорово упрощает эту задачу.
```
SELECT 
train_id, 
station, 
time as "station_time", 
lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
AS time_to_next_station 
FROM train_schedule 
ORDER BY 1 , 3;
```
Мы создаем наше окно СЕКЦИОНИРОВАНИЕМ по train_id и сортируя секцию по time (времени прибытия на станцию). Оконная функция LEAD() получает значение столбца из следующей строки в окне. Мы вычисляем время до следующей станции вычитанием из времени, полученного посредством оконной функции LEAD, времени из столбца time текущей строки. Результаты показаны ниже.

Опираясь на предыдущий пример, представим, что нам нужно вычислить полное время поездки до текущей станции. Вы можете использовать оконную функцию MIN() для получения времени отправления для каждого окна и вычесть его из текущего времени прибытия на станцию для каждой строки в окне.
```
SELECT 
train_id, 
station, 
time as "station_time", 
time - min(time) OVER (PARTITION BY train_id ORDER BY time) 
AS elapsed_travel_time, 
lead(time) OVER (PARTITION BY train_id ORDER BY time) - time 
AS time_to_next_station 
FROM train_schedule;
```
Наше окно не изменилось. Мы по-прежнему выполняем разбиение по train_id и упорядочиваем окно по времени прибытия на станцию. Изменились только вычисления, которые мы выполняем для каждой строки в окне. В запросе выше мы вычитаем из времени текущей строки самое раннее время в окне, которое имеет место, когда поезд отходит от первой станции. Это дает нам время поездки для каждой станции по ходу поезда.

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

Ссылки по теме
1. Накопительные итоги

Оконные функции T-SQL

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

В истории поддержки стандарта оконных функций в Microsoft SQL Server есть две ключевые точки:
- В SQL Server 2005 была реализована поддержка стандартной функциональности.
- В SQL Server 2012 поддержка оконных функций была расширена.
Отсутствует поддержка некоторой стандартной функциональности, но с улучшениями в SQL Server 2012, поддержку оконных функций можно считать достаточно обширной. В этом небольшом руководстве я расскажу как о реализации этой функциональности в SQL Server, так и о стандартной функциональности, которая в этом сервере отсутствует. Каждый раз, упоминая новую функциональность, я буду указывать, поддерживается ли она в SQL Server, а также в какой версии появилась эта поддержка.

С момента появления поддержки оконных функций в SQL Server 2005 я обнаружил, что все чаще использую эти функции для совершенствования своих решений. Я методично заменяю старые решения, в которых применяются классические, традиционные конструкции языка, более новыми оконными функциями. А результаты обычно удается получить проще и более эффективно. Это настолько удобно, что в большинстве своих решений, требующих запроса данных, я теперь использую оконные функции.

Также стандартные SQL-системы и реляционные системы управления базами данных (РСУБД) все больше движутся в сторону аналитических решений, и оконные функции являются важной частью этой тенденции. Поэтому мне кажется, что оконным функциям принадлежит будущее в области запроса данных средствами SQL Server, а время, затраченное на их изучение, не пропадет зря.

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

1. Окна в SQL Server
1. Основы оконных функций
2. Обзор решений с использованием оконных функций
3. Структура оконных функций
4. Запросы
5. Агрегатные функции
6. Вложенные операторы в агрегатных функциях
7. Функции ранжирования
8. Аналитические функции
9. Функции смещения
2. Сортировка и оптимизация
1. Функции гипотетического набора
2. Функции обратного распределения и смещения
3. Конкатенация строк
4. Индексирование
5. Оптимизация функций ранжирования
6. Использование APPLY
7. Оптимизация функций агрегирования и смещения
8. Оптимизация аналитических функций
3. Решения с использованием оконных функций
1. Вспомогательные виртуальные таблицы чисел
2. Последовательности значений даты и времени
3. Последовательности ключей
4. Разбиение на страницы
5. Удаление повторений
6. Сведение данных
7. Выбор первых n элементов в группе
8. Моды
9. Вычисление нарастающих итогов
10. Максимальное количество параллельных интервалов
11. Упаковка интервалов
12. Пробелы и диапазоны
13. Медианы
14. Условные агрегаты
15. Сортировка иерархий
Оконные функции SQL

2 Май 2020 , Data engineering, 86112 просмотров, Introduction to Window Functions in SQL

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

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

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

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

В связи с пандемией коронавируса необходимо оптимизировать расходы путем сокращения сотрудников или понижения их зарплат. Ваш вечнонедовольный директор приходит к вам с просьбой выяснить кто получает больше всего в каждом департаменте. Как поступить? Можно использовать агрегатные функции, в нашем случае MAX , чтобы выяснить максимальную зарплату в каждом отделе:
```
SELECT department, MAX(gross_salary) as max_salary FROM Salary GROUP BY 1; 
```
Результат выполнения запроса:

Чтобы узнать кто эти "счастливчики" на сокращение можно выделить запрос в подзапрос и объединить с исходной таблицей путём JOIN:
```
SELECT id, first_name, department, t.gross_salary FROM Salary JOIN ( SELECT department, MAX(gross_salary) as gross_salary FROM Salary GROUP BY 1 ) t USING(gross_salary, department); 
```
Но тут вы вспоминаете, что эту же задачу можно решить, используя оконные функции, которые вы проходили на одной из лекций по SQL в универе в бородатом году. Как? Используя всё ту же агрегатную функцию MAX , задав "окно". Окном в нашем случае будут сотрудники одного департамента (строки с одинаковым значением в колонке department).
```
SELECT id, first_name, department, gross_salary, MAX(gross_salary) OVER (PARTITION BY department) as max_gross_salary FROM Salary; 
```
Окно задаётся через выражение OVER (PARTITION BY ), т.е. строки мы как бы группируем по признаку в указанных колонках, конкретно в этом случае по признаку принадлежности к департаменту в компании. Результат запроса:

Чтобы отфильтровать потенциальных кандидатов на сокращение можно выделить запрос в подзапрос:
```
SELECT * FROM ( SELECT id, first_name, department, gross_salary, MAX(gross_salary) OVER (PARTITION BY department) as max_gross_salary FROM Salary ) t WHERE max_gross_salary = gross_salary ORDER BY id; 
```
Результат будет точно таким же как и при объединении. Итак, с чувством собственного величия, ощущая себя цифровым палачом вы отправляете результат своему начальнику. Он смотрит на вывод и говорит, что у Аркадия из IT отдела зарплата 300 000, но другой сотрудник в этом же отделе может получать 295 000, разница между ними будет несущественна. Покажи мне пропорцию зарплат в отделе относительно суммы всех зарплат в этом отделе, а также относительно всего фонда оплаты труда!

Как решать? Можно пойти тем же путём, используя подзапросы:
```
WITH gross_by_departments AS ( SELECT department, SUM(gross_salary) as dep_gross_salary FROM Salary GROUP BY 1 ) SELECT id, first_name, department, gross_salary, ROUND(CAST(gross_salary AS numeric(9, 2)) / dep_gross_salary * 100, 2) as dep_ratio, ROUND(CAST(gross_salary AS numeric(9, 2)) / (SELECT SUM(gross_salary) FROM Salary) * 100, 2) as total_ratio FROM Salary JOIN gross_by_departments USING(department) ORDER BY department, dep_ratio DESC 
```
На этой таблице видно, что зарплата Нины это 71% расходов на HR отдел, но лишь 10.5% от всего ФОТ, а вот Аркадий выделился, конечно. Его зарплата это 41% от зарплаты всего IT отдела и 21% от всего ФОТ! Идеальный кандидат на сокращение �� Но не кажется ли вам, что SQL запрос малость сложный? Давайте попробуем его написать через оконные функции:
```
SELECT id, first_name, department, gross_salary, ROUND(CAST(gross_salary AS numeric(9,2)) / SUM(gross_salary) OVER (PARTITION BY department) * 100, 2) as dep_ratio, ROUND(CAST(gross_salary AS numeric(9,2)) / SUM(gross_salary) OVER () * 100, 2) as total_ratio FROM Salary ORDER BY department, dep_ratio DESC; 
```
Кратко, понятно, содержательно! Выражение OVER() означает, что окном для применения функции являются все строки, т.е. SUM(gross_salary) OVER() , означает что сумма будет посчитана по всем зарплатам независимо от департамента в котором работает сотрудник.

Что дальше

В примере выше мы использовали исключительно агрегатные функции как оконные, но в стандарте SQL есть исключительно оконные функции, которые невозможно использовать как агрегатные, это значит, что их невозможно применить при обычной группировке. Вот лишь часть оконных функций, доступных в PostgreSQL:
- first_value
- last_value
- lead
- lag
- rank
- dense_rank
- row_number
Со всеми доступными оконными функциями можно ознакомиться в официальной документации PostgreSQL.

Использование оконных функций

В задаче определения самого высокооплачиваемого сотрудника мы использовали агрегатные функции MAX , SUM , давайте рассмотрим чисто оконную функцию first_value . Она возвращает первое значение согласно заданного окна, т.е. применимо к нашей задаче она должна вернуть имя сотрудника у которого самая высокая зарплата в департаменте.
```
SELECT id, first_name, department, gross_salary, first_value(first_name) OVER (PARTITION BY department ORDER BY gross_salary DESC ) as highest_paid_employee FROM Salary 
```
last_value делает то же самое только наоборот, возвращает самую последнюю строчку. Давайте найдём с помощью неё самого низкооплачиваемого сотрудника в департаменте.
```
SELECT id, first_name, department, gross_salary, last_value(first_name) OVER (PARTITION BY department ORDER BY gross_salary DESC) AS lowest_paid_employee FROM Salary 
```
Если внимательно взглянуть на результат выполнения запроса, то можно понять, что он неверный. Почему? А потому что мы не указали диапазон/границы окна относительно текущей строки. По умолчанию, если не задано выражение ORDER BY внутри OVER , то границами окна являются все строки, если ORDER BY задан, то границей для текущей строки будут все предшествующие строки и текущая, в терминах SQL это ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . В этом можно убедиться, если внимательно взглянуть на результат выполнения крайнего запроса.

Как исправить ситуацию? Расширить границы окна. Перепишем наш запрос, указав в качестве границ все предшествующие строки в окне и все последующие. В терминах SQL это выражение ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING :
```
SELECT id, first_name, department, gross_salary, last_value(first_name) OVER ( PARTITION BY department ORDER BY gross_salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as lowest_paid_employee FROM Salary 
```
Визуально это выглядит примерно как на картинке ниже.

Границы можно определять рядом выражений:
- N PRECEDING, N строк до текущей строки
- CURRENT ROW, текущая строка
- UNBOUNDED PRECEDING, все строки, предшествующие текущей
- UNBOUNDED FOLLOWING, все последующие строки
- N FOLLOWING, N строк после текущей строки
Интересные записи:
- Apache Airflow и XCom
- Как стать Data Engineer
- Курс Apache Airflow 2.0
- Amazon Redshift и Python
- Строим Data Lake на Amazon Web Services
- Введение в Apache Airflow
- Введение в Data Engineering: дата-пайплайны. Курс.
- TaskFlow API в Apache Airflow 2.0
Похожие публикации: