Что такое оконные функции в sql
Перейти к содержимому

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

  • автор:

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

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

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; 

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

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; 

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

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; 

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

RANK

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

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

  • 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 и другие), но об этом поговорим в другой раз.

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

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

Синтаксис

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-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. Накопительные итоги

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

      2 Май 2020 , Data engineering, 88748 просмотров, 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
      • Строим Data Lake на Amazon Web Services
      • Amazon Redshift и Python
      • Введение в Apache Airflow
      • Введение в Data Engineering: дата-пайплайны. Курс.
      • TaskFlow API в Apache Airflow 2.0

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

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