АГРЕГИРУЮЩИЕ ФУНКЦИИ

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

Звучит громоздко, но на примере будет все понятно.

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

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

with cte as (
    select
        club,
        avg(overall) as avg_overall
    from
        fifa_players_2018
    group by
        club
    order by
        avg_overall desc
)

select
    players.club,
    short_name,
    avg_overall,
    overall
from
    fifa_players_2018 as players
join 
    cte 
    on players.club = cte.club
order by
    overall desc

Теперь решим через оконную функцию:

select
    club,
    short_name,
    overall,
    avg(overall) over (partition by club) 
from
    fifa_players_2018
order by
    overall desc

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

Давайте рассмотрим, как работает оконная функция.

• AVG(overall) - считаем среднее рейтинга
• OVER - обязательная часть конструкции оконки, мы ее всегда пишем. Это означает, что мы обращаемся к окну. 
• (PARTITION BY club) - само окно, тут PARTITION означает группировку, после нее идет название столбца или столбцов. 
• Если у нас нет партиции (то есть группировки), то просто указываем пустое окно (). В таком случае оконка будет выглядеть так: AVG(overall) OVER ()

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

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

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