Сортировка внутри окна

В предыдущих статьях мы рассмотрели оконные функции и партиционирование в SQL. Теперь давайте перейдём к ещё одной важной особенности оконных функций — сортировке внутри окна.

Зачем нужна сортировка в окне?

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

Пример использования

Постановка задачи

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

Изначально мы имеем следующую выборку о бронированиях:

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

Разделение данных на партиции

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

MySQL
SELECT user_id,
       start_date,
       total AS reservation_price,
       SUM(total) OVER (
            PARTITION BY user_id
       ) AS total_expenses
FROM Reservations;

В результате выполненного запроса в колонке total_expenses вывелась общая сумма затраченных средств с разбивкой по пользователям. Но это не совсем то, что мы хотим: данные в таблице не упорядочены по дате и мы не видим как общие расходы росли со временем, мы видим только финальные расходы.

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

MySQL
SELECT user_id,
       start_date,
       total AS reservation_price,
       SUM(total) OVER (
           PARTITION BY user_id
           ORDER BY start_date
       ) AS cumulative_total
FROM Reservations;

Теперь мы получили то, что и хотели. Но что изменилось после добавления ORDER BY start_date?

1. Данные в рамках партиции стали отсортированы по дате начала бронирования
2. Изменился способ подсчёта общей суммы затраченных средств: теперь сумма в рамках партиции накапливается, а не выводится как финальная. Это связано с одной особенностью использования сортировки без явного указания ROWS|RANGE в выражении OVER. На этом остановимся поподробнее.

Особенности использования сортировки без указания рамок окна

Как мы помним полный синтаксис оконной функции выглядит следующим образом:

MySQL
SELECT <оконная_функция>(<поле_таблицы>)
OVER (
      [PARTITION BY <столбцы_для_разделения>]
      [ORDER BY <столбцы_для_сортировки>]
      [ROWS|RANGE <определение_диапазона_строк>]
)

Помимо блока с PARTITION BY и ORDER BY в нем присутствует опциональный блок ROWS|RANGE, на нем мы остановимся детально в следующей статье, но сейчас важнее, то для чего он нужен. Он позволяет указать рамки окна относительно текущей строки, которые будут использоваться для вычисления в оконной функции.

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

При использовании ORDER BY, если в блоке ROWS|RANGE ничего не указано, то в оконной функции автоматически применяется правило RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это означает, что окно будет начинаться с первой строки и заканчиваться текущей строкой.

То есть значения для колонки cumulative_total будут высчитываться следующим образом:

Подробнее об рамках окон и их определении в следующей статье.