В этой статье разберемся с оконными функциями SQL и для большего понимания проведем параллель с реализациями аналогичных задач в Python.
В целом оконные функции выводят все те же групповые значения только несколько иным образом по сравнению со стандартными операциями группировки. Поэтому начнем с последних.
Рассмотрим их работу на примере набора данных о пассажирах Титаника, загруженного в базу данных ранее. Например, ниже представлены запросы, с помощью которых можно вывести среднее количество выживших в зависимости от класса и пола:
select AVG(survived) as avg_survived
from titanic_stat
group by pclass
select AVG(survived) as avg_survived
from titanic_stat
group by sex
В отличие от group by, который возвращает по одной записи на каждое сгруппированное значение, оконная функция выдает агрегированную статистику для каждой строки группы.
Оконные функции задаются вместе с инструкцией OVER. В ней определяется набор строк, которые будут использованы для агрегации (перечисляются после ключевой фразы PARTITION BY или ORDER BY). Инструкция OVER может многократно использоваться в одном SELECT, каждая со своим разделением и сортировкой.
Ниже представлен запрос, выводящий для каждого пассажира ряд столбцов и два агрегированных значения - средние от количества выживших для его группы по полу и классу:
select name, sex, pclass, age , survived,
AVG(survived) over (partition by sex) as sex_survived,
AVG(survived) over (partition by pclass) as class_survived
from titanic_stat
order by sex_survived desc, class_survived desc
В Python также имеются инструменты для группировки значений с аналогичными способами вывода результата. Это, прежде всего, библиотека Pandas (подробнее о группировке писал здесь):
Чтобы сделать вывод аналогичным оконному можно использовать метод apply: