Оконные функции SQL за 7 минут

Наука о данных

Оконные функции SQL за 7 минут

Полное руководство по использованию оконных функций для комплексного анализа данных

Если вы находите оконные функции запутанными, эта статья для вас.

Мы рассмотрим, что это такое, когда и как их использовать с помощью трех реальных примеров.

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

Оконная функциявыполняет вычисления для набора строк таблицы , которые так или иначе связаны с текущей строкой.

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

Незаметно оконная функция может получать доступ не только к текущей строке результата запроса.

Ниже представлена визуализация операций оконных функций по сравнению с агрегатами.

Понятие о перегородках и окнах

Чтобы понять оконные функции, вам необходимо понять эти две концепции:

Разделы. Разделение — это способ разделить результирующий набор на более мелкие подмножества на основе одного или нескольких столбцов с помощью предложения PARTITION BY. Эти подмножества обрабатываются как отдельные группы для вычисления оконной функции.
Фрейм Windows: фрейм окна определяет диапазон строк в разделе, с которым работает оконная функция. Кадр указывается с помощью спецификации ROWS, RANGE или GROUPS в оконной функции, а порядок определяется с помощью ORDER BY

Ниже представлена визуализация оконной рамы в перегородке.

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

ROWS|RANGE|GROUPS BETWEEN win_start AND win_end

где win_start и win_end принимают одно из следующих значений:

UNBOUNDED PRECEDING (все строки до)
# PRECEDING (# строк до)
CURRENT ROW
# FOLLOWING (# строк после)
UNBOUNDED FOLLOWING (все строки после)

А вот визуализация трех типов спецификаций оконных рам.

Написание оконных функций

Попробуем написать оконную функцию.

Рассмотрим следующую таблицу данных о продажах:

Если бы я хотел рассчитать промежуточный общий объем продаж для продуктов A и B, давайте сначала определим следующее:

функция окна: SUM() по продажам
порядок: Дата (по возрастанию)
раздел: Продукт
рамка окна:весь раздел (без ограничений), так как мне нужен промежуточный итог по всем продажам.

Вот визуализированный запрос.

Вот как это написать на SQL:

SELECT 
  Date, 
  Product, 
  Sales, 
  SUM(Sales) OVER (
    PARTITION BY Product 
    ORDER BY 
      Date
  ) AS RunningTotal 
FROM 
  sales 
ORDER BY 
  Product;

Обратите внимание, если указано ORDER BY, то рамка по умолчанию RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Давайте посмотрим на синтаксис любой оконной функции.

Синтаксис оконной функции

SELECT <column(s)>,
  <window_function()> OVER (
    PARTITION BY <partition column(s)>
    ORDER BY <order column(s)>
    [ROWS|RANGE|GROUPS <frame specification>]
)
FROM <table_name>;

Вы видели оконные функции, используемые для вычисления промежуточного итога; но когда еще вы должны использовать их в целом?

Когда их использовать?

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

в наборе строк и по-прежнему сохранять результат на уровне строки.
на основе скользящего окна

Вот несколько примеров использования данных о продажах:

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

Теперь пришло время узнать обо всех оконных функциях.

Значение, ранги и агрегаты

Оконные функции можно разделить на следующие категории.

Назначение этих оконных функций должно быть очевидно из их названий.

Вот полезная таблица с кратким объяснением большинства из них.

Теперь давайте рассмотрим 3 реальных случая для ценности, ранжирования и агрегации.

Тематические исследования

Приведенные ниже тематические исследования взяты из DataLemur, бесплатной платформы для отработки вопросов на собеседовании по SQL.

Скользящие средние твитов (агрегированные)

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

Во-первых, давайте подсчитаем количество твитов для каждого пользователя за каждую дату; мы можем добиться этого с помощью GROUP BY для идентификатора пользователя и даты и COUNT для tweet_id

Мы оборачиваем этот запрос в CTE с именем tweet_cnt.

WITH tweet_cnt AS (
  SELECT
    user_id,
    tweet_date,
    COUNT(tweet_id) AS tweet_num
  FROM
    tweets
  GROUP BY
    user_id,
    tweet_date
)

Мы получим таблицу, как показано ниже.

Теперь нам нужна 3-дневная скользящая средняя; помните, что мы узнали об оконных рамах?

