Оконные функции выполняют вычисления для набора записей, объединённых по какому-либо признаку. Такой набор называют окном. Вместе с текущей записью оконная функция обрабатывает остальные записи, которые входят в то же окно. Для каждой записи функция выводит одно значение. Этим оконная функция отличается от агрегирующей: и та и другая вычисляются для набора записей, но оконная функция не объединяет записи в одну, как агрегирующая, сохраняя независимость записей. Проще говоря, оконные функции возвращают столько же значений, сколько получили на вход.
Окна, по которым будет рассчитана оконная функция, можно задать внутри выражения OVER — его содержат все оконные функции. Это выражение определяет, как разделить записи, которые обработает функция.
Внутри выражения OVER находится оператор PARTITION BY. Он разделяет записи на группы, или разделы, в зависимости от значения в поле user_id. Записи с одинаковым user_id окажутся в одном окне. Для каждого из окон будет рассчитан результат оконной функции.
Задача 1
Напишите запрос, который выведет все поля таблицы tools_shop.orders и отдельным полем суммарную стоимость всех заказов.
SELECT * , SUM(total_amt) OVER() AS sum
FROM tools_shop.orders
Задача 2
Напишите запрос, который выведет все поля таблицы tools_shop.users и отдельным полем количество пользователей в этой таблице.
SELECT *, COUNT(user_id) OVER()
FROM tools_shop.users
PARTITION BY
Задача 1
Напишите запрос, который выведет все поля таблицы tools_shop.orders и отдельным полем суммарную стоимость заказов для каждого пользователя.
SELECT *, SUM(total_amt) OVER(PARTITION BY user_id)
FROM tools_shop.orders
Задача 2
Напишите запрос, который выведет все поля таблицы tools_shop.orders и отдельным полем суммарную стоимость оплаченных заказов за каждый месяц.
SELECT *,
SUM(total_amt) OVER (PARTITION BY DATE_TRUNC('month', paid_at)) AS monthly
FROM tools_shop.orders
ROW_NUMBER()
С помощью оконных функций можно ранжировать записи, то есть нумеровать их по определённому правилу. Для ранжирования в SQL используют функцию ROW_NUMBER(). Эта функция возвращает порядковый номер записи в окне.
Задача 1
Выведите все поля таблицы tools_shop.items, добавив поле с рангом записи.
SELECT *, ROW_NUMBER() OVER ()
FROM tools_shop.items
Задача 2
Выведите все поля таблицы tools_shop.orders, добавив поле с рангом записи.
SELECT *, ROW_NUMBER() OVER ()
FROM tools_shop.orders
ORDER BY
Оператор позволяет ранжировать записи. Ранжировать записи по возрастанию значений в поле или по убыванию. Для этого используется ключевое слово DESC. Если указать ASC, записи будут проранжированы по возрастанию — так же, как и по умолчанию.
Задача 1
Проранжируйте записи в таблице tools_shop.users по дате регистрации — от меньшей к большей. Напишите запрос, который выведет идентификатор пользователя с рангом 2021.
WITH dr AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY created_at) AS dr
FROM tools_shop.users)
SELECT user_id
FROM dr
WHERE dr = 2021;
Задача 2
Проранжируйте записи в таблице tools_shop.orders по дате оплаты заказа — от большей к меньшей. Напишите запрос, который выведет стоимость заказа с рангом 50.
WITH dt AS
(SELECT *,
ROW_NUMBER() OVER (ORDER BY paid_at DESC) AS dt
FROM tools_shop.orders)
SELECT 1431
FROM dt
WHERE dt = 50;
RANK() и DENSE_RANK()
Функции RANK() и DENSE_RANK() отличаются от ROW_NUMBER() логикой ранжирования повторяющихся значений. Функция RANK() присваивает одинаковым значениям одинаковый ранг. В отличие от функции RANK(), DENSE_RANK() не учитывает количество записей и назначает ранги последовательно.
Задача 1
Проранжируйте записи в таблице tools_shop.order_x_item в зависимости от значения item_id — от меньшего к большему. Записи с одинаковым item_id должны получить один ранг. Ранги можно указать непоследовательно.
SELECT *, RANK() OVER (ORDER BY item_id)
FROM tools_shop.order_x_item
Задача 2
Проранжируйте записи в таблице tools_shop.users в зависимости от значения в поле created_at — от большего к меньшему. Записи с одинаковым значением created_at должны получить один ранг. Ранги должны быть указаны последовательно.
SELECT *, DENSE_RANK() OVER (ORDER BY created_at DESC) FROM tools_shop.users
NTILE()
NTILE() — ещё одна функция ранжирования, которую используют в SQL. Она позволяет назначать записям фиксированное количество рангов — в зависимости от аргумента, который передают функции.
Задача 1
Разбейте заказы из таблицы tools_shop.orders на десять групп, отсортировав их по значению суммы заказа по возрастанию.
Выведите поля:
- идентификатор заказа;
- сумма заказа;
- ранг группы.
SELECT order_id, total_amt, NTILE(10) OVER (ORDER BY total_amt)
FROM tools_shop.orders
Задача 2
Разбейте пользователей в таблице tools_shop.users на пять групп так, чтобы в первую группу попали пользователи, которые недавно зарегистрировались.
Выгрузите поля:
- идентификатор пользователя;
- дата регистрации;
- ранг группы.
SELECT user_id, created_at, NTILE(5) OVER (ORDER BY created_at DESC)
FROM tools_shop.users
Сочетание операторов
Сочетая PARTITION BY и ORDER BY с функциями ранжирования, значения можно проранжировать внутри групп. Функцию NTILE() тоже можно использовать в сочетании с оператором PARTITION BY. В этом случае записи разделятся на группы не по всей таблице, а внутри каждой из групп, сформированных PARTITION BY. После формирования групп записи внутри них сортируются по полю, указанному в ORDER BY. Затем NTILE() выделяет подгруппы, разбивая записи на заданное количество частей и присваивая им ранги.
Задача 1
Выведите все поля таблицы tools_shop.orders и проранжируйте заказы для каждого клиента в зависимости от даты оплаты заказа — от меньшей к большей.
SELECT *, RANK() OVER (PARTITION BY user_id ORDER BY paid_at)
FROM tools_shop.orders
Задача 2
Выведите все поля таблицы tools_shop.events и проранжируйте события для каждого клиента в зависимости от его даты — от большей к меньшей.
SELECT *,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY CAST(event_time AS timestamp) DESC)
FROM tools_shop.events
Агрегирующие оконные функции
Агрегирующие оконные функции — это функции AVG, MIN, MAX, SUM и COUNT. Чтобы использовать агрегирующие функции в качестве оконных, после функции указывают выражение OVER и передают функции аргумент — поле, по которому нужно рассчитать значение. Также в выражении OVER можно указать операторы PARTITION BY и ORDER BY.
Задача 1
Рассчитайте общее количество заказов в таблице tools_shop.orders по дням. Выведите все поля таблицы и новое поле с количеством заказов.
SELECT *, COUNT(created_at) OVER (PARTITION BY created_at::date)
FROM tools_shop.orders
Задача 2
Рассчитайте общую выручку в таблице tools_shop.orders по месяцам. Выведите все поля таблицы и новое поле с суммой выручки.
SELECT *,
SUM(total_amt) OVER (PARTITION BY CAST(DATE_TRUNC('month', created_at) AS date))
FROM tools_shop.orders