Руководство для начинающих по пониманию оконных функций SQL. Часть 2

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

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

Обзор контента

• Cumulative Sum
• Ranking Window Functions
• Use cases
• Offset window functions
• Key Takeaways

Совокупная сумма

Мы уже рассматривали примеры, когда выражение over() либо не имело параметров, либо имело разделение по параметрам. Теперь мы рассмотрим второй возможный параметр выражения over() — порядок по.

Давайте запросим идентификатор сотрудника, имя сотрудника, отдел, зарплату и сумму всех зарплат:

 select employee_id, employee_name, department, salary, sum(salary) over() from salary 

Теперь мы добавим порядок по параметру в выражение over() :

 select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary 

Думаю, нам нужно повнимательнее посмотреть, что здесь произошло:

1. Прежде всего, идентификатор сотрудника теперь отсортирован по убыванию.

   
   

2. В столбце, полученном в результате применения оконной функции, теперь отображается накопительная сумма.

   
   

Я думаю, вы знакомы с накопительной суммой. Суть его проста — накопительная сумма или промежуточный итог означает «сколько пока». Определение накопительной суммы — это сумма данной последовательности, которая увеличивается или становится больше при большем количестве сложений.

Вот что мы имеем в нашем примере: для сотрудника с наибольшим значением сотрудника_id зарплата равна 3700, а накопительная сумма также равна 3700. У второго сотрудника зарплата равна 1500, а совокупная сумма равна 5200. Третий сотрудник , при зарплате 2900, имеет совокупную сумму 8100 и так далее.

Параметр order by в выражении over() определяет порядок. В случае агрегирующих оконных функций он определяет порядок нарастающего итога.

В выражении over() можно указать атрибуты разделения и порядка.

 select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary 

В этом случае нарастающий итог будет рассчитываться по разделам.

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

Тем не менее, мы приведем пример расчета кумулятивного среднего — он сообщает нам среднее значение ряда значений до определенного момента:

 select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary 

Функции окна ранжирования

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

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

 select employee_id, employee_name, department, salary from salary 

Теперь мы добавляем еще один столбец с помощью оконной функции row_number() over() :

 select employee_id, employee_name, department, salary, row_number() over() from salary 

Оконная функция row_number() over() присвоила строкам номера, не меняя их порядка. Пока это не приносит нам особой пользы, не так ли?

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

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

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

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary

Давайте рассмотрим каждую функцию окна ранжирования:

1. Оконная функция row_number() over(порядок по убыванию зарплаты) ранжирует строки в порядке убывания зарплаты и присваивает номера строк. Обратите внимание, что у Энни и Тони одинаковая зарплата, но им присвоены разные номера.

   
   

2. Оконная функция Rank() over(порядок по убыванию зарплаты ) присваивает ранги в порядке убывания зарплаты. Он присваивает одинаковый ранг идентичным значениям, но следующее значение получает новый номер строки.

   
   

3. Оконная функция Densent_rank() over(порядок по убыванию зарплаты) присваивает ранги в порядке убывания зарплаты. Он присваивает одинаковый ранг одинаковым значениям.

   
   

4. Оконная функция процент_ранг() over(order по убыванию зарплаты) — это относительный (процентный) ранг текущей строки, рассчитываемый по формуле: (ранг — 1) / (общее количество строк в разделе — 1).

   
   

5. Оконная функция ntile(5) over(порядок по убыванию зарплаты) делит количество строк на 5 равных частей и присваивает каждой части номер. Количество частей указывается внутри функции ntile(5) .

   
   

Случаи использования

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

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Иногда может потребоваться нумерация строк в порядке убывания заработной платы внутри отделов (отделов). Это можно сделать, добавив раздел по атрибуту в выражение over():

 select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary 

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

 select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 

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

 select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 

Такого рода задачи очень распространены, особенно когда нужно отобразить определенное количество строк внутри разделов (групп) в порядке возрастания или убывания какого-либо атрибута. На практике я постоянно использую оконную функцию row_number() over() и, конечно же, Densent_rank() over() .

Функции окна смещения

Эти функции позволяют вам возвращать данные из других строк в зависимости от их расстояния от текущей строки. Чтобы сделать это более наглядным, давайте рассмотрим функции first_value(), Last_value() и nth_value().

 select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department 

 select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department

Функции first_value(salary) over(раздел по отделам) и Last_value(salary) over(раздел по отделам) отображают первое и последнее значения зарплаты в пределах раздела (отдела).

В свою очередь, функция nth_value(salary, 2) over(partition by Department) показывает второе значение зарплаты внутри раздела (отдела). Обратите внимание, что в nth_value() указывается дополнительный аргумент — номер строки внутри раздела. В нашем случае номер строки равен 2, поэтому функция отображает второе значение зарплаты.

Помимо вышеперечисленного, существуют также функции lag() и lead() . Функция lag() используется для получения значения из строки, предшествующей текущей строке. Функция lead() используется для получения значения из строки, следующей за текущей.

 select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 

Как видите, функция lag(salary) over(order by зарплата) сдвигает зарплаты на одну строку вниз, а функция lead(salary) over(order by зарплата) сдвигает зарплаты на одну строку вверх. Хотя эти функции очень похожи, я считаю более удобным использовать lag() .

 select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department 

Здесь lag() выполняет ту же функцию, что и раньше, но теперь конкретно внутри разделов (отделов).

Ключевые выводы

И, наконец, краткий обзор того, что мы рассмотрели сегодня:

• Накопленная сумма представляет собой промежуточную сумму последовательности, накапливающуюся при каждом последующем добавлении.

  
  

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

  
  

• К функциям окна смещения относятся first_value() , Last_value() и nth_value() , позволяющие извлекать данные из других строк на основе их расстояния от текущей строки. Не забывайте о функциях lag() и lead() . Функция lag() может быть полезна для получения значения из строки, предшествующей текущей строке, а функция lead() используется для получения значения из строки, следующей за текущей.

  
  

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