Статьи
Управление данными

Пойми функции SQL Window раз и навсегда

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

Цель этой статьи — объяснить оконные функции в SQL шаг за шагом, в доступной форме, чтобы вам не пришлось полагаться только на заучивание синтаксиса.

Наш набор данных прост — шесть строк данных о доходах для двух регионов за 2023 год.

Оконные функции — это подгруппы

Если бы мы взяли этот набор данных и выполнили команду GROUP BY с суммированием доходов по каждому региону, то результат был бы очевиден, не так ли? Осталось бы только две строки — по одной для каждого региона — и сумма доходов для каждого региона.
Я хочу, чтобы вы воспринимали оконные функции очень похоже на это, но вместо сокращения количества строк агрегация будет выполняться "в фоновом режиме", а значения будут добавляться к нашим существующим строкам.

Сначала давайте рассмотрим пример:
SELECT
 id,
    date,
    region,
    revenue,
    SUM(revenue) OVER () as total_revenue
FROM
    sales
Обратите внимание, что у нас нет никакого GROUP BY, и наш набор данных остался нетронутым. Однако нам удалось получить сумму всех доходов. Прежде чем углубляться в то, как это работает, давайте быстро обсудим полный синтаксис, прежде чем начнем наращивать наши знания.

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

Синтаксис выглядит следующим образом:
SUM([some_column]) OVER (PARTITION BY [some_columns] ORDER BY [some_columns])
Разбирая каждую часть, у нас есть:

  • Агрегация или оконная функция: SUM, AVG, MAX, RANK, FIRST_VALUE
  • Ключевое слово OVER, которое указывает, что это оконная функция
  • Раздел PARTITION BY, который определяет группы
  • Раздел ORDER BY, который определяет, является ли функция выполняемой по порядку (мы рассмотрим это позже)

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

Постепенное построение нашего понимания

Переходя к чему-то действительно полезному, теперь мы применим группировку в нашей функции.
Первоначальный расчет останется, чтобы показать вам, что мы можем запускать более одной оконной функции одновременно, что означает возможность выполнения различных агрегаций в одном запросе без необходимости использования подзапросов.
SELECT
    id,
    date,
    region,
    revenue,
    SUM(revenue) OVER (PARTITION BY region) as region_total,
    SUM(revenue) OVER () as total_revenue
FROM sales
Как было сказано, мы используем PARTITION BY, чтобы определить группы (окна), которые будут использоваться нашей агрегационной функцией! Таким образом, сохраняя наш набор данных нетронутым, у нас есть:

  • Общий доход для каждого региона
  • Общий доход для всего набора данных

Мы также не ограничены одной группой. Подобно GROUP BY, мы можем разделить данные по региону и кварталу, например:
SELECT
    id,
    date,
    region,
    revenue,
    SUM(revenue) OVER (PARTITION BY 
          region, 
          date_trunc('quarter', date)
    ) AS region_quarterly_revenue
FROM sales
Как следует из приведенной выше таблицы, только две точки данных сгруппировались по региону и кварталу!

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

Переходим к ключевому слову ORDER BY. Оно определяет функцию скользящего окна. Вы наверняка хоть раз в жизни слышали о скользящей сумме (Running Sum). Если нет, стоит обратиться к примеру, чтобы все стало понятно.
SELECT
    id,
    date,
    region,
    revenue,
    SUM(revenue) OVER (ORDER BY id) as running_total
FROM sales
Здесь построчно суммируется доход со всеми предыдущими значениями. В данном случае вычисления выполняются в соответствии с порядком столбца id (хотя это мог быть и любой другой столбец).

Данный пример не особенно полезен, поскольку в нем суммируются данные по случайным месяцам и двум регионам. Но используя полученные знания, можно теперь вычислить суммарный доход по региону. Для этого применим скользящую сумму в каждой группе.
SELECT
    id,
    date,
    region,
    revenue,
    SUM(revenue) OVER (PARTITION BY region ORDER BY date) as running_total
FROM sales
Разберем, что здесь происходит:

  • Проходим по всем регионам месяц за месяцем и суммируем доходы.
  • Выполним вычисления для одного региона, переходим к следующему, начиная с нуля и снова двигаясь дальше по месяцам.

Интересно заметить, что при написании этих скользящих функций используется “контекст” других строк. Иными словами, для получения скользящей суммы в одной точке необходимо знать предыдущие значения для предыдущих строк. Это становится более очевидным при ручной выборке количества последующих/предыдущих строк, которые необходимо агрегировать.
SELECT
    id,
    date,
    region,
    revenue,
    SUM(revenue) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) 
    AS useless_sum
FROM
    sales
