В прошлый раз мы научились делить данные на окна. Но что, если нам нужно посчитать результат не по всей группе, а сумму с соседней строкой?
Когда я осваивал оконные функции, для меня самым сложным было разобраться не с 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