Здесь нам понадобится оконная рама.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

Для каждой даты мы получаем две последние даты и текущую дату и вычисляем среднее количество твитов.

Поскольку нам нужны скользящие средние значения для каждого пользователя, нам нужно разделить на user_id

Ниже приведен запрос.

SELECT
  user_id,
  tweet_date,
  ROUND(AVG(tweet_num) OVER(
    PARTITION BY user_id
    ORDER BY tweet_date ASC
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) as rolling_avg_3d
FROM tweet_cnt;

И вот желаемый результат.

Перейдем к следующему кейсу.

Повторные платежи (стоимость)

Иногда платежные операции повторяются из-за ошибок.

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

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

Мы можем использовать функцию LAG() для получения значений из предыдущей строки, по умолчанию значение LAG равно 1.

Какие столбцы нужно разделить? В данном случае merchant_id, credit_card_id и amount.

Ниже приведен запрос.

LAG(transaction_timestamp) OVER(
  PARTITION BY merchant_id, credit_card_id, amount 
ORDER BY transaction_timestamp)

Мы можем получить предыдущую строку, но нам все равно нужно вычислить разницу во времени в минутах.

Для этого мы можем вычесть текущую строку из предыдущей строки, а затем извлечь минуты с помощью функции ИЗВЛЕЧЕНИЕ.

Вот запрос для этого, завернутый в CTE.

WITH payments AS (
  SELECT 
    merchant_id, 
    EXTRACT(EPOCH FROM transaction_timestamp - 
      LAG(transaction_timestamp) OVER(
        PARTITION BY merchant_id, credit_card_id, amount 
        ORDER BY transaction_timestamp)
    )/60 AS minute_difference 
FROM transactions
)

Чтобы ответить на первоначальный вопрос, нам нужно отфильтровать строки, где time_diff ≤ 10.

SELECT
  COUNT(merchant_id)
FROM 
  payments
WHERE 
  time_diff <= 10

Три лучших зарплаты департамента (рейтинг)

Это сложный вопрос SQL от Leetcode.

Вам дана таблица сотрудников и отделов, и вы должны вычислить высокооплачиваемых сотрудников в каждом отделе.

Лидер с высоким доходом в отделе – это сотрудник, чья зарплата входит в три самые высокие уникальные зарплаты в этом отделе.

Вот таблицы Employee и Department.

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

Вот краткий обзор

ROW_NUMBER — уникальное целое число для каждой строки в разделе на основе порядка (1, 2, 3, 4)
RANK — аналогично row_number, но присваивает такое же значение галстукам (1, 2, 2, 4)
DENSE_RANK — аналогично rank, но не пропускает значения (1, 2, 2, 3)

Итак, какой здесь правильный?

Если вы угадали DENSE_RANK, вы попали прямо в точку! Мы не хотели бы пропускать какие-либо ранги, и мы также хотим рассмотреть ничьи в рангах.

Теперь давайте разберемся с разделом. В этом случае это будет DepartmentId, так как это относится к каждому отделу.

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

WITH department_ranking AS (
  SELECT
    Name AS Employee,
    Salary,
    DepartmentId,
    DENSE_RANK() OVER (
      PARTITION BY DepartmentId 
      ORDER BY Salary DESC
    ) AS rnk
  FROM Employee
)

Теперь, когда мы присвоили ранги каждому сотруднику по отделам, нам нужно добавить информацию об отделе, присоединившись к таблице отделов, а затем отфильтровать ранги, чтобы мы получили только 3 лучших уникальных.

SELECT
  d.Name AS Department,
  r.Employee,
  r.Salary
FROM department_ranking AS r
JOIN Department AS d
  ON r.DepartmentId = d.Id
  -- prefilter in join
  AND r.rnk <= 3 
ORDER BY 
  d.Name ASC, 
  r.Salary DESC;

Это все для этой статьи!

Надеюсь, вы лучше разобрались в оконных функциях.

Вот полезная шпаргалка от LearnSQL!

Не забудьте подписаться на публикацию bitgrit Data Science Publication, чтобы быть в курсе!

Следуйте за нами по ссылкам ниже 👇 для материалов по науке о данных!

Оконные функции SQL за 7 минут

Наука о данных