оконные функции помогают увидеть данные красивыми способами
Что такое оконные функции?
SQL имеет множество функциональных возможностей для анализа данных. Одним из них являются оконные функции. Оконные функции можно использовать для создания новых столбцов — разделенных на существующий столбец, то есть на группу, или, другими словами, на окно. Новые столбцы обычно создаются для выполнения расчетов по ряду записей внутри окна — это окно может быть для команды, человека, типа продукта, географического местоположения — чего-то, что содержит множество записей. . Этот набор вычислений включает в себя — сумму, среднее значение, количество, номер строки, ранг, плотность_ранга, отставание, опережение. Здесь мы рассмотрим лишь некоторые из них, чтобы не переваривать слишком много информации.
Можете ли вы продемонстрировать несколько примеров?
Давайте возьмем пример, выберем набор данных и рассмотрим различные действия, которые мы можем делать с окнами. У нас есть таблица покупки.
table name: purchases id, puchaseDate, purchaseAmountDollars 12, 2022–01–01, 5 12, 2022–01–02, 10 12, 2022–01–05, 40
Таблица purchases содержит идентификатор человека, дату покупки для транзакций и сумму покупки.
категория 1: функции ранжирования
В качестве простого первого шага мы можем присвоить номер строки(индекс) каждой транзакции для каждого человека. Ниже приведен код для него.
SELECT ID, puchaseDate, purchaseAmount, row_number() over(partition by id order by purchaseDate asc) AS purchaseNumber FROM purchases
Посмотрите, как здесь используются row_number() и over(partition by ..). over( partition by ..) — это то, что вы будете использовать для разбиения данных, другими словами, при выборе группы — окна, над который, вы хотите, чтобы ваш расчет был сделан. В данном случае это вычисление row_number() — вы хотите ранжировать записи в каждом окне, то есть внутри каждого человека (столбец идентификатора).
The Result: id, puchaseDate, purchaseAmountDollars, purchaseNumber 12, 2022–01–01, 5, 1 12, 2022–01–02, 10, 2 12, 2022–01–05, 40, 3
В дополнение к функции row_number() SQL предлагает функции rank() и плотности_rank() — обе они присваивают ранг на основе значения столбца, указанного в предложении order by. row_number() этого не делал — он просто присваивал ранг — на основе позиции строки — он не смотрел ни на какое значение столбца.
Существует также функция процент_ранк(), которая генерирует процентный ранг, т.е. в процентах. Это полезно, когда вы хотите ответить на такой вопрос, как «дайте мне 10% лучших покупок». Существует ранг tile(), где вы можете указать количество корзин, которые вы хотите ранжировать, например: ntile(10) даст вам 10 корзин в ранге.
категория 2: получить предыдущее или следующее значение
Теперь давайте немного переключим передачу и попробуем что-нибудь посложнее.
Допустим, мы хотим найти наибольшее увеличение суммы покупки между двумя последовательными покупками для всех клиентов. Мы хотим выяснить, какое наибольшее увеличение суммы покупки произошло по сравнению с последовательными транзакциями?
Для этого нам нужно найти разницу между всеми последовательными транзакциями, а затем выбрать максимальную. Сначала мы используем over(partition by ..), чтобы сначала создать окно для каждого человека (столбец id). Затем обратите внимание, как мы используем order by для сортировки всех записей в каждом окне.
SELECT ID, purchaseAmount, LAG(purchaseAmount, 1, Null) OVER (PARTITION BY ID
ORDER BY date ASC) AS previousPurchaseAmount
FROM purchases
Теперь, чтобы получить предыдущую сумму покупки для каждой покупки, мы используем функцию lag(). Эта функция получает предыдущее значение столбца для каждой строки в окне. LAG(purchaseAmount, 1, Null) — говорит — дайте мне предыдущее значение столбца PurchaseAmount, если его нет, дайте мне Null. Обратите внимание, что здесь аргумент секунд равен «1», что означает «дай мне запись на 1 позицию позади». Если бы это было 2, это дало бы запись 2 позиции позади каждой строки.
SELECT MAX(increaseInPruchaseAmount) as maxIncrease FROM
(
SELECT (purchaseAmount - previousPurchaseAmount) as increaseInPruchaseAmount FROM
(
SELECT ID, purchaseAmount, LAG(purchaseAmount, 1, Null) OVER (PARTITION BY ID
ORDER BY date ASC) AS previousPurchaseAmount
FROM transactions
) withPreviousAmount
) withIncreaseAmount
RESULT:
maxIncrease
40
Точно так же вы можете использовать lead() вместо lag(). lead() возвращает вам следующее значение строки. Это полезно, когда вы хотите получить следующее значение в ряду строк.
категория 3: запуск агрегатов
Воспользуемся одной из агрегатных функций — sum(). sum() можно использовать для вычисления скользящей суммы значений столбца в окне. Найдем сумму всех покупок для человека.
SELECT ID, SUM(purchaseAmount) OVER (PARTITION BY ID
ORDER BY date ASC) AS runningSumPurchaseAmount
FROM purchases
The Result:
id, puchaseDate, runningSumPurchaseAmount
12, 2022–01–01, 5
12, 2022–01–02, 15
12, 2022–01–05, 40, 3, Y
Точно так же вы можете использовать скользящее среднее — avg() или текущее количество — count().
Заключение и мысли на прощание
Выше приведены некоторые способы использования концепции Windows и ее функций в SQL. Будут ситуации, когда они могут вам понадобиться. Хороший способ запомнить их — это визуализировать их и запомнить несколько примеров использования.
