Как в SQL настроить фокус окна?

В прошлый раз мы научились делить данные на окна. Но что, если нам нужно посчитать результат не по всей группе, а сумму с соседней строкой?

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

Инструкция ROWS позволяет ограничить строки в окне, указывая фиксированное количество строк, предшествующих или следующих за текущей.

RANGE, в отличие от ROWS, работает не с физическими строками, а с диапазоном значений ORDER BY. Поэтому несколько строк с одинаковым значением могут попадать в одно окно.

Обе инструкции ROWS и RANGE всегда используются вместе с ORDER BY.

Возьмем наш пример и посчитаем сумму по текущей и следующей строке внутри каждого дня:

SELECT 
  date
, medium
, conversions
, SUM(conversions) OVER(PARTITION BY date ORDER BY medium ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum 
FROM orders

Результат выполнения и логику запроса смотрите на картинке.

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

▪️UNBOUNDED PRECEDING - указывает, что окно начинается с первой строки группы;
▪️UNBOUNDED FOLLOWING - с помощью данной инструкции можно указать, что окно заканчивается на последней строке группы;
▪️CURRENT ROW - инструкция указывает, что окно начинается или заканчивается на текущей строке;
▪️BETWEEN «граница окна» AND «граница окна» - указывает нижнюю и верхнюю границу;
▪️«Значение» PRECEDING - определяет число строк перед текущей строкой (не допускается в RANGE);
▪️«Значение» FOLLOWING - определяет число строк после текущей строки (не допускается в RANGE).

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

Далее разберем самые полезные оконные функции.

#харды #sql