В этом запросе указывается, что для каждой строки необходимо выбрать одну строку позади и две строки впереди, чтобы получить сумму этого диапазона! Такая возможность, дающая полный контроль над группированием данных, позволяет чрезвычайно эффективно решать различные задачи.

Наконец, последнее, что стоит разобрать, прежде чем перейти к более сложному примеру, — это функция RANK. Ее часто включают в вопросы для собеседования. Логика, лежащая в ее основе, такая же, как и во всем, что мы видели до сих пор.
SELECT
    *,
    RANK() OVER (PARTITION BY region ORDER BY revenue DESC) as rank,
    RANK() OVER (ORDER BY revenue DESC) as overall_rank
FROM
    sales
ORDER BY region, revenue DESC
Как и раньше, используем ORDER BY, чтобы указать порядок, которого надо придерживаться, идя построчно, и PARTITION BY, чтобы указать подгруппы.

В первом столбце ранжируется каждая строка в рамках каждого региона, что означает, что у нас будет несколько “ранжированных” строк в наборе данных. Второй столбец — это ранги всех строк в наборе данных.

Заполнение пропущенных впереди данных

Такая проблема возникает время от времени, и для ее решения в контексте SQL требуется активное использование оконных функций. Для рассмотрения этого понятия будем использовать другой набор данных, содержащий временные метки и измерения температуры. Наша цель — заполнить строки, в которых отсутствуют измерения температуры, последним измеренным значением.
Вот что ожидается получить в итоге:
Стоит отметить, что при использовании Pandas эту проблему можно решить запуском df.ffill(). Однако при работе с SQL подобная задача становится немного сложнее.

Первый шаг к ее решению заключается в том, чтобы каким-то образом сгруппировать NULL-значения с предыдущим ненулевым значением. Важно понимать, что для этого потребуется скользящая функция. То есть это функция, которая будет “проходить строку за строкой”, определяя, когда встретится нулевое значение, а когда — ненулевое.

Решением является использование COUNT и, более конкретно, вычисления значений измерений температуры. В следующем запросе выполняются как обычные скользящие вычисления, так и вычисления значений температуры.
SELECT
    *,
    COUNT() OVER (ORDER BY timestamp) as normal_count,
    COUNT(temperature) OVER (ORDER BY timestamp) as group_count
from sensor
  • В первом случае подсчитывается каждая строка по возрастающей.
  • Во втором случае подсчитывается каждое значение температуры, не считая тех случаев, когда оно было NULL.

Колонка normal_count бесполезна для нас — это просто пример того, как выглядит скользящая функция COUNT. Однако второе вычисление, group_count, приближает нас к решению задачи!

Обратите внимание: этот способ подсчета гарантирует, что подсчитывается первое значение, стоящее непосредственно перед первым NULL, а затем, когда функция встречает NULL, ничего не происходит. Это обеспечивает “тегирование” каждого последующего NULL тем значением, которое было до прекращения измерения.

Чтобы двигаться дальше, скопируем первое тегированное значение во все остальные строки в той же группе. То есть для группы 2 все строки должны быть заполнены значением 15.0.

Как думаете, какая функция поможет это сделать? На данный вопрос есть не один ответ, но, надеюсь, хотя бы понятно, что сейчас мы рассматриваем простую оконную агрегацию с PARTITION BY.
SELECT
    *,
    FIRST_VALUE(temperature) OVER (PARTITION BY group_count) as filled_v1,
    MAX(temperature) OVER (PARTITION BY group_count) as filled_v2
FROM (
    SELECT
        *,
        COUNT(temperature) OVER (ORDER BY timestamp) as group_count
    from sensor
) as grouped
ORDER BY timestamp ASC
Чтобы добиться желаемого результата, можно использовать как FIRST_VALUE, так и MAX. Главное — получить первое ненулевое значение. Поскольку известно, что каждая группа содержит одно ненулевое значение и множество нулевых значений, обе эти функции работают!

Данный пример — отличный способ попрактиковаться в работе с оконными функциями. Если хотите решить похожую задачу, попробуйте добавить два датчика, а затем заполнить значения предыдущим показанием этого датчика. Что-то вроде этого:
Можете это сделать? Тут не используется ничего, чего вы не узнали из вышеизложенного.

Теперь вы знаете, как работают оконные функции в SQL, так что пора переходить к выводам!

Подведение итогов

Что вы узнали:
  • Ключевое слово OVER используется для написания оконных функций.
  • PARTITION BY используется для указания подгрупп (окон).
  • Если используется только ключевое слово OVER(), то окном будет весь набор данных.
  • ORDER BY используется, когда нужна скользящая функция, то есть для построчного вычисления.
  • Оконные функции полезны, когда требуется сгруппировать данные для выполнения агрегирования, и при этом надо сохранить датасет в первоначальном виде.
2024-10-06 08:00