в предыдущая статья мы говорили об оконных функциях — прекрасном инструменте для решения различных аналитических задач; они могут предоставить вам доступ к таким функциям, как расширенная аналитика и манипулирование данными, без необходимости писать сложные запросы. Прежде чем продолжить чтение, я бы рекомендовал начать с первой части, поскольку она поможет понять основную идею работы оконных функций в 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 Думаю, нам нужно повнимательнее посмотреть, что здесь произошло: Прежде всего, теперь отсортирован по убыванию. идентификатор сотрудника В столбце, полученном в результате применения оконной функции, теперь отображается накопительная сумма. Я думаю, вы знакомы с накопительной суммой. Суть его проста — накопительная сумма или промежуточный итог означает «сколько пока». Определение накопительной суммы — это сумма данной последовательности, которая увеличивается или становится больше при большем количестве сложений. Вот что мы имеем в нашем примере: для сотрудника с наибольшим значением зарплата равна 3700, а накопительная сумма также равна 3700. У второго сотрудника зарплата равна 1500, а совокупная сумма равна 5200. Третий сотрудник , при зарплате 2900, имеет совокупную сумму 8100 и так далее. сотрудника_id Параметр order by в выражении определяет порядок. В случае агрегирующих оконных функций он определяет порядок нарастающего итога. over() В выражении можно указать атрибуты разделения и порядка. over() select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary В этом случае нарастающий итог будет рассчитываться по разделам. Гульника! Если в выражении указаны оба атрибута, сначала всегда идет секция by, а затем . Например: . over() порядок по over(разделение по отделам, заказ по сотруднику_id) После того, как мы обсудили нарастающую сумму, нужно сказать, что, возможно, это единственный тип нарастающей суммы, который часто используется. Нарастающее среднее значение и совокупный подсчет, напротив, используются редко. Тем не менее, мы приведем пример расчета кумулятивного среднего — он сообщает нам среднее значение ряда значений до определенного момента: 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 Давайте рассмотрим каждую функцию окна ранжирования: Оконная функция ранжирует строки в порядке убывания зарплаты и присваивает номера строк. Обратите внимание, что у Энни и Тони одинаковая зарплата, но им присвоены разные номера. row_number() over(порядок по убыванию зарплаты) Оконная функция ) присваивает ранги в порядке убывания зарплаты. Он присваивает одинаковый ранг идентичным значениям, но следующее значение получает новый номер строки. Rank() over(порядок по убыванию зарплаты Оконная функция присваивает ранги в порядке убывания зарплаты. Он присваивает одинаковый ранг одинаковым значениям. Densent_rank() over(порядок по убыванию зарплаты) Оконная функция — это относительный (процентный) ранг текущей строки, рассчитываемый по формуле: (ранг — 1) / (общее количество строк в разделе — 1). процент_ранг() over(order по убыванию зарплаты) Оконная функция делит количество строк на 5 равных частей и присваивает каждой части номер. Количество частей указывается внутри функции . ntile(5) over(порядок по убыванию зарплаты) ntile(5) Гульника! В отличие от агрегатных функций, например sum(salary), функции ранжирования, например row_number(), не принимают столбец внутрь. Однако в функции 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 Гульника! Во всех трех оконных функциях это существенный чтобы указать раздел по параметру. Параметр order by не является обязательным, но указав его, вы можете изменить порядок строк внутри раздела. Например, в запросе ниже мы отсортировали по зарплате внутри раздела (отдела), и теперь first_value — это самая высокая зарплата в разделе. 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(раздел по отделам) В свою очередь, функция показывает второе значение зарплаты внутри раздела (отдела). Обратите внимание, что в указывается дополнительный аргумент — номер строки внутри раздела. В нашем случае номер строки равен 2, поэтому функция отображает второе значение зарплаты. nth_value(salary, 2) over(partition by Department) nth_value() Помимо вышеперечисленного, существуют также функции и . Функция используется для получения значения из строки, предшествующей текущей строке. Функция lead() используется для получения значения из строки, следующей за текущей. lag() lead() lag() 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() Гульника! Для этих функций обязательно указывать порядок по параметру в выражении over(). Вы также можете указать секционирование с помощью раздела, но это не обязательно. 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 и сделает вас более уверенным и быстрым в выполнении рутинных задач